Janady13/missionmatch / db/migrations/0068_audit_logs_tenant_read.sql

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / db/migrations/0068_audit_logs_tenant_read.sql
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	720d47fae476b0d38477ae4401697d372b1ea2bc
Branch	feature/opportunity-to-outcome
Path or archive member	db/migrations/0068_audit_logs_tenant_read.sql
Original SHA-256	1d9d4a8e9dce5ee8ff132336370df10cfd5d82a97e57d6c63da83c172cf82ae7
Git blob SHA	eed0ac2a1850b27328d89722f90b587322827ac2
Reading-copy SHA-256	1d9d4a8e9dce5ee8ff132336370df10cfd5d82a97e57d6c63da83c172cf82ae7

Original source ↗

Every recorded occurrence

These 2 records contain identical original source bytes. Their paths and revisions remain part of the archive.

Original record	Branch or archive	Revision / identifier	Original location
Janady13/missionmatch / db/migrations/0068_audit_logs_tenant_read.sql	feature/opportunity-to-outcome	720d47fae476b0d38477ae4401697d372b1ea2bc	Open original
Janady13/missionmatch / db/migrations/0068_audit_logs_tenant_read.sql	feature/aioe-upgrade	2178e7298d0e5d9af83afa12402fd587f0ba6ed8	Open original

Recovered source transcript

Download complete source text ↓66 lines · 3.8 KB

-- +goose Up
-- Row level security on audit_logs, done properly this time.
--
-- The table is tenant scoped, the application role holds SELECT, and it had no policy at all: the largest
-- cross tenant surface in the schema was protected by the convention that every query remembers an
-- organization predicate. old_values and new_values are jsonb snapshots of candidate rows, so one
-- forgotten WHERE leaks another tenant's PII rather than metadata.
--
-- Migration 0064 attempted this and TOOK LOGIN DOWN, so the failure is worth stating precisely. Postgres
-- applies a SELECT policy to RETURNING. The audit insert uses RETURNING. A platform event such as a login
-- is written with organization_id NULL, before any tenant is known. The policy compared organization_id to
-- the request GUC, and NULL = anything is NULL rather than true, so the row was inserted and then filtered
-- out of its own RETURNING clause; the scan found nothing and the login path panicked on the nil.
--
-- Two things follow, and both are encoded below.
--
-- FIRST, the policy must cover the NULL organization row, because that row is written and read back by a
-- caller who has no tenant context yet and never will on that request. It is included by an explicit
-- IS NULL arm rather than by loosening the comparison, so the intent is legible.
--
-- SECOND, who may READ a platform event is a real decision and not an implementation detail. A failed
-- login carries an email address and a source IP. Letting one tenant's administrator read another
-- tenant's failed logins is precisely the disclosure this migration exists to prevent, and it would be an
-- odd way to close a leak by opening a smaller one. So platform events are visible only when there is no
-- tenant context at all, which is the state the writer itself is in. A tenant scoped reader sees their own
-- organization's rows and nothing else, and an operator investigating platform events reads them the way
-- everything else at that tier is read, through the migrator role, which is BYPASSRLS and unaffected by
-- this policy.
ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_logs FORCE ROW LEVEL SECURITY;

-- +goose StatementBegin
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_policy WHERE polname = 'audit_logs_tenant_read') THEN
        EXECUTE $p$
            CREATE POLICY audit_logs_tenant_read ON audit_logs FOR SELECT
            USING (
                -- The caller's own organization.
                organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid
                -- Or a platform event, readable only when the reader has no tenant context either, which
                -- is the same state the writer was in. This is the arm that makes RETURNING work on the
                -- login path: without it the insert succeeds and the row vanishes from its own output.
                OR (organization_id IS NULL
                    AND NULLIF(current_setting('app.current_org_id', true), '') IS NULL)
            )
        $p$;
    END IF;

    -- Writes stay unpoliced. The application must be able to record a platform event before any tenant is
    -- known, and an append only log whose writes can be refused by a policy is a log that silently loses
    -- exactly the security events it exists for. Integrity is enforced by the append only triggers and the
    -- REVOKE of UPDATE and DELETE in 0001, not by RLS.
    IF NOT EXISTS (SELECT 1 FROM pg_policy WHERE polname = 'audit_logs_insert_any') THEN
        EXECUTE 'CREATE POLICY audit_logs_insert_any ON audit_logs FOR INSERT WITH CHECK (true)';
    END IF;
END
$$;
-- +goose StatementEnd

-- +goose Down
DROP POLICY IF EXISTS audit_logs_tenant_read ON audit_logs;
DROP POLICY IF EXISTS audit_logs_insert_any ON audit_logs;
ALTER TABLE audit_logs NO FORCE ROW LEVEL SECURITY;
ALTER TABLE audit_logs DISABLE ROW LEVEL SECURITY;