PostgreSQL roles, privileges and default privileges

PostgreSQL has a single concept for users and groups: the role. A role with the LOGIN attribute can connect; a role without it is used as a group that other roles are members of. CREATE USER is simply CREATE ROLE with LOGIN implied.

Access then works in layers. To read a table, a role needs CONNECT on the database, USAGE on the schema and SELECT on the table. Missing any layer gives a permission error.

Who owns what

The role that creates an object owns it. Only the owner (or a superuser) can alter or drop it, and that right cannot be granted to others. Everyone else starts with no privileges except those granted to PUBLIC, a pseudo-role meaning “all roles”. By default PUBLIC gets CONNECT and TEMPORARY on databases and EXECUTE on functions, but nothing on tables.

Since PostgreSQL 15, PUBLIC no longer has CREATE on the public schema in new databases. Databases upgraded or restored from older versions keep their old permissions; you can adopt the new default with REVOKE CREATE ON SCHEMA public FROM PUBLIC;.

A typical setup: owner, read-write and read-only

A common pattern is one role that owns the schema objects (used for migrations), plus group roles for read-write and read-only access. Application and human logins become members of a group.

1. Create the group roles and the login roles. Use \password in psql or createuser --pwprompt if you do not want passwords in your SQL history or server log.
2. Lock down the database and schema, then grant the layers to the groups.
3. Grant table privileges on what exists now.
4. Set default privileges so future tables get the same grants.

CREATE ROLE app_owner LOGIN PASSWORD 'CHANGE_ME';
CREATE ROLE app_rw NOLOGIN;
CREATE ROLE app_ro NOLOGIN;
CREATE ROLE appuser LOGIN PASSWORD 'CHANGE_ME' IN ROLE app_rw;
CREATE ROLE report LOGIN PASSWORD 'CHANGE_ME' IN ROLE app_ro;

CREATE DATABASE appdb OWNER app_owner;
\c appdb
REVOKE CONNECT, TEMPORARY ON DATABASE appdb FROM PUBLIC;
GRANT CONNECT ON DATABASE appdb TO app_rw, app_ro;

CREATE SCHEMA app AUTHORIZATION app_owner;
GRANT USAGE ON SCHEMA app TO app_rw, app_ro;

Grant privileges on existing objects. ALL TABLES IN SCHEMA covers only tables that exist right now:

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;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_ro;

Default privileges for future objects

ALTER DEFAULT PRIVILEGES sets the privileges that objects get when they are created later. It never changes existing objects. It also applies only to objects created by a specific role: the one named in FOR ROLE, or the role running the command if you leave it out. Membership does not count, so defaults set for app_owner do not apply to tables that postgres creates.

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;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO app_ro;

The classic mistake is running migrations as a different role than the one in FOR ROLE. If new tables are missing grants, check who owns them with \dt app.* and compare with \ddp, which lists default privileges.

Predefined roles

PostgreSQL ships built-in roles that bundle common rights, so you do not need superuser for everything:

• pg_read_all_data / pg_write_all_data: read or write all tables, views and sequences in every schema. They do not bypass row-level security.
• pg_monitor: read monitoring views and functions; includes pg_read_all_settings and pg_read_all_stats.
• pg_signal_backend: cancel queries or end sessions of other non-superuser roles.
• pg_maintain: run VACUUM, ANALYZE, REINDEX and similar on all relations (recent versions).
• pg_read_server_files, pg_write_server_files, pg_execute_server_program: effectively superuser-level, grant with great care.

GRANT pg_monitor TO monitoring;
GRANT pg_read_all_data TO report;

Checking and revoking

\du
\dp app.*
\ddp
SELECT has_table_privilege('report', 'app.orders', 'SELECT');
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app FROM app_rw;

In \dp output privileges appear as letters such as arwd (INSERT=a, SELECT=r, UPDATE=w, DELETE=d) in entries of the form grantee=privileges/grantor. An empty grantee means PUBLIC.

Before dropping a role, move or drop what it owns. REASSIGN OWNED BY old_role TO app_owner; transfers ownership in the current database, and DROP OWNED BY old_role; removes its remaining objects and deletes them permanently, so run REASSIGN OWNED first and check the result in every database before using DROP OWNED.

Official documentation

• Privileges
• ALTER DEFAULT PRIVILEGES
• Predefined roles