postgres-core-security-model

Quick Reference :

PostgreSQL has a single identity primitive : the role. There is no separate "user" or "group" object. CREATE USER is identical to CREATE ROLE ... LOGIN. Every role carries attributes (LOGIN, SUPERUSER, CREATEDB, CREATEROLE, INHERIT, REPLICATION, BYPASSRLS, CONNECTION LIMIT, PASSWORD, VALID UNTIL) and can be a member of other roles (forming a directed graph). Privileges on database objects are granted via GRANT per object class : TABLE, SEQUENCE, DATABASE, SCHEMA, FUNCTION, TYPE, LANGUAGE, DOMAIN, FOREIGN SERVER, TABLESPACE, LARGE OBJECT, PARAMETER. Two grant systems coexist : (1) role membership (GRANT app_reader TO alice) controls which roles a session can act as via SET ROLE and which privileges are inherited ; (2) object privileges (GRANT SELECT ON TABLE orders TO app_reader) controls what a role can do to an object. ALTER DEFAULT PRIVILEGES is a separate command that pre-registers privileges on objects that do not exist yet ; it does NOT retroactively grant on existing objects.

PostgreSQL ships fifteen predefined roles (pg_read_all_data, pg_write_all_data, pg_read_all_settings, pg_read_all_stats, pg_stat_scan_tables, pg_monitor, pg_database_owner, pg_signal_backend, pg_read_server_files, pg_write_server_files, pg_execute_server_program, pg_checkpoint, pg_maintain (v17+), pg_use_reserved_connections (v16+), pg_create_subscription (v16+)) which let you delegate operational privilege without granting SUPERUSER. v16 rewrote role-membership semantics : GRANT role TO member now takes explicit WITH ADMIN | INHERIT | SET { TRUE | FALSE } options and members no longer auto-administer themselves. RLS (Row-Level Security) sits on top of object privileges and is covered in postgres-core-rls-policies ; pg_hba.conf controls who can connect at all and is covered in postgres-errors-connection-auth.

When To Use This Skill :

ALWAYS use this skill when :

• Provisioning a new application user, read-only reporting user, or migration role
• Auditing "who can see / write / execute X" across a database
• Reviewing whether a role should be SUPERUSER, CREATEROLE, or use a predefined role instead
• Adding a new schema and deciding default privileges for objects created in it
• Encountering ERROR: permission denied for table X / ERROR: permission denied for schema Y
• Designing GRANT chains across application tiers (owner, app, reader, analyst)