PostgreSQL row-level security isolation lab

Exercises row-level security, transaction-local tenant context, pooled connections, and negative cross-tenant cases.

Application Security 3 min read

Implementation: Tested

Implementation

1\set ON_ERROR_STOP on
2 
3CREATE ROLE tenant_migrator NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOBYPASSRLS;
4CREATE ROLE tenant_runtime NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOBYPASSRLS;
5\getenv tenant_app_password POSTGRES_PASSWORD
6CREATE ROLE tenant_app LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOBYPASSRLS
7    PASSWORD :'tenant_app_password';
8GRANT tenant_runtime TO tenant_app;
9\unset tenant_app_password
10 
11CREATE SCHEMA app AUTHORIZATION tenant_migrator;
12GRANT USAGE ON SCHEMA app TO tenant_runtime;
13 
14SET ROLE tenant_migrator;
15 
16CREATE TABLE app.customer_record (
17    id uuid PRIMARY KEY,
18    tenant_id uuid NOT NULL,
19    display_name text NOT NULL,
20    created_at timestamptz NOT NULL DEFAULT clock_timestamp()
21);
22 
23ALTER TABLE app.customer_record ENABLE ROW LEVEL SECURITY;
24ALTER TABLE app.customer_record FORCE ROW LEVEL SECURITY;
25 
26CREATE POLICY tenant_isolation ON app.customer_record
27    FOR ALL
28    TO tenant_runtime, tenant_migrator
29    USING (
30        tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid
31    )
32    WITH CHECK (
33        tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid
34    );
35 
36GRANT SELECT, INSERT, UPDATE, DELETE ON app.customer_record TO tenant_runtime;
37RESET ROLE;
38 
39COMMENT ON POLICY tenant_isolation ON app.customer_record IS
40    'Fail-closed tenant context: absent context sees no rows; malformed UUID raises an error; writes require the active tenant.';

1\set ON_ERROR_STOP on
2\pset pager off
3 
4CREATE OR REPLACE FUNCTION pg_temp.assert_equal(actual bigint, expected bigint, message text)
5RETURNS void LANGUAGE plpgsql AS $$
6BEGIN
7  IF actual IS DISTINCT FROM expected THEN
8    RAISE EXCEPTION 'assertion failed: % (actual %, expected %)', message, actual, expected;
9  END IF;
10END;
11$$;
12 
13SET ROLE tenant_runtime;
14 
15BEGIN;
16SELECT set_config('app.tenant_id', '11111111-1111-1111-1111-111111111111', true);
17INSERT INTO app.customer_record (id, tenant_id, display_name)
18VALUES ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '11111111-1111-1111-1111-111111111111', 'Tenant A record');
19COMMIT;
20 
21BEGIN;
22SELECT set_config('app.tenant_id', '22222222-2222-2222-2222-222222222222', true);
23INSERT INTO app.customer_record (id, tenant_id, display_name)
24VALUES ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', '22222222-2222-2222-2222-222222222222', 'Tenant B record');
25COMMIT;
26 
27BEGIN;
28SELECT set_config('app.tenant_id', '11111111-1111-1111-1111-111111111111', true);
29SELECT pg_temp.assert_equal((SELECT count(*) FROM app.customer_record), 1, 'tenant A sees only its row');
30SELECT pg_temp.assert_equal((SELECT count(*) FROM app.customer_record WHERE tenant_id = '22222222-2222-2222-2222-222222222222'), 0, 'cross-tenant select returns no row');
31DO $$
32DECLARE affected bigint;
33BEGIN
34  UPDATE app.customer_record SET display_name = 'blocked update'
35  WHERE id = 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb';
36  GET DIAGNOSTICS affected = ROW_COUNT;
37  PERFORM pg_temp.assert_equal(affected, 0, 'cross-tenant update affects no row');
38END;
39$$;
40ROLLBACK;
41 
42BEGIN;
43SELECT set_config('app.tenant_id', '11111111-1111-1111-1111-111111111111', true);
44DO $$
45BEGIN
46  BEGIN
47    INSERT INTO app.customer_record (id, tenant_id, display_name)
48    VALUES ('cccccccc-cccc-cccc-cccc-cccccccccccc', '22222222-2222-2222-2222-222222222222', 'must fail');
49    RAISE EXCEPTION 'cross-tenant insert unexpectedly succeeded';
50  EXCEPTION
51    WHEN insufficient_privilege THEN
52      RAISE NOTICE 'PASS: cross-tenant insert rejected by WITH CHECK';
53  END;
54END;
55$$;
56ROLLBACK;
57 
58BEGIN;
59RESET app.tenant_id;
60SELECT pg_temp.assert_equal((SELECT count(*) FROM app.customer_record), 0, 'missing tenant context sees no rows');
61DO $$
62BEGIN
63  BEGIN
64    INSERT INTO app.customer_record (id, tenant_id, display_name)
65    VALUES ('dddddddd-dddd-dddd-dddd-dddddddddddd', '11111111-1111-1111-1111-111111111111', 'must fail');
66    RAISE EXCEPTION 'insert without context unexpectedly succeeded';
67  EXCEPTION
68    WHEN insufficient_privilege THEN
69      RAISE NOTICE 'PASS: write without tenant context rejected';
70  END;
71END;
72$$;
73ROLLBACK;
74 
75BEGIN;
76DO $$
77BEGIN
78  PERFORM set_config('app.tenant_id', 'not-a-uuid', true);
79  BEGIN
80    PERFORM count(*) FROM app.customer_record;
81    RAISE EXCEPTION 'malformed tenant context unexpectedly succeeded';
82  EXCEPTION
83    WHEN invalid_text_representation THEN
84      RAISE NOTICE 'PASS: malformed tenant context failed closed';
85  END;
86END;
87$$;
88ROLLBACK;
89 
90BEGIN;
91SELECT set_config('app.tenant_id', '11111111-1111-1111-1111-111111111111', true);
92SELECT pg_temp.assert_equal((SELECT count(*) FROM app.customer_record), 1, 'connection reuse transaction A');
93COMMIT;
94BEGIN;
95SELECT pg_temp.assert_equal((SELECT count(*) FROM app.customer_record), 0, 'SET LOCAL context cleared before reused transaction');
96ROLLBACK;
97 
98RESET ROLE;
99\echo 'PASS: runtime RLS negative tests completed'

1"use strict";
2 
3const assert = require("node:assert/strict");
4const { Pool } = require("pg");
5 
6const password = process.env.POSTGRES_PASSWORD;
7assert.ok(password, "POSTGRES_PASSWORD is required for the disposable pooled-client test");
8 
9const tenantA = "11111111-1111-1111-1111-111111111111";
10const tenantB = "22222222-2222-2222-2222-222222222222";
11const recordA = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa";
12const recordB = "bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb";
13 
14const connection = {
15  host: "127.0.0.1",
16  port: Number(process.env.POSTGRES_PORT || "55432"),
17  database: "tenant_lab",
18  user: "tenant_app",
19  password,
20  application_name: "postgresql-rls-pool-boundary-test",
21  connectionTimeoutMillis: 5000,
22  idleTimeoutMillis: 1000,
23};
24 
25async function backendPid(client) {
26  const result = await client.query("SELECT pg_backend_pid() AS pid");
27  return Number(result.rows[0].pid);
28}
29 
30async function beginTenant(client, tenantId) {
31  await client.query("BEGIN");
32  await client.query("SET LOCAL ROLE tenant_runtime");
33  if (tenantId !== null) {
34    await client.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId]);
35  }
36}
37 
38async function rollback(client) {
39  await client.query("ROLLBACK");
40}
41 
42async function assertMissingContext(client, label) {
43  const context = await client.query(
44    "SELECT current_setting('app.tenant_id', true) AS tenant_id",
45  );
46  assert.ok(
47    context.rows[0].tenant_id === null || context.rows[0].tenant_id === "",
48    `${label}: previous transaction-local tenant context leaked`,
49  );
50  const visible = await client.query("SELECT id FROM app.customer_record ORDER BY id");
51  assert.deepEqual(visible.rows, [], `${label}: missing context must expose no rows`);
52}
53 
54async function verifySinglePhysicalSessionReuse() {
55  const pool = new Pool({ ...connection, max: 1 });
56  try {
57    const first = await pool.connect();
58    let firstPid;
59    try {
60      firstPid = await backendPid(first);
61      await beginTenant(first, tenantA);
62      const visible = await first.query("SELECT id FROM app.customer_record ORDER BY id");
63      assert.deepEqual(visible.rows.map((row) => row.id), [recordA]);
64      await first.query("COMMIT");
65    } finally {
66      first.release();
67    }
68 
69    const reused = await pool.connect();
70    try {
71      assert.equal(await backendPid(reused), firstPid, "max=1 pool should reuse its physical session");
72      await beginTenant(reused, null);
73      await assertMissingContext(reused, "reacquired physical session");
74      await rollback(reused);
75    } finally {
76      reused.release();
77    }
78 
79    const errored = await pool.connect();
80    try {
81      assert.equal(await backendPid(errored), firstPid);
82      await beginTenant(errored, tenantA);
83      await assert.rejects(
84        errored.query("SELECT 1 / 0"),
85        (error) => error && error.code === "22012",
86        "division error should abort the tenant transaction",
87      );
88      await rollback(errored);
89    } finally {
90      errored.release();
91    }
92 
93    const afterError = await pool.connect();
94    try {
95      assert.equal(await backendPid(afterError), firstPid);
96      await beginTenant(afterError, null);
97      await assertMissingContext(afterError, "session after query error");
98      await rollback(afterError);
99    } finally {
100      afterError.release();
101    }
102 
103    const cancelled = await pool.connect();
104    try {
105      assert.equal(await backendPid(cancelled), firstPid);
106      await beginTenant(cancelled, tenantB);
107      await cancelled.query("SET LOCAL statement_timeout = '50ms'");
108      await assert.rejects(
109        cancelled.query("SELECT pg_sleep(2)"),
110        (error) => error && error.code === "57014",
111        "statement timeout should cancel the tenant query",
112      );
113      await rollback(cancelled);
114    } finally {
115      cancelled.release();
116    }
117 
118    const afterCancellation = await pool.connect();
119    try {
120      assert.equal(await backendPid(afterCancellation), firstPid);
121      await beginTenant(afterCancellation, null);
122      await assertMissingContext(afterCancellation, "session after cancellation");
123      await rollback(afterCancellation);
124    } finally {
125      afterCancellation.release();
126    }
127  } finally {
128    await pool.end();
129  }
130}
131 
132async function verifyConcurrentTenants() {
133  const pool = new Pool({ ...connection, max: 2 });
134  let readyCount = 0;
135  let releaseReady;
136  const bothReady = new Promise((resolve) => {
137    releaseReady = resolve;
138  });
139 
140  async function queryAsTenant(tenantId, expectedRecord) {
141    const client = await pool.connect();
142    try {
143      await beginTenant(client, tenantId);
144      const pid = await backendPid(client);
145      readyCount += 1;
146      if (readyCount === 2) {
147        releaseReady();
148      }
149      await bothReady;
150      const visible = await client.query("SELECT id FROM app.customer_record ORDER BY id");
151      assert.deepEqual(visible.rows.map((row) => row.id), [expectedRecord]);
152      await client.query("COMMIT");
153      return pid;
154    } catch (error) {
155      await rollback(client).catch(() => {});
156      throw error;
157    } finally {
158      client.release();
159    }
160  }
161 
162  try {
163    const pids = await Promise.all([
164      queryAsTenant(tenantA, recordA),
165      queryAsTenant(tenantB, recordB),
166    ]);
167    assert.notEqual(pids[0], pids[1], "concurrent tenants must use independent sessions");
168 
169    const clients = await Promise.all([pool.connect(), pool.connect()]);
170    try {
171      await Promise.all(
172        clients.map(async (client, index) => {
173          await beginTenant(client, null);
174          await assertMissingContext(client, `concurrent pooled session ${index + 1}`);
175          await rollback(client);
176        }),
177      );
178    } finally {
179      clients.forEach((client) => client.release());
180    }
181  } finally {
182    await pool.end();
183  }
184}
185 
186async function main() {
187  await verifySinglePhysicalSessionReuse();
188  await verifyConcurrentTenants();
189  console.log(
190    "PASS: pg 8.22.0 pool tests covered physical-session reuse, missing context, " +
191      "errors, cancellation, and concurrent tenants.",
192  );
193}
194 
195main().catch((error) => {
196  console.error(error && error.stack ? error.stack : error);
197  process.exitCode = 1;
198});

Run it

labs/postgresql-rls/run-tests.sh
labs/postgresql-rls/run-tests.ps1

This disposable lab tests a transaction-scoped tenant context, complete USING and WITH CHECK policies, forced row security, and role properties that prevent ordinary application identities from bypassing the boundary.

Prerequisites and tested versions

Docker Engine or Docker Desktop with Compose v2.
PowerShell 7+ on Windows or a POSIX shell on Linux/macOS.
Node.js 22+ and npm for the application integration test.
Maintained PostgreSQL client: pg 8.22.0, lockfile pinned.
Image: postgres:18.4-alpine3.24, pinned to the PostgreSQL 18.4 patch release checked on 2026-07-21. For a long-lived environment, pin the image digest in the deployment's own dependency process.

Run

PowerShell:

./labs/postgresql-rls/run-tests.ps1

POSIX shell:

./labs/postgresql-rls/run-tests.sh

The scripts generate an ephemeral local password, install the lockfile-pinned pg client, start the service, run SQL runtime/boundary/catalog suites plus the pooled-client suite, and remove containers and volumes. The password is not persisted in a tracked file. Pass -Keep or --keep to retain the service for inspection.

Expected output includes PASS messages for cross-tenant reads and writes, mutation of a visible row's tenant key, missing and malformed context, connection reuse, forced RLS for the table owner, catalog flags, non-bypass role attributes, policy count, policy roles, and complete policy expressions. Any SQL error or failed assertion returns a nonzero status.

Security properties exercised

Tenant identity is set with transaction-local set_config(..., true); it does not leak into the next transaction on a pooled connection.
Missing context matches no rows and fails writes. Malformed UUID context raises an error instead of falling back to a broader scope.
Both row visibility and new row values are constrained, including an attempted update that moves a visible row into a different tenant.
FORCE ROW LEVEL SECURITY is exercised while running as the table owner.
Catalog tests reject missing RLS flags, bypass-capable application roles, extra policies, unexpected policy roles, and absent or trivially true expressions.

Application integration boundary

Every request and background job must establish the tenant inside the same database transaction as the protected queries. A background worker is not implicitly a system-wide tenant: it should process one explicit tenant per transaction, or use a separate narrowly scoped maintenance role and separately reviewed policy. Do not place a connection in a pool after a session-scoped tenant setting.

Limitations

The test connects as the local database administrator and uses SET ROLE to exercise non-bypass identities. It does not model managed-service administrator roles, network controls, connection-pool middleware, migration orchestration, replication, backups, side channels, or application authorization. PostgreSQL superusers and roles with BYPASSRLS bypass RLS by design; they require separate administrative controls and monitoring.