70 lines
2.3 KiB
PL/PgSQL
70 lines
2.3 KiB
PL/PgSQL
-- AwoooP RLS Canary Wave 1.3: outbound message evidence table
|
|
-- Date: 2026-05-12
|
|
--
|
|
-- Scope:
|
|
-- - awooop_outbound_message
|
|
--
|
|
-- Why this table:
|
|
-- Latest production evidence before staging: all rows are project_id='awoooi'
|
|
-- and all rows have send_status='sent'. Runtime write paths call
|
|
-- get_db_context(project_id) before inserting outbound messages.
|
|
--
|
|
-- Safety:
|
|
-- - fail-closed policy only; no NULL/empty-string app.project_id bypass.
|
|
-- - aborts if target is missing project_id, has NULL project_id, or has
|
|
-- more rows than the reviewed canary cap.
|
|
-- - run with a migration/operator role, not through the production app role.
|
|
|
|
BEGIN;
|
|
|
|
SET LOCAL lock_timeout = '5s';
|
|
SET LOCAL statement_timeout = '30s';
|
|
|
|
DO $$
|
|
DECLARE
|
|
total_rows bigint;
|
|
null_project_rows bigint;
|
|
BEGIN
|
|
IF to_regclass('public.awooop_outbound_message') IS NULL THEN
|
|
RAISE EXCEPTION 'RLS canary target table does not exist: awooop_outbound_message';
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.columns
|
|
WHERE table_schema = 'public'
|
|
AND table_name = 'awooop_outbound_message'
|
|
AND column_name = 'project_id'
|
|
) THEN
|
|
RAISE EXCEPTION 'RLS canary target missing project_id: awooop_outbound_message';
|
|
END IF;
|
|
|
|
SELECT COUNT(*), COUNT(*) FILTER (WHERE project_id IS NULL)
|
|
INTO total_rows, null_project_rows
|
|
FROM awooop_outbound_message;
|
|
|
|
IF null_project_rows <> 0 THEN
|
|
RAISE EXCEPTION 'RLS canary target has NULL project_id rows: %, nulls=%',
|
|
'awooop_outbound_message', null_project_rows;
|
|
END IF;
|
|
|
|
IF total_rows > 1000 THEN
|
|
RAISE EXCEPTION 'RLS canary wave1.3 reviewed cap exceeded: %, rows=%',
|
|
'awooop_outbound_message', total_rows;
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
ALTER TABLE awooop_outbound_message ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE awooop_outbound_message FORCE ROW LEVEL SECURITY;
|
|
|
|
DROP POLICY IF EXISTS outbound_msg_tenant_isolation ON awooop_outbound_message;
|
|
DROP POLICY IF EXISTS awooop_outbound_message_tenant ON awooop_outbound_message;
|
|
|
|
CREATE POLICY awooop_outbound_message_tenant ON awooop_outbound_message
|
|
FOR ALL TO awooop_app
|
|
USING (project_id = current_setting('app.project_id', TRUE))
|
|
WITH CHECK (project_id = current_setting('app.project_id', TRUE));
|
|
|
|
COMMIT;
|