PostgreSQL roles and privileges cheat sheet - read-only users, app owners, default privileges and \"permission denied\"

#The model in one minute

• Roles are users and groups at the same time. A role with LOGIN is a user.
• To read app.orders, a role needs CONNECT on the database, USAGE on schema app, and SELECT on the table.
• Privileges on existing tables don't cover tables created later. Set default privileges for those.
• The owner of an object can do anything with it. Make a group role the owner, not a person.

#Standard role layout

-- group roles (no login)
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_rw    NOLOGIN;
CREATE ROLE app_ro    NOLOGIN;

-- schema owned by the owner role
CREATE SCHEMA app AUTHORIZATION app_owner;

-- database and schema access
GRANT CONNECT ON DATABASE sales TO app_rw, app_ro;
GRANT USAGE ON SCHEMA app TO app_rw, app_ro;

-- existing objects
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_ro;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_rw;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_rw;

-- future objects created by app_owner
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO app_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT USAGE, SELECT ON SEQUENCES TO app_rw;

-- login users
CREATE ROLE svc_orders LOGIN PASSWORD '<strong password>' IN ROLE app_rw;
CREATE ROLE jane       LOGIN PASSWORD '<strong password>' IN ROLE app_ro;
CREATE ROLE migrator   LOGIN PASSWORD '<strong password>' IN ROLE app_owner;

Warning

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner only applies to objects created by app_owner. If the migration tool connects as migrator and creates tables as migrator, the defaults don't apply. Have it run SET ROLE app_owner; first, or set the defaults for the role that actually creates the objects.

#PostgreSQL 14+ shortcuts

GRANT pg_read_all_data  TO auditor;    -- SELECT on everything in every schema
GRANT pg_write_all_data TO etl_admin;  -- INSERT/UPDATE/DELETE everywhere
GRANT pg_monitor        TO monitoring; -- read stats views

These are broad. Use them for auditors and monitoring, not for applications.

#PostgreSQL 15+ and the public schema

From PostgreSQL 15, ordinary users can't create objects in the public schema by default. On older versions, remove that right yourself:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE sales FROM PUBLIC;   -- then grant CONNECT explicitly

#Who has what

-- role memberships
SELECT r.rolname AS role, m.rolname AS member
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
ORDER BY 1, 2;

-- table privileges in a schema
SELECT grantee, table_name, string_agg(privilege_type, ', ' ORDER BY privilege_type) AS privs
FROM information_schema.role_table_grants
WHERE table_schema = 'app'
GROUP BY grantee, table_name ORDER BY table_name, grantee;

-- default privileges
SELECT pg_get_userbyid(defaclrole) AS for_role, defaclnamespace::regnamespace AS schema,
       defaclobjtype AS type, defaclacl
FROM pg_default_acl;

-- can this role do this?
SELECT has_table_privilege('jane', 'app.orders', 'SELECT'),
       has_schema_privilege('jane', 'app', 'USAGE');

In psql: \du (roles), \dp app.* (table privileges), \ddp (default privileges), \dn+ (schema privileges).

#Fixing "permission denied"

#Changing owners

ALTER TABLE app.orders OWNER TO app_owner;

-- everything owned by a departing user, in the current database
REASSIGN OWNED BY old_dev TO app_owner;
DROP OWNED BY old_dev;      -- removes their remaining privileges
DROP ROLE old_dev;          -- after running the two above in every database

Caution

DROP OWNED BY drops objects the role still owns in the current database. Always run REASSIGN OWNED first and check the result.

#Managed services

• On RDS/Aurora the master user is a member of rds_superuser. On Azure Flexible Server it's azure_pg_admin. Neither is a true superuser, so some commands are restricted.
• Use IAM authentication (RDS) or Microsoft Entra authentication (Azure) for people, so access follows your identity provider.

#Checklist

• Owner, read-write and read-only group roles per application
• Logins inherit from group roles only
• Default privileges set for the role that creates objects
• public schema and database locked down
• Periodic review of memberships and grants