PostgreSQL Row-Level Security: Test With the App Role

Suppose you’re adding an invoices screen to a SaaS app. Your test account can see its invoices, so the release looks ready. Then you realise the test used the same database login that ran the migrations. You still don’t know what happens when a second tenant asks for an invoice it doesn’t own.

Test PostgreSQL row-level security through the connection your app actually uses. Seed two tenants in staging, then ask for both tenants’ rows with the runtime login. Repeat with a different tenant and with no tenant context. A query that returns the right invoice proves little until you’ve asked it to return the wrong one.

First, check who PostgreSQL thinks the app is

PostgreSQL policies add row restrictions on top of ordinary privileges. A superuser or role with BYPASSRLS bypasses them. The table owner ordinarily bypasses them too, unless the table uses FORCE ROW LEVEL SECURITY. Keep the migration owner separate from the app login, and check that the app cannot assume a privileged role. FORCE changes the owner’s policy behavior, but an owner can still alter the table and policies. PostgreSQL documents these exceptions.

On a staging request, run this on the same connection and after any role switch the app normally makes. current_user is the effective role. row_security_active tells you whether row security is active for that table in the current context. If it returns false, investigate the table and role before interpreting a passing isolation test. See the session and access inquiry functions.

SELECT session_user AS login_role,
       current_user AS effective_role,
       row_security_active('app.invoices'::regclass) AS rls_active;

Keep the tenant ID inside the request’s transaction

For a small read-only example, imagine app.invoices has a tenant_id column of type text, and the app role is app_runtime. Run the following as the migration owner on a disposable staging table. The schema usage and table grants are separate from the policy. Read existing policies first on a real schema, because permissive policies can combine with OR and widen visibility.

GRANT USAGE ON SCHEMA app TO app_runtime;
GRANT SELECT ON app.invoices TO app_runtime;
ALTER TABLE app.invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY invoices_tenant_read ON app.invoices
  FOR SELECT TO app_runtime
  USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), ''));

This policy compares the row’s tenant to a connection setting. A missing or empty value becomes NULL, so the equality check doesn’t allow a row. For each request, resolve the tenant from authenticated membership, then set it and run the query in the same explicit transaction. Use a bound parameter for the value in application code. set_config with its third argument set to true limits the setting to the current transaction. PostgreSQL explains the setting functions and policy composition.

BEGIN;
SELECT set_config('app.tenant_id', 'tenant_a', true);
SELECT id, tenant_id FROM app.invoices ORDER BY id;
COMMIT;

If you use PgBouncer transaction pooling, it assigns a server connection for the transaction and releases it when the transaction ends. Put the setup and queries through the same transaction handle. Avoid a session-wide tenant setting, which can outlive the request. Transaction-local settings can revert to a pre-existing session value, so test for leftover state on a reused connection too. PgBouncer’s feature matrix describes the pooling modes. See also PostgreSQL’s transaction-local setting rules.

Give the test two tenants and something to reject

Seed one invoice for each tenant using the fixture role. With the runtime login and the usual pool path, run these checks through the same routes your users call. For the missing-context database check, deliberately omit the setup in a controlled test, even if the application normally rejects that request earlier.

1. With tenant A set, verify that its own invoice is returned. Query tenant B’s invoice by its known ID and expect no row.
2. Switch to tenant B in a new transaction and repeat both checks in reverse. A test pool constrained to one reusable backend can help expose context left on the connection.
3. Start a fresh transaction with no tenant set. Expect no rows from the example policy. Repeat after both a committed request and a rolled-back request.
4. If the app writes invoices, test its actual write policies separately. Use a known foreign row and try to change an owned row’s tenant. Check both the response and the stored state. A policy can hide a target row from an update, while a failed WITH CHECK on a proposed new row raises an error.

If row security is inactive, fix the role or table setup. If the failure follows a reused connection, inspect how the app sets tenant context and binds its queries to the transaction. If an active policy still exposes another tenant’s row, check other permissive policies and any views or privileged functions the route uses. Test those paths directly. For the wider decision about shared databases versus separate tenant infrastructure, see Krova’s tenant isolation guide.

This guide was prepared with AI assistance using the linked documentation. The invoice scenario and SQL are illustrative, and the SQL was not executed for this article.