PostgreSQL Security Hardening: A Practical Checklist for Production

When a database is breached, the post-mortem rarely describes a sophisticated zero-day. Far more often it describes a PostgreSQL port open to the internet, an application connecting as a superuser, a password shared across five systems, or a backup sitting unencrypted in a storage bucket. These gaps are boring — and that is exactly why they survive for years. This is the checklist we work through on a security review, ordered from the most common problems to the more advanced ones.

1. Limit who can reach the database at all

The cheapest security control is not being reachable. PostgreSQL should listen only on the interfaces it needs (listen_addresses), and a firewall or cloud security group should allow connections only from application servers, poolers and administrative hosts.

• No direct access from the internet — administrators connect through a VPN or a bastion host.
• Separate network rules for production and test environments, so a compromised test server cannot reach production data.
• Review the rules regularly: temporary "just for this migration" openings have a habit of becoming permanent.

2. Make pg_hba.conf strict and explicit

The pg_hba.conf file decides which users may connect to which databases, from where, and how they must authenticate. Read it line by line — rules are matched top to bottom, and the first match wins.

• Never use trust for network connections. It allows login without any password.
• Use scram-sha-256 instead of md5. SCRAM has been the default password encryption since PostgreSQL 14; old MD5 hashes stay MD5 until each password is reset.
• Use hostssl rather than host for remote connections, so unencrypted logins are refused.
• Be specific. A line allowing all users to all databases from 0.0.0.0/0 is a finding in itself.

3. Apply least privilege to roles

Applications should not connect as postgres or any other superuser. A practical role model:

• An owner role that owns the schema and is used only for migrations.
• An application role with only the SELECT, INSERT, UPDATE and DELETE rights it needs.
• A read-only role for reporting and analytics. The predefined roles pg_read_all_data and pg_monitor cover common needs without granting superuser.
• Personal accounts for administrators instead of a shared login, so actions can be traced to a person.

Also check the public schema. Since PostgreSQL 15 ordinary users can no longer create objects in it by default, but databases upgraded from older versions keep the old, more permissive rights unless you revoke them: REVOKE CREATE ON SCHEMA public FROM PUBLIC;

4. Encrypt data in transit and at rest

• Enable TLS on the server (ssl = on) and use a certificate from your internal or public CA. Configure clients with sslmode=verify-full so they also check the server's identity.
• Encrypt disks or volumes. In the cloud this is usually a checkbox — make sure it was ticked for every instance, snapshot and replica.
• Encrypt backups and WAL archives, and keep the keys separate from the backups themselves.

5. Log and audit what matters

Without logs you cannot answer the first question after an incident: what happened, and when? At minimum enable log_connections and log_disconnections, and log DDL with log_statement = 'ddl'. For regulated environments, the pgAudit extension provides detailed, structured audit logging of reads and writes on sensitive tables. Ship logs off the database server, so an attacker with server access cannot quietly erase them.

6. Protect backups as carefully as the database

A backup is a complete copy of your data, often with weaker protection than the original. Restrict who can read the backup storage, encrypt it, keep at least one copy that cannot be deleted or overwritten from the production environment, and — as we describe in our guide to restore testing — prove regularly that it can actually be restored.

7. Keep PostgreSQL patched and supported

Minor releases contain security fixes and are designed to be applied with a simple restart. Apply them within weeks, not years. And make sure every database runs a supported major version: PostgreSQL 14, for example, receives its final update in November 2026 — see what to do about PostgreSQL 14 end of life.

Why this matters for compliance

For many organisations in Latvia and across the EU, NIS2 and the national laws implementing it turn these practices from "good idea" into an obligation: access control, encryption, logging, backup and vulnerability management are all explicitly expected. A documented hardening checklist, applied and reviewed, is also exactly the evidence an auditor will ask for.