Skip to content

Multi-tenancy isolation and Postgres row-level security

Row security puts the tenant filter inside the database, for every role except the table's owner, superusers and bypassers.

Predict first

Before you read: predict

ExamHall keeps every university's attempts in one shared table. A migration, run as the login examhall_app, did this:

CREATE TABLE attempts (id int PRIMARY KEY, university_id text NOT NULL, score int);
ALTER TABLE attempts ENABLE ROW LEVEL SECURITY;
CREATE POLICY university_isolation ON attempts
  USING (university_id = current_setting('app.university', true));

The table holds 3 attempts of uni-a and 5 of uni-b. The API logs in as examhall_app too. For a uni-a proctor it runs:

BEGIN;
SELECT set_config('app.university', 'uni-a', true);
SELECT count(*) FROM attempts;
COMMIT;

What does the count return?

Pick one

The 30-second version

Tenancy is a dial: shared tables with a tenant_id column are cheapest, but one missing WHERE leaks every tenant; a database per tenant isolates and costs more. Row-level security adds a policy to every query on a table. Owners, superusers and BYPASSRLS roles skip it unless the owner is forced, so the API logs in as a role owning nothing.

Row security puts the tenant filter inside the database, for every role except the table's owner, superusers and bypassers.

How it works

Step 1 of 12

A dial. Shared tables with a tenant_id column cost least; a schema, a database or a whole stack per tenant isolates more and costs more.
In the kitchen
  • one rack, every item labelled by company; a lockable office; a leased floor with its own mailroom → shared tables with tenant_id; a schema; a database per tenant (step 1)

Analogy: A coworking space's post rack

Small companies rent hot desks, and all their post sits in one shared rack, each item labelled with its company. Reception hands a member only the post labelled with the company on that member's line of today's sign-in sheet.

At the coworking space In Postgres
one rack, every item labelled by company; a lockable office; a leased floor with its own mailroom shared tables with tenant_id; a schema; a database per tenant (step 1)
a house rule pinned up that reception was never told to apply policies on a table without ENABLE (step 4)
reception checks each item's label against today's sign-in line the policy compares the row with the transaction's setting (steps 4, 9)
the operator's staff, who put up the rack, take post freely unless the rules make them sign in the owner bypasses unless FORCE (step 6)
the building's fire officer goes everywhere superusers and BYPASSRLS (step 6)
one company's launch party fills every shared meeting room a noisy neighbour (step 12)

Breaks down when: reception sees a person and can ask who they are. Postgres sees only the setting's value, whoever set it, and with no value it returns no rows without a word. Wrong inference: "the database knows which tenant is calling."

Contrasting case: a company on its own floor with its own mailroom: no shared rack, so nothing to filter (a silo, step 2).

In real terms: row security is a WHERE clause the database adds for you, to each query of every role that does not own the table, is not a superuser and lacks BYPASSRLS.

Examples, run for real

4.1 ExamHall EXH-2720: an endpoint without its WHERE

The proctor console's new "recent attempts" endpoint shipped without WHERE university_id = $1, and the API logged in as the migration role. MR !3020 enables row security on attempts with a policy on app.university, moves the API to a login examhall_api that owns nothing, and forces the table so the migration role examhall_owner obeys too. The national exam board keeps its own database, so no policy is involved there (step 2).

4.1 before (no row security, API as examhall_owner): uni-c proctor's list 9 rows · from other universities 6
4.1 after MR !3020 (policy, FORCE, API as examhall_api): uni-c list 3 rows · from other universities 0
4.1 after MR !3020, other logins: examhall_owner 3 rows (forced) · postgres (superuser) 9 rows · a request that sets no university 0 rows

The endpoint still has no WHERE; the policy supplies it (steps 3–4). The superuser still sees everything, so no application traffic logs in as one (step 6).

4.2 Ticketing: a request with no organiser, and the export that needs them all

A box-office platform's orders policy reads app.organiser_id. The nightly export needs every organiser's orders, and a first draft opened a hole in the policy for it:

4.2 box_api, no organiser set · policy as written: 0 rows · with "current_setting(...) IS NULL OR" added: 12 rows of 3 organisers
4.2 export as box_export (BYPASSRLS), policy as written: 12 rows · box_api with no organiser set: 0 rows

The hole lets any request that forgets its setting read every organiser (step 9). A separate login with BYPASSRLS gives the export what it needs, and the API stays closed (step 6).

Picture it

three bays side by side, separated by tall white partition walls, all open to one front aisle. Left: a man in a green jacket pushes a flat trolley carrying a green crate past green shelving stacked with boxes. Middle: a man in a blue suit reaches into an open grey-green locker holding blue boxes. Right: a woman in a green jacket pulls open a drawer of a tall blue filing cabinet holding folders.
An illustration to help it stick. The mechanism is in the sections above.

Try it

Answer first, then check. Nothing is saved.

Question 1 of 7

A box-office platform keeps every event organiser's tickets in one tickets table. The table's owner set up:

ALTER TABLE tickets ENABLE ROW LEVEL SECURITY;
CREATE POLICY organiser_isolation ON tickets
  USING (organiser_id = current_setting('app.organiser_id')::int);
GRANT SELECT, UPDATE ON tickets TO box_api;

The API logs in as box_api, which owns nothing. Ticket 5 belongs to organiser 4. Organiser 4's support tool tries to hand it over to organiser 9:

BEGIN;
SELECT set_config('app.organiser_id', '4', true);
UPDATE tickets SET organiser_id = 9 WHERE id = 5;

What does the UPDATE do?

Pick one

Question 2 of 7

The box-office orders table holds 20,000 orders from 200 organisers, 100 each, with an index on organiser_id. Its owner enabled row security with this policy and ran ANALYZE orders:

CREATE POLICY organiser_isolation ON orders
  USING (organiser_id = current_setting('app.organiser_id')::int);

The API logs in as box_api, which owns nothing. It sets app.organiser_id to 17 for the transaction and runs EXPLAIN SELECT count(*) FROM orders;.

What does the plan show for the organiser check?

Pick one

Question 3 of 7

The box-office events table has row security enabled and two policies:

CREATE POLICY organiser_isolation ON events
  USING (organiser_id = current_setting('app.organiser_id')::int);
CREATE POLICY published_read ON events FOR SELECT
  USING (published);

Organiser 4 has 5 events, 1 of them published. Organiser 9 has 4, 2 of them published. Organiser 12 has 3, 1 of them published. The API logs in as box_api, which owns nothing, and runs for organiser 4: SELECT count(*) FROM events;.

What does it return?

Pick one

Question 4 of 7

A box-office platform serves 1,200 small event organisers and one national stadium operator. The stadium's on-sale days bring more load than all the small organisers together.

Which isolation plan fits?

Pick one

Question 5 of 7

EXH-2721: ExamHall's per-university results report logs in as examhall_reports, which owns no table and is not a superuser. The migration role examhall_owner set up the attempts table with:

ALTER TABLE attempts ENABLE ROW LEVEL SECURITY;
ALTER TABLE attempts FORCE ROW LEVEL SECURITY;
CREATE POLICY university_isolation ON attempts
  USING (university_id = current_setting('app.university', true));
GRANT SELECT ON attempts TO examhall_reports;

The table holds 3 attempts of uni-a and 5 of uni-b. For uni-a, the report runs SELECT count(*) FROM attempts; in a transaction that first sets app.university to uni-a with set_config(…, true), and gets 8.

Which one change, made by a database administrator, makes the report count only uni-a's 3?

Pick one

Question 6 of 7

A payments platform's migration, run as the owner role ledger_owner, contains:

ALTER TABLE payouts ENABLE ROW LEVEL SECURITY;
CREATE POLICY merchant_isolation ON payouts TO ledger_api
  USING (merchant_id = current_setting('app.merchant_id')::int);
CREATE VIEW payout_totals AS
  SELECT merchant_id, sum(amount) AS total FROM payouts GROUP BY merchant_id;
GRANT SELECT ON payouts, payout_totals TO ledger_api;

The merchant dashboard logs in as ledger_api, sets app.merchant_id per transaction and reads payout_totals. Merchant 3's dashboard lists a total for each of the platform's 5 merchants.

Which one change makes it list merchant 3's total and no other?

Pick one

Question 7 of 7

A team-chat service keeps every workspace's members in one members table (workspace_id, email). Row security is on, with the policy USING (workspace_id = current_setting('app.workspace_id')::int), and the API's role owns nothing. The table needs one constraint so that a workspace never holds the same email twice, and adding a member must reveal nothing about another workspace's members.

Which setup, with the insert the API would use, meets both?

Pick one

Go deeper

Pro tips
  1. Run the integration tests as the API's own login, and keep one test that opens a transaction with no tenant set and expects zero rows. When: a new table, policy or login is added (steps 6, 9). Status: RAN (PostgreSQL 18.3 via PGlite; the same counts on postgres:17-alpine, crosscheck-pg17.mjs).
Source

PostgreSQL is quoted from the version 18 pages (the graded engine is 18.3); each quoted passage also appears verbatim in the version 17 page of the same name (sources-check.mjs, all five pages; the version 17 CREATE ROLE page, pg-createrole.txt, was fetched in the R1 fix round on 2026-10-03), the version of the local cross-check image. Valid as of 2026-10-03.

  • PostgreSQL 18 documentation, 5.9. Row Security Policies: https://www.postgresql.org/docs/18/ddl-rowsecurity.html (pg18-rowsecurity.txt). The curriculum row's source. Quoted: "If no policy exists for the table, a default-deny policy is used, meaning that no rows are visible or can be modified"; "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"; permissive policies are "combined using the “OR” Boolean operator"; "Referential integrity checks, such as unique or primary key constraints and foreign key references, always bypass row security".
  • PostgreSQL 18 documentation, CREATE POLICY: https://www.postgresql.org/docs/18/sql-createpolicy.html (pg18-createpolicy.txt). Quoted: "Note that row-level security must be enabled on the table (using ALTER TABLE ... ENABLE ROW LEVEL SECURITY) in order for created policies to be applied"; "If only a USING clause is specified, then that clause will be used for both USING and WITH CHECK cases"; the USING expression "will be added to queries that refer to the table if row-level security is enabled".
  • PostgreSQL 18 documentation, CREATE VIEW: https://www.postgresql.org/docs/18/sql-createview.html (pg18-createview.txt). Quoted: "If any of the underlying base relations has row-level security enabled, then by default, the row-level security policies of the view owner are applied".
  • PostgreSQL 18 documentation, SET: https://www.postgresql.org/docs/18/sql-set.html (pg18-set.txt). The curriculum row's source. Quoted: "The effects of SET LOCAL last only till the end of the current transaction, whether committed or not." Supports step 9; the pooled-connection consequence is the pooling record's.
  • PostgreSQL 18 documentation, CREATE ROLE: https://www.postgresql.org/docs/18/sql-createrole.html (pg18-createrole.txt). Quoted: "These clauses determine whether a role bypasses every row-level security (RLS) policy."
  • AWS whitepaper, SaaS Tenant Isolation Strategies: Isolating Resources in a Multi-Tenant Environment (published 2020-08-01, marked "for historical reference only" by AWS): https://docs.aws.amazon.com/whitepapers/latest/saas-tenant-isolation-strategies/saas-tenant-isolation-strategies.html and its pages Silo isolation, Pool isolation, The bridge model, Tier-based isolation, Isolation: Security or noisy neighbor?, Pooled storage isolation strategies and Application-enforced pool isolation (aws-*.txt). The curriculum row's source, and the only source that keys an item. Quoted, as of 2026-10-03: "a mix of silo and pool isolation models that have been offered up as tiers to our tenants"; "The more resources are shared, the more chances there are for one tenant to impact the experience of another"; "if you have a thousand tenants, that would likely begin to impact operational efficiency and agility"; "Silo addresses this concern by offering a dedicated environment with no potential of noisy neighbor scenarios".

Related lessons

Checked against the tools and docs as of 2026-10-03.