PostgreSQL Row-Level Security: Test Tenant Isolation with the Real Role

A row-level security policy can look correct in SQL and still provide no protection in the application's actual connection. Table owners and specially privileged roles can bypass ordinary policy enforcement. Testing as an administrator can therefore give a misleading picture of tenant isolation.

PostgreSQL RLS testing starts with the role the application really uses. Then check reads, inserts, updates, and deletes separately, including denied cases. A policy that protects one SELECT query doesn't automatically protect the whole tenant lifecycle.

Identify the database identity

Record the connecting role, any role switching, table ownership, and privileges. Check whether the application uses a role with special bypass authority. Keep administrative maintenance connections separate from ordinary application traffic.

The PostgreSQL row-security documentation explains policy behaviour and bypass cases. Enabling RLS without understanding the executing role can leave the main application outside the boundary you intended to test.

Don't treat a tenant ID supplied by the caller as trusted identity. The application or platform must establish the user's authorised tenant context through an appropriate mechanism. RLS enforces an expression; it doesn't decide whether an arbitrary input claim is authentic.

Use an isolated example to learn the mechanics

A simple teaching design can associate rows with separate database roles. In a disposable environment, after creating suitable test roles, an illustrative policy is:

CREATE TABLE tenant_notes (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  owner_role name NOT NULL,
  note text NOT NULL
);
ALTER TABLE tenant_notes ENABLE ROW LEVEL SECURITY;
CREATE POLICY notes_by_role ON tenant_notes
  USING (owner_role = current_user)
  WITH CHECK (owner_role = current_user);

This demonstrates a policy condition, not a production multi-tenant architecture. The test roles still need appropriate grants, and the tests must run as those roles rather than the owner. Review identity-sequence permissions where the schema requires them.

Use SQL Formatter to inspect synthetic policy SQL. It doesn't execute policies, inspect privileges, or prove tenant isolation. Run database tests in an environment you control.

Separate visibility from acceptable writes

Read policies determine which rows are visible in the relevant operation. Write checks determine whether new row values are permitted. Test an update that attempts to move a row into another tenant, not only an ordinary edit within the same tenant.

Check insert behaviour with a missing tenant field, an incorrect tenant, and an allowed tenant. Check delete behaviour too. A user shouldn't gain a destructive operation merely because a policy was written with reads in mind.

Review how multiple policies combine according to PostgreSQL's rules. Adding a new policy can broaden or constrain behaviour in ways a reviewer may not expect. Preserve the intended contract and test the combined result, not each policy only in isolation.

Use the platform's authenticated context correctly

If a platform supplies helpers or verified identity context, follow its documented integration. Supabase's RLS guidance discusses its database access patterns and role considerations. Those helpers don't automatically apply to an unrelated plain PostgreSQL connection.

Avoid setting an arbitrary session variable from user input and calling it secure tenant identity. Consider who can set it, how the connection is authenticated, and whether the database trusts the mechanism appropriately. Review the full context-establishment path.

Keep elevated service access out of browsers and untrusted clients. A privileged server path may be necessary for maintenance, but it needs its own application-level restrictions and audit evidence.

Test pooled connections and context reset

Connection pools reuse sessions. Confirm that tenant or role context is established for each request and cleared or scoped correctly afterward. One user's context must not persist into another user's operation.

Use two synthetic tenants and interleave requests. Include errors and cancelled transactions so reset logic is tested beyond the happy path. A normal sequential test can miss the exact reuse pattern that leaks context.

Record the query, executing role, context source, and expected result in fixtures. Keep real customer records out of the test. For sanitised expectations, JSON Formatter can make result contracts readable without becoming a policy validator.

Include a test that runs the same query through the application rather than only a database console. That confirms the connection role and context-establishment code match the isolated policy test. A secure laboratory role does not prove that a production connection uses it.

Check indirect leakage and surrounding queries

Review aggregate results, search, exports, relationships, and error messages. Tenant isolation includes what a user can infer as well as which full rows they can read. Constraints and other database mechanisms may have behaviour that needs separate analysis.

For API exports, see cursor pagination. Every page must preserve authorisation context; a valid cursor isn't permission to access another tenant's rows. Also verify that background jobs use the intended scope.

Run these tests after schema, policy, role, or platform changes. Preserve negative tests as release checks so future refactoring doesn't silently bypass the protection.

Conclusion

Test RLS with the actual application identity and the full operation set. Verify read and write boundaries, trusted tenant context, and pool reuse. A policy is useful only when the deployed connection and surrounding workflow enforce the isolation you intended.