PostgreSQL row-level security: test policies and roles

PostgreSQL row-level security, or RLS, applies policies to the rows a database role can read or change. A table grant still determines whether the role can perform an operation at all. A useful test therefore checks both: an allowed operation on the role’s own row and a denied attempt against another role’s row.

This guide builds a two-tenant lab with separate database roles. We ran 38 checks on isolated PostgreSQL 18.6, including genuine tenant login sessions, rejected writes, owner behavior, and cleanup. The example uses the database role as the tenant identity; it does not authenticate web users or configure an application connection pool.

Separate the owner from the tenant roles

Use a fresh disposable PostgreSQL cluster and an administrative connection. The schema and role names below must be unused. Roles are cluster-wide, so creating a separate database on a production cluster is not enough isolation for this exercise.

The owner controls the table and policies. Roles rls_a and rls_b receive ordinary data privileges, while rls_reader receives only SELECT. None has superuser or BYPASSRLS privileges. NOLOGIN lets us demonstrate permissions through administrative role switching without putting passwords into the SQL.

CREATE ROLE rls_owner NOLOGIN NOSUPERUSER NOBYPASSRLS;
CREATE ROLE rls_a NOLOGIN NOSUPERUSER NOBYPASSRLS;
CREATE ROLE rls_b NOLOGIN NOSUPERUSER NOBYPASSRLS;
CREATE ROLE rls_reader NOLOGIN NOSUPERUSER NOBYPASSRLS;
CREATE SCHEMA rls_lab AUTHORIZATION rls_owner;
SET ROLE rls_owner;
CREATE TABLE rls_lab.notes (
    id integer PRIMARY KEY,
    tenant_role text NOT NULL,
    body text NOT NULL
);
INSERT INTO rls_lab.notes VALUES
(1, 'rls_a', 'A draft'), (2, 'rls_b', 'B draft');
GRANT USAGE ON SCHEMA rls_lab TO rls_a, rls_b, rls_reader;
GRANT SELECT, INSERT, UPDATE, DELETE ON rls_lab.notes TO rls_a, rls_b;
GRANT SELECT ON rls_lab.notes TO rls_reader;
ALTER TABLE rls_lab.notes ENABLE ROW LEVEL SECURITY;
RESET ROLE;

The table has one note for each tenant. Its tenant_role column is deliberately simple text matching the database role name. This is a teaching model, not a recommendation to make role names your permanent application identifiers.

Ordinary privileges and policies remain separate controls. The GRANT reference describes the former; PostgreSQL’s row security documentation describes the latter. Our setup does not grant TRUNCATE or ownership to the tenants.

Verify default deny before adding a policy

RLS is enabled, but no policy exists yet. Run this from the administrative connection:

SET ROLE rls_a;
SELECT count(*) AS visible_rows FROM rls_lab.notes;
RESET ROLE;

Observed visible-row count:

0

The tenant has SELECT permission but sees zero rows. Our additional insertion attempt also failed before the policy existed. A count of zero here means no rows were visible to this role, not that the table was empty: the administrator separately counted two.

Use USING for existing rows and WITH CHECK for new contents

Create one explicit policy for the two tenant roles:

SET ROLE rls_owner;
CREATE POLICY own_notes ON rls_lab.notes
    FOR ALL TO rls_a, rls_b
    USING (tenant_role = current_user::text)
    WITH CHECK (tenant_role = current_user::text);
RESET ROLE;

USING limits existing rows available to the role. WITH CHECK evaluates the proposed contents of inserted or updated rows. Both expressions require the tenant field to match current_user. FOR ALL also covers deleting visible rows, subject to the table grants.

The CREATE POLICY reference explains command-specific behavior. We spell out both expressions so the write rule is visible during review.

SET ROLE rls_a;
SELECT current_user, id, body FROM rls_lab.notes ORDER BY id;
RESET ROLE;
SET ROLE rls_b;
SELECT current_user, id, body FROM rls_lab.notes ORDER BY id;
RESET ROLE;

Captured output, in unaligned psql format:

rls_a|1|A draft
rls_b|2|B draft

A sees only note 1 and B sees only note 2. Adding an explicit predicate for note 2 to A’s SELECT still returned no rows. The reader role continued to see zero because no policy applied to it, despite its SELECT grant.

Test allowed writes and invisible targets

This transaction inserts and edits A’s own note, then targets B’s existing note. ROLLBACK leaves the original fixture unchanged:

BEGIN;
SET LOCAL ROLE rls_a;
INSERT INTO rls_lab.notes VALUES (3, 'rls_a', 'New note')
RETURNING id, tenant_role, body;
UPDATE rls_lab.notes SET body = 'Updated note' WHERE id = 3
RETURNING id, tenant_role, body;
WITH changed AS (
    UPDATE rls_lab.notes SET body = 'Wrong tenant' WHERE id = 2
    RETURNING id
)
SELECT count(*) AS changed_other_tenant FROM changed;
ROLLBACK;

3|rls_a|New note
3|rls_a|Updated note
0

The insert and own-row update returned note 3. The attempted update of note 2 changed zero rows. Its target was filtered out; that result does not establish that the ID does not exist.

Our delete tests followed the same pattern: deleting A’s row was allowed inside a transaction that was rolled back, while targeting B’s row deleted nothing. The original two rows remained after the tests.

Require rejected writes to fail

Run each of the next blocks separately. Both intentionally raise an error. In an interactive session, execute ROLLBACK after the error before continuing. With psql configured to stop on errors, the remaining ROLLBACK line may not run; closing that connection rolls back the open transaction. Our runner used a separate connection for each negative case.

A cannot insert a new note labeled as B’s:

BEGIN;
SET LOCAL ROLE rls_a;
INSERT INTO rls_lab.notes VALUES (4, 'rls_b', 'Wrong tenant');
ROLLBACK;

A also cannot move its existing note to B by changing the tenant field:

BEGIN;
SET LOCAL ROLE rls_a;
UPDATE rls_lab.notes SET tenant_role = 'rls_b' WHERE id = 1;
ROLLBACK;

Both tests failed with SQLSTATE 42501 and a row-security policy violation. We checked the original rows afterward, including their tenant fields and bodies. This distinguishes a rejected new row from the earlier update that simply found no visible target.

Attempt as A	Observed result
Read A’s note	One row
Read or update B’s note by ID	No visible or changed rows
Insert or edit a note belonging to A	Allowed, then rolled back
Insert a B note or relabel A’s note as B	Policy violation, SQLSTATE 42501
TRUNCATE the table	Denied by missing table privilege
Disable RLS	Denied because A does not own the table

Check owner bypass and FORCE ROW LEVEL SECURITY

Testing only with the table owner can conceal a missing policy. Our ordinary owner saw both rows until FORCE was enabled:

SET ROLE rls_owner;
SELECT count(*) AS owner_before_force FROM rls_lab.notes;
ALTER TABLE rls_lab.notes FORCE ROW LEVEL SECURITY;
SELECT count(*) AS owner_after_force FROM rls_lab.notes;
RESET ROLE;

2
0

The counts were 2, then 0. After FORCE, the owner had no applicable policy in this lab, so it saw nothing. A still saw its own row. We separately verified that the administrative superuser and a temporary BYPASSRLS role with SELECT privileges could still read both rows. FORCE does not constrain those privileged roles.

The owner can change the table’s security settings, so FORCE is not protection against a hostile owner. Keep application connections separate from ownership and bypass roles. Setting row_security = off as A did not grant access in our test; the read failed instead.

Verify the identity boundary with real logins

The SET ROLE reference explains why administrative switching is only part of the test. An administrator’s authenticated session can have role-switching powers that an application login should not have.

Inside the private test cluster, the runner temporarily enabled logins for the test roles with random credentials. Separate A and B connections each read only their own note. A’s authenticated connection could not assume B or the owner, could insert its own note, and could not insert a B note. All test roles were removed during cleanup.

This policy trusts current_user, not a tenant value supplied by a browser. Setting an arbitrary app.tenant session variable to B did not change A’s access. If a real application shares one database login across users, this sample does not distinguish those users. Establish a trusted identity mapping, limit who can change it, and test how the pool assigns and resets it before adopting a policy.

Review grants and every applicable policy

In one controlled test, we gave the reader a matching policy but removed its SELECT grant. The read failed. A policy does not supply a missing table privilege.

In another transaction, we added a second permissive SELECT policy for A with USING (true). A then saw both rows. Rolling back restored one-row visibility. Review all applicable policies together; adding a broad permissive policy can widen access even when the original tenant policy looks correct.

These checks do not audit views, security-definer functions, application authentication, concurrent policy changes, or information revealed by integrity constraints. They also do not verify backup completeness. Treat the PostgreSQL backup and restore guide as a separate recovery procedure with explicit role and data checks. For query costs, the EXPLAIN ANALYZE guide covers plan inspection; RLS correctness alone says nothing about performance.

Remove the lab objects

Return to the administrative connection and remove only the objects created for this exercise:

DROP TABLE rls_lab.notes;
DROP SCHEMA rls_lab;
DROP ROLE rls_a, rls_b, rls_reader, rls_owner;

Our final checks confirmed that the schema and all test roles were gone. The isolated server was stopped. Before using a policy in an application, repeat the allowed and denied cases through its actual connection path, and verify that the connection cannot acquire another tenant’s identity or bypass role.