64 lines
2.3 KiB
PL/PgSQL
64 lines
2.3 KiB
PL/PgSQL
 
|
-- 0001_base: settings, append-only audit_events, ps_notify, grants.
|
||
|
|
-- Runs as ps_migrate. Roles ps_migrate and ps_app exist already (initdb or --bootstrap-roles).
|
||
|
|
-- schema_migrations is created by the runner, not here.
|
||
|
|
|
||
|
|
GRANT USAGE ON SCHEMA public TO ps_app;
|
||
|
|
GRANT SELECT ON schema_migrations TO ps_app;
|
||
|
|
|
||
|
|
-- Everything ps_migrate creates from here on is usable by ps_app; append-only tables revoke below.
|
||
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE ps_migrate IN SCHEMA public
|
||
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ps_app;
|
||
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE ps_migrate IN SCHEMA public
|
||
|
|
GRANT USAGE, SELECT ON SEQUENCES TO ps_app;
|
||
|
|
|
||
|
|
CREATE TABLE settings (
|
||
|
|
key text PRIMARY KEY,
|
||
|
|
value jsonb NOT NULL,
|
||
|
|
version int NOT NULL DEFAULT 1,
|
||
|
|
updated_at timestamptz,
|
||
|
|
updated_by uuid
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE audit_events (
|
||
|
|
id bigserial PRIMARY KEY,
|
||
|
|
at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
prev_hash bytea,
|
||
|
|
row_hash bytea,
|
||
|
|
user_id uuid,
|
||
|
|
session_id uuid,
|
||
|
|
role_snapshot text,
|
||
|
|
action text NOT NULL,
|
||
|
|
subject_type text,
|
||
|
|
subject_id text,
|
||
|
|
packet_id bigint,
|
||
|
|
detail jsonb,
|
||
|
|
it_reason text,
|
||
|
|
ip inet
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX audit_events_at_brin ON audit_events USING brin (at);
|
||
|
|
|
||
|
|
CREATE FUNCTION ps_append_only() RETURNS trigger LANGUAGE plpgsql AS $$
|
||
|
|
BEGIN
|
||
|
|
RAISE EXCEPTION '% is append-only (% refused)', TG_TABLE_NAME, TG_OP
|
||
|
|
USING ERRCODE = 'insufficient_privilege';
|
||
|
|
END;
|
||
|
|
$$;
|
||
|
|
|
||
|
|
CREATE TRIGGER audit_events_append_only
|
||
|
|
BEFORE UPDATE OR DELETE ON audit_events
|
||
|
|
FOR EACH ROW EXECUTE FUNCTION ps_append_only();
|
||
|
|
CREATE TRIGGER audit_events_no_truncate
|
||
|
|
BEFORE TRUNCATE ON audit_events
|
||
|
|
FOR EACH STATEMENT EXECUTE FUNCTION ps_append_only();
|
||
|
|
|
||
|
|
-- Explicit, after the default privileges: the app may only read and append audit rows.
|
||
|
|
REVOKE UPDATE, DELETE, TRUNCATE ON audit_events FROM ps_app;
|
||
|
|
GRANT SELECT, INSERT ON audit_events TO ps_app;
|
||
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON settings TO ps_app;
|
||
|
|
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ps_app;
|
||
|
|
|
||
|
|
CREATE FUNCTION ps_notify(channel text, payload text) RETURNS void
|
||
|
|
LANGUAGE sql AS $$ SELECT pg_notify(channel, payload) $$;
|
||
|
|
GRANT EXECUTE ON FUNCTION ps_notify(text, text) TO ps_app;
|