DEV Community

Cover image for How to Secure a PostgreSQL Server: Production Security Best Practices
FlatlinerAtheist | CLI-NERDS
FlatlinerAtheist | CLI-NERDS

Posted on Originally published at clinerds.com

How to Secure a PostgreSQL Server: Production Security Best Practices

#database #postgres #security

A fresh PostgreSQL install on Ubuntu or Debian listens only on localhost. Trouble usually starts later, when the port is opened for an app server and nobody goes back to check pg_hba.conf or which role the app logs in as. This guide walks through network exposure, client authentication, TLS, roles, logging, and backups on a production server, with a check for each change.

The examples use PostgreSQL 16 from apt, with configuration in /etc/postgresql/16/main/. Ubuntu 24.04 ships 16. Debian 12's own repository ships 15, so adjust the paths or install 16 from apt.postgresql.org. Check what you have:

pg_lsclusters
Enter fullscreen mode Exit fullscreen mode 
Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
Enter fullscreen mode Exit fullscreen mode

Control where PostgreSQL listens

listen_addresses sets which interfaces the server binds to. The default, localhost, is enough when the application runs on the same machine. If an app server on a private network must connect, add that one private address. * binds every interface, public ones included.

# /etc/postgresql/16/main/postgresql.conf
listen_addresses = 'localhost,10.0.1.10'
Enter fullscreen mode Exit fullscreen mode

This setting is read only at startup, so a reload will not apply it:

sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode 
sudo ss -tlnp | grep 5432
Enter fullscreen mode Exit fullscreen mode

You want 127.0.0.1:5432 (and possibly [::1]:5432) plus 10.0.1.10:5432. Seeing 0.0.0.0:5432 or *:5432 means every interface.

Add a firewall rule that allows only the app server:

sudo ufw allow from 10.0.1.20 to any port 5432 proto tcp
Enter fullscreen mode Exit fullscreen mode

If ufw is not enabled yet, run sudo ufw allow OpenSSH before sudo ufw enable so you keep your session. The SSH hardening guide covers the rest of that side. Then confirm from outside your network that 5432 is closed. If PostgreSQL runs in a container with a published port, Docker's iptables rules bypass ufw, and the Docker Compose stack guide shows how to keep it private.

Admin access from your laptop does not need an open port. Use an SSH tunnel:

ssh -N -L 5432:localhost:5432 admin@db1.example.com
Enter fullscreen mode Exit fullscreen mode 
psql "host=localhost port=5432 dbname=appdb user=app_ro"
Enter fullscreen mode Exit fullscreen mode

If PostgreSQL also runs on your laptop, pick another local port such as 15432. The tunnelled connection reaches the server from localhost, so the localhost rules in pg_hba.conf apply.

Write pg_hba.conf rules that match only what you intend

PostgreSQL reads /etc/postgresql/16/main/pg_hba.conf from top to bottom and uses the first line that matches the connection type, database, user, and client address. There is no fall-through. If that line's authentication fails, the connection is rejected and later lines are never tried. A broad rule near the top therefore overrides any stricter rule below it.

host matches TCP connections with or without TLS, hostssl matches only TLS connections, and hostnossl matches only unencrypted ones. A tight file looks like this:

# TYPE    DATABASE  USER     ADDRESS        METHOD
local     all       postgres                peer
local     all       all                     peer
host      all       all      127.0.0.1/32   scram-sha-256
host      all       all      ::1/128        scram-sha-256
hostssl   appdb     app_rw   10.0.1.20/32   scram-sha-256
hostssl   appdb     app_ro   10.0.1.0/24    scram-sha-256
hostnossl all       all      0.0.0.0/0      reject
Enter fullscreen mode Exit fullscreen mode

Each remote line names one database, one role, and the smallest CIDR that works. The last line rejects unencrypted remote attempts with a clear error. Never use trust on a network line.

Keep the local all postgres peer line, because it lets sudo -u postgres psql work on the server whatever else you break. Before reloading, check for errors. The pg_hba_file_rules view reads the file on disk, so mistakes show up before they take effect:

sudo -u postgres psql -c "SELECT rule_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules;"
Enter fullscreen mode Exit fullscreen mode

If the error column is empty on every row, reload with sudo systemctl reload postgresql or run SELECT pg_reload_conf(); as a superuser. If the file fails to parse, PostgreSQL keeps the previous rules and logs a warning.

Move existing passwords to SCRAM

scram-sha-256 has been the default password_encryption since PostgreSQL 14. MD5 is the legacy, weaker method. A scram-sha-256 rule only works for roles whose stored password is a SCRAM secret, and servers upgraded from older versions often still hold MD5 hashes. Check as a superuser:

SELECT rolname, left(rolpassword, 14) AS hash_prefix
FROM pg_authid
WHERE rolpassword IS NOT NULL;
Enter fullscreen mode Exit fullscreen mode

Rows starting with md5 need their password set again. Confirm SHOW password_encryption; returns scram-sha-256, then reset each one in psql:

\password app_rw
Enter fullscreen mode Exit fullscreen mode

You can type the same password, since only the stored format changes. \password hashes on the client, so the plain text never reaches server logs or shell history.

Turn on TLS and verify it from clients

Debian and Ubuntu packages usually create the cluster with ssl = on and a self-signed "snakeoil" certificate. Traffic is encrypted, but clients cannot verify they reached the right server. See what you have:

grep -E '^ssl' /etc/postgresql/16/main/postgresql.conf
Enter fullscreen mode Exit fullscreen mode

Replace the snakeoil pair with a certificate from your internal CA (or a public CA) issued for the name clients connect to:

ssl = on
ssl_cert_file = '/etc/postgresql/16/main/tls/server.crt'
ssl_key_file = '/etc/postgresql/16/main/tls/server.key'
Enter fullscreen mode Exit fullscreen mode

PostgreSQL refuses a private key that group or other users can read. Give it to postgres with mode 0600:

sudo chown postgres:postgres /etc/postgresql/16/main/tls/server.key
sudo chmod 600 /etc/postgresql/16/main/tls/server.key
Enter fullscreen mode Exit fullscreen mode

TLS settings apply on reload. Run sudo systemctl reload postgresql and check /var/log/postgresql/postgresql-16-main.log for certificate errors.

On clients, libpq's default sslmode=prefer falls back to plain text and never checks the certificate. require encrypts but skips verification. verify-full checks the certificate chain and the hostname:

psql "host=db1.internal.example dbname=appdb user=app_rw sslmode=verify-full sslrootcert=/etc/ssl/certs/internal-ca.crt"
Enter fullscreen mode Exit fullscreen mode

This fails against the snakeoil certificate, as it should. Inside a session, confirm encryption:

SELECT ssl, version, cipher FROM pg_stat_ssl WHERE pid = pg_backend_pid();
Enter fullscreen mode Exit fullscreen mode

Separate the owner role from the application role

If the application role owns its tables, an SQL injection can drop them. Use three roles: app_owner owns the schema and is used only by migrations, app_rw is what the application uses at runtime, and app_ro is for reporting. None of them is a superuser.

CREATE ROLE app_owner LOGIN;
CREATE ROLE app_rw LOGIN CONNECTION LIMIT 40;
CREATE ROLE app_ro LOGIN CONNECTION LIMIT 5;
CREATE DATABASE appdb OWNER app_owner;
Enter fullscreen mode Exit fullscreen mode

Set each password with \password. CONNECTION LIMIT caps concurrent sessions per role, so a leaking connection pool fails with too many connections for role "app_rw" instead of using up max_connections for everyone. Then connect to appdb as postgres:

REVOKE ALL ON DATABASE appdb FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT CONNECT ON DATABASE appdb TO app_rw, app_ro;
GRANT USAGE ON SCHEMA public TO app_rw, app_ro;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_rw;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_ro;
Enter fullscreen mode Exit fullscreen mode

PostgreSQL 15 and later already remove CREATE on public from PUBLIC in new databases. The REVOKE is harmless there and matters on databases carried over from version 14 or older.

ON ALL TABLES covers only existing tables. For tables that migrations create later, set default privileges:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO app_ro;
Enter fullscreen mode Exit fullscreen mode

These fire only for objects created by app_owner. If migrations run as another role, the app gets permission denied after the next deploy. Review them with \ddp, then test the read-only role in a transaction you roll back:

BEGIN;
SET LOCAL ROLE app_ro;
DELETE FROM orders;
ROLLBACK;
Enter fullscreen mode Exit fullscreen mode

The DELETE should fail with permission denied for table orders.

Optional row-level security for shared tables

Skip this unless several tenants share one table. Row-level security makes the database filter rows, so a query that forgets WHERE tenant_id = ... still returns one tenant's data.

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON documents
  USING (tenant_id = current_setting('app.tenant_id', true)::bigint);
Enter fullscreen mode Exit fullscreen mode

The application sets the tenant inside each transaction:

BEGIN;
SET LOCAL app.tenant_id = '42';
SELECT * FROM documents;
COMMIT;
Enter fullscreen mode Exit fullscreen mode

The true argument makes current_setting return NULL when the variable is unset, so no rows match. Without a separate WITH CHECK clause, the same expression also blocks writes into another tenant. Superusers and the table owner bypass policies unless you add FORCE ROW LEVEL SECURITY. Anyone with app_rw credentials can set the variable to any tenant, so this guards against application bugs and does not replace access control in the app.

Log connections without logging secrets

Connection logs record who connected, from where, and to which database:

log_connections = on
log_disconnections = on
log_line_prefix = '%m [%p] %q%u@%d %h '
Enter fullscreen mode Exit fullscreen mode

%u is the user, %d the database, and %h the client host. Reload, then look for failed attempts:

sudo grep -E 'authentication failed|no pg_hba.conf entry' /var/log/postgresql/postgresql-16-main.log
Enter fullscreen mode Exit fullscreen mode

Avoid log_statement = 'all' in production. It writes every statement to the log, including ALTER ROLE ... PASSWORD '...' and any tokens or personal data your application sends in queries. 'ddl' records schema and role changes and is usually enough.

Back up and test restores

Backups are what you fall back on when a compromised account deletes data or a migration goes wrong. Create a directory only postgres can read:

sudo install -d -o postgres -g postgres -m 700 /var/backups/postgres
Enter fullscreen mode Exit fullscreen mode

Dump in custom format, which is compressed and lets pg_restore pick objects:

sudo -u postgres pg_dump -Fc -f /var/backups/postgres/appdb-$(date +%F).dump appdb
Enter fullscreen mode Exit fullscreen mode

pg_dump skips roles. sudo -u postgres pg_dumpall --globals-only -f /var/backups/postgres/globals-$(date +%F).sql captures them, password hashes included, so encrypt any copy that leaves the server. Schedule both with cron; the cron guide covers logging failed runs.

Restore a dump before you need it, into a scratch database:

sudo -u postgres createdb appdb_restore_test
Enter fullscreen mode Exit fullscreen mode 
sudo -u postgres pg_restore -d appdb_restore_test /var/backups/postgres/appdb-2026-09-17.dump
Enter fullscreen mode Exit fullscreen mode

Compare row counts on a few key tables against production, then run sudo -u postgres dropdb appdb_restore_test.

A nightly dump can lose up to a day of writes. For a shorter window, use continuous WAL archiving with point-in-time recovery. pgBackRest manages base backups, WAL archives, retention, and restores, and its user guide covers the setup.

Troubleshooting connection errors

no pg_hba.conf entry for host "10.0.1.20", user "app_rw", database "appdb" means no line matched, and the end of the message says whether the connection was encrypted. Check the client IP the server actually sees (NAT or a Docker bridge can change it), the exact role and database names, and whether a plain connection is meeting hostssl-only rules.

password authentication failed for user "app_rw" right after moving to scram-sha-256 usually means the stored password is still MD5. The server log often adds a DETAIL line saying the user has no valid SCRAM secret. Reset it with \password. Drivers too old for SCRAM fail with authentication method 10 not supported and need upgrading.

When the client is not using TLS, some drivers default to plain connections and hit the reject line. Set sslmode=verify-full. server does not support SSL, but SSL was required means ssl is off on the server. root certificate file ... does not exist means the client needs sslrootcert. A hostname mismatch means the certificate does not include the name or IP you connected to.

Connection refused from a remote host after editing listen_addresses means you reloaded instead of restarting.

Production checklist: PostgreSQL security best practices

If you came here asking "how do I secure a PostgreSQL server?", this list is the short answer. Every item above it is the long answer. The security features that matter most are network binding, pg_hba.conf client rules, SCRAM password authentication, TLS encryption, least-privilege roles, and tested backups — in that order.

  • ss -tlnp shows 5432 only on localhost and private addresses, and an external port check shows it closed.
  • pg_hba.conf uses hostssl with named databases, roles, and narrow CIDRs, all with scram-sha-256.
  • No md5 prefixes remain in pg_authid.
  • The certificate is CA-issued, the key is 0600, and clients use verify-full.
  • The app role is neither owner nor superuser, default privileges are set, and roles have connection limits.
  • Connection logging is on and log_statement is not all.
  • Dumps and globals run nightly, leave the server encrypted, and a restore has been tested.

Top comments (0)