SQL from zero · Chapter 32 of 37

Invisible walls

By the end of this chapter you can make Postgres itself decide which rows each member may read and write, so a forgotten WHERE 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: Safe and correct
  • 4 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.

Why you need this

Meera looks worried. "Our new mobile API will log in to Postgres as the member role." Every query it sends must end with "only this member's messages". If one query forgets that WHERE, Rohan reads everybody's DMs.

Last chapter's grants cannot help: they work per table, so member may read messages (every row) or not at all. Anya wants the rule inside the database, where nobody can forget it.

What you build: 4 tasks

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

  1. Task 1

    own-messages.sql: each member reads only their own messages

    Run setup.sql first. Turn on row-level security for messages, and add a policy for reading so that a session sees only the messages its member sent or received. The member is the one whose id is in the setting app.user_id.

    The policy is for every role (do not name one). A member with no messages sees no rows.

  2. Task 2

    send-as-yourself.sql: send messages only as yourself

    Add a policy on messages so that a member can send (insert) a message only as themselves: the new row's sender_id must be the id in app.user_id. Being the receiver is not enough, so a message from anya to rohan is refused when rohan is logged in.

    The Check runs own-messages.sql first, then this file, so add only the new policy here.

  3. Task 3

    moderator-reports.sql: own reports for members, all for moderators

    Run setup.sql first. Turn on row-level security for reports, then add two policies. The member role reads only the reports its member filed (reporter_id is the id in app.user_id) and can change none of them. The moderator role reads every report and can update any of them.

    Name the role in each policy (one for member, one for moderator).

  4. Task 4 · interview question

    Interview: force-rls.sql, the walls apply to the owner too

    You are app, the owner of messages, so its policies skip you. Make the rules on messages apply to the table's owner too: with app.user_id set to rohan's id, app then sees only rohan's messages.

    The Check runs own-messages.sql first, then this file.

What you will learn

  • Row-level security: once it is on, a role sees only the rows a policy lets it see.
  • USING and WITH CHECK: one rule for the rows a command can see, one for the rows it writes.
  • FOR and TO: which commands, and whose, a policy covers.
  • The owner and FORCE: why app skips the rules, and how to stop that.

More in Safe and correct

Build "Invisible walls" yourself

MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.