Row level security: three Postgres mistakes, tested

This is a scripted Python and psql experiment with downloadable source and recorded output. No AI agent or model ran the test.

What Postgres row level security does

Row level security (RLS) lets PostgreSQL filter rows for each query. A policy attaches to a table and holds up to two expressions. USING decides which existing rows a role can see or target. WITH CHECK decides which new or changed rows a role may store. The PostgreSQL manual describes the feature in Row Security Policies and gives the syntax in CREATE POLICY.

Supabase RLS uses this same PostgreSQL feature, but we did not test Supabase. Every command here runs on plain PostgreSQL, and the three mistakes apply wherever the same policies are used.

Scope of the fixture

• Target: a cluster that initdb creates in a temporary directory, started on a unix socket inside that directory with listen_addresses empty. There is no TCP listener. The runner deletes the cluster at the end.
• Roles: app_owner owns the table and is not a superuser. app_user is the role the application should use. The postgres superuser only reads state back.
• Tenant context: after connecting, the application runs SET app.tenant_id = 'acme'. Policies read the value with current_setting('app.tenant_id', true).
• Data: one invoices table with four synthetic rows, two for tenant acme and two for tenant globex.

The fixture is intentionally insecure in its vulnerable configurations and touches nothing outside its temporary directory. The Excession Labs Acceptable Use Policy describes authorized use. This article gives no permission to test another system. For related reading on the same boundary, see tenant isolation and broken access control.

Run the downloadable fixture

Download fixture.py and verify.py into one directory. You need Python 3.9 or later and a PostgreSQL install that provides initdb, postgres, pg_isready and psql. We ran PostgreSQL 16.15 only. Run:

python3 verify.py --output rerun

The output directory must be new. For each configuration the runner creates a fresh database, loads the table, applies the policies, then opens a separate psql session per test. It saves each session's full output and stops and deletes the cluster. No network target, login or paid API is involved.

Our recorded command was python3 verify.py --output recorded-run, run on 10 November 2026 with Python 3.15.2 on macOS arm64. It printed {"checks": 30, "passed": true, "failures": []}. That means every case matched its expected outcome, including the vulnerable ones, and that results.json records "cluster_deleted": true. The cluster used port 53847 on a unix socket that no longer exists.

Postgres row level security: observed results

Each mistake has a vulnerable configuration and a fixed one. The positive controls confirm that legitimate access survives the fix. The negative controls confirm that denial happens where the policy says it should.

The superuser row is not a failure. PostgreSQL documents that superusers and roles with the BYPASSRLS attribute always bypass row security, and that FORCE ROW LEVEL SECURITY does not change that.

A policy with only USING was not the flaw

Before the run we expected that a policy with USING and no WITH CHECK would let a tenant write into another tenant. It did not. A single FOR ALL policy with only USING denied both the cross-tenant INSERT and the unconditional UPDATE in the control database. The CREATE POLICY page explains why: if only a USING clause is specified, that clause is used for both the USING and WITH CHECK cases.

The flaw appeared when someone wrote an explicit WITH CHECK that left the tenant out. An explicit check replaces the default, so WITH CHECK (amount >= 0) validated the amount and allowed any tenant value. The UPDATE with a WHERE clause was denied even in the vulnerable configuration. The manual says that when an UPDATE reads columns, for example in its WHERE clause, the SELECT policy is also checked against the existing and the new row. The new globex row failed the SELECT policy. An UPDATE that reads no column skips that check, so the unconditional one above moved all three visible acme rows.

Verify the finding before you report it

Capture the session, the role and the table state. Three recorded excerpts follow.

Mistake 1. Tenant acme runs a query that forgot its WHERE clause on a table without RLS (full output):

SET app.tenant_id = 'acme';
SELECT id, tenant_id, amount FROM invoices ORDER BY id;
 id | tenant_id | amount 
----+-----------+--------
  1 | acme      |    100
  2 | acme      |    250
  3 | globex    |    400
  4 | globex    |     75
(4 rows)

Mistake 2. RLS is enabled and the policy exists, yet the owner role gets the same four rows (full output). The flags in pg_class explain it: relrowsecurity is t and relforcerowsecurity is f (output). Also check pg_roles for rolsuper and rolbypassrls on the role your application uses. We did not run that query in the fixture.

Mistake 3. After the two vulnerable writes, a superuser reads every row (full output). Row 5 was inserted into globex by an acme session, and the unconditional UPDATE moved rows 1, 2 and 6 to globex:

 id | tenant_id | amount 
----+-----------+--------
  1 | globex    |    100
  2 | globex    |    300
  3 | globex    |    400
  4 | globex    |     75
  5 | globex    |    999
  6 | globex    |     10
(6 rows)

A useful report names the connecting role, the table, the policy definitions, the exact statement and the rows that changed. Read the data back as a second identity. A write that reports success is not proof until you see the row.

Fix the RLS policy and retest

Mistake 1 is fixed by enabling RLS and adding a policy. Mistake 2 needs one more statement from fixture.py:

ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

With that line, the owner role returned 2 rows instead of 4. The superuser still returned 4. For mistake 3, the fixed write policies check the tenant as well as the amount:

CREATE POLICY tenant_insert ON invoices FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true) AND amount >= 0);
CREATE POLICY tenant_update ON invoices FOR UPDATE
  USING (tenant_id = current_setting('app.tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true) AND amount >= 0);

We retested every case against the fixed databases. The cross-tenant INSERT and both cross-tenant UPDATEs returned ERROR: new row violates row-level security policy for table "invoices". Acme could still insert its own row and change its own amount, and the negative amount was still refused. The final table held five rows with rows 1, 2 and 6 still under acme (output).

Our recommendation, which the fixture does not test, is to connect the application as a role that does not own the tables and has no BYPASSRLS. Keep the owner role for migrations. FORCE then acts as a second layer if the wrong role is ever used.

Inspect the evidence bundle

• Machine-readable results: all 30 cases, roles, SQL, expected and observed outcomes, versions and hashes.
• Recorded psql commands: the socket directory and port belong to the finished run. Use verify.py for a fresh cluster.
• SHA-256 manifest: an integrity check, not independent attestation.
• Fixture instructions and limits, fixture source and verification source.

All data is synthetic. No production credentials, customer records or external services were involved.

What this experiment does not cover

This is one table in one PostgreSQL version, 16.15, with trust authentication on a unix socket. We did not test DELETE, restrictive policies, several permissive policies on one table, views, security-definer functions, ON CONFLICT upserts, connection pools that reuse a session with a stale app.tenant_id, the BYPASSRLS attribute, or Supabase. The fixed result says nothing about an unrelated schema.

In a real engagement, list each table that holds tenant data, record the role the application connects as, and test reads and writes from two tenants. Retest the same cases after every policy change. Keep failed and inconclusive runs with the successful ones. The same two-identity method applies to HTTP APIs, as shown in API authorization testing.

Excession Labs runs this kind of test in its security assessments, using a second tenant account to prove each cross-tenant effect. See how a finding like this looks in a sample report.