Roles and privileges

For DBAs and operators

Roles and membership

CREATE ROLE analyst NOLOGIN;
CREATE ROLE alice LOGIN PASSWORD '…';
GRANT analyst TO alice;                      -- INHERIT TRUE by default
GRANT analyst TO dave WITH INHERIT FALSE;    -- must SET ROLE analyst

Since PostgreSQL 16 membership has per-grant options INHERIT, SET and ADMIN, and CREATEROLE no longer lets a role manage roles it was not granted ADMIN on. Superusers (and the bootstrap role) skip all privilege checks, including RLS; BYPASSRLS skips only RLS.

Privileges

Table: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, MAINTAIN (MAINTAIN is new in PostgreSQL 17: VACUUM, ANALYZE, REINDEX, CLUSTER, REFRESH MATERIALIZED VIEW, LOCK). Schema: USAGE, CREATE. Database: CONNECT, CREATE, TEMP. \dp shows ACLs, e.g. postgres=arwdDxtm/postgres (letters for each privilege, m = MAINTAIN) and analyst=r/postgres.

Defaults that surprise people

• PUBLIC (everyone) gets CONNECT and TEMP on new databases and EXECUTE on new functions.
• Since PostgreSQL 15 PUBLIC no longer has CREATE on the public schema, and the schema is owned by pg_database_owner. Apps that silently created tables there now fail with ERROR: permission denied for schema public.
• GRANT … ON ALL TABLES IN SCHEMA only covers existing tables. For future ones use ALTER DEFAULT PRIVILEGES [FOR ROLE creator] IN SCHEMA sales GRANT SELECT ON TABLES TO analyst; it applies to objects created by that creator role only.
• Only the owner (or a member of the owning role) can ALTER/DROP: ERROR: must be owner of table orders. Use a group role as owner in shared environments.

Predefined roles

pg_read_all_data / pg_write_all_data (14+), pg_monitor, pg_read_all_stats, pg_read_all_settings, pg_signal_backend, pg_checkpoint (15+), pg_use_reserved_connections and pg_create_subscription (16+), pg_maintain (17+: MAINTAIN on all relations), pg_signal_autovacuum_worker (18), pg_read_server_files, pg_write_server_files, pg_execute_server_program. Granting these is much safer than handing out superuser.

Row-level security

ALTER TABLE sales.orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY own_rows ON sales.orders
  USING (sales_rep = current_user);

With RLS enabled and no matching policy, a role sees no rows (no error). Owners are exempt unless FORCE ROW LEVEL SECURITY. pg_read_all_data does not bypass RLS. Inserting a row the policy would hide fails with ERROR: new row violates row-level security policy for table "orders". pg_dump sets row_security = off and errors out rather than dump partial data.

SELECT r.rolname, m.rolname AS member_of, am.inherit_option
  FROM pg_auth_members am
  JOIN pg_roles r ON r.oid = am.member
  JOIN pg_roles m ON m.oid = am.roleid;
SELECT has_table_privilege('alice', 'sales.orders', 'SELECT');