70 lines
4.6 KiB
Go
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}})
|
|
}
|