Postgres in depth · Chapter 41 of 65
Instructors see only their own revenue
By the end of this chapter you can make one table show each role, and each logged-in user, only the rows that are theirs, and know who skips the rules. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Safety, security and storage
- 4 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
Kabir is opening an instructor dashboard: every instructor sees what their own courses earned. Priya does not want each query in the dashboard to remember a WHERE instructor = ...: one forgotten filter and Neha sees Rahul's money.
Chapter 40's privileges work on whole tables and whole columns. They can say "instructor may read order_items", but not "only the rows of your own courses". For that, Postgres has row-level security (RLS): rules on the table itself that decide which rows exist for whom, whatever query is asked.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
instructor-revenue.sql: each instructor's own order items
Instructors log in to the dashboard as the role instructor, which may read order_items (the lesson shows the grants). The dashboard sets lco.instructor_email to the logged-in instructor's email before every query.
Make order_items show the role instructor only the lines of courses taught by the instructor whose email is in lco.instructor_email. When the setting is not set, instructor sees no rows (and no error). app, the owner, still sees every line.
The Check reads order_items as instructor with Neha Kapoor's email, with Rahul Iyer's, and with none, on both datasets.
- Task 2
live-classes.sql: read everyone's classes, change your own
Instructors may read every live class (the timetable is public), but add, change and delete only their own: rows whose instructor_id is the instructor with the email in lco.instructor_email.
Afterwards, as instructor with Neha's email: every class is visible; adding a class for Neha works; adding one for Kabir Mehta is refused with a row-level security error; changing or deleting Kabir's classes changes nothing; changing her own works; and handing her own class over to Kabir is refused.
The role instructor already has SELECT, INSERT, UPDATE and DELETE on live_classes.
- Task 3
support-students.sql: support never sees deleted students
Support (the role support) must not see students who deleted their account (deleted_at is set). Arjun (the role analyst) still reads every student. Both roles have SELECT on students.
Afterwards support sees only the students whose deleted_at is empty, analyst sees all of them, and app still sees all of them.
Careful: once row-level security is on, a role with no policy sees no rows at all.
- Task 4 · interview question
Interview: force-rls.sql, the owner is not exempt
enrollments already has row-level security and a policy: you see only the enrollments of the student whose email is in lco.student_email (the lesson makes it). The LCO website connects as app, the table's owner, so the policy never applies to it, and one bug could show every student's courses.
Make the policy apply to app as well. Then, in the same file, set lco.student_email to Diya Patel's email (diya.patel@example.com) and return one column, visible_enrollments: how many enrollments app now sees.
Afterwards a new session as app with no student set sees no enrollments.
What you will learn
- ENABLE ROW LEVEL SECURITY: default deny, row by row.
- Policies: USING for the rows you may see, WITH CHECK for the rows you may write.
- Permissive and restrictive policies, and how they combine.
- Per-user rules: a setting the app sets for each request.
- Who skips RLS: owners, superusers and BYPASSRLS, and FORCE.
More in Safety, security and storage
Build "Instructors see only their own revenue" yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. 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 in depth are free. This one comes with the all labs plan.