Review PostgreSQL RLS across roles, views and functions

Review PostgreSQL row-level security with separate database logins and a disposable fixture before trusting an agent's access-control checklist. Our recorded experiment shows how policies, table owners, views, function owners and grants can change which rows a caller sees.

Know what this experiment can establish

Supabase Platform Workflows is an independently packaged adaptation of Supabase's MIT instruction skill. We qualified five PostgreSQL statements, declared each change and archived the complete pinned original. The download contains guidance, not a running Supabase project or a security certification. Inspect its adaptation record and license before installing it.

This guide describes a native PostgreSQL 17.11 experiment completed on October 4, 2026. Its 52 recorded observations comprise six source/package integrity checks and 46 native database observations. Four synthetic records belong to two independent login roles. The database lived in temporary storage on an internal Docker network; the non-root probe had a read-only filesystem, resource limits and no production mounts. Every data-changing demonstration was rolled back, and the final original rows were checked again.

We did not execute an AI client or Supabase Auth/JWT, the Data API, PostgREST, CLI, MCP, Storage, Realtime, vectors or a migration workflow. In a typical Supabase application, users share a database role and application identity comes through a different mechanism. Our current_user predicate identifies a database login; it is not a replacement for auth.uid() in that application. A passing local database experiment does not establish that a deployed API authenticates or authorizes its users correctly.

Separate table grants from row policies

The fixture used an ordinary fixture_owner, two non-elevated logins named fixture_alice and fixture_bob, and a temporary privileged setup connection. The ordinary roles had no superuser, BYPASSRLS, role-creation, database-creation or replication attribute and no memberships. Alice had schema USAGE and SELECT, INSERT, UPDATE and DELETE on the test table, without schema CREATE, temporary-table permission or TRUNCATE.

The table contained IDs 101 and 102 for Alice, and 201 and 202 for Bob. Before enabling RLS, Alice's table grant exposed all four rows. With RLS enabled and no applicable policy, her SELECT returned none. After the ownership policy was created, Alice saw only 101 and 102; Bob saw only 201 and 202. These three observed states make useful checkpoints when an application reports either excessive access or unexpectedly empty results.

In the disposable fixture, the relevant policy was:

ALTER TABLE bb_fixture.records ENABLE ROW LEVEL SECURITY;
CREATE POLICY own_rows ON bb_fixture.records
  FOR ALL TO fixture_alice, fixture_bob
  USING (tenant = current_user);

Table grants and row predicates answer different questions. Inspect both, using the same effective role and statement as the application. Supabase's RLS guide provides its application-specific identity examples. Before borrowing those examples, identify the exposed schema, API roles, ownership field and source of trusted identity in your own project.

Check existing rows and proposed rows

Our initial ALL policy intentionally omitted an explicit WITH CHECK clause. Alice could update the payload of her own record, insert her own synthetic record and delete her own record inside rolled-back transactions. An UPDATE or DELETE targeting Bob's ID selected zero rows. Changing Alice's own record to Bob's tenant failed with SQLSTATE 42501, as did inserting a new record for Bob.

The result matters because a checklist can incorrectly describe an omitted clause as an automatic ownership-transfer vulnerability. For ALL and UPDATE policies, PostgreSQL reuses USING for the proposed-row check when WITH CHECK is absent. The CREATE POLICY reference documents that fallback. An explicit clause can still make the intended rule easier to review:

ALTER POLICY own_rows ON bb_fixture.records
  WITH CHECK (tenant = current_user);

Repeating the ownership-reassignment attempt after that change also failed with 42501. This confirms the two observed forms behaved alike for this fixture; it does not prove that an arbitrary collection of production policies is equivalent. Inspect every applicable policy and every operation. Our separate permissive-policy experiment later widened access, despite the ownership policy remaining present.

We also created a separate table with an UPDATE policy and table SELECT rights, but no SELECT row policy. UPDATE ... WHERE id = 101 RETURNING id affected no rows. Adding a corresponding SELECT policy allowed the own record, and the transaction was rolled back. This observation is specific to a statement that reads table columns. Capture the actual WHERE, SET and RETURNING clauses when diagnosing an empty update; do not infer a universal result for every unconditional UPDATE.

Inspect view ownership and invoker behavior

Alice selected four rows through a default view owned by the ordinary table owner before FORCE was enabled. A second view with security_invoker = true, owned by the same role and reading the same table, returned her two rows. The relevant view definition was:

CREATE VIEW bb_fixture.invoker_view
  WITH (security_invoker = true)
  AS SELECT * FROM bb_fixture.records;

After applying FORCE ROW LEVEL SECURITY to the underlying table, the default owner view returned zero rows in our fixture. No policy admitted the ordinary owner, so that result follows the fixture's deliberately narrow policy. The invoker view continued to return Alice's two rows. A default view therefore did not behave as an unconditional bypass across both configurations.

PostgreSQL's CREATE VIEW notes explain the difference between checking underlying relations as the view owner and as the caller. The invoker option is available from PostgreSQL 15. Check the deployed version, actual view owner, grants and table policy; do not copy the option into an older server and assume it worked. A view can also invoke functions, so reviewing its table access alone leaves part of the execution path unexplained.

Inspect function owners and callable grants

Our side-effect-free functions counted fixture rows through fully qualified table names. An ordinary-owner SECURITY DEFINER function returned four before FORCE and zero after FORCE. A SECURITY INVOKER function returned Alice's two rows. A setup-superuser-owned SECURITY DEFINER function returned four even after FORCE. The functions used a fixed search path and had no writes, external calls or Supabase identity logic.

The changed counts came from the effective owner and table configuration. SECURITY DEFINER selects the function owner's privilege context; the keyword alone does not establish whether a particular table's RLS is bypassed. PostgreSQL's CREATE FUNCTION security notes also call for a controlled search path and carefully scoped execution privileges. Review the action performed by a privileged function, including authorization inside it, instead of using the keyword as a generic fix for permission errors.

We observed PUBLIC's default EXECUTE permission on a newly created function in this database. Revoking it from the superuser-owned counting function blocked Alice's call with 42501. Granting EXECUTE explicitly to Alice, then revoking her schema USAGE, blocked the call again. SQL function callability required both permissions in this fixture. Whether a function is exposed through a Supabase API is a further question that our SQL experiment did not test.

When introducing a privileged function, review creation and privilege changes together so a broad default is not left exposed between steps. Check altered default privileges, existing grants, owner changes, schema placement and the caller's actual route to the function. Avoid copying the temporary superuser demonstration into an application: it was a controlled observation in a disposable database, not a recommended application role.

Review bypass roles and combined policies

FORCE constrained the ordinary owner, but the temporary BYPASSRLS login and setup superuser still read all four records. Alice could not SET ROLE to the bypass login because she had no membership. Setting row_security = off in Alice's session produced an error when she queried the protected table; it did not silently give her the missing rows.

We then added a permissive SELECT policy with USING(true) for Alice. Her result grew from two records to all four. Adding a restrictive ownership policy narrowed the result to two again. Both temporary policies were removed afterwards. This supplies a concrete reason to examine the full policy set, rather than validating one ownership expression in isolation.

The PostgreSQL RLS documentation describes privileged-role exceptions and policy composition. For an application review, record the effective connection role and any role-switching path alongside the table's owner, enabled/forced flags and applicable policies. Reproduce the actual operation as each relevant caller. A test run only as the setup superuser cannot demonstrate the tenant restrictions experienced by a normal application user.

Test limits outside the row filter

Alice could not read Bob's record with ID 201, yet inserting her own tenant record with that same primary key failed with SQLSTATE 23505. The hidden key's existence was observable through the uniqueness error. RLS did not make that constraint invisible. Decide whether such existence information is acceptable for your application, and handle errors and key design with that requirement in mind.

TRUNCATE first failed with 42501 because Alice lacked its privilege. We then granted TRUNCATE only inside the disposable experiment. Her transaction emptied the whole table despite RLS, and its rollback restored all four records. The grant was removed afterwards. This was a deliberate test of a whole-table permission, not a production mitigation or a claim that RLS can restrict TRUNCATE to a tenant's rows.

Retain a result matrix for your own bounded tests: caller, statement, visible IDs, affected rows, error code and rollback outcome. Include denied paths and intentionally broad grants as well as successful own-record reads. Concurrent policy races, triggers, foreign keys, partitions, security-sensitive function bodies and application response handling were not covered by this small fixture and need their own evidence where relevant.

Use the evidence when selecting a skill

The resource profile links the package checksum, complete manifest, declared changes, archived original and scoped scenario. Use those together: the source checks explain which instructions were packaged; the SQL observations explain selected local behavior. Neither substitutes for testing the application's real authentication and authorization path. The development skill selection guide describes how to compare source evidence and practical records without treating every catalog label as a compatibility claim.

Our download and local fixture use free instruction files and existing local database tooling. They did not create a Supabase cloud project, activate a plan or call a paid model. Your chosen model, hosted project and optional services can have separate costs. Bring your own explicitly scoped credentials only when you choose to test a wider platform workflow, and keep them out of public prompts and evidence.

This guide was drafted with AI assistance and reviewed against the pinned source, current primary references and the recorded local experiment. The five qualifications are maintained independently by BB Skills; they do not imply Supabase endorsement. Before applying them to a different PostgreSQL release or Supabase project, verify the current documentation and rerun the relevant checks with that environment's roles, grants, policies and API configuration.