PostgreSQL’s permission system is powerful, but it trips people up: a user can connect but “permission denied for table”, or a read-only user can’t see tables created next week. The model makes sense once you know it has layers (database, schema, object), and that privileges apply to objects that already exist unless you set default privileges.

Roles are users and groups

PostgreSQL has only roles. A role with LOGIN acts as a user, and a role without it acts as a group:

CREATE ROLE app_readonly NOLOGIN;                      -- group
CREATE ROLE app_readwrite NOLOGIN;                     -- group
CREATE ROLE reporting LOGIN PASSWORD 'StrongPass1';    -- user
GRANT app_readonly TO reporting;                       -- membership

CREATE USER is just CREATE ROLE … LOGIN. See creating PostgreSQL users and databases.

Grant privileges to group roles, and add users to groups. When someone leaves, or a new service appears, you change membership, not dozens of grants.

The layers

Layer Privileges Example
Cluster Role attributes LOGIN, CREATEDB, CREATEROLE, SUPERUSER
Database CONNECT, CREATE, TEMP GRANT CONNECT ON DATABASE app TO app_readonly;
Schema USAGE, CREATE GRANT USAGE ON SCHEMA public TO app_readonly;
Tables, views SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
Sequences USAGE, SELECT, UPDATE Needed for serial inserts
Functions EXECUTE

A role needs all layers: CONNECT on the database, USAGE on the schema, and SELECT on the table. Missing schema USAGE is the classic cause of “permission denied for schema public”.

A read-only role

GRANT CONNECT ON DATABASE app TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readonly;

PostgreSQL 14+ also has a predefined role for this: GRANT pg_read_all_data TO reporting; (read everything in every database). Use it with care.

A read-write application role

GRANT CONNECT ON DATABASE app TO app_readwrite;
GRANT USAGE ON SCHEMA public TO app_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readwrite;

CREATE ROLE app_web LOGIN PASSWORD 'AnotherStrongPass' IN ROLE app_readwrite;

Default privileges: the step everyone forgets

GRANT … ON ALL TABLES covers existing tables only. Tables created later by migrations won’t be readable by app_readonly, unless you set default privileges for the role that creates them:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite;

Check them with \ddp in psql.

Separate owner and runtime roles

A good pattern:

  • app_owner: owns the schema and tables; used only by migrations.
  • app_web: used by the running application, with read-write data access but no DDL (can’t drop tables).
  • reporting: read-only.

If the web application is compromised, the attacker can’t drop or alter tables. See database migrations.

The public schema

Since PostgreSQL 15, ordinary users can no longer create objects in the public schema by default. On older versions, revoke it yourself:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Also consider revoking CONNECT on databases from PUBLIC, so roles can only connect to databases you grant explicitly.

Row-level security (RLS)

For multi-tenant apps, RLS restricts which rows a role sees:

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::int);

The app sets SET app.tenant_id = 42 per request or transaction. Table owners and superusers bypass RLS unless you use FORCE ROW LEVEL SECURITY.

Authentication is separate

Permissions decide what a role can do. pg_hba.conf decides who can connect from where, and how (peer, scram-sha-256, certificates). See PostgreSQL remote connections and peer authentication failed.

Inspecting permissions

\du                       -- roles and memberships
\l                        -- database privileges
\dn+                      -- schema privileges
\dp tablename             -- table privileges
\ddp                      -- default privileges
SELECT has_table_privilege('reporting', 'public.orders', 'SELECT');

Quick reference

Goal Command
Group role CREATE ROLE g NOLOGIN;
User in group CREATE ROLE u LOGIN PASSWORD '…' IN ROLE g;
Read access CONNECT + USAGE + SELECT ON ALL TABLES
Future tables ALTER DEFAULT PRIVILEGES FOR ROLE owner …
Lock down public REVOKE CREATE ON SCHEMA public FROM PUBLIC;
Row filtering RLS policies

Related: database security and psql commands.

  • PostgreSQL
  • Security
  • Database