How to Run PostgreSQL Securely on a VPS

The default PostgreSQL install on Ubuntu is already reasonably safe: it listens on loopback only and uses peer authentication for local sockets. Most security incidents come from changes people make afterwards — opening listen_addresses = '*' to get a remote client working, and handing the application the postgres superuser because a GRANT failed.

This covers the four things that matter: where it listens, how clients authenticate, whether the connection is encrypted, and what the application role can actually do.

Requirements

PostgreSQL 17 from the PGDG repository on Ubuntu 24.04. Configuration lives in Debian’s layout:

sudo -u postgres psql -c 'SHOW config_file;'

              config_file
---------------------------------------
 /etc/postgresql/17/main/postgresql.conf

Everything below assumes that path. On a Docker install the same settings apply but arrive as command flags or a mounted config file.

Keep the listener off the public internet

# Same-host application: leave it here.
listen_addresses = 'localhost'

# Another server on a private network: bind that interface explicitly.
# listen_addresses = 'localhost,10.0.1.14'

port = 5432
max_connections = 100

Never '*'. If a remote client needs access, name the private address. If the client is across the internet, use a VPN or an SSH tunnel instead:

ssh -N -L 5432:localhost:5432 deploy@db.example.com

Confirm what is actually bound after a restart:

sudo systemctl restart postgresql@17-main
sudo ss -tlnp | grep 5432

LISTEN 0  200  127.0.0.1:5432  0.0.0.0:*  users:(("postgres",pid=8842,fd=6))

127.0.0.1:5432 is correct. 0.0.0.0:5432 means the port is reachable from anywhere your firewall allows.

Authentication rules

pg_hba.conf is evaluated top to bottom and the first matching line wins — including when it rejects. A permissive line above a strict one makes the strict one dead code.

# TYPE     DATABASE  USER      ADDRESS        METHOD
local      all       postgres                 peer
local      all       all                      scram-sha-256

# Application on this host, over loopback.
host       appdb     app_rw    127.0.0.1/32   scram-sha-256

# Read-only analytics from the private network, TLS required.
hostssl    appdb     app_ro    10.0.1.0/24    scram-sha-256

# Nothing else.
host       all       all       0.0.0.0/0      reject
hostssl    all       all       0.0.0.0/0      reject

Three deliberate choices there:

scram-sha-256 rather than md5. MD5 password verifiers are trivially crackable from a database dump. Make sure new passwords are hashed that way:

password_encryption = scram-sha-256

Existing users keep their old verifier until the password is set again, so after changing this, run ALTER ROLE ... PASSWORD ... for every role.

hostssl for the non-loopback rule, so a client cannot negotiate plaintext.

An explicit reject at the bottom. The implicit default is also rejection, but a written rule shows in pg_hba_file_rules and makes the intent reviewable.

Reload and check that PostgreSQL parsed the file the way you meant:

sudo -u postgres psql -c 'SELECT pg_reload_conf();'
sudo -u postgres psql -x -c \
  'SELECT line_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules;'

Any row with a non-null error is a line PostgreSQL ignored.

Turn on TLS

Generate a certificate for the server’s hostname. Self-signed is fine when you control the clients.

cd /etc/postgresql/17/main
sudo openssl req -new -x509 -days 825 -nodes \
  -subj "/CN=db.internal.example.com" \
  -addext "subjectAltName=DNS:db.internal.example.com" \
  -out server.crt -keyout server.key
sudo chmod 600 server.key
sudo chown postgres:postgres server.crt server.key

ssl = on
ssl_cert_file = '/etc/postgresql/17/main/server.crt'
ssl_key_file = '/etc/postgresql/17/main/server.key'
ssl_min_protocol_version = 'TLSv1.2'

PostgreSQL refuses to start if server.key is group- or world-readable, which is a useful check rather than an obstacle.

On the client, sslmode=require encrypts but does not verify the server — it stops passive sniffing, not an active attacker. Distribute server.crt and use verify-full:

psql "host=db.internal.example.com dbname=appdb user=app_ro \
      sslmode=verify-full sslrootcert=/etc/ssl/certs/pg-internal.crt"

Create a least-privilege application role

The application must not own its own schema. Separate the owner from the login.

-- Owner holds the objects. Nobody logs in as it.
CREATE ROLE app_owner NOLOGIN;
CREATE DATABASE appdb OWNER app_owner;

\connect appdb

-- Strip the rights every role gets implicitly.
REVOKE ALL ON DATABASE appdb FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

CREATE SCHEMA app AUTHORIZATION app_owner;

-- Read/write role used by the application.
CREATE ROLE app_rw LOGIN PASSWORD 'generate-this-do-not-type-it';
GRANT CONNECT ON DATABASE appdb TO app_rw;
GRANT USAGE ON SCHEMA app TO app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_rw;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_rw;

-- Read-only role for analytics and dashboards.
CREATE ROLE app_ro LOGIN PASSWORD 'a-different-generated-string';
GRANT CONNECT ON DATABASE appdb TO app_ro;
GRANT USAGE ON SCHEMA app TO app_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_ro;

-- Future tables created by app_owner inherit these grants.
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT USAGE, SELECT ON SEQUENCES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO app_ro;

The ALTER DEFAULT PRIVILEGES lines are the ones people miss. Without them, every migration that creates a table produces ERROR: permission denied for table ... the next time the app runs.

Generate passwords rather than inventing them:

openssl rand -base64 24

Timeouts, limits and logging

A single stuck client holding an open transaction blocks VACUUM and can hold locks that stall writes. Set boundaries at the role level so they apply to the application without affecting your own admin sessions:

ALTER ROLE app_rw SET statement_timeout = '30s';
ALTER ROLE app_rw SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE app_rw SET lock_timeout = '5s';
ALTER ROLE app_rw CONNECTION LIMIT 40;

Logging that is worth having on:

log_connections = on
log_disconnections = on
log_min_duration_statement = 500      # log statements slower than 500 ms
log_line_prefix = '%m [%p] %q%u@%d '
log_lock_waits = on

log_min_duration_statement gives you slow queries without the volume of logging everything. log_connections turns a suspicious login into something you can find afterwards.

The CONNECTION LIMIT above matters more than it looks. PostgreSQL allocates a backend process per connection, each with its own memory, so max_connections is a resource decision rather than a capacity dial. Raising it to fix too many clients already trades one failure for a slower, harder-to-diagnose one.

The actual fix is a pooler. PgBouncer in transaction mode multiplexes hundreds of idle application connections onto a handful of server ones, because most application connections are idle most of the time. Point the application at the pooler, keep max_connections modest, and set the per-role limit as a backstop so one misbehaving service cannot consume every slot.

Transaction mode has one constraint worth knowing before you adopt it: session state does not survive between transactions. SET outside a transaction, session-level advisory locks and LISTEN/NOTIFY all behave differently. Most web applications are unaffected; check yours rather than assuming.

Verify

# 1. Correct listener
sudo ss -tlnp | grep 5432

# 2. The app role cannot create tables
psql "postgresql://app_rw@127.0.0.1/appdb" \
  -c 'CREATE TABLE should_fail (id int);'

ERROR:  permission denied for schema app

# 3. Remote connections are actually encrypted
psql "host=10.0.1.14 dbname=appdb user=app_ro sslmode=verify-full" \
  -c 'SELECT ssl, version, cipher FROM pg_stat_ssl WHERE pid = pg_backend_pid();'

 ssl | version |         cipher
-----+---------+------------------------
 t   | TLSv1.3 | TLS_AES_256_GCM_SHA384

# 4. Nothing else on the network can reach the port
nmap -Pn -p 5432 203.0.113.42

That last one must report filtered or closed.

Common errors

FATAL: no pg_hba.conf entry for host "10.0.1.9", user "app_ro", database "appdb", no encryption — the client connected without TLS and only a hostssl rule matches. Add sslmode=require or better on the client side.

FATAL: password authentication failed for user "app_rw" with a password you know is right — the role’s stored verifier is still MD5 after you switched password_encryption. Re-set the password with ALTER ROLE.

connection to server at "127.0.0.1", port 5432 failed: Connection refused — PostgreSQL is not running, or listen_addresses does not include the address you are dialling. Check ss -tlnp before anything else.

FATAL: role "app_owner" is not permitted to log in — working as intended. NOLOGIN roles hold privileges; they are not accounts.

ERROR: permission denied for table invoices for a table that was just created by a migration — ALTER DEFAULT PRIVILEGES was not set, or the migration ran as a role other than app_owner.

FATAL: sorry, too many clients already — connections exceed max_connections. Raising it is rarely the fix; a connection pooler such as PgBouncer in transaction mode is, because idle application connections are what consume the slots.

PostgreSQL will not start after enabling TLS, with private key file "server.key" has group or world access in the log. Set it to 0600 and owned by postgres.

Production notes

Take backups with pg_dump -Fc at minimum, and add WAL archiving when losing a day of writes is not acceptable. Restore one into a scratch database on a schedule — the untested backup is the one that fails.

Keep the server patched. PostgreSQL minor releases are bug and security fixes only and are safe to apply; they need a restart, not a dump and reload. Major version upgrades are a separate exercise with a maintenance window.

Be deliberate about extensions. CREATE EXTENSION generally requires elevated privileges, and some extensions execute code with the server’s operating-system permissions. Install the ones you need as postgres, then leave the application role without the privilege to add more.

Set the search_path explicitly on the application role rather than relying on the default. A role whose search_path still includes a writable schema can be tricked into calling a shadowing function:

ALTER ROLE app_rw SET search_path = app, pg_catalog;

Revoking CREATE ON SCHEMA public from PUBLIC, as the setup above does, closes most of that hole. Pinning the search path closes the rest.

Audit roles quarterly:

SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolcanlogin
FROM pg_roles
WHERE rolname NOT LIKE 'pg\_%'
ORDER BY rolsuper DESC, rolname;

Anything with rolsuper = t other than postgres needs a justification you can state out loud.