Postgres with Drizzle · Chapter 26 of 40

The invisible walls

By the end of this chapter you can make Postgres itself decide which rows a member may see or add, so a forgotten filter in the code cannot leak anyone's data. 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 has signed a company that wants MingleSphere for its five regional offices, on one condition: no office may ever see another office's posts. Meera adds that private drafts must stay visible to their author alone.

The obvious fix is WHERE author_id = ? on every query. MingleSphere has hundreds of queries, and new ones every week. Anya has seen how that ends: one forgotten filter in one endpoint, and one member reads another's data. Row-level security (RLS) moves the rule into the table. Every query against it, from any code or any psql session, is filtered by the table's policies, whether the query asks for it or not.

What you build: 5 tasks

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

  1. Task 1

    enableRLS: turn on row-level security

    Write enableRLS(tableName). tableName is a string, a table's name (the check passes users). It turns on row-level security for that table, then reads the table's relrowsecurity flag from pg_class to confirm it.

    Return an object with enabled: true when relrowsecurity is true for that table. Afterwards: RLS is enabled on the table.

  2. Task 2

    createBasicPolicy: each member sees only their own row

    Write createBasicPolicy(). It inserts two members, alice and bob (a username and an email each), turns on row-level security on users and forces it for the table's owner too, and creates a policy named users_see_own for select that lets a row through when its id equals current_setting('app.current_user_id', true) as an integer.

    Then, on a client of its own: begin, set app.current_user_id to alice's id for this transaction only, select all users, commit, and release the client.

    Return an object with visibleRows: how many rows that select returned, a number (1: only alice).

  3. Task 3

    testDefaultDeny: no policy means no rows

    Write testDefaultDeny(). It inserts one member, then turns on row-level security on users and forces it for the owner, creating no policy at all, and selects all users.

    Return an object with rowCount: how many rows that select returned, a number (0: with no policy, nothing is allowed).

  4. Task 4

    createInsertPolicy: members may only add themselves

    Write createInsertPolicy(). It turns on row-level security on users and forces it, then creates two policies: users_select_all for select, letting every row through (using true), and users_insert_own for insert, with a check that the new row's username equals current_setting('app.current_username', true).

    Then it tries two inserts, each in a transaction on a client of its own with app.current_username set to alice: inserting a member alice must succeed (commit it), and inserting a member eve must fail (catch the error and roll back).

    Return an object with insertAllowed (true when the alice insert worked) and insertBlocked (true when the eve insert threw).

  5. Task 5

    listPolicies: the policies on a table

    Write listPolicies(tableName). tableName is a string. It reads the policies on that table from the pg_policies view.

    Return result.rows: objects with policyname, cmd, qual and with_check, ordered by policyname. The check creates users_see_own and users_insert_own on users first, and expects exactly those two.

What you will learn

  • ENABLE and FORCE ROW LEVEL SECURITY: turn the rules on, also for the table's owner.
  • CREATE POLICY ... USING: which existing rows a query may see.
  • set_config and current_setting: tell Postgres, per transaction, who is asking.
  • WITH CHECK: which new rows an insert may write, and pg_policies to list them.

More in Row-Level Security

Build The invisible walls 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.