Files
pages/internal/store/user_space_postgres.go
2026-11-15 05:47:14 +09:00

70 lines
4.6 KiB
Go

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}})
}