Roles & privileges

Control who can do what with GRANT and REVOKE.

Who can do what

Postgres access control is built on roles (users and groups are both roles). You grant and revoke privileges on objects:

CREATE ROLE analyst;
GRANT SELECT ON books TO analyst;        -- read-only
GRANT INSERT, UPDATE ON orders TO analyst;
REVOKE INSERT ON orders FROM analyst;

The principle of least privilege: give each role only what it needs. Group privileges into roles, then grant the role to users. has_table_privilege(role, table, action) checks an effective privilege.

Privileges accumulate and REVOKE takes back exactly what it names — analyst keeps UPDATE on orders after losing INSERT. has_table_privilege() answers for the state you ended up in, which is the only question that matters at query time.