PostgreSQL User and Privilege Management

PostgreSQL has a flexible role system, but granting the right privileges takes a few more commands than you might expect. This is a compact cheat sheet for the tasks that come up most often.

Inspecting databases and tables

In psql, these meta-commands are the quickest way to look around:

\l          -- list databases
\c app_db   -- connect to a database
\dt         -- list tables in the current schema
\du         -- list roles
\dp         -- show table privileges

The same information via plain SQL:

SELECT datname FROM pg_database;

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';

Creating a role and a database

CREATE ROLE app_user WITH LOGIN PASSWORD 'change-me';
CREATE DATABASE app_db OWNER app_user;

If the application role owns the database and creates its own tables, it already has full rights on them and you rarely need extra grants.

Granting least-privilege access

Often, though, the schema is managed by a migration role and the application should only read and write data. Grant exactly that instead of ALL PRIVILEGES:

GRANT CONNECT ON DATABASE app_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;

GRANT SELECT, INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA public TO app_user;

GRANT USAGE, SELECT
  ON ALL SEQUENCES IN SCHEMA public TO app_user;

Don't forget future tables

GRANT ... ON ALL TABLES only affects tables that exist now. For tables created later (e.g. by migrations), set default privileges for the role that creates them:

ALTER DEFAULT PRIVILEGES FOR ROLE migration_user IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

ALTER DEFAULT PRIVILEGES FOR ROLE migration_user IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app_user;

PostgreSQL 15+: the public schema changed

Since PostgreSQL 15, ordinary users can no longer create objects in the public schema by default. If a non-owner role needs to create tables there, grant it explicitly:

GRANT CREATE ON SCHEMA public TO migration_user;

Changing ownership

ALTER TABLE orders OWNER TO app_user;
ALTER DATABASE app_db OWNER TO app_user;

-- move everything a role owns in the current database to another role
REASSIGN OWNED BY old_user TO new_user;