Privileges, Default Privileges, Role Design, SET ROLE, and Least-Privilege Administration

Learning outcomes

Authentication answers who connected; authorization answers what that role may do. ServiceHub needs application DML, reporting reads, and schema deployment without turning every login into an object owner or superuser. PostgreSQL roles can represent identities, privilege groups, or ownership—separating those responsibilities makes least privilege auditable.

1. Owner role, capability roles, and login identities

DROP SCHEMA IF EXISTS ch20_app CASCADE;DROP ROLE IF EXISTS ch20_api_login;DROP ROLE IF EXISTS ch20_report_login;DROP ROLE IF EXISTS ch20_deploy_login;DROP ROLE IF EXISTS ch20_reader;DROP ROLE IF EXISTS ch20_writer;DROP ROLE IF EXISTS ch20_schema_owner;CREATE ROLE ch20_schema_owner NOLOGIN;CREATE ROLE ch20_reader NOLOGIN;CREATE ROLE ch20_writer NOLOGIN;CREATE ROLE ch20_api_login LOGIN;CREATE ROLE ch20_report_login LOGIN;CREATE ROLE ch20_deploy_login LOGIN NOINHERIT;GRANT ch20_reader TO ch20_report_login  WITH INHERIT TRUE, SET FALSE;GRANT ch20_reader, ch20_writer TO ch20_api_login  WITH INHERIT TRUE, SET FALSE;GRANT ch20_schema_owner TO ch20_deploy_login  WITH INHERIT FALSE, SET TRUE;

The API inherits data capabilities but cannot SET ROLE to those group roles. The deploy login does not automatically inherit owner privileges; it must explicitly SET ROLE ch20_schema_owner, creating an auditable privilege elevation boundary.

2. Inspect membership options instead of inferring them

SELECT r.rolname AS granted_role,       m.rolname AS member_role,       am.admin_option,       am.inherit_option,       am.set_option,       g.rolname AS grantorFROM pg_auth_members AS amJOIN pg_roles AS r ON r.oid = am.roleidJOIN pg_roles AS m ON m.oid = am.memberJOIN pg_roles AS g ON g.oid = am.grantorWHERE r.rolname LIKE 'ch20_%'   OR m.rolname LIKE 'ch20_%'ORDER BY member_role, granted_role;

INHERIT controls automatic privilege use through membership chains. SET controls whether the original session identity may SET ROLE through that membership path. ADMIN controls whether membership can be granted onward.

3. Create owned data under the NOLOGIN owner

CREATE SCHEMA IF NOT EXISTS ch20_app AUTHORIZATION ch20_schema_owner;SET ROLE ch20_schema_owner;CREATE TABLE ch20_app.invoice (  invoice_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  customer_id bigint NOT NULL,  amount numeric(12,2) NOT NULL CHECK (amount >= 0),  status text NOT NULL CHECK (status IN ('draft','issued','paid')),  created_at timestamptz NOT NULL DEFAULT clock_timestamp());CREATE FUNCTION ch20_app.invoice_total(p_customer_id bigint)RETURNS numericLANGUAGE sqlSTABLEAS $$  SELECT COALESCE(sum(amount),0)  FROM ch20_app.invoice  WHERE customer_id = p_customer_id$$;RESET ROLE;

Objects are owned by a role that cannot log in. Day-to-day application credentials therefore cannot accidentally become table/function owners simply because they created a migration object.

4. Revoke ambient function execution and grant only needed capabilities

PostgreSQL functions normally grant EXECUTE to PUBLIC when created. A least-privilege deployment revokes that default for sensitive routines and grants explicit consumers.

REVOKE ALL ON FUNCTION ch20_app.invoice_total(bigint) FROM PUBLIC;GRANT USAGE ON SCHEMA ch20_app TO ch20_reader, ch20_writer;GRANT SELECT ON ch20_app.invoice TO ch20_reader;GRANT SELECT, INSERT, UPDATE, DELETEON ch20_app.invoiceTO ch20_writer;GRANT USAGE, SELECTON SEQUENCE ch20_app.invoice_invoice_id_seqTO ch20_writer;GRANT EXECUTEON FUNCTION ch20_app.invoice_total(bigint)TO ch20_reader;

5. Positive and negative authorization tests

SET ROLE ch20_api_login;INSERT INTO ch20_app.invoice(customer_id,amount,status)VALUES (501,125.50,'issued');SELECT * FROM ch20_app.invoice;ALTER TABLE ch20_app.invoice ADD COLUMN attacker_note text;-- Expected: must be owner / permission denied.RESET ROLE;

SET ROLE ch20_report_login;SELECT * FROM ch20_app.invoice;SELECT ch20_app.invoice_total(501);INSERT INTO ch20_app.invoice(customer_id,amount,status)VALUES (999,1.00,'draft');-- Expected: permission denied.RESET ROLE;

These tests prove object-level authorization under the simulated roles. They do not prove network authentication or RLS behavior; those are separate layers.

6. SET ROLE gives the deployment identity a narrow elevation path

SET ROLE ch20_deploy_login;SELECT session_user, current_user;SET ROLE ch20_schema_owner;SELECT session_user, current_user;ALTER TABLE ch20_app.invoiceADD COLUMN external_reference text;RESET ROLE;RESET ROLE;

When the deploy login becomes the owner role, new objects are owned by ch20_schema_owner. SET ROLE does not make the role a superuser and does not import arbitrary privileges from the original login; permission checks use the selected current role and what it inherits.

7. ALTER DEFAULT PRIVILEGES belongs to the future creator

Default privileges apply to objects created in the future by a specific current/target role. Membership alone does not make another role's default privileges apply at creation time.

SET ROLE ch20_schema_owner;ALTER DEFAULT PRIVILEGES IN SCHEMA ch20_appGRANT SELECT ON TABLES TO ch20_reader;ALTER DEFAULT PRIVILEGES IN SCHEMA ch20_appGRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ch20_writer;ALTER DEFAULT PRIVILEGES IN SCHEMA ch20_appGRANT USAGE, SELECT ON SEQUENCES TO ch20_writer;ALTER DEFAULT PRIVILEGES IN SCHEMA ch20_appREVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;ALTER DEFAULT PRIVILEGES IN SCHEMA ch20_appGRANT EXECUTE ON FUNCTIONS TO ch20_reader;CREATE TABLE ch20_app.invoice_note (  note_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  invoice_id bigint NOT NULL REFERENCES ch20_app.invoice(invoice_id),  note text NOT NULL);RESET ROLE;

SELECT  has_table_privilege('ch20_report_login','ch20_app.invoice_note','SELECT')    AS report_reads_future_table,  has_table_privilege('ch20_api_login','ch20_app.invoice_note','INSERT')    AS api_writes_future_table,  has_sequence_privilege(    'ch20_api_login',    'ch20_app.invoice_note_note_id_seq',    'USAGE'  ) AS api_uses_future_sequence;

ALTER DEFAULT PRIVILEGES does not retroactively change invoice. Existing objects must be granted/revoked separately.

8. Wrong grantor: a common deployment mistake

ALTER DEFAULT PRIVILEGES IN SCHEMA ch20_appGRANT SELECT ON TABLES TO ch20_report_login;-- If future tables are actually created after SET ROLE ch20_schema_owner,-- the above login-role default is irrelevant to those creations.

Diagnose default-privilege surprises by identifying the role that actually executed CREATE TABLE/CREATE FUNCTION, then inspect its defaults. Do not keep adding grants to unrelated roles until the symptom disappears.

9. Predefined roles are capabilities, not harmless convenience groups

SELECT rolnameFROM pg_rolesWHERE rolname IN (  'pg_monitor',  'pg_read_all_data',  'pg_write_all_data',  'pg_read_server_files',  'pg_write_server_files',  'pg_execute_server_program')ORDER BY rolname;

pg_monitor is useful for observability. pg_read_all_data/pg_write_all_data are broad data privileges and do not bypass RLS. Server-file/program roles can be extremely powerful and may enable superuser-level impact through OS/server access. Grant predefined roles only with an explicit threat/operations review.

10. Checkpoint

Authoritative references

Authentication, TLS, authorization, and policy behavior is security- and version-sensitive. The lesson uses these PostgreSQL 18 primary sources.

• PostgreSQL 18 — Privileges
• PostgreSQL 18 — Role Membership
• PostgreSQL 18 — ALTER DEFAULT PRIVILEGES
• PostgreSQL 18 — SET ROLE
• PostgreSQL 18 — Predefined Roles