47 lines
1.8 KiB
PL/PgSQL
47 lines
1.8 KiB
PL/PgSQL
-- SEC-3: make the audit log genuinely append-only.
|
|
--
|
|
-- The application must be able to INSERT into audit_log and SELECT from it, and
|
|
-- nothing else. Enforcing this at the database role level means that even an
|
|
-- application bug — or an attacker with the application's credentials — cannot
|
|
-- rewrite history.
|
|
--
|
|
-- Run this ONCE after the first migration, as a superuser:
|
|
-- psql -U postgres -d pharma -f scripts/harden_audit.sql
|
|
--
|
|
-- Assumes the application connects as the role `pharma_app`. Create that role
|
|
-- separately from the migration role: migrations need DDL rights, the running
|
|
-- application does not.
|
|
|
|
-- 1. The application role (create it if it does not exist).
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'pharma_app') THEN
|
|
CREATE ROLE pharma_app LOGIN PASSWORD 'change-me-before-production';
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
-- 2. Normal rights on every other table.
|
|
GRANT USAGE ON SCHEMA public TO pharma_app;
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO pharma_app;
|
|
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO pharma_app;
|
|
|
|
-- 3. audit_log is the exception: insert and read only.
|
|
REVOKE UPDATE, DELETE, TRUNCATE ON audit_log FROM pharma_app;
|
|
|
|
-- 4. Same policy for tables created by future migrations.
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO pharma_app;
|
|
|
|
-- 5. Belt and braces: a trigger, so the rule survives a mis-granted privilege.
|
|
CREATE OR REPLACE FUNCTION audit_log_is_append_only() RETURNS trigger AS $$
|
|
BEGIN
|
|
RAISE EXCEPTION 'audit_log is append-only: % is not permitted', TG_OP;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS trg_audit_log_append_only ON audit_log;
|
|
CREATE TRIGGER trg_audit_log_append_only
|
|
BEFORE UPDATE OR DELETE ON audit_log
|
|
FOR EACH ROW EXECUTE FUNCTION audit_log_is_append_only();
|