PostgreSQL security hardening (status audit, network, TLS, authentication, privileges, audit, encryption)

This is not an installation guide: it finds and closes the security gaps of a running PostgreSQL server. First the read-only Status audit lists the findings by severity and tells you which card each one leads to; then you close the areas one by one or all of them in order: listen address, firewall and pg_hba; TLS and sslmode=verify-full on the client; scram-sha-256 and blocking network logins for postgres; role design and PUBLIC privileges; pgaudit and central logging; LUKS / pgcrypto; encrypted backups; operating system; version tracking. At the end the audit runs again and the closed findings drop off the list.

Topology: Single PostgreSQL server + application server (client verification); replica/Patroni environments covered in the notes

Scenarios

• Status audit: what are my gaps? (read-only) — The first scan to run: pg_hba, listen address, TLS, md5 passwords, superusers, PUBLIC privileges, audit, file permissions and firewall; it changes nothing.
• Full hardening (all areas in order) — Newly installed or never hardened server: all areas are applied in a safe order; PostgreSQL is restarted once or twice (a maintenance window is required).
• Network and connections: who can connect from where? (listen, firewall, pg_hba) — PostgreSQL listens on all interfaces, pg_hba has 0.0.0.0/0 or trust, the port is open to everyone. Changing listen_addresses requires a restart.
• Encrypt connections (TLS, sslmode=verify-full) — ssl = off or clients do not verify the certificate; server certificate, hostssl and verify-full on the client. Only a reload is needed, no downtime.
• Authentication and passwords (scram-sha-256, remote login for postgres) — md5/password method, md5-hashed passwords, the postgres user can log in over the network; migration to scram-sha-256 and a personal DBA account. No downtime; passwords are reset.
• Least privilege: roles, PUBLIC and the application account — The application account owns the tables or is a superuser, everyone can write to the public schema; read/write/owner roles and a privilege report. No downtime.
• Audit: who did what? (pgaudit, central logging) — The auditor or the security team wants a record of DDL and role changes; pgaudit installation and shipping the log to a central server. One restart is required.
• Encrypt data at rest (LUKS / pgcrypto) — Protection against a stolen disk or backup is required. PostgreSQL has no built-in TDE: LUKS at the disk level (downtime for the move) or pgcrypto at the column level.
• Backup security (encrypted backups, permissions) — Backup files are unencrypted or in a world-readable directory; pgBackRest repository encryption or pg_dump + GPG, .pgpass and directory permissions. No downtime.
• Operating system side (service account, permissions, core dumps, SELinux) — The postgres account has a usable password, data directory permissions are loose, SELinux is Permissive/Disabled. The core dump setting takes effect at the next restart.
• Patches and version: am I on a supported version? — Checks the version, end-of-support date and pending minor updates; links to the patch guide for applying them. Read-only.

Before you start

• PostgreSQL … is running and you can connect as postgres from the server itself (sudo -iu postgres psql) without a password (local all postgres peer in pg_hba). If it was installed with the PostgreSQL installation guide, this is already in place.
• Data directory …, configuration directory …; psql and pg_dump are under …. They are on the PATH of the postgres user's login shell; when calling them as root, use the full path.
• Two separate SSH sessions are open to the server: you apply the steps in one; the other (with sudo -iu postgres psql open) is for rolling back if a wrong rule locks you out. After every step that restricts access, test the new connection in a third session; do not close the old sessions until you see it succeed.
• Maintenance window: PostgreSQL will be restarted for the areas you selected (listen_addresses, shared_preload_libraries, core dump limit, disk move). The application team has been informed.
• You have the list of all applications that connect to the database and their connection details (server IPs, users, driver versions). Before restricting anything, the audit step answers who connects from where; a client not on the list cannot connect after the restriction.
• There is a current backup with a tested restore (pgBackRest or pg_dump). Hardening does not touch the data, but role/privilege changes and the disk move are prone to errors that cannot be undone.

Phases of the guide

• Status audit (read-only) — List the in-database findings · List the schema and function findings in every database · Audit the operating system side
• Before you start: fallback point — Take a copy of the current configuration and roles · Enable the additional configuration directory (conf.d)
• Privileges — 1. Diagnosis — Show roles, ownership and PUBLIC privileges
• Privileges — 2. Safety net — Dump the current privileges
• Privileges — 3. Apply — Create the group roles and login accounts · Set passwords for the new login accounts · Set up database and schema privileges · Transfer the objects owned by the application account to the owner role · Privilege report
• Privileges — 4. Verification — Verify that the application account cannot run DDL and can write data
• Privileges — 5. Follow-up — Switch the migration tool to the owner role
• Authentication — 1. Diagnosis — Show password hashes, methods and accounts
• Authentication — 2. Safety net — Check driver compatibility
• Authentication — 3. Apply — Store new passwords with scram-sha-256 · Load the setting and reset the passwords of md5 accounts · Change the md5/password method in pg_hba to scram-sha-256 · Block network logins for the postgres superuser · Create the personal DBA account
• Authentication — 4. Verification — Verify the hashes and the rejection of superuser network logins · scram login from the application server and rejection of postgres
• Authentication — 5. Follow-up — Review unused and expired accounts
• TLS — 1. Diagnosis — Show the current SSL status and unencrypted clients
• TLS — 2. Safety net — Prepare the certificate directory
• TLS — 3. Apply — Create a test CA and the server certificate · Create the key and the certificate signing request (CSR) · Put the signed certificate and the CA chain in place · Verify the certificate, key and chain · Enable TLS on the server · Load the settings and verify that TLS is enabled …
• TLS — 4. Verification — Verify that unencrypted connections are rejected
• TLS — 5. Follow-up: certificate renewal — Certificate renewal procedure
• Network — 1. Diagnosis: who connects from where? — Show the listen address, pg_hba rules and active clients
• Network — 2. Safety net — Keep the fallback session open
• Network — 3. Apply — Narrow the listen address · Rewrite pg_hba.conf with least access · Check and load the rules · Open the port in the firewall only to allowed networks · Restart PostgreSQL for the listen address
• Network — 4. Verification — Test an allowed and a rejected connection from the application server
• Network — 5. Follow-up — Monitor rejected connections
• Audit — 1. Diagnosis — Show the audit and log settings
• Audit — 2. Safety net — Check the location and free space of the log disk
• Audit — 3. Apply — Install the pgaudit package · Add pgaudit to the preloaded libraries and write the settings · Restart to activate pgaudit · Create the pgaudit extension · Forward the log to the central server (rsyslog) · Retention of the local audit file (logrotate) …
• Audit — 4. Verification — Make a DDL and a role change and see them in the log
• Audit — 5. Follow-up — Monitor attempts to disable auditing
• Backup — 1. Diagnosis — Show the encryption status and permissions of the backups
• Backup — 2. Safety net — Prepare the place where the encryption key will be stored
• Backup — 3. Apply — Define the new encrypted repository · Create the encrypted repository and take the first full backup · Import the GPG public key on the server · Encrypted backup script · Backup directory, permissions and manual test · Schedule the encrypted backup (cron) …