Test PostgreSQL Row Security With the Role Your App Uses

In practice, simply seeing ALTER TABLE … ENABLE ROW LEVEL SECURITY on a table can be a false positive. A table may have RLS enabled, but if the policy is misconfigured or bypassed by the execution path, unauthorized rows or mutations could still happen. The key is to define the exact contract: which rows each application role should see and what mutations it should be allowed. Then we must empirically verify that contract. In other words, we treat RLS as a security policy to be tested, not blindly trusted. If the tests pass, we ACCEPT the change; if not, we REPAIR or HOLD it. In the worst case we may have to RECONCILE if data may have been exposed before fixing.

This article shows a full reproducible test matrix for a simple multi-tenant table. We create a “tenant oracle” with five rows (two belong to app_a, two to app_b, and one to the table owner). We enable RLS and attach a policy that allows only rows whose tenant_role = current_user. We then simulate each actual application identity (distinct database roles) and run SELECT and DML statements. We compare the returned row IDs and SQL errors against expectations. We include negative controls (the table owner and a role with BYPASSRLS) to illustrate the difference between desired tenancy isolation and privilege bypass. Only when each app role sees exactly its own rows and is blocked from others (and attempts to violate the policy fail) do we approve the policy. This data-driven approach, combined with logging and catalog checks, ensures the runtime role’s identity and permissions really match the intended access, rather than assuming “RLS on” is enough.

1. Define the access claim before checking a flag

Before testing, clearly state what the access-control policy should do. For each application role, identify:

• Authorized row set: which row IDs it should see.

• Allowed actions: which mutations (INSERT/UPDATE/DELETE) on its rows, and which should be forbidden.

• Runtime identity: which database role or session context the application actually uses.

• Policy existence vs enforcement: RLS must be both present and active for that role. By default, even with RLS enabled, the table owner and superusers bypass it. And if no policy covers a role, default-deny applies.

For example, in a two-tenant app with roles app_a and app_b, we claim “app_a may see IDs {101, 102} and modify only those rows; app_b may see {201, 202} and modify only those; the owner role’s own row {901} is separate.” The policy itself will only name app_a and app_b (plus the owner for uniformity) in its target list. We must verify that setup actually enforces this: any deviation triggers a REPAIR or HOLD decision. The possible outcomes of testing are:

• ACCEPT: All expected visible rows and allowed mutations match the declaration, and forbidden actions are reliably blocked.

• REPAIR: Some policy or privilege is incorrect or missing; fix and retest.

• HOLD: A needed path or evidence is missing (e.g. we can’t fully test the real login); do not approve without clarity.

• RECONCILE: Evidence indicates data was exposed or changed outside policy; involve data owners and forensic review.

In short, don’t just check the RLS flag. Define the contract and collect evidence. Only then do we decide ACCEPT or take corrective action.

2. Pin an isolated cluster and separate the identities

All experiments must run in a disposable, single-node PostgreSQL 18 cluster, with no external connections. Record the exact software versions (for example, PostgreSQL 18.1 on Linux with psql 18.1). We use Unix-socket or local TCP (localhost) authentication only. The manifest should include the PostgreSQL version (SELECT version()), OS release, and any container/image digests used.

We start with an administrator or bootstrap session (e.g. postgres superuser) to create roles and schema. Then each application role is tested through its own login or by a distinct session with SET ROLE to that user. The key principle is that the test session’s effective role must match how the application authenticates. A privileged admin’s SET ROLE app_a; SELECT... can illustrate expected behavior, but it does NOT prove how the app user behaves in production. We ensure at least one full connection as each role (for instance, psql -U app_a -d testdb) to confirm the real session identity.

By keeping sessions isolated, we avoid subtle cross-effects. Privilege setup (grants, schema ownership) is done in the bootstrap phase. Then disconnect that session. All policy testing uses independent connections. This clean separation prevents “if we forget to change back” issues.

2.1 Provisioning authority is not the acceptance identity

The roles that create or grant privileges (e.g. the initial DBA or superuser) are not used by the application. For example, we create the tenant table and grant SELECT/INSERT privileges to app_a and app_b in an admin session. But later we do not simply RESET ROLE app_a in that session and conclude “test passed.” Instead, we log in as app_a. This distinction matters because session_user remains the bootstrap user when using SET ROLE, whereas the app’s connection would have session_user = app_a. We verify both session_user and current_user in each test to be sure the effective identity is correct:

-- In a new connection as app_a:
SELECT session_user, current_user;
 session_user | current_user 
--------------+--------------
 app_a        | app_a

-- If we had done this in admin then SET ROLE:
-- session_user would still be admin, current_user=app_a

This confirms we are truly testing the app’s login, not an admin-emulated role.

By using isolated sessions for each role’s operations, we ensure that the test environment matches the “real path” the application takes to the database. If the application uses password auth or a specific connection string, tests should mirror that. Any discrepancy (for example, a connection pool sharing a session) should be noted as HOLD, since we haven’t verified it.

3. Build the five-row tenant oracle

We now create a simple tenant table and seed data. All SQL below is for PostgreSQL 18. We define:

-- 1) Create roles (non-superuser owner, tenant roles, and an admin with bypass)
CREATE ROLE rls_owner WITH LOGIN;           -- table owner, no bypass/no super
CREATE ROLE app_a WITH LOGIN;              -- application role A
CREATE ROLE app_b WITH LOGIN;              -- application role B
CREATE ROLE admin_role WITH LOGIN BYPASSRLS; -- admin with BYPASSRLS (not superuser)

-- 2) Create schema/table under rls_owner
SET ROLE rls_owner;
CREATE SCHEMA tenant_schema;
CREATE TABLE tenant_schema.tenants (
    id           INT PRIMARY KEY,
    tenant_role  TEXT NOT NULL,
    data         TEXT NOT NULL
);

-- 3) Populate with 5 rows (two for app_a, two for app_b, one for rls_owner)
INSERT INTO tenant_schema.tenants (id, tenant_role, data) VALUES
  (101, 'app_a',   'data A1'),
  (102, 'app_a',   'data A2'),
  (201, 'app_b',   'data B1'),
  (202, 'app_b',   'data B2'),
  (901, 'rls_owner', 'owner data');

-- 4) Grant privileges to tenant roles
GRANT SELECT, INSERT, UPDATE, DELETE ON tenant_schema.tenants TO app_a;
GRANT SELECT, INSERT, UPDATE, DELETE ON tenant_schema.tenants TO app_b;
-- (Admin has BYPASSRLS but no need for grants as a superuser-equivalent)
RESET ROLE;

At this point, current_user = rls_owner for the above creation, and the table’s owner is the non-privileged rls_owner. (By default rls_owner bypasses RLS, but we will use FORCE to change that later.) We deliberately keep the schema separate (though any schema works) and use a distinct table owner so our tests isolate policy effects.

We explain the tenant logic: using a tenant_role text column equal to the role’s name is a lab simplification. In a real system, tenant identity might come from authentication or a JWT claim, not a row. Here we use this column and the current_user setting so that the RLS predicate is simply tenant_role = current_user. This avoids having to manage an application-specific user mapping in the database.

Finally, we enable RLS and create the policy:

ALTER TABLE tenant_schema.tenants ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_policy ON tenant_schema.tenants
    TO app_a, app_b, rls_owner
    USING (tenant_role = current_user)
    WITH CHECK (tenant_role = current_user);

This policy applies to all commands (the default) for roles app_a, app_b, and rls_owner. It says a row is visible if tenant_role = current_user, and likewise a new/updated row must also satisfy that check, or else the action fails. By default WITH CHECK defaults to the same expression if omitted, but we write it explicitly for clarity. Note that no policy is defined for PUBLIC or other roles. The policy’s role list is exactly these three, which we record in the manifest (TO app_a, app_b, rls_owner). Non-listed roles (unless BYPASSRLS/super) will have no matching policy (see Section 8).

At this point, the schema, data, roles, and policy are fully defined. We have an “oracle” of five rows and know which IDs each role should see or modify:

• app_a ⇒ visible IDs {101,102}

• app_b ⇒ visible IDs {201,202}

• rls_owner (owner) ⇒ normally all (101,102,201,202,901), until forced RLS in a later step.

• admin_role (BYPASSRLS) ⇒ all by bypass.

• No other role has any privilege to do anything with this table.

With the predicate and grants set, we can test.

4. Inspect ownership, attributes, membership, and grants

Before running queries, we validate our setup via the catalogs. We confirm the table owner, RLS flags, role attributes, and privileges.

Role attributes: Query the built-in roles table. We expect rls_owner, app_a, app_b as non-super, non-bypass roles; admin_role with BYPASSRLS. For example:

SELECT rolname, rolsuper, rolbypassrls
FROM pg_roles
WHERE rolname IN ('rls_owner','app_a','app_b','admin_role');

-- Expected output (each row):
--  rolname   | rolsuper | rolbypassrls
-- ----------+----------+--------------
--  rls_owner | f        | f
--  app_a     | f        | f
--  app_b     | f        | f
--  admin_role| f        | t

This shows that none of our roles are superuser (rolsuper=false) and only admin_role has rolbypassrls=true. By default, new roles have NOBYPASSRLS unless explicitly given. So indeed only admin_role bypasses RLS.

Table ownership and RLS flags: Check the pg_class catalog for our table:

SELECT relname, relowner::regrole, relrowsecurity, relforcerowsecurity
FROM pg_class
JOIN pg_namespace ON relnamespace = pg_namespace.oid
WHERE pg_namespace.nspname='tenant_schema' AND relname='tenants';

-- Expected:
--  relname  | relowner  | relrowsecurity | relforcerowsecurity
-- ----------+-----------+---------------+--------------------
--  tenants  | rls_owner | t             | f

We should see relrowsecurity = true (we enabled it) and relforcerowsecurity = false (we have not applied FORCE yet). The relowner is rls_owner, matching our setup. This confirms RLS is on but not forced on the owner.

Policy listing: The system view pg_policies lets us inspect the policy definition:

SELECT policyname, cmd, roles, qual, with_check
FROM pg_policies
WHERE tablename='tenants';

-- Expected (one row):
-- policyname | tenant_policy
-- cmd        | ALL
-- roles      | {app_a,app_b,rls_owner}
-- qual       | (tenant_role = current_user)
-- with_check | (tenant_role = current_user)

This matches our CREATE POLICY arguments: the roles list shows all three, the USING and WITH CHECK clauses are recorded (as text expressions).

Table privileges: Even with RLS, SQL privileges still apply. Confirm each role has SELECT/INSERT/UPDATE/DELETE:

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema='tenant_schema' AND table_name='tenants'
  AND grantee IN ('app_a','app_b');

-- Expected:
-- grantee | privilege_type
-- --------+----------------
-- app_a   | SELECT
-- app_a   | INSERT
-- app_a   | UPDATE
-- app_a   | DELETE
-- app_b   | SELECT
-- app_b   | INSERT
-- app_b   | UPDATE
-- app_b   | DELETE

rls_owner as the table owner implicitly has all privileges, and admin_role (superuser/BYPASS) can access everything. This confirms our GRANTs. Note: even with a correct policy, if a user lacked SELECT privilege, RLS wouldn’t grant it. So privileges and policies are separate layers, and we verify both.

4.1 Separate table privilege from row permission

It’s important to understand that RLS does not replace GRANT/REVOKE. A policy cannot grant access to someone who lacks basic table privileges. Conversely, GRANTing SELECT to app_a doesn’t let them see any rows unless the RLS policy allows it. For example, if we mistakenly revoked SELECT from app_b, they couldn’t query the table at all, policy or not. To illustrate, if we had run REVOKE SELECT ON tenants FROM app_a; then even a query like SELECT * FROM tenants; as app_a would error “permission denied,” regardless of policy. Conversely, if no policy applied (e.g. app_b not in any policy), then even with SELECT privilege app_b would see no rows (see Section 8). Thus our acceptance test must consider both privilege and RLS permission. We will not rely on grant tests alone; we rely on the row-level results from actual queries.

5. Reproduce the owner-bypass false assurance

Now we simulate each role’s view under the current RLS setting (enabled, but no FORCE yet). We run the same SELECT query from each role. For clarity, we list only IDs (and tenant column) ordered by ID.

Expected visible IDs without FORCE:

• app_a: {101, 102}

• app_b: {201, 202}

• rls_owner (owner, bypass): {101, 102, 201, 202, 901}

• admin_role (BYPASSRLS): {101, 102, 201, 202, 901}

We test these:

-- As app_a:
SET ROLE app_a;
SELECT id, tenant_role, data FROM tenant_schema.tenants ORDER BY id;

 id  | tenant_role |    data    
-----+-------------+------------
 101 | app_a       | data A1
 102 | app_a       | data A2
(2 rows)

Only IDs 101 and 102 appear, which matches the policy for app_a.

-- As app_b:
RESET ROLE;
SET ROLE app_b;
SELECT id, tenant_role, data FROM tenant_schema.tenants ORDER BY id;

 id  | tenant_role |    data    
-----+-------------+------------
 201 | app_b       | data B1
 202 | app_b       | data B2
(2 rows)

Only 201 and 202, as expected for app_b.

-- As owner (rls_owner) before FORCE:
RESET ROLE;
SET ROLE rls_owner;
SELECT id, tenant_role, data FROM tenant_schema.tenants ORDER BY id;

 id  | tenant_role |    data    
-----+-------------+------------
 101 | app_a       | data A1
 102 | app_a       | data A2
 201 | app_b       | data B1
 202 | app_b       | data B2
 901 | rls_owner   | owner data
(5 rows)

The owner sees all 5 rows. This shows RLS is currently bypassed for the table owner, as documented. If we had only checked the count or existence of RLS, we might mistakenly think the owner’s view is “normal.” But in fact the owner can see every tenant’s data, which is usually undesirable for strict tenant isolation. This illustrates that owner bypass gives a false sense of security. The owner is supposed to be a privileged user, not a stand-in for a tenant.

-- As admin_role (BYPASSRLS):
RESET ROLE;
SET ROLE admin_role;
SELECT id, tenant_role, data FROM tenant_schema.tenants ORDER BY id;

 id  | tenant_role |    data    
-----+-------------+------------
 101 | app_a       | data A1
 102 | app_a       | data A2
 201 | app_b       | data B1
 202 | app_b       | data B2
 901 | rls_owner   | owner data
(5 rows)

As expected, admin_role (with BYPASSRLS) also sees all rows. We note that neither of these privileged roles should be used by the application for tenant operations; their wide access is not evidence of correct isolation.

Observation: In this disabled-owner (but enabled-RLS) scenario, each app role saw only its own tenant’s rows, which is correct. The owner’s broad view reminds us that we need to restrict the owner’s bypass. Only after we apply FORCE (next) will the owner be treated like any app user. For now, we record that under current config, a select by owner or BYPASSRLS user is not trustworthy evidence of isolation.

6. Apply FORCE and rerun the role matrix

We now require the table owner to obey RLS by issuing ALTER TABLE... FORCE ROW LEVEL SECURITY. This makes rls_owner subject to policies. We leave the policy unchanged. Now rerun the SELECTs:

ALTER TABLE tenant_schema.tenants FORCE ROW LEVEL SECURITY;

-- As app_a, no change expected:
SET ROLE app_a;
SELECT id, tenant_role FROM tenant_schema.tenants ORDER BY id;
 id  | tenant_role
-----+-------------
 101 | app_a
 102 | app_a
(2 rows)

-- As app_b, no change expected:
RESET ROLE;
SET ROLE app_b;
SELECT id, tenant_role FROM tenant_schema.tenants ORDER BY id;
 id  | tenant_role
-----+-------------
 201 | app_b
 202 | app_b
(2 rows)

-- As owner (rls_owner) after FORCE:
RESET ROLE;
SET ROLE rls_owner;
SELECT id, tenant_role FROM tenant_schema.tenants ORDER BY id;
 id  | tenant_role
-----+-------------
 901 | rls_owner
(1 row)

Now the owner only sees its own row {901}, following the exact same rule as for other roles. This shows FORCE ROW LEVEL SECURITY did limit the owner, as documented.

-- Admin_role (BYPASSRLS) still sees all:
RESET ROLE;
SET ROLE admin_role;
SELECT id, tenant_role FROM tenant_schema.tenants ORDER BY id;
 id  | tenant_role
-----+-------------
 101 | app_a
 102 | app_a
 201 | app_b
 202 | app_b
 901 | rls_owner
(5 rows)

The BYPASSRLS role is unaffected by FORCE (as expected).

Analysis: After forcing RLS, rls_owner no longer has elevated row visibility. However, this is a cosmetic fix for tests: in a real system, the owner role still could drop the policy or disable RLS entirely. Thus even with FORCE, we should not assume rls_owner is a safe tenant identity. We consider rls_owner a human DBA or schema owner, not an application user. The real tenant principals are app_a and app_b.

6.1 What FORCE changes and what it cannot prove

Applying FORCE ROW LEVEL SECURITY guarantees that in this test run, the owner behaves like an ordinary role. But it cannot prove that policies themselves are correct or that an attacker with object-owner rights can’t re-enable bypass. It also doesn’t affect bypass-privileged users. In effect, FORCE only turns the owner into a normal user for the purpose of selection and modification. It does not change that the owner (or superuser/BYPASS roles) still have DDL ability on the table. We should not consider “owner only sees 901” as a final security guarantee; it just shows how the policy operates when the owner is forced.

In short: Forcing RLS is a useful test tool, but the real test is always how the application’s actual login behaves. We should later consider whether the table should be owned by a benign role (like a dedicated schema owner) or restrict its use. For now, we move on, noting that the policy with FORCE now logically isolates tenants at the data level.

7. Validate writes against the same contract

Row security also affects DML (INSERT/UPDATE/DELETE) via the USING and WITH CHECK expressions. We now test modifications. For each role app_a and app_b, we check:

• Allowed insert into own tenant: e.g. app_a inserting a new row with tenant_role='app_a'.

• Denied insert into other tenant: app_a inserting with tenant_role='app_b'.

• Invisible update (no effect): app_a updating a row owned by app_b should touch 0 rows (silently ignored).

• Changing tenant on owned row: app_a updating its own row’s tenant to 'app_b' should error (WITH CHECK fails).

• Invisible delete (no effect): app_a deleting app_b’s row should delete 0 rows.

We run each in isolation. We restore the table to the original five-row state between cases (we show it conceptually; in a script you’d use BEGIN/ROLLBACK).

-- TEST: allowed insert for app_a
RESET ROLE;
SET ROLE app_a;
BEGIN;
INSERT INTO tenant_schema.tenants (id, tenant_role, data)
  VALUES (150, 'app_a', 'new A3');
COMMIT;
-- The INSERT should succeed (1 row inserted).
-- Verify via admin observer:
RESET ROLE;
SET ROLE admin_role;
SELECT id, tenant_role FROM tenant_schema.tenants ORDER BY id;

 id  | tenant_role
-----+-------------
 101 | app_a
 102 | app_a
 150 | app_a    -- newly inserted
 201 | app_b
 202 | app_b
 901 | rls_owner
(6 rows)

The new row (150, app_a) is present, showing app_a could insert its own tenant row. The command reported INSERT 0 1 (omitted above), and there was no error. This matches the policy (WITH CHECK allowed it).

-- TEST: denied cross-tenant insert by app_a
RESET ROLE;
SET ROLE app_a;
BEGIN;
INSERT INTO tenant_schema.tenants (id, tenant_role, data)
  VALUES (151, 'app_b', 'x');
COMMIT;

ERROR:  new row violates row-level security policy for table "tenants"
SQLSTATE: 42501

This insert attempt failed. Because the WITH CHECK expression (tenant_role = current_user) is false ('app_b'!= 'app_a'), PostgreSQL rejects the insert with an RLS violation error (SQLSTATE 42501). No rows were added.

-- TEST: invisible update (does not error) by app_a on app_b's row
RESET ROLE;
SET ROLE app_a;
BEGIN;
UPDATE tenant_schema.tenants
  SET data = 'CHANGED'
  WHERE id = 201;
COMMIT;
-- app_a should see (and affect) 0 rows, not an error.

UPDATE 0

Since row 201 belongs to tenant 'app_b', app_a's USING predicate filters it out. PostgreSQL treats it as “no matching rows” rather than throwing an error. The output UPDATE 0 confirms that 0 rows were updated. (No error occurred; invisible means “zero affected” rather than denied.)

-- TEST: changing tenant on owned row by app_a
RESET ROLE;
SET ROLE app_a;
BEGIN;
UPDATE tenant_schema.tenants
  SET tenant_role = 'app_b'
  WHERE id = 101;
COMMIT;

ERROR:  new row violates row-level security policy for table "tenants"
SQLSTATE: 42501

Here, row 101 (currently tenant 'app_a') is visible to app_a so the update is allowed to target it. However, changing tenant_role to 'app_b' fails the WITH CHECK check. The error is thrown (same SQLSTATE). No rows are changed, and we get UPDATE 0 internally with error.

-- TEST: invisible delete by app_a on app_b's row
RESET ROLE;
SET ROLE app_a;
BEGIN;
DELETE FROM tenant_schema.tenants WHERE id = 202;
COMMIT;

DELETE 0

Row 202 is owned by 'app_b' and is invisible to app_a. So deleting it results in “DELETE 0” with no error, since the delete condition matches nothing. (Again, invisibility yields zero effect, not a privilege error.)

Analogous tests for app_b are symmetric (swap roles). For instance, app_b inserting (205, 'app_b') should succeed, but inserting (206, 'app_a') should error, etc. We would get identical outcomes.

7.1 An invisible update is not the same as a rejected write

The above tests highlight a subtle point: operations on non-visible rows quietly do nothing, whereas violating a policy check raises an error. For example, when app_a tried to update or delete app_b’s rows, the command simply affected 0 rows. No exception was thrown; PostgreSQL acts as if the row did not exist (subject to later permission checks, though no DML was done). This silent behavior contrasts with cross-tenant inserts or tenant changes, which actively fail. In effect, an UPDATE/DELETE on an invisible row is not an outright violation; it’s logically skipped.

When reviewing logs or results, it’s important to distinguish "no rows found" from "access denied". A 0 rows affected result is expected in these negative cases, whereas an error (and its SQLSTATE) indicates a definite policy violation. Our acceptance criteria require that allowed operations succeed (possibly committed) and forbidden ones either fail with error or affect 0 rows (depending on RLS semantics). We record each expected SQLSTATE or row count alongside the intended effect (see ledger in Section 12).

8. Test the absence of an applicable policy

We now validate the default-deny rule by removing our policy (while leaving RLS enabled). This tests the scenario “RLS on but no matching policy.” In practice, a policy that doesn’t list app_a or app_b would similarly result in no access.

ALTER TABLE tenant_schema.tenants DROP POLICY tenant_policy;

With no policy, any normal user should see no rows at all. Test for app_a and app_b:

SET ROLE app_a;
SELECT id FROM tenant_schema.tenants;

 id
----
(0 rows)

SET ROLE app_b;
SELECT id FROM tenant_schema.tenants;

 id
----
(0 rows)

Both return 0 rows, as expected. Even though app_a and app_b still have SELECT privileges on the table, without an applicable policy RLS defaults to deny all rows. No error is thrown; the SELECT is simply empty. This confirms that removing the policy produces a silent zero-result default denial for normal roles.

Importantly, we did not disable RLS, so the policy definitions still exist in our code (we only dropped them). After observing the empty results, we immediately restore the policy as before (and would in a script use DROP... or a transaction if needed, then re-CREATE the policy exactly as in Section 3) before further testing. This ensures we can continue testing the original scenario without manual reset.

9. Verify the connection path the application really uses

Finally, we double-check that our assumed application login matches reality. If an application mistakenly used a privileged user and then SET ROLE, the RLS test above might be invalid. We ensure the app truly connects as app_a or app_b. One way is to inspect session_user during a real login:

$ psql -U app_a -d testdb -c "SELECT session_user, current_user;"

 session_user | current_user
--------------+--------------
 app_a        | app_a

This shows the connection’s authenticated identity is app_a. Contrast this with doing SET ROLE from an admin session:

-- (within psql as admin_role)
SELECT session_user, current_user;
-- shows session_user=admin_role, current_user=admin_role
SET ROLE app_a;
SELECT session_user, current_user;
-- now session_user=admin_role, current_user=app_a

The second case reveals that SET ROLE kept session_user=admin_role. Thus a SET ROLE test did not fully replicate an app login. We therefore rely on actual connections as app_a and app_b.

This mirrors good API/design practice: authentication should happen at the edge (e.g. the app server uses its DB credentials), rather than an admin starting one session and impersonating. We link this to the idea of API authentication and authorization boundaries: just as an API must authenticate the correct user token, our database must use the correct role at login time. In our tests, we verify both the session user and the query results for each role. If the real connection came in as something unexpected, that would be a HOLD condition.

At this point, we have exercised every relevant path: the actual login roles, plus the owner and bypass roles as negative controls. We have collected row sets and DML outcomes for each. The next step is to fix any issues found.

10. Repair the smallest responsible layer

If any test above had failed to meet expectations, we now apply a targeted fix. We consider three layers: authentication/ownership, privileges, and policy logic. Each fix is applied in isolation and then retested:

• Ownership/login: The table owner (rls_owner) is not a tenant, so its bypass was an artifact. One repair could be to change the table’s owner to a neutral role (e.g. a dedicated schema admin with no tenant data). For example:

ALTER TABLE tenant_schema.tenants OWNER TO admin_role;

This ensures no tenant’s personal data is under a “user” account. (Alternatively, we could drop or archive the rls_owner row entirely if appropriate.) Changing ownership may require re-granting permissions afterwards. After changing owner, we would repeat the SELECT tests for rls_owner (if it still exists) and tenant roles to ensure behavior is still correct and that the owner-bypass gap is truly closed.

• Privileges: If we had forgotten to grant a needed permission (e.g. if app_a could not INSERT at all), we would now GRANT it. For instance:

GRANT INSERT ON tenant_schema.tenants TO app_a;

Then rerun the insert tests. The policy would then govern which INSERTs succeed. We double-check information_schema.role_table_grants again if needed. We do not grant more privileges than necessary; for example, we would not grant app_a the right to modify rows belonging to app_b. Privileges and RLS remain separate.

• Policy predicate: Suppose the policy expression were wrong (e.g. a typo in the column name). We would use ALTER POLICY or drop/create to fix it. For example, if we had accidentally allowed 'app_a' = current_user for both USING and WITH CHECK, we would correct it to match the tenant_role column. We keep the policy as restrictive as intended; do not widen it arbitrarily. After any policy change, re-run the entire SELECT and DML matrix.

We apply the minimum repair that makes all tests pass. For instance, if app_b unexpectedly could see app_a’s row 101, it might be because the USING clause was too broad (maybe we omitted CURRENT_USER). The fix would be to tighten that clause, then retest.

Because each fix can affect the entire matrix, we repeat the relevant tests (or ideally, the whole suite) after each change. We do not simply allow all rows with a single “policy to PUBLIC” or INSERT privilege hack. We follow the principle of least privilege and least change. This is akin to secure cloud configuration reviews where one isolates and tests each change before broadening scope.

10.1 Keep the repair reversible without reopening tenant access

All changes in testing should be reversible. For example, rather than deleting the old policy immediately, one might rename it first (if PostgreSQL supported that) or take a dump. Here we can script schema changes and roll back if needed. A good practice is to run fixes on a dev/test copy of the database first.

In our case, if we changed the table owner, we should ensure we can change it back (or re-run the lab script) without loss. We also avoid using irreversible operations like TRUNCATE without backup. Each fix is incremental so that we can observe its effect.

For instance, after granting a missing privilege, we would re-run only the affected tests (e.g. the INSERT tests for that role). Then if needed, revert the grant. We ensure logs or schema versions capture these fixes.

The idea is that at any point we could restore the DB to its original state (policy intact or removed as baseline) if the change was undesired. This may involve transactional DDL (if supported) or simply re-applying the fixture script. Ultimately, we want a clean way to present “before and after” the repair without muddying the evidence.

11. Reconcile records and preserve incident evidence

If our tests revealed that some role had already seen or modified rows it shouldn’t have (for example, if the policy had been off in production), we must treat this as a potential data exposure. In that case, we reconcile: inventory which records each role accessed or changed.

To do this, we collect logs and snapshots. For example, if the app allows it, database audit logs (e.g. pg_audit or log_statement = 'mod') can tell us which rows were returned or modified by each role. If no logs exist, we compare a before/after dump. We should ask the application owners: did the app possibly read or write those out-of-bounds rows? If so, we classify it as an incident.

Importantly, fixing the policy does not erase prior exposure. If app_a could see rows 201,202 before our fix, those users might have been able to download another tenant’s data. We document exactly what was exposed or altered. For example, if app_a managed to INSERT a cross-tenant row (it shouldn’t have, but if a bug allowed it), we note that new row ID. We might need to delete or reconcile such rows with the data owner (e.g. ask tenant B if that inserted row was erroneous).

We keep a clear audit trail: which queries were run, what the expected vs actual outputs were, and time-stamps. If admin_role or the owner was accessing data in violation, note that too; even though they had privileges, it might breach tenant agreements.

If there is evidence of unauthorized access, we mark the change as RECONCILE. The immediate fix is to lock down the policy (REPAIR). Then we work with data owners and possibly legal/compliance teams to remediate (e.g. notify affected customers, do forensic analysis).

At each step, we label states: “Policy enabled” vs “Policy disabled,” “FORCE on/off,” and the observed row sets. If any row shows up in an unexpected result, we must pause and examine it before marking PASS.

12. Make the acceptance decision from a complete ledger

We now compile the evidence into a decision. The ledger should list each test (role and operation), the expected vs actual outcome, and whether it matched the security contract. For clarity, a summary table can help:

The app_b mutation tests follow the same role-swapped pattern. Record every tested outcome in the actual ledger.

If every Allowed row/action was correctly allowed, and every Forbidden row/action was blocked (via no rows or error), then we can ACCEPT the policy. If any mismatch occurred (e.g., an app role saw an extra row or a mutation succeeded when it shouldn’t have), we must REPAIR and retest. If some expected test is missing (e.g. we still can’t simulate the exact app login), we HOLD decision. If there’s evidence that data was touched wrongly in the past, we RECONCILE as above.

All four decision categories are tabulated:

• ACCEPT: All tests passed: permitted operations succeeded exactly once, forbidden ops either affected 0 or threw the right exception.

• REPAIR: Found at least one failing test due to misconfiguration or missing privilege/policy (and not a data leak incident).

• HOLD: The test matrix is incomplete (e.g. missing login path or custom session code) so we cannot fully assert correctness.

• RECONCILE: We saw evidence of a security breach (unauthorized row access/modification) that needs remediation beyond fixing the policy.

12.1 Evidence that blocks approval

For instance, if app_a SELECT had returned 201, that alone blocks approval and demands REPAIR. Or if an INSERT into another tenant had succeeded, that would be a severe failure. Suppose a test had shown UPDATE... WHERE id=201; by app_a unexpectedly changing data; that means the policy is broken. Even if the fix is straightforward, we label it REPAIR (and then re-run tests to get to ACCEPT).

A case that triggers HOLD is if the application’s connection pool uses, say, SET SESSION AUTHORIZATION in an unusual way. If our lab cannot replicate that, we must note the gap.

If admin_role or the owner did something out of bounds (like inserted a tenant record they shouldn’t), that is a risk, but typically those roles are expected to have DB-wide privileges. We don’t automatically FAIL acceptance for admin seeing rows, because by definition they bypass. The important part is that non-privileged roles were tested and found correct.

The decision table above, with expected vs actual, forms our acceptance artifact. Only if the entire ledger is “OK” do we give a green light (ACCEPT). Otherwise, we document next steps (which policy to alter, what remediation needed, etc.) and hold deployment until fixed.

13. Assign ownership to the role-and-policy release

Once the policy and tests are accepted, we formalize handoff. We identify:

• Runtime policy owner: This is typically the security or DBA team responsible for the application. They will maintain the RLS definitions.

• Schema/table owner: If changed (e.g. to admin_role or a dedicated role), we note that. In general, the owner should not be a tenant user. Document who should own the table long-term (for example, security_team role).

• Triggers for review: Any DDL change (ALTER TABLE) or any roster change (new app role, new tenant) should re-trigger this testing process. For example, if a new application role app_c is added, we need a new policy or test. We should re-run at least the SELECT-matrix for new roles.

• Evidence bundle: Store the test scripts, results, and decision table (as above) in version control or a ticket. This serves as proof that we reviewed this change. The minimal bundle would include the SQL setup file, the test script output (or expected results), and the notes on any fixes applied.

• Handoff note: Finally, we write a short note that includes the review date and responsible team, for example: “RLS policy for tenant_schema.tenants reviewed. Verified that app_a sees only IDs 101/102, app_b sees 201/202, with expected denies. Owner rls_owner now under RLS (see FORCE). No issues found. Policy and grants documented.” Any references to policy management (e.g. using backend engineering responsibilities) can guide the ongoing maintenance process.

At this stage we have completed the role-and-policy release. The tested policy is ready for production (pending the above criteria) or otherwise fixed. Future schema or version upgrades must include rerunning this matrix.

14. Develop database access-review skills with Refonte Learning

This kind of disciplined access review and security testing is a core skill for data professionals. Refonte Learning’s Database Administrator Essentials program covers access control and security best practices as part of its hands-on curriculum. The program is a 3-month, part-time training (12–14 hours/week) designed for aspiring DBAs, focusing on topics such as role-based access control, security hardening, backup/recovery, and performance tuning. It emphasizes real-world projects and mentorship (for example, building and testing policies like the ones above). Completing such a program can help you systematically develop the skills to conduct thorough PostgreSQL RLS and privilege reviews under expert guidance.

For readers interested in a structured path, Database Administrator Essentials is one place to gain these competencies (curriculum confirms role-based access control and security among its core subjects). It is an internship-track program that prepares backend engineers to take ownership of these kinds of operational security tasks in their work.

Throughout this lab, we treated RLS not just as a checkbox, but as an access contract to test. By building an independent “oracle” and capturing evidence step-by-step, we ensure no surprises in production. This thorough approach, tied to broader database security and access-control practice, gives the confidence needed to approve an RLS policy change or to flag it for repair.