Postgres with Drizzle · Chapter 27 of 40
The policy workshop
By the end of this chapter you can put several policies on one table and know how Postgres combines them: owner-only writes, rules that add up, rules that veto, and roles. 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
Meera needs rules with more shape than chapter 26's walls. Anyone may read a post, but only its author may edit it. A feed shows your own posts plus anything popular. A deleted post must never come back, whatever other rule lets it through. Vikram wants the admin tools and the app to run as different database roles.
In one USING condition, full of OR and AND NOT, every new rule means a rewrite, and one misplaced bracket opens the table to everyone. Postgres lets you write one small policy per rule, and combines them for you.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
createOwnerWritePolicy: only the author may edit a post
Write createOwnerWritePolicy(). It inserts two members, user1 and user2 (a username and an email each), and one post by each. Then it turns on row-level security on posts and forces it for the table's owner, and creates two policies: posts_select_all for select, letting every row through (using true), and posts_owner_write for update, letting a row through when its author_id equals current_setting('app.current_user_id', true) as an integer.
Then, as user1, it tries two updates, each in a transaction on a client of its own with app.current_user_id set to user1's id for that transaction: one changes the content of user1's post (where author_id is user1's id), the other changes the content of user2's post (where author_id is user2's id). Commit each, and release each client.
Return an object with ownUpdateCount and otherUpdateCount: the rowCount of each update, as numbers (1 and 0).
- Task 2
createPermissivePolicies: own posts or popular ones
Write createPermissivePolicies(). It inserts user1 and user2, a post by user1 with likes_count 5 and a post by user2 with likes_count 15. Then it turns on row-level security on posts and forces it, and creates two permissive policies for select: posts_author_read, letting a row through when its author_id equals current_setting('app.current_user_id', true) as an integer, and posts_popular_read, letting a row through when likes_count is greater than 10.
Then, on a client of its own: begin, set app.current_user_id to user1's id for this transaction, select all posts, commit, and release the client.
Return an object with visibleCount: how many rows that select returned, a number (2: user1's own post and the popular one).
- Task 3
createRestrictivePolicy: deleted posts stay hidden
Write createRestrictivePolicy(). It inserts one member and two posts by them, then soft-deletes one of the posts (sets its deleted_at to now). Then it turns on row-level security on posts and forces it, and creates two policies for select: posts_all_read, a permissive one letting every row through (using true), and posts_not_deleted, a restrictive one letting a row through only when deleted_at is null. Then it selects all posts.
Return an object with visibleCount: how many rows that select returned, a number (1: only the post that is not deleted).
- Task 4
createRoleBasedPolicies: an admin role and a user role
Write createRoleBasedPolicies(). It creates two roles, app_admin and app_user, unless they already exist: roles belong to the whole server and survive every Check, so the function must work when they are already there. Then it grants app_admin all privileges on posts and users, grants app_user select, insert and update on posts and users, and grants both usage on the public schema and usage on all sequences in the public schema.
Return an object with rolesCreated: true. Afterwards: both roles exist in pg_roles.
- Task 5
getPolicyDetails: a report of every policy
Write getPolicyDetails(tableName). tableName is a string. It reads the policies on that table from the pg_policies view.
Return result.rows: objects with policyname, permissive, roles, cmd, using_expr (the qual column, renamed) and with_check, ordered by policyname. The check creates posts_all_read (select) and posts_owner_write (update) on posts first, and expects exactly those two; posts_all_read must say PERMISSIVE in permissive.
What you will learn
- A policy per command: FOR UPDATE, FOR DELETE, and why a write needs a select policy too.
- Permissive policies: they add up with OR.
- AS RESTRICTIVE: a policy that every row must also pass.
- CREATE ROLE and GRANT, and the permissive and roles columns of pg_policies.
More in Row-Level Security
Build The policy workshop 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.