Testing row-level security in CI, the secure way

Row-level security is the kind of feature that works perfectly in the demo, because the demo connects as the table owner, and the table owner walks straight past every policy you wrote. The tests passed. The tests were wrong.

The short answer

Enable and FORCE row level security on every tenant table, read the tenant with current_setting(name, true) so a missing value returns no rows, and use security_invoker views. In CI, run tests as the application's own login: own rows only, no rows without a tenant, no cross-tenant insert or update, and a catalog check that every table has RLS forced.

Updated September 24, 2026Houssam Hammoudi, CTOTested with PostgreSQL 18.6

On this page

What goes wrong
What the docs say
The secure configuration
Prove it
Mistakes people make
Checklist

What goes wrong

Row-level security (RLS) lets Postgres filter rows by a policy, for example "only rows where tenant_id matches the current tenant". It is a strong last line of defense for multi-tenant apps. It also fails silently in several ways.

The table owner, superusers and roles with BYPASSRLS skip policies unless the table is forced. Many apps connect as the owner, so the policy never runs. Views run with their owner's rights by default, so a view can leak rows the table would hide. A table added next quarter has no RLS at all until someone remembers.

None of these produce an error. Queries just return more rows than they should. The only way to know is to test, as the app's own login, every time the schema changes.

What the docs say

Superusers and roles with the BYPASSRLS attribute always bypass the row security system when accessing a table. Table owners normally bypass row security as well, though a table owner can choose to be subject to row security with ALTER TABLE ... FORCE ROW LEVEL SECURITY.

Source: PostgreSQL 18 docs, Row Security Policies

If no policy exists for the table, a default-deny policy is used, meaning that no rows are visible or can be modified.

Source: PostgreSQL 18 docs, Row Security Policies

This option causes the underlying base relations to be checked against the privileges of the user of the view rather than the view owner.

Source: PostgreSQL 18 docs, CREATE VIEW, security_invoker

The docs describe each rule. They do not give you a test that notices when someone breaks one, and that is where multi-tenant leaks come from.

The secure configuration

The schema: a NOLOGIN owner, a separate app login, RLS enabled and forced, a policy that fails closed, and a security_invoker view.

sql

-- schema.sql: a two-tenant table protected by row-level security (the fixed version).
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_user LOGIN PASSWORD 'test-user-pw';
CREATE SCHEMA app AUTHORIZATION app_owner;
GRANT USAGE ON SCHEMA app TO app_user;
SET ROLE app_owner;

CREATE TABLE app.documents (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id int  NOT NULL,
  title     text NOT NULL
);
ALTER TABLE app.documents ENABLE ROW LEVEL SECURITY;
-- FORCE: the policy also applies to the table owner.
ALTER TABLE app.documents FORCE ROW LEVEL SECURITY;
-- current_setting(..., true) returns NULL when the app forgot to set a tenant:
-- NULL never equals anything, so the query sees no rows (fail closed).
CREATE POLICY tenant_isolation ON app.documents
  USING      (tenant_id = current_setting('app.tenant_id', true)::int)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::int);

-- A view runs with its owner's rights unless security_invoker is on.
CREATE VIEW app.document_titles WITH (security_invoker = true) AS
  SELECT id, tenant_id, title FROM app.documents;

GRANT SELECT, INSERT, UPDATE, DELETE ON app.documents TO app_user;
GRANT SELECT ON app.document_titles TO app_user;
RESET ROLE;
INSERT INTO app.documents (tenant_id, title) VALUES (1, 'tenant 1 plan'), (1, 'tenant 1 budget'), (2, 'tenant 2 secrets');

The app sets the tenant per transaction, so a pooled connection never carries it into the next request:

sql

BEGIN;
SELECT set_config('app.tenant_id', '1', true);   -- true = local to this transaction
SELECT * FROM app.documents;
COMMIT;

The test suite. It connects as the app's login, never as a superuser or the owner, and exits non-zero on any failure:

#!/bin/sh
# rls-tests.sh: tenant-isolation tests. Runs as the application's own login, like the app does.
# Usage: PGHOST=... PGDATABASE=... PGUSER=app_user PGPASSWORD=... sh rls-tests.sh
fail=0
q() { psql -X -q -t -A -v ON_ERROR_STOP=1 -c "$1" 2>&1; }
check() { # name, expected, actual
  if [ "$2" = "$3" ]; then echo "PASS  $1"; else echo "FAIL  $1 (expected: $2, got: $3)"; fail=1; fi
}
check "tenant 1 sees only its own rows" "2" \
  "$(q "begin; select set_config('app.tenant_id','1',true); select count(*) from app.documents; commit;" | sed -n 2p)"
check "no tenant set: no rows" "0" \
  "$(q "select count(*) from app.documents;")"
check "tenant 1 cannot insert a row for tenant 2" "ERROR" \
  "$(q "begin; select set_config('app.tenant_id','1',true); insert into app.documents (tenant_id,title) values (2,'x'); commit;" | grep -o '^ERROR' | head -1)"
check "tenant 1 cannot move a row to tenant 2" "ERROR" \
  "$(q "begin; select set_config('app.tenant_id','1',true); update app.documents set tenant_id = 2; commit;" | grep -o '^ERROR' | head -1)"
check "the view applies the policy too" "2" \
  "$(q "begin; select set_config('app.tenant_id','1',true); select count(*) from app.document_titles; commit;" | sed -n 2p)"
check "every table in schema app has RLS enabled and forced" "" \
  "$(q "select string_agg(c.relname, ',') from pg_class c join pg_namespace n on n.oid = c.relnamespace where n.nspname = 'app' and c.relkind = 'r' and not (c.relrowsecurity and c.relforcerowsecurity);")"
check "the app login cannot bypass RLS" "f|f" \
  "$(q "select rolsuper, rolbypassrls from pg_roles where rolname = current_user;")"
exit $fail

In CI, the job needs only a throwaway Postgres, your migrations, a seed with two tenants, and the app's credentials. As shell steps (any CI system):

bash

# Start a disposable database for this job.
docker run -d --name ci-db -e POSTGRES_PASSWORD=ci-only-password -p 127.0.0.1:5432:5432 postgres:18-alpine
until docker exec ci-db pg_isready -q -U postgres; do sleep 1; done
# Migrations and two tenants of seed data, as the admin.
PGPASSWORD=ci-only-password psql -h 127.0.0.1 -U postgres -v ON_ERROR_STOP=1 -f db/migrations.sql -f db/seed-two-tenants.sql
# The suite, as the app. A non-zero exit fails the job.
PGHOST=127.0.0.1 PGDATABASE=postgres PGUSER=app_user PGPASSWORD=test-user-pw sh db/rls-tests.sh
docker rm -f ci-db

Prove it

The suite against a schema with three common mistakes: the app login owns the table without FORCE, the view has no security_invoker, and a second table was added without RLS. Six of seven tests fail:

bash

PGHOST=127.0.0.1 PGDATABASE=postgres PGUSER=app_user PGPASSWORD=test-user-pw sh rls-tests.sh; echo "exit code: $?"

text

FAIL  tenant 1 sees only its own rows (expected: 2, got: 3)
FAIL  no tenant set: no rows (expected: 0, got: 3)
FAIL  tenant 1 cannot insert a row for tenant 2 (expected: ERROR, got: )
FAIL  tenant 1 cannot move a row to tenant 2 (expected: ERROR, got: )
FAIL  the view applies the policy too (expected: 2, got: 4)
FAIL  every table in schema app has RLS enabled and forced (expected: , got: documents,comments)
PASS  the app login cannot bypass RLS
exit code: 1

The view test counts 4 rows because the cross-tenant insert in test 3 really went through. The catalog check names both tables that are not protected. The same suite against the fixed schema:

text

PASS  tenant 1 sees only its own rows
PASS  no tenant set: no rows
PASS  tenant 1 cannot insert a row for tenant 2
PASS  tenant 1 cannot move a row to tenant 2
PASS  the view applies the policy too
PASS  every table in schema app has RLS enabled and forced
PASS  the app login cannot bypass RLS
exit code: 0

The test script is secure-tests/testing-row-level-security-ci/run.sh; the suite itself is rls-tests.sh next to it.

Mistakes people make

Testing as the owner or a superuser

Both bypass RLS by default, so every test passes. Run the suite with the exact login the app uses, and include the rolsuper/rolbypassrls check so a future change to that login fails the build.

ENABLE without FORCE

ENABLE ROW LEVEL SECURITY does not apply to the owner. Add FORCE as well, even if the app does not connect as the owner today.

current_setting without the second argument

current_setting('app.tenant_id') raises an error when the setting is missing, and some code paths "fix" that by setting a default tenant. With true as the second argument it returns NULL, and the policy matches no rows.

Session-level tenant settings with a connection pool

SET app.tenant_id without LOCAL stays on the connection. The next request on that connection runs as the previous tenant. Use set_config(..., true) inside a transaction.

No test for new tables

A new table has no RLS until someone adds it. The catalog check turns "someone remembers" into a failing build.

Checklist

Every tenant table has ENABLE and FORCE ROW LEVEL SECURITY.
Policies use current_setting('app.tenant_id', true) and have WITH CHECK.
Views over tenant tables use security_invoker = true.
The app login is not the owner, not a superuser and not BYPASSRLS.
The tenant is set with set_config(..., true) inside each transaction.
CI runs the suite as the app login against a fresh database with two tenants.
The suite includes a catalog check for tables without forced RLS.
A failing test blocks the merge.

RLS is a promise the database makes on your behalf. A test suite is how you check that it keeps it every time someone changes the schema.

H2-CSSE

Learn it on a live range

Secure APIs and authorization in code, in Secure Software Engineering: a real host in your browser, and every objective checked on the machine.

Start free

The Secure Way

More on databases

Postgres with TLS that verifies, backups you can restore, least-privilege roles and row-level security.

All databases guides

Testing row-level security in CI, the secure way

#What goes wrong

#What the docs say

#The secure configuration

#Prove it

#Mistakes people make

#Testing as the owner or a superuser

#ENABLE without FORCE

#current_setting without the second argument

#Session-level tenant settings with a connection pool

#No test for new tables

#Checklist