Postgres in depth · Chapter 40 of 65
Who sees what
By the end of this chapter you can give each role exactly the privileges it needs, on tables, columns and functions, including tables that do not exist yet. 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
Until today everyone at LCO used the role app, which owns every table. Arjun's dashboard tool connected as app too, and last week a typo in one of its queries ran an UPDATE without a WHERE. Priya wants every person and every program to have its own role that can do only its job: Arjun reads sales, Sana's support team fixes emails but never sees phone numbers, and only support may refund.
Nothing you have learned so far limits who can do what: app owns everything, and an owner can do anything to what it owns.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
analyst-access.sql: Arjun reads the sales tables
Arjun logs in as the role analyst, which today can read nothing. Let analyst read the four tables his revenue reports use: orders, order_items, courses and enrollments.
Nothing else: no other table, and no INSERT, UPDATE or DELETE anywhere. The Check compares every privilege on public's tables afterwards, and reads and writes as analyst.
- Task 2
support-columns.sql: support sees no phone numbers
Sana's support team logs in as support. On students they may read only id, name, email and signed_up_at, and they may change only email (students write in to fix a typo).
Afterwards, support's SELECT of those columns works, SELECT * on students is refused (phone is hidden), and an UPDATE of email works. Grant nothing on the whole table.
- Task 3
refund-function.sql: only support may refund
The function refund_order(bigint) (the lesson shows how to make it) marks a paid order refunded. It is SECURITY DEFINER, so it runs with app's rights whoever calls it.
Make it callable only by support (and app, its owner). Afterwards analyst and instructor calling it get "permission denied for function refund_order", and support can call it.
Careful: nobody has been granted anything on it, yet analyst can call it right now. Find out who can, and take that away.
- Task 4 · interview question
Interview: default-privileges.sql, tables that do not exist yet
LCO keeps adding tables, and Arjun wants to read every new one without asking each time. Make every table app creates in schema public from now on readable (SELECT only) by analyst.
Do not change today's tables. The Check runs your file, then creates a new table as app and reads it as analyst (it must work), inserts as analyst (refused), and reads as support (refused).
Careful: granting on all tables in the schema covers only the tables that exist when it runs.
What you will learn
- Roles: users and groups are the same thing in Postgres.
- Privileges and GRANT: what each letter in arwdDxt means.
- Column privileges: hiding one column of a table.
- PUBLIC: the role that means everyone, and what it gets for free.
- Default privileges: rules for objects that do not exist yet.
More in Safety, security and storage
Build "Who sees what" 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.