Security and access
Most database breaches need no clever attack: an app that logs in as the table owner, a shared superuser password, a pg_hba.conf line that lets anyone try. This lesson shows how PostgreSQL decides who may connect, what each role may touch and which rows it sees, so you can lock down Mangrove's order database and prove it.
By the end you can
- Design a role matrix with owner, app, read-only, migration and human roles, and explain how membership and INHERIT pass rights along
- Trace a privilege check through database, schema, table, column and function, including the defaults granted to PUBLIC
- Write a pg_hba.conf that forces TLS and SCRAM, in the right order, and check it with pg_hba_file_rules
- Add row-level security that also holds for the table owner, and know which roles skip it
- Choose encryption at rest, column encryption and audit logging, and rotate a database password without downtime
The everyday picture
Think of a big grocery store after closing time. A guard at the staff door checks a list: which badge may enter, through which door, and whether the badge must be shown or a code typed. That list is read from the top, and the first line that fits decides.
Inside, badges are personal, but the keys hang on job keyrings: the cashier ring opens the tills, the manager ring opens the office. A cashier's drawer opens only for her own till. A camera records who opened the safe, and the safe has its own lock.
In PostgreSQL, pg_hba.conf is the guard's list, roles are badges and keyrings, grants are keys, row-level security is the till drawer, audit logs are the camera and column encryption is the safe.
Roles: badges, keyrings and who inherits what
PostgreSQL has one kind of account, the role. A role with LOGIN is a personal badge. A role without LOGIN is a group, a keyring handed to others with GRANT app_rw TO api_svc_a. Put every privilege on group roles and give logins only memberships. Then a job's rights change in one place, and removing a person means dropping one login. The basic four-role setup is in Running Postgres in production; here you decide who holds which keyring, and how.
Membership passes rights in two ways. If the member inherits, it uses the group's privileges at once. If not, it must run SET ROLE order_owner first, and then acts as that role. Since Postgres 16 you choose this per grant: GRANT order_owner TO nadia WITH INHERIT FALSE, SET TRUE lets Nadia become the owner when she means to, while her normal session cannot drop a table by accident. Before 16, the member's INHERIT or NOINHERIT attribute decided for all its memberships.
Three attributes skip normal checks, so count them on every review. SUPERUSER skips every privilege check and every row-level policy; only the DBAs' postgres role should have it, never an app, dashboard or script. BYPASSRLS ignores row-level security; backup jobs may need it, apps never do. CREATEROLE manages other roles; Postgres 16 narrowed it to roles it administers, but it is still close to admin power.
For jobs that need system information but no table data, use the predefined roles. pg_monitor lets a metrics exporter read the statistics views. pg_signal_backend lets on-call cancel another role's runaway query (never a superuser's). pg_read_all_data (Postgres 14+) reads every table, which suits a backup login and nothing else.
Quick check
Nadia's login was granted order_owner WITH INHERIT FALSE, SET TRUE. In a fresh session she runs DROP TABLE rider_shift_old;, a table owned by order_owner. What happens?
Without inheritance, membership gives her nothing until she switches role. SET TRUE allows the switch, and after SET ROLE order_owner she acts as the owner. The tempting first answer is how membership behaves with INHERIT TRUE, which is exactly what you avoid for powerful roles held by people.