PostgreSQL Users, Roles & Backups - PostgreSQL Tutorial

Roles and permissions

In Postgres, users and groups are both roles.

SQL

CREATE ROLE app_user WITH LOGIN PASSWORD 'strong-password';
CREATE ROLE readonly;

GRANT CONNECT ON DATABASE bookstore 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 ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- apply to tables created later too
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

Least privilege

Your application should not connect as postgres (the superuser). Create a dedicated role with only the permissions it needs. A SQL injection in an app connected as superuser can do anything; the same bug on a restricted role usually cannot.

A read-only reporting role:

SQL

CREATE ROLE reporting WITH LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE bookstore TO reporting;
GRANT USAGE ON SCHEMA public TO reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;

Row-level security

Restrict which rows a role can see — the foundation of multi-tenant applications:

SQL

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY own_orders ON orders
    FOR SELECT
    USING (user_id = current_setting('app.user_id')::BIGINT);

The application sets SET app.user_id = '42' per request, and the database enforces the boundary even if a query forgets its WHERE.

Backups

Bash

# single database, compressed custom format
pg_dump -U postgres -Fc bookstore > bookstore.dump

# plain SQL
pg_dump -U postgres bookstore > bookstore.sql

# everything, including roles
pg_dumpall -U postgres > all.sql

Restoring:

Bash

pg_restore -U postgres -d bookstore bookstore.dump
psql -U postgres -d bookstore -f bookstore.sql

An untested backup is not a backup. Restore it into a scratch database on a schedule and confirm the data is really there.

Monitoring

SQL

-- currently running queries
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC;

-- cancel / kill
SELECT pg_cancel_backend(pid);
SELECT pg_terminate_backend(pid);

-- table and index sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Maintenance

Postgres uses MVCC — updates leave behind dead row versions that autovacuum cleans up. It is on by default; the things to watch are long-running transactions (which block cleanup) and tables with very high update rates.

SQL

VACUUM ANALYZE books;
SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

Security checklist

App connects as a limited role, never superuser.
Passwords via environment variables, never in the repository.
scram-sha-256 authentication in pg_hba.conf.
TLS for any connection crossing a network.
Postgres not exposed to the public internet.
Parameterised queries in the application — always.