PostgreSQL RLS tests: roles, writes and session context

PostgreSQL row-level security needs tests that use the same ordinary database roles as the application. A successful query from a superuser cannot establish that another user's rows are hidden. This guide explains a small, reproducible experiment with the Supabase PostgreSQL skill: 40 named observations, four unchanged upstream SQL blocks and separate connections for a writer, reader, ordinary table owner and unprivileged role.

The experiment uses PostgreSQL 17.11 and synthetic orders belonging to two integer identities. It tests database behavior, not an authentication service. No hosted Supabase project, real user, production database, paid API or AI assistance executing the complete skill was involved. The downloadable evidence records exact source and runner hashes.

Start with ordinary roles and a positive control

First establish which role connects and which table privileges it inherits. The writer in this fixture has SELECT, INSERT and UPDATE on orders; it has neither DELETE nor TRUNCATE. Its role attributes also exclude superuser, role creation, database creation, replication and BYPASSRLS. These assertions prevent a privileged test connection from quietly becoming the subject of the test.

Before enabling RLS, the ordinary writer can read all three initial rows. After the unchanged upstream policy and FORCE ROW LEVEL SECURITY statements run, the writer with identity 123 sees its two rows. An ordinary table owner is also filtered while FORCE is enabled. Those positive controls show that the connection and data are usable before interpreting a failed operation.

Use schema exploration to identify tables, owners and existing policies before designing your own disposable fixture. Do not copy this destructive setup into an existing database.

Separate table privileges from row policies

A policy limits accessible rows; it does not grant table access. The upstream read-only role can select the declared product table but cannot initially select orders. A separately marked fixture grant adds SELECT on orders so the reader can then demonstrate row filtering. The unprivileged role remains unable to select orders even after receiving a matching synthetic identity value.

Operation and connection	Observed result	What it establishes
Writer, matching identity, SELECT	Matching rows returned	Permitted reads work under this policy
Reader, INSERT	SQLSTATE 42501	Missing table privilege rejects the write
Writer, different owner's UPDATE	Zero rows affected	Target row is invisible to this writer
Writer, mismatched INSERT	SQLSTATE 42501	The policy rejects the new row
Unprivileged role, SELECT	SQLSTATE 42501	A policy does not supply a missing grant

The same error code can arise from different controls. Record the role, command, expected row state and policy configuration alongside the code. A negative test without an allowed operation is weak evidence: the database might simply reject everything.

Check writes and unchanged rows separately

The original policy applies FOR ALL and supplies USING without a separate WITH CHECK expression. PostgreSQL uses that expression for the relevant new-row check too. The matching writer can insert a fourth row and update its own quantity. An insert for identity 456 and an attempt to change an existing row's owner both raise 42501.

The fixture then reads the rows through a separate setup connection. Rejected ownership changes leave the original owner and quantity intact. An UPDATE targeting the other identity affects zero rows and leaves that row unchanged. These are distinct outcomes; do not replace them with a generic “write blocked” assertion. DELETE and TRUNCATE are rejected by this writer's table privileges. TRUNCATE is not itself governed by RLS.

Treat session context as a claim, not authentication

The native example uses current_setting('app.current_user_id') cast to bigint. In the fixture, the ordinary writer can set that custom value to 456 and immediately read the other identity's row. This is an intentional counterexample: a caller-controlled session variable does not verify who the caller is. Do not offer users arbitrary SQL under the assumption that this value protects ownership.

A reader that has not set the value gets 42704; a non-integer value gets 22P02. These errors come from this exact expression and setup. They do not prove every missing or malformed claim fails in the same way. An application needs trusted identity verification and a controlled method for establishing database context.

Within one transaction, SET LOCAL changes the writer's context to 456. At commit the earlier session value 123 returns. This observation follows the PostgreSQL 17 SET documentation. It is not a connection-pool or concurrent-request test. Verify reset behavior with the actual pool and transaction boundaries your application uses.

An API's public response projection is another separate layer. The official FastAPI contract skill helps inspect response filtering, but filtering fields cannot authenticate an identity or authorize a database row.

Test owners, bypasses and extra policies

The ordinary table owner sees all four final rows when FORCE is temporarily removed, then sees its three matching rows when FORCE is restored. The bootstrap superuser sees all rows even under FORCE. Production application connections should not inherit a bypass role merely to make tests pass.

Adding a permissive SELECT policy with USING (true) widens the writer's result to all four rows. Removing it restores the filter. Permissive policies combine with OR, so an additional broad policy can defeat the intended restriction. Review all applicable policies rather than inspecting only the one recently added.

A separate table with RLS enabled and no policy returns no rows to the ordinary writer and rejects INSERT. Setting row_security to off does not let that writer bypass the orders policy; the attempted SELECT throws 42501. The PostgreSQL 17 RLS documentation describes these distinctions. Use the security review checklist to turn them into questions about the application's actual authorization boundary.

Keep index setup separate from performance claims

The fourth unchanged SQL block creates an index on orders.user_id. The ordinary owner, which lacks CREATE on the public schema in this fixture, receives 42501 when attempting it. The isolated bootstrap role then runs the original statement successfully, and the fixture checks the resulting index definition. Application roles receive no additional privileges.

The first experiment stopped at that permission error. The corrected experiment preserves the rejection as an explicit observation and moves only the index setup to the bootstrap role. All 40 observations come from the corrected runner whose hash appears in the evidence.

Creating the index does not demonstrate a faster query. There is no latency benchmark, planner comparison, production cardinality or cache-control experiment here. The existing older missing-index scenario remains a historical record for the previous package checksum. Consult PostgreSQL optimization guidance for a separately designed plan and measurement experiment.

Reproduce the scoped experiment and inspect its limits

Recorded	Outside this experiment
PostgreSQL 17.11, Python 3.13.16, psycopg 3.2.10	Other database releases and drivers
Four unchanged upstream SQL blocks and 40 named observations	Every reference or the complete AI-client skill
Separate ordinary-role connections and synthetic rows	Real identity verification or production authorization
Internal Docker network with no host ports	Hosted Supabase, auth.uid() and private SECURITY DEFINER helpers
One transaction-local context restoration	Pool reuse, concurrency and performance benchmarks

The package's examples/README.md explains the disposable Docker setup and strict input paths. Dependencies must be installed separately; no paid service or credential is required. The runner requires an empty fixture database and the named internal host, and exits when those safeguards do not match. It writes fresh evidence for a new run; the bundled record describes only the run dated October 7, 2026.

Check provenance, licensing and version history

The upstream source is Supabase agent-skills at c9be0e931b7930f7d02126d04774d904c381e7d7. All 37 original files, including the MIT license, were checked against Git blob IDs and SHA-256 values. The original SKILL.md remains unchanged in upstream-original/; the marked preface, runner, evidence and this guide are independent BB Skills work.

The catalog version c9be0e931b79.bb1 identifies this reviewed package; it does not change the upstream metadata version 1.1.1. The old package and its missing-index scenario are retained. Existing favorites, reviews and the resource URL still refer to the same catalog item. Read the new checksum and current scenario together before citing an observation. MIT notices for Supabase and the independent additions appear separately. No upstream endorsement, comprehensive security certification, Google indexing or AI citation is claimed.