Post

PostgreSQL Privileges: How Access Works, and a Cheat Sheet

Why a PostgreSQL user can connect but gets 'permission denied', the three doors every query passes (database, schema, table), a tested setup for an application user, the traps (default privileges, sequences, PUBLIC), a read-only role recipe, and a GRANT cheat sheet.

PostgreSQL Privileges: How Access Works, and a Cheat Sheet

Everyone meets this one. You create a user, you grant it “all privileges”, the app connects, and then:

1
ERROR:  permission denied for table orders

The fix isn’t another GRANT ALL. PostgreSQL checks several separate doors, and each has its own key. Once you see the doors, you can set up any user in a minute, and debug any “permission denied” quickly.

What we’ll cover

  1. The three doors
  2. Who is who: roles, owners, PUBLIC
  3. Setting up an application user, step by step, and what each step really does
  4. Traps that surprise people
  5. A read-only role
  6. Looking at what you have
  7. Cheat sheet

(Everything here was run on PostgreSQL 17. This post continues the PostgreSQL starter kit.)


The three doors

To read one row from orders, a user passes three checks in order:

1
2
database  ──►  schema  ──►  table (and its sequences)
CONNECT        USAGE        SELECT / INSERT / UPDATE / DELETE
DoorQuestionPrivilege
DatabaseMay this user connect to the database?CONNECT
SchemaMay it look inside the schema (usually public)?USAGE (and CREATE to make tables)
TableMay it touch the rows?SELECT, INSERT, UPDATE, DELETE …

Miss one and you get “permission denied”, and the message names the door that stopped you (for database, for schema public, for table orders). That makes the error useful: read which object it names.


Who is who: roles, owners, PUBLIC

  • Role. In PostgreSQL a “user” is just a role that can log in. CREATE USER is CREATE ROLE ... LOGIN. Roles can also be groups: you grant a role to another role.
  • Owner. Whoever creates an object owns it, and the owner can do everything to it without any GRANT. That is why an app user that runs its own migrations rarely needs table grants.
  • PUBLIC. A built-in pseudo-role meaning “everyone”. By default it can CONNECT to every database. I checked: a brand-new user with no grants connected to my app database straight away.

Setting up an application user

The classic script, with what each step is really for:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE DATABASE myapp_db;
CREATE USER myapp_user WITH PASSWORD 'change_me';

\c myapp_db

GRANT CONNECT ON DATABASE myapp_db TO myapp_user;                -- door 1
GRANT USAGE, CREATE ON SCHEMA public TO myapp_user;              -- door 2
ALTER SCHEMA public OWNER TO myapp_user;                         -- owner of the schema

GRANT ALL PRIVILEGES ON ALL TABLES    IN SCHEMA public TO myapp_user;   -- door 3, existing tables
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myapp_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES    TO myapp_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO myapp_user;

It works: after it, myapp_user creates tables and inserts rows. But several lines are doing less than they look like:

StepWhat I found
GRANT CONNECTChanges nothing by itself, because PUBLIC could already connect. It only matters after you lock PUBLIC out (see traps).
GRANT USAGE, CREATE ON SCHEMARedundant once the user owns the schema (next line).
GRANT ALL ON ALL TABLESOnly affects tables that already exist and belong to someone else. Tables the app creates itself are already its own.
ALTER DEFAULT PRIVILEGESApplies to objects created by the role that ran the command, here postgres, not myapp_user. \ddp shows Owner: postgres.

That last one is the subtle part. Default privileges answer: “when role X creates a table in the future, who gets what?” If you run it as the admin, it covers tables the admin creates later, which is useful if an admin also creates tables the app must read. For tables the app creates itself, it does nothing. To target another creator, name it: ALTER DEFAULT PRIVILEGES FOR ROLE some_role ....

The simpler version

If this database exists for one app, make the app user the owner and skip most of the above:

1
2
CREATE USER myapp_user WITH PASSWORD 'change_me';
CREATE DATABASE myapp_db OWNER myapp_user;

The owner can create schemas and tables, and run migrations. Then lock the door for everyone else:

1
REVOKE CONNECT ON DATABASE myapp_db FROM PUBLIC;

I verified the effect: another user then gets FATAL: User does not have CONNECT privilege, while a user with its own CONNECT grant still connects. The owner is never locked out of its own database.

Since PostgreSQL 15 the public schema no longer lets everyone create tables. On a fresh PG 17 database, a user without grants got permission denied for schema public on CREATE TABLE. On PG 14 and older, every user could. That’s why scripts written years ago contain schema grants.


Traps that surprise people

1. GRANT ALL ON DATABASE does not give table access

1
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO other_user;

I ran this, then SELECT * FROM orders as that user: still permission denied for table orders. On a database, “all” means just CREATE, CONNECT and TEMPORARY. Privileges don’t flow downward. Every object type needs its own grant.

2. Inserting into a serial table needs the sequence too

A table with id serial (or an identity column) has a hidden sequence. With only GRANT INSERT ON table, the insert fails:

1
ERROR:  permission denied for sequence seqt_id_seq

Add GRANT USAGE ON SEQUENCE seqt_id_seq TO writer; and it works. That’s why scripts grant on ALL SEQUENCES too.

3. RETURNING needs SELECT

With INSERT and sequence rights, a plain insert worked, but INSERT ... RETURNING id failed with permission denied for table. Returning the row is also reading it.

4. Column-level grants exist

1
GRANT SELECT (id) ON seqt TO writer;

SELECT id worked, SELECT v was denied. Useful for hiding sensitive columns.

5. You can’t drop a role that still has privileges

1
ERROR:  role "writer" cannot be dropped because some objects depend on it

It lists the grants. Remove them, then drop:

1
2
DROP OWNED BY writer;     -- removes its grants (and drops objects it owns!)
DROP ROLE writer;

If it owns data you want to keep, hand it over first: REASSIGN OWNED BY writer TO newowner;.


A read-only role

A common need: reports, dashboards, an analyst. Make a group role with read rights, then grant it to people:

1
2
3
4
5
6
7
8
9
10
CREATE ROLE readonly NOLOGIN;
GRANT CONNECT ON DATABASE myapp_db TO readonly;
GRANT USAGE   ON SCHEMA public       TO readonly;
GRANT SELECT  ON ALL TABLES IN SCHEMA public TO readonly;                     -- existing tables

-- tables the app creates LATER (note FOR ROLE: the creator is the app user)
ALTER DEFAULT PRIVILEGES FOR ROLE myapp_user IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly;

GRANT readonly TO analyst;       -- analyst is a normal login role

I tested both halves: a table created before the grant and one created by myapp_user afterwards were both readable by the analyst, while an INSERT was denied.

Since PostgreSQL 14 there’s a shortcut for the same job across the whole database:

1
GRANT pg_read_all_data TO analyst;      -- read every table, view and sequence

(pg_write_all_data is the matching write role.) It’s broad, so use it for auditors and admins, not for apps.


Looking at what you have

1
2
3
4
5
\l                    databases and their privileges
\du                   roles
\dn+                  schemas, owners and privileges
\dp orders            table privileges (and column privileges)
\ddp                  default privileges (who they apply to)

Reading the access column of \dp:

1
2
myapp_user=arwdDxtm/myapp_user      ← grantee = privileges / granted by
readonly=r/myapp_user

The letters: r read (SELECT), a append (INSERT), w write (UPDATE), d DELETE, D TRUNCATE, x REFERENCES, t TRIGGER, m MAINTAIN (PG 17), U USAGE, C CREATE or CONNECT depending on object type (c is CONNECT on a database), T TEMPORARY.

To ask a direct question:

1
2
3
SELECT has_table_privilege('analyst', 'orders', 'SELECT');          -- t / f
SELECT has_column_privilege('writer', 'seqt', 'v', 'SELECT');
SELECT has_database_privilege('analyst', 'myapp_db', 'CONNECT');

Cheat sheet

Which privilege on which object

ObjectPrivileges
DatabaseCONNECT, CREATE (make schemas), TEMPORARY
SchemaUSAGE, CREATE
Table / viewSELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
SequenceUSAGE (nextval), SELECT (read value), UPDATE (setval)
ColumnSELECT, INSERT, UPDATE, REFERENCES
FunctionEXECUTE

Granting and revoking

1
2
3
4
5
6
7
8
9
10
11
GRANT  CONNECT ON DATABASE db TO role;
GRANT  USAGE ON SCHEMA public TO role;
GRANT  SELECT, INSERT, UPDATE, DELETE ON TABLE t TO role;
GRANT  SELECT ON ALL TABLES IN SCHEMA public TO role;        -- existing tables only
GRANT  USAGE ON ALL SEQUENCES IN SCHEMA public TO role;
GRANT  SELECT (col) ON t TO role;                            -- column level
GRANT  role_a TO role_b;                                     -- membership (group role)
GRANT  ... TO role WITH GRANT OPTION;                        -- let them pass it on

REVOKE INSERT ON t FROM role;
REVOKE CONNECT ON DATABASE db FROM PUBLIC;                   -- lock out the world

Future tables

1
2
ALTER DEFAULT PRIVILEGES FOR ROLE creator IN SCHEMA public
    GRANT SELECT ON TABLES TO role;

Ownership and cleanup

1
2
3
4
5
6
CREATE DATABASE db OWNER role;
ALTER  DATABASE db OWNER TO role;
ALTER  TABLE t OWNER TO role;
REASSIGN OWNED BY old_role TO new_role;
DROP   OWNED BY old_role;
DROP   ROLE old_role;

“Permission denied”: which door?

Message saysMissing
for database "x" / no CONNECT privilegeGRANT CONNECT ON DATABASE (or PUBLIC was revoked)
for schema publicGRANT USAGE (read) or CREATE (make tables) on the schema
for table xtable grant (SELECT, INSERT…); for RETURNING also SELECT
for sequence x_id_seqGRANT USAGE ON SEQUENCE
must be owner of table xALTER/DROP need ownership, not just grants
FATAL: password authentication failednot privileges at all: password, or pg_hba.conf

Summary

  • A query passes three doors: database (CONNECT), schema (USAGE), table (SELECT…, plus the sequence for serial columns). Each needs its own grant, and the error message names the failing one.
  • Owners need no grants. For a single-app database, CREATE DATABASE … OWNER app_user is the simplest setup.
  • PUBLIC can connect by default; revoke it when you want a database locked down.
  • GRANT ALL ON DATABASE does not reach the tables, and ALTER DEFAULT PRIVILEGES only covers objects made by the role it names (default: the one running it).
  • For read access, use a group role (readonly) plus ALTER DEFAULT PRIVILEGES FOR ROLE …, or pg_read_all_data for broad read.
This post is licensed under CC BY 4.0 by the author.