← All cheat sheets

POSTGRESQL HARDENING

Plain-text reference · 6 KB. Read it, search it (Ctrl-F) or print it.

Baseline settings for a PostgreSQL server: listen only where needed,
a strict pg_hba.conf, SCRAM passwords, TLS with certificate checks,
least-privilege roles, timeouts and useful logs. Checked against the
PostgreSQL 18 documentation (October 2026; supported versions are 14 to
18, and 14 reaches end of life on 12 November 2026). Where an older
version differs, the sheet says so.
Settings marked "conf" go in postgresql.conf, or use ALTER SYSTEM SET.

FIND THE FILES, RELOAD SAFELY#

SELECT version();
SHOW config_file;              -- postgresql.conf in use
SHOW hba_file;                 -- pg_hba.conf in use
SELECT pg_reload_conf();       -- reload after editing either file
-- Settings that need a restart instead of a reload:
SELECT name, setting FROM pg_settings WHERE pending_restart;
-- Lines of pg_hba.conf that failed to parse (check BEFORE reloading):
SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;

LISTEN ONLY WHERE NEEDED#

# conf. The default 'localhost' accepts only loopback TCP connections.
# Name the one address clients use instead of '*' (restart needed):
listen_addresses = 'localhost,192.0.2.10'
# Then firewall port 5432 so only the application hosts reach it.

PG_HBA.CONF: FIRST MATCH WINS#

# The FIRST line matching connection type, database, user and address
# is used. There is no fall-through: if its check fails, the
# connection is refused. No match at all = refused.
# TYPE      DATABASE  USER      ADDRESS          METHOD
local       all       postgres                   peer
local       all       all                        scram-sha-256
hostssl     appdb     app_user  198.51.100.0/24  scram-sha-256
host        all       all       0.0.0.0/0        reject
host        all       all       ::/0             reject
# local = Unix socket; peer = the OS user name must match the role.
# hostssl = TLS only. A "host" line also accepts plain TCP.
# Never use "trust" outside a throwaway lab: anyone who can connect
# may log in as ANY role, superusers included.

PASSWORDS: SCRAM, NOT MD5#

# conf. scram-sha-256 is the default since PostgreSQL 14; keep it.
password_encryption = 'scram-sha-256'
-- Roles still holding an MD5 hash (superuser only):
SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%';
-- Reset them; psql's \password sends the password already hashed,
-- so it never appears in the history or the server log:
\password app_user
-- Avoid: ALTER ROLE app_user PASSWORD 'secret';  (cleartext to the
--   server, may end up in the client history or the server log)
# MD5 passwords are deprecated in PostgreSQL 18 (warnings on CREATE /
# ALTER ROLE) and will be removed in a future major version.
# Clients need SCRAM support (libpq 10+, most current drivers).

TLS FOR CONNECTIONS#

# conf (default ssl = off). Key file readable only by the server user.
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_min_protocol_version = 'TLSv1.2'   # the default; TLSv1.3 if all
                                       #   clients support it
# Client side: verify the certificate AND the host name.
psql "host=db.example.com dbname=appdb user=app_user sslmode=verify-full sslrootcert=/etc/ssl/certs/db-ca.pem"
# sslmode=require encrypts but does not check who answers.
-- Which sessions use TLS, and which version:
SELECT a.usename, a.client_addr, s.ssl, s.version
FROM pg_stat_ssl s JOIN pg_stat_activity a USING (pid)
WHERE a.backend_type = 'client backend';

ROLES AND PRIVILEGES#

-- Superusers should be the exception:
SELECT rolname FROM pg_roles WHERE rolsuper;
-- The application never runs as a superuser or as the owner role:
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_user LOGIN;
CREATE DATABASE appdb OWNER app_owner;
-- Every role gets CONNECT and TEMPORARY on new databases by default:
REVOKE ALL ON DATABASE appdb FROM PUBLIC;
GRANT CONNECT ON DATABASE appdb TO app_user;
-- Connected to appdb:
CREATE SCHEMA app AUTHORIZATION app_owner;
GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_user;
-- serial columns also need USAGE on their sequence (identity columns do not):
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO app_user;
-- Tables and sequences app_owner creates later get the same grants:
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT USAGE ON SEQUENCES TO app_user;
-- PostgreSQL 14 and older (or a cluster upgraded from one): anyone
-- may create objects in the public schema. Remove that:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
-- Read-only access to everything (reports, backups), PostgreSQL 14+:
GRANT pg_read_all_data TO report_user;
-- (it does not bypass row-level security policies)

TIMEOUTS#

# conf
authentication_timeout = '30s'               # default 1min
idle_in_transaction_session_timeout = '10min'  # default 0 = off
-- Per role, so admin sessions are not cut:
ALTER ROLE app_user SET statement_timeout = '30s';

LOGGING#

# conf, PostgreSQL 18: log_connections takes a list of aspects
log_connections = 'receipt,authentication,authorization'
#   older versions: log_connections = on
log_disconnections = on
log_line_prefix = '%m [%p] %q%u@%d from %h '
log_statement = 'ddl'          # CREATE / ALTER / DROP; 'all' also logs
                               #   data, use it only for short periods
# Failed logins are always logged, whatever log_connections says.

CHECK FROM OUTSIDE#

# A plain-text connection must be refused (no hostnossl/host match):
psql "host=db.example.com dbname=appdb user=app_user sslmode=disable"
#   expect: FATAL: pg_hba.conf rejects connection ... no encryption
pg_isready -h db.example.com -p 5432   # is the port reachable at all?
# From a host that should have no access: the port should be
# filtered by the firewall, not just refused by pg_hba.conf.

Defensive reference on CyberRamen. Offensive / red-team sheets live on OffensiveRamen.com.