The services were connecting as the Postgres superuser, which silently bypasses RLS

The bug

db/init.sql did the right things on paper. Row-level security was enabled on patients, documents, chunks, phi_tokens and jobs, each with a tenant_isolation policy:

CREATE POLICY tenant_isolation ON chunks
  USING      (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

It also created an app_rw role, with a hard-coded password, that nothing used. The gateway and the worker both connected with DATABASE_URL as the hipaa role: the database owner and, in the compose file, the superuser. Postgres exempts superusers from row-level security entirely, and a table's owner bypasses it too unless FORCE ROW LEVEL SECURITY is set. So all five policies were silently ignored. The README's claim that another tenant's jobs return 404 "via RLS" was false. The job lookup in gateway/main.py is SELECT status, result, finished_at FROM jobs WHERE id=$1, with no tenant filter at all, because the policy was meant to supply it. With the policy bypassed, any tenant's key could read any job by id.

Five tables under RLS, zero enforced against the role in use. I found it by asking what role the eval harness's leak check was running as. If the harness reads chunks as the owner, it reads every tenant's chunks, and a zero-leak result proves nothing about isolation.

The fix

Both services connect as app_rw. The role is created in db/init.sql without a password and cannot log in until db/02_app_role.sh sets one. The split is forced by the Postgres image: init .sql files get no environment substitution, init .sh files do. The password is passed as a psql variable, so it never appears in SQL text or in a log line:

: "${PG_APP_PASSWORD:?PG_APP_PASSWORD must be set in the postgres service environment}"
psql -v ON_ERROR_STOP=1 -v pw="$PG_APP_PASSWORD" --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<'SQL'
ALTER ROLE app_rw PASSWORD :'pw';
SQL

The grants were wrong too. The blanket GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_rw had given the application role UPDATE on audit_log. The REVOKE UPDATE, DELETE ON audit_log FROM PUBLIC above it does not touch a direct grant, so append-only was defeated by the very next statement. The grants now read:

GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_rw;
GRANT DELETE ON chunks, phi_tokens TO app_rw;        -- re-ingest replaces derived rows
REVOKE UPDATE, DELETE ON audit_log FROM app_rw;      -- append-only again
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_rw;
-- no CREATE on schema public: the app never creates tables

Tenant context is transaction-local. Every handler in gateway/main.py and tenant_conn() in worker/store.py set it with set_config('app.tenant_id', $1, true). The true means "local to this transaction". With a session-level SET, a pooled connection returned after tenant A's request would carry A's context into whatever transaction borrowed it next.

The harness refuses to lie. eval/run_eval.py checks its own role before it reads a single chunk:

if con.execute("SELECT rolsuper OR rolbypassrls FROM pg_roles WHERE rolname = current_user").fetchone()[0]:
    raise SystemExit("DATABASE_URL must use the RLS-bound app role (app_rw), not the owner/superuser")

The probes

Two tenants, A and B, each with its own API key, and one ingested document under A.

GET /v1/jobs/{A's job}   Authorization: Bearer {key A}   ->  200
GET /v1/jobs/{A's job}   Authorization: Bearer {key B}   ->  404  {"detail":"job not found"}

-- psql as app_rw
SELECT count(*) FROM chunks;                                          -- app.tenant_id unset
 0
SELECT set_config('app.tenant_id', '<A>', true); SELECT count(*) FROM chunks WHERE tenant_id = '<B>';
 0                                                                    -- and A's rows are visible
SELECT set_config('app.tenant_id', '<B>', true); SELECT count(*) FROM chunks WHERE tenant_id = '<A>';
 0                                                                    -- the reverse
SELECT set_config('app.tenant_id', '<A>', true); INSERT INTO chunks (tenant_id, ...) VALUES ('<B>', ...);
 ERROR:  new row violates row-level security policy for table "chunks"
SELECT has_schema_privilege('app_rw', 'public', 'CREATE');
 f

-- psql as the owner, for contrast
SELECT count(*) FROM chunks;                                          -- every tenant's rows

The owner session seeing everything is the before state. That is what the gateway and the worker had been running as.

Before and after

The design point

Tenant isolation must not depend on application code being bug-free. A lookup with no tenant filter (which is exactly what the job endpoint is, by design), a wrong join, a connection that came back from the pool with someone else's context: with row-level security enforced, each of those returns zero rows or an error. Without it, each returns another tenant's records. But that guarantee is only real if the policy applies to the role the application actually uses. A policy the connecting role is exempt from is documentation, not a control, and it is worse than no policy because it is reassuring. The invariants this enforces, and how each is tested, are on the security model page.

What to check in your own pipeline

• From inside the application's own connection: SELECT rolsuper, rolbypassrls FROM pg_roles WHERE rolname = current_user. If either is true, your policies are not applying.
• Check table ownership too. Owners bypass RLS unless FORCE ROW LEVEL SECURITY is set.
• Run the negative probes as the application role: no context set, expect zero rows; an INSERT for the wrong tenant, expect an error.
• Read every blanket GRANT ... ON ALL TABLES and check what it gave on the tables that are supposed to be append-only.
• Set tenant context with set_config(..., true) or SET LOCAL, never a session-level SET, on any pooled connection.
• Make the test harness refuse to run as a role that bypasses RLS, so a passing leak check means something.
• List the tables that have no policy, and write that list down where the next person will read it.

The leak check that this role change made meaningful is described in the recall metric post.

← All posts