cayce-ops/deploy/roles.sql

-- cayce-ops — least-privilege per-daemon roles (audit round 2, point 3)
--
-- Apply with psql and MANDATORY password variables; the script refuses to run
-- without them (no literal credentials):
--
--   psql -h 127.0.0.1 -U postgres -d cayce_ops -f deploy/roles.sql \
--     -v scanner_password='…' -v executor_password='…' \
--     -v maild_password='…'    -v publisher_password='…' \
--     -v monitor_password='…'  -v approver_password='…'
--
-- Model:
--   cayce_scanner   : INSERT proposals only (+ INSERT audit_log, daemon_runs)
--   cayce_executor  : UPDATE proposals WITHOUT approved/signature columns
--   cayce_maild     : UPDATE mail_messages WITHOUT approved/signature columns
--   cayce_publisher : INSERT content_items, UPDATE WITHOUT approved/signature
--   cayce_monitor   : SELECT everything (+ INSERT audit_log for anchors)
--   cayce_approver  : THE ONLY role that can write status='approved',
--                     approved_hash, operator_signature, approved_by/at
--
-- A BEFORE trigger rejects status='approved' writes from any role except
-- cayce_approver — column grants alone cannot express that condition.
--
-- NOTE: do NOT apply on production yourself; the operator does.

\set ON_ERROR_STOP on

\if :{?scanner_password}
\else
\echo 'ERROR: missing variables. Pass -v scanner_password=… executor_password=… maild_password=… publisher_password=… monitor_password=… approver_password=…'
SELECT * FROM _roles_sql_missing_required_password_variables;
\endif
\if :{?executor_password}
\else
\echo 'ERROR: missing executor_password'
SELECT * FROM _roles_sql_missing_required_password_variables;
\endif
\if :{?maild_password}
\else
\echo 'ERROR: missing maild_password'
SELECT * FROM _roles_sql_missing_required_password_variables;
\endif
\if :{?publisher_password}
\else
\echo 'ERROR: missing publisher_password'
SELECT * FROM _roles_sql_missing_required_password_variables;
\endif
\if :{?monitor_password}
\else
\echo 'ERROR: missing monitor_password'
SELECT * FROM _roles_sql_missing_required_password_variables;
\endif
\if :{?approver_password}
\else
\echo 'ERROR: missing approver_password'
SELECT * FROM _roles_sql_missing_required_password_variables;
\endif

-- idempotency: detach old incarnations before dropping
DROP FUNCTION IF EXISTS cayce_ops_approval_gate() CASCADE;
REVOKE ALL ON SCHEMA public FROM
  cayce_scanner, cayce_executor, cayce_maild, cayce_publisher, cayce_monitor, cayce_approver;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM
  cayce_scanner, cayce_executor, cayce_maild, cayce_publisher, cayce_monitor, cayce_approver;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM
  cayce_scanner, cayce_executor, cayce_maild, cayce_publisher, cayce_monitor, cayce_approver;

DROP ROLE IF EXISTS cayce_scanner;
DROP ROLE IF EXISTS cayce_executor;
DROP ROLE IF EXISTS cayce_maild;
DROP ROLE IF EXISTS cayce_publisher;
DROP ROLE IF EXISTS cayce_monitor;
DROP ROLE IF EXISTS cayce_approver;

CREATE ROLE cayce_scanner   LOGIN PASSWORD :'scanner_password';
CREATE ROLE cayce_executor  LOGIN PASSWORD :'executor_password';
CREATE ROLE cayce_maild     LOGIN PASSWORD :'maild_password';
CREATE ROLE cayce_publisher LOGIN PASSWORD :'publisher_password';
CREATE ROLE cayce_monitor   LOGIN PASSWORD :'monitor_password';
CREATE ROLE cayce_approver  LOGIN PASSWORD :'approver_password';

GRANT USAGE ON SCHEMA public TO cayce_scanner, cayce_executor, cayce_maild,
  cayce_publisher, cayce_monitor, cayce_approver;

-- sequences everybody needs
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public
  TO cayce_scanner, cayce_executor, cayce_maild, cayce_publisher, cayce_monitor, cayce_approver;

-- common reads
GRANT SELECT ON proposals, audit_log, mail_messages, content_items, daemon_runs
  TO cayce_scanner, cayce_executor, cayce_maild, cayce_publisher, cayce_monitor, cayce_approver;

-- every daemon appends to audit_log and daemon_runs (never UPDATE/DELETE)
GRANT INSERT ON audit_log
  TO cayce_scanner, cayce_executor, cayce_maild, cayce_publisher, cayce_monitor, cayce_approver;
GRANT INSERT ON daemon_runs
  TO cayce_scanner, cayce_executor, cayce_maild, cayce_publisher, cayce_monitor;

-- scanner: propose only
GRANT INSERT ON proposals TO cayce_scanner;

-- executor: transition proposals WITHOUT touching approval columns
GRANT UPDATE (status, attempts, updated_at) ON proposals TO cayce_executor;
-- (there is no error column; adjust if added) — see trigger below for status

-- maild: classify + drafts + send bookkeeping WITHOUT approval columns
GRANT INSERT, UPDATE (classification, confidence, draft_status, draft_reply,
  in_reply_to, message_id, from_addr, to_addr, subject, body, processed_at)
  ON mail_messages TO cayce_maild;

-- publisher: plan drafts and record publishes WITHOUT approval columns
GRANT INSERT ON content_items TO cayce_publisher;
GRANT UPDATE (status, attempts, error, media_path, scheduled_for, published_at, post_ref)
  ON content_items TO cayce_publisher;

-- approver: the ONLY writer of approvals and signatures
GRANT SELECT, UPDATE ON proposals TO cayce_approver;
GRANT SELECT, UPDATE ON mail_messages TO cayce_approver;
GRANT SELECT, UPDATE ON content_items TO cayce_approver;
GRANT INSERT ON audit_log TO cayce_approver;

-- ---------------------------------------------------------------------------
-- approval gate: status/draft_status='approved' and signature columns may
-- only be written by cayce_approver (one trigger per table — column sets differ)
-- ---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION cayce_ops_gate_proposals() RETURNS trigger AS $$
BEGIN
  IF current_user IN ('cayce_approver', 'postgres') THEN
    RETURN NEW;
  END IF;
  IF NEW.status::text = 'APPROVED' THEN
    RAISE EXCEPTION 'cayce-ops: only cayce_approver may set proposals.status=approved (user=%)', current_user;
  END IF;
  IF TG_OP = 'UPDATE' AND NEW.operator_signature IS DISTINCT FROM OLD.operator_signature THEN
    RAISE EXCEPTION 'cayce-ops: only cayce_approver may write operator_signature (user=%)', current_user;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION cayce_ops_gate_mail() RETURNS trigger AS $$
BEGIN
  IF current_user IN ('cayce_approver', 'postgres') THEN
    RETURN NEW;
  END IF;
  IF NEW.draft_status::text = 'APPROVED' THEN
    RAISE EXCEPTION 'cayce-ops: only cayce_approver may set mail draft_status=approved (user=%)', current_user;
  END IF;
  IF TG_OP = 'UPDATE' AND NEW.operator_signature IS DISTINCT FROM OLD.operator_signature THEN
    RAISE EXCEPTION 'cayce-ops: only cayce_approver may write operator_signature (user=%)', current_user;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION cayce_ops_gate_content() RETURNS trigger AS $$
BEGIN
  IF current_user IN ('cayce_approver', 'postgres') THEN
    RETURN NEW;
  END IF;
  IF NEW.status::text = 'APPROVED' THEN
    RAISE EXCEPTION 'cayce-ops: only cayce_approver may set content status=approved (user=%)', current_user;
  END IF;
  IF TG_OP = 'UPDATE' AND NEW.operator_signature IS DISTINCT FROM OLD.operator_signature THEN
    RAISE EXCEPTION 'cayce-ops: only cayce_approver may write operator_signature (user=%)', current_user;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS proposals_approval_gate ON proposals;
CREATE TRIGGER proposals_approval_gate BEFORE INSERT OR UPDATE ON proposals
  FOR EACH ROW EXECUTE FUNCTION cayce_ops_gate_proposals();

DROP TRIGGER IF EXISTS mail_approval_gate ON mail_messages;
CREATE TRIGGER mail_approval_gate BEFORE INSERT OR UPDATE ON mail_messages
  FOR EACH ROW EXECUTE FUNCTION cayce_ops_gate_mail();

DROP TRIGGER IF EXISTS content_approval_gate ON content_items;
CREATE TRIGGER content_approval_gate BEFORE INSERT OR UPDATE ON content_items
  FOR EACH ROW EXECUTE FUNCTION cayce_ops_gate_content();