127 lines
6.3 KiB
SQL
127 lines
6.3 KiB
SQL
DO $roles$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_roles WHERE rolname = 'thoth_sessions_runtime') THEN
|
|
CREATE ROLE thoth_sessions_runtime NOLOGIN NOBYPASSRLS NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_roles WHERE rolname = 'thoth_sessions_migrator') THEN
|
|
CREATE ROLE thoth_sessions_migrator NOLOGIN NOBYPASSRLS NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
|
|
END IF;
|
|
END
|
|
$roles$;
|
|
|
|
ALTER ROLE thoth_sessions_runtime NOLOGIN NOBYPASSRLS NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
|
|
ALTER ROLE thoth_sessions_migrator NOLOGIN NOBYPASSRLS NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
|
|
|
|
REVOKE ALL ON SCHEMA thoth_sessions FROM PUBLIC;
|
|
REVOKE ALL ON ALL TABLES IN SCHEMA thoth_sessions FROM PUBLIC;
|
|
REVOKE ALL ON ALL SEQUENCES IN SCHEMA thoth_sessions FROM PUBLIC;
|
|
REVOKE ALL ON SCHEMA thoth_sessions FROM thoth_sessions_runtime, thoth_sessions_migrator;
|
|
REVOKE ALL ON ALL TABLES IN SCHEMA thoth_sessions FROM thoth_sessions_runtime, thoth_sessions_migrator;
|
|
REVOKE ALL ON ALL SEQUENCES IN SCHEMA thoth_sessions FROM thoth_sessions_runtime, thoth_sessions_migrator;
|
|
|
|
GRANT USAGE ON SCHEMA thoth_sessions TO thoth_sessions_runtime;
|
|
GRANT SELECT, INSERT, UPDATE ON thoth_sessions.principals TO thoth_sessions_runtime;
|
|
GRANT SELECT, INSERT, UPDATE ON thoth_sessions.principal_preferences TO thoth_sessions_runtime;
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON thoth_sessions.sessions TO thoth_sessions_runtime;
|
|
GRANT SELECT, INSERT, UPDATE ON thoth_sessions.session_artifacts TO thoth_sessions_runtime;
|
|
GRANT SELECT, INSERT ON thoth_sessions.review_decisions TO thoth_sessions_runtime;
|
|
GRANT SELECT, INSERT ON thoth_sessions.audit_log TO thoth_sessions_runtime;
|
|
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA thoth_sessions TO thoth_sessions_runtime;
|
|
|
|
ALTER TABLE thoth_sessions.principals ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE thoth_sessions.principals FORCE ROW LEVEL SECURITY;
|
|
ALTER TABLE thoth_sessions.principal_preferences ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE thoth_sessions.principal_preferences FORCE ROW LEVEL SECURITY;
|
|
ALTER TABLE thoth_sessions.sessions ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE thoth_sessions.sessions FORCE ROW LEVEL SECURITY;
|
|
ALTER TABLE thoth_sessions.session_artifacts ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE thoth_sessions.session_artifacts FORCE ROW LEVEL SECURITY;
|
|
ALTER TABLE thoth_sessions.review_decisions ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE thoth_sessions.review_decisions FORCE ROW LEVEL SECURITY;
|
|
ALTER TABLE thoth_sessions.audit_log ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE thoth_sessions.audit_log FORCE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY principals_owner_or_admin ON thoth_sessions.principals
|
|
FOR ALL
|
|
USING (
|
|
pg_catalog.current_setting('thoth_sessions.is_admin', true) = 'true'
|
|
OR (issuer = pg_catalog.current_setting('thoth_sessions.actor_issuer', true)
|
|
AND subject = pg_catalog.current_setting('thoth_sessions.actor_subject', true))
|
|
)
|
|
WITH CHECK (
|
|
pg_catalog.current_setting('thoth_sessions.is_admin', true) = 'true'
|
|
OR (issuer = pg_catalog.current_setting('thoth_sessions.actor_issuer', true)
|
|
AND subject = pg_catalog.current_setting('thoth_sessions.actor_subject', true))
|
|
);
|
|
|
|
CREATE POLICY preferences_owner_or_admin ON thoth_sessions.principal_preferences
|
|
FOR ALL
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM thoth_sessions.principals p
|
|
WHERE p.id = principal_preferences.principal_id
|
|
AND (pg_catalog.current_setting('thoth_sessions.is_admin', true) = 'true'
|
|
OR (p.issuer = pg_catalog.current_setting('thoth_sessions.actor_issuer', true)
|
|
AND p.subject = pg_catalog.current_setting('thoth_sessions.actor_subject', true)))
|
|
)
|
|
)
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM thoth_sessions.principals p
|
|
WHERE p.id = principal_preferences.principal_id
|
|
AND (pg_catalog.current_setting('thoth_sessions.is_admin', true) = 'true'
|
|
OR (p.issuer = pg_catalog.current_setting('thoth_sessions.actor_issuer', true)
|
|
AND p.subject = pg_catalog.current_setting('thoth_sessions.actor_subject', true)))
|
|
)
|
|
);
|
|
|
|
CREATE POLICY sessions_owner_or_admin ON thoth_sessions.sessions
|
|
FOR ALL
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM thoth_sessions.principals p
|
|
WHERE p.id = sessions.principal_id
|
|
AND (pg_catalog.current_setting('thoth_sessions.is_admin', true) = 'true'
|
|
OR (p.issuer = pg_catalog.current_setting('thoth_sessions.actor_issuer', true)
|
|
AND p.subject = pg_catalog.current_setting('thoth_sessions.actor_subject', true)))
|
|
)
|
|
)
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM thoth_sessions.principals p
|
|
WHERE p.id = sessions.principal_id
|
|
AND (pg_catalog.current_setting('thoth_sessions.is_admin', true) = 'true'
|
|
OR (p.issuer = pg_catalog.current_setting('thoth_sessions.actor_issuer', true)
|
|
AND p.subject = pg_catalog.current_setting('thoth_sessions.actor_subject', true)))
|
|
)
|
|
);
|
|
|
|
CREATE POLICY artifacts_owner_or_admin ON thoth_sessions.session_artifacts
|
|
FOR ALL
|
|
USING (
|
|
EXISTS (SELECT 1 FROM thoth_sessions.sessions s WHERE s.id = session_artifacts.session_id)
|
|
)
|
|
WITH CHECK (
|
|
EXISTS (SELECT 1 FROM thoth_sessions.sessions s WHERE s.id = session_artifacts.session_id)
|
|
);
|
|
|
|
CREATE POLICY decisions_owner_or_admin ON thoth_sessions.review_decisions
|
|
FOR ALL
|
|
USING (
|
|
EXISTS (SELECT 1 FROM thoth_sessions.sessions s WHERE s.id = review_decisions.session_id)
|
|
)
|
|
WITH CHECK (
|
|
EXISTS (SELECT 1 FROM thoth_sessions.sessions s WHERE s.id = review_decisions.session_id)
|
|
);
|
|
|
|
CREATE POLICY audit_admin_read ON thoth_sessions.audit_log
|
|
FOR SELECT
|
|
USING (pg_catalog.current_setting('thoth_sessions.is_admin', true) = 'true');
|
|
CREATE POLICY audit_actor_write ON thoth_sessions.audit_log
|
|
FOR INSERT
|
|
WITH CHECK (
|
|
pg_catalog.current_setting('thoth_sessions.is_admin', true) = 'true'
|
|
OR (actor_issuer = pg_catalog.current_setting('thoth_sessions.actor_issuer', true)
|
|
AND actor_subject = pg_catalog.current_setting('thoth_sessions.actor_subject', true))
|
|
);
|