Postgres with Drizzle · Chapter 28 of 40

The fortress in production

By the end of this chapter you can keep tenants apart with one policy, set "who is asking" safely on pooled connections, find the policy in a query plan, and report which tables are protected. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Row-Level Security
  • 5 tasks
  • PostgreSQL
  • In the all labs plan

The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.

Why you need this

Vikram's first enterprise deal puts several companies on one MingleSphere, each a tenant, and the contract says no tenant may ever see another's members. Chapter 26 filtered by member; now every row carries a tenant, and one policy must guard reads and writes.

Production adds three worries. The app uses a connection pool, so a setting left on a connection travels to the next request, maybe another tenant's. Rohan asks what the policy costs per query. And if someone turns RLS off during a late-night fix, nobody notices until data leaks.

What you build: 5 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    setupMultiTenant: each tenant sees only its members

    Write setupMultiTenant(). It adds a tenant_id integer column to users (if it does not exist yet) and inserts three members: two with tenant_id 1 and one with tenant_id 2 (a username and an email each). Then it turns on row-level security on users and forces it for the table's owner, and creates a policy named users_tenant_isolation, for every command, letting a row through when its tenant_id equals current_setting('app.tenant_id', true) as an integer.

    Then it selects all users twice, each time in a transaction on a client of its own: once with app.tenant_id set to 1 for that transaction, once with it set to 2. Commit each and release each client.

    Return an object with tenant1Visible and tenant2Visible: how many rows each select returned, as numbers (2 and 1).

  2. Task 2

    testCrossTenantIsolation: prove tenant 1 sees only tenant 1

    Write testCrossTenantIsolation(). It sets up the same way as task 1: the tenant_id column, two members of tenant 1 and one of tenant 2, RLS enabled and forced on users, and the users_tenant_isolation policy. Then, in a transaction on a client of its own with app.tenant_id set to 1, it selects all users, commits, and releases the client.

    Return an object with isolated (true when every row the select returned has tenant_id 1 and there are exactly two of them) and visibleCount (how many rows it returned, a number: 2).

  3. Task 3

    rlsWithTransaction: the tenant set with SET LOCAL

    Write rlsWithTransaction(). It adds the tenant_id column and inserts two members, one with tenant_id 1 and one with tenant_id 2, then turns on row-level security on users, forces it, and creates the users_tenant_isolation policy. Then, on a client of its own: begin, set the tenant to 1 with SET LOCAL app.tenant_id, select all users, commit, and release the client.

    Return an object with rowCount (how many rows the select returned, a number: 1) and tenantId (the tenant the select ran as, the number 1).

  4. Task 4

    explainWithRLS: the policy in the query plan

    Write explainWithRLS(). It adds the tenant_id column, inserts one member with tenant_id 1, turns on row-level security on users, forces it, and creates the users_tenant_isolation policy. Then, in a transaction on a client of its own with app.tenant_id set to 1, it runs EXPLAIN (FORMAT JSON) for a select of all users, commits, and releases the client.

    Return an object with plan (the QUERY PLAN value of the result's first row) and hasFilter (true when that plan, turned into a JSON string, contains Filter).

  5. Task 5

    inspectRLSStatus: a status report for users and posts

    Write inspectRLSStatus(). It reads pg_class and pg_policies and returns one row for each of the tables users and posts.

    Return result.rows: objects with table_name, rls_enabled (relrowsecurity), rls_forced (relforcerowsecurity) and policy_count (how many policies pg_policies lists for that table, as a number, 0 when there are none), ordered by table_name. The check first turns on and forces RLS on users with one policy, and expects users enabled with at least one policy and posts not enabled.

What you will learn

  • A policy for every command: one USING that filters reads, updates and deletes, and checks inserts.
  • SET LOCAL and set_config(..., true): a setting that ends with the transaction, and why plain SET leaks.
  • EXPLAIN: the policy appears in the plan as a filter.
  • pg_class and pg_policies: an RLS status report.

More in Row-Level Security

Build The fortress in production yourself

MingleSphereQL grows up: its backend talks to Postgres through Drizzle. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.