package store import ( "context" "pageswiki/internal/db" ) // User-space migration follows publication: its policies share the navigation // generation and administration lock. No runtime trigger creation or optional // adoption of incomplete schemas is permitted on PostgreSQL. func (s *Store) ensurePostgreSQLUserSpace(ctx context.Context) error { if err := s.ensurePostgreSQLPublication(ctx); err != nil { return err } statements := []string{ `CREATE TABLE user_space_policies( domain_uuid TEXT COLLATE "C" NOT NULL,principal_uuid TEXT COLLATE "C" NOT NULL, profile_visibility TEXT NOT NULL CHECK(profile_visibility IN ('private','domain','public')), avatar_visibility TEXT NOT NULL CHECK(avatar_visibility IN ('private','domain','public')), root_visibility TEXT NOT NULL CHECK(root_visibility IN ('private','domain','public')), descendant_visibility TEXT NOT NULL CHECK(descendant_visibility IN ('private','domain','public')), version BIGINT NOT NULL,actor_uuid TEXT NOT NULL,updated_at_ms BIGINT NOT NULL, PRIMARY KEY(domain_uuid,principal_uuid))`, `CREATE TABLE user_space_path_policies( domain_uuid TEXT COLLATE "C" NOT NULL,principal_uuid TEXT COLLATE "C" NOT NULL,path TEXT COLLATE "C" NOT NULL, visibility TEXT NOT NULL CHECK(visibility IN ('private','domain')),actor_uuid TEXT NOT NULL,updated_at_ms BIGINT NOT NULL, PRIMARY KEY(domain_uuid,principal_uuid,path))`, `CREATE INDEX idx_user_space_path_policy ON user_space_path_policies(domain_uuid,path,principal_uuid)`, `CREATE TABLE user_space_policy_events(id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, domain_uuid TEXT COLLATE "C" NOT NULL,principal_uuid TEXT COLLATE "C" NOT NULL, actor_uuid TEXT NOT NULL,before_json TEXT NOT NULL,after_json TEXT NOT NULL,created_at_ms BIGINT NOT NULL)`, `CREATE INDEX idx_user_space_policy_events ON user_space_policy_events(domain_uuid,principal_uuid,id DESC)`, `CREATE FUNCTION pages_user_space_path_guard() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN PERFORM pg_advisory_xact_lock(1885431653,1853058931); IF EXISTS(SELECT 1 FROM user_space_path_policies usp WHERE usp.domain_uuid=OLD.domain_uuid AND usp.path=OLD.path AND NOT EXISTS(SELECT 1 FROM principal_identities i WHERE i.principal_uuid=usp.principal_uuid AND i.provider_type='password' AND NEW.domain_uuid=OLD.domain_uuid AND left(NEW.path,length('/u/'||i.subject||'/'))='/u/'||i.subject||'/')) THEN RAISE EXCEPTION USING ERRCODE='23514',MESSAGE='user_space_policy_scope'; END IF; RETURN NEW; END $$`, `CREATE TRIGGER user_space_path_policy_move BEFORE UPDATE OF path,domain_uuid ON paths FOR EACH ROW EXECUTE FUNCTION pages_user_space_path_guard()`, `CREATE FUNCTION pages_user_space_path_changed() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF TG_OP='DELETE' THEN DELETE FROM user_space_path_policies WHERE domain_uuid=OLD.domain_uuid AND path=OLD.path; ELSE UPDATE user_space_path_policies SET path=NEW.path WHERE domain_uuid=OLD.domain_uuid AND path=OLD.path; END IF; RETURN NULL; END $$`, `CREATE TRIGGER user_space_path_policy_rename AFTER UPDATE OF path ON paths FOR EACH ROW EXECUTE FUNCTION pages_user_space_path_changed()`, `CREATE TRIGGER user_space_path_policy_delete AFTER DELETE ON paths FOR EACH ROW EXECUTE FUNCTION pages_user_space_path_changed()`, } assertions := []string{ `SELECT domain_uuid,principal_uuid,profile_visibility,avatar_visibility,root_visibility,descendant_visibility,version,actor_uuid,updated_at_ms FROM user_space_policies WHERE 1=0`, `SELECT domain_uuid,principal_uuid,path,visibility,actor_uuid,updated_at_ms FROM user_space_path_policies WHERE 1=0`, `SELECT id,domain_uuid,principal_uuid,actor_uuid,before_json,after_json,created_at_ms FROM user_space_policy_events WHERE 1=0`, } for _, table := range []string{"user_space_policies", "user_space_path_policies"} { statements = append(statements, `CREATE TRIGGER navigation_`+table+` AFTER INSERT OR UPDATE OR DELETE ON `+table+` FOR EACH STATEMENT EXECUTE FUNCTION pages_navigation_changed()`) assertions = append(assertions, `SELECT 1 WHERE NOT EXISTS(SELECT 1 FROM pg_trigger WHERE tgrelid='`+table+`'::regclass AND tgname='navigation_`+table+`' AND tgenabled='O' AND NOT tgisinternal)`) } for _, name := range []string{"user_space_path_policy_move", "user_space_path_policy_rename", "user_space_path_policy_delete"} { assertions = append(assertions, `SELECT 1 WHERE NOT EXISTS(SELECT 1 FROM pg_trigger WHERE tgrelid='paths'::regclass AND tgname='`+name+`' AND tgenabled='O' AND NOT tgisinternal)`) } return db.ApplyPostgreSQLMigrations(ctx, s.DB, "store-user-space", []db.PostgreSQLMigration{{Version: 1, Name: "user-space policy and path fencing", Statements: statements, Assertions: assertions}}) }