Postgres on the public internet — six checks before you sleep

For an internal cluster, a private CA or a self-signed cert is fine; for anything customer-reachable, use Let's Encrypt. The placement and modes below match what `pg_ctl -D` expects.

Verify it worked: psql -U postgres -c "SHOW ssl;" # should now print: on

Note: Reload (`pg_reload_conf()`) is enough for the server to start ACCEPTING TLS connections; you do NOT need a restart unless you change `ssl_passphrase_command`. Existing plaintext connections stay alive until they close — set a deploy window if you also flip `hostssl`-only in pg_hba.

Edit `pg_hba.conf` to require password auth on every non-local rule. The example below preserves local-socket trust (often required for `postgres` user maintenance) but forces SCRAM on TCP.

Verify it worked: psql -U postgres -c "SELECT count(*) FROM pg_hba_file_rules WHERE auth_method = 'trust' AND type LIKE 'host%';" # should print: 0

Note: If clients have stored MD5-hashed passwords from a PG ≤9.6 era, they need to be re-set under `password_encryption = scram-sha-256` before the new rule will accept them — see next section.

Setting `password_encryption` first is essential — `ALTER USER` re-hashes using whatever the current setting is, so without this line you re-hash MD5 to MD5.

Verify it worked: SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%'; -- should return 0 rows

Note: Clients on libpq ≥10 negotiate SCRAM automatically. JDBC ≥42.2.0, pgx ≥3.0, and asyncpg ≥0.13 all support SCRAM. Embedded / legacy clients (libpq from PG 9.6 and older) cannot connect to a SCRAM role at all — you'll see `FATAL: password authentication failed`. Either upgrade the client or, transitionally, leave the specific legacy role on MD5 and audit it separately.

The default `log_line_prefix` omits the client address, which makes the audit trail useless. The five-field prefix below is what most production Postgres deployments converge on.

ALTER SYSTEM SET log_connections = 'on';
ALTER SYSTEM SET log_disconnections = 'on';
ALTER SYSTEM SET log_line_prefix = '%m [%p] %r %u@%d ';
SELECT pg_reload_conf();

Verify it worked: -- After a reload, watch the log file (or kubectl logs the pod) and -- open a new psql session. You should see: -- 2026-05-26 13:42:11.111 UTC [123] 10.0.0.5(54321) app_user@app LOG: connection authorized: user=app_user database=app SELECT name, setting, source FROM pg_settings WHERE name LIKE 'log_%connections';

Note: On managed Postgres (RDS, Cloud SQL, Crunchy Bridge) the GUC is sometimes locked. Use the provider's parameter group instead — same setting names. If `SHOW` still returns `off` after reload, run the verify query: `source = 'command line'` means the orchestrator is overriding you.

Most "why does the app need superuser?" stories trace back to one specific command (often `CREATE EXTENSION` or `COPY … FROM PROGRAM`). Identify the actual need and grant that, not the universe.

Verify it worked: SELECT rolname FROM pg_roles WHERE rolsuper AND rolname <> 'postgres'; -- should be empty

This is the same SQL our [Add Exporter scoped-user guide](/exporters/postgres/scoped-user) ships — repeated here so you can copy from the article and stay on the page.

CREATE USER prometheus_exporter WITH PASSWORD 'a-strong-rotated-password';
GRANT pg_monitor TO prometheus_exporter;

Verify it worked: -- Login as the new user and confirm it can read stats but cannot mutate. -- (Expect ✓ on the SELECT and ✗ on the INSERT.) SET ROLE prometheus_exporter; SELECT count(*) FROM pg_stat_activity; -- expect: a number INSERT INTO pg_catalog.pg_class VALUES (0); -- expect: ERROR: permission denied RESET ROLE;