Roles: Manage ownership & read/write permissions

Superuser: Can create roles & assign users

Owners: Can edit privileges to roles of owned tables

Minimum Privileges

Minimum Privileges: The minimum to execute a query

SELECT

• Any query requires USAGE perm on the schema
• When using SELECT, the reported columns must have SELECT perm
• When using NATURAL JOIN, the joined keys must have SELECT perm
• Creating a view doesn’t require read perm, but selecting from it does.

INSERT

• If the table has a foreign key, you need SELECT and UPDATE perm on the other table’s key (need UPDATE because the other table stores reference links)

DELETE:

• Requires DELETE and SELECT perms, doesn’t require UPDATE on foreign key sources
• When deleting from a foreign key source, need SELECT and UPDATE perms on tables referencing this foreign key as well. (Related to reaction policy)

Privileges

https://www.postgresql.org/docs/12/ddl-priv.html

Privilege Modification Commands:

// Change owner 
ALTER TABLE table_name OWNER TO new_owner;

// Grant priviledge
// GRANT <operations> ON <objects> TO <roles>;
GRANT UPDATE ON table_name TO a_user;

// Revoke priviledge
// REVOKE <operations> ON <objects> FROM <roles>;
REVOKE UPDATE ON table_name FROM a_user;

// View privileges of database
// Access privileges: user=privileges/from (e.g. azalea=arwdDxt/azalea)
\\dp

Privilege Categories: