Postgresql

Tested on: Ubuntu 24.04 LTS, Nginx 1.26.x, Apache 2.4.x, PostgreSQL 16.x, Python 3.12. The principles are language- and stack-independent; the recipes are concrete examples. Why this matters Default logging on every component in a typical stack captures more personal data than the operator usually realises. A standard Nginx access log line: 192.0.2.42 - - [17/May/2026:14:01:12 +0000] "GET /search?q=password+reset+for+alice%40example.com HTTP/2.0" 200 4231 "https://example.com/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 14_4) AppleWebKit/605.1.15..." …retains, for as long as the log is kept: ...

Tested on: PostgreSQL 16.x on Ubuntu 24.04 LTS, restic 0.16.x. The patterns are version-portable to PostgreSQL 14+ with minor command differences; the principles are unchanged. Why this matters A backup you have never restored is a hope. Most “we have backups” incidents reduce to: The backup ran nightly for two years and the most recent twelve failed silently. The backup ran successfully, but the dump’s permissions excluded a schema the application started using six months ago. The backup ran successfully and is intact, but lives on the same disk as the database — so when the disk dies, both die. The backup ran successfully, off-site, encrypted, restorable — but the encryption key was on the same server, and that server is the one that just died. This guide gives you a PostgreSQL backup baseline that survives each of those failure modes. It covers logical backups (pg_dump) for portability, physical backups with WAL archiving for point-in-time recovery, encrypted off-site storage, and a restoration drill cadence. ...

Tested on: Ubuntu 24.04 LTS, PostgreSQL 16.x (apt package from pgdg repository). Commands assume the default data directory at /etc/postgresql/16/main/. Why this matters PostgreSQL ships with defaults that are reasonable for a development laptop and wrong for an internet-exposed server. Three settings in particular cause most real-world incidents: listen_addresses = 'localhost' is fine — until someone changes it to '*' for “convenience” and the database is suddenly reachable from anywhere the firewall lets through. pg_hba.conf defaults still allow trust and md5 in some packaging. trust is no authentication. md5 is no longer considered safe and should be replaced with scram-sha-256. TLS is off by default. Application traffic to the database — including passwords and query results — travels in cleartext on the loopback or on the LAN. This guide fixes those three problems and tightens a handful of related items. It does not cover row-level security, backup hardening, or replication — those get their own guides. ...

What this script does postgres-audit is a read-only Bash + psql script that reports on each recommendation from the PostgreSQL hardening guide: Network exposure (listen_addresses) Authentication: password_encryption, pg_hba.conf entries (no trust, no md5, remote rules using hostssl) TLS (ssl, minimum protocol version) Logging defaults (log_connections, log_disconnections, log_hostname, log_statement) Roles and privileges (non-default SUPERUSER, PUBLIC schema CREATE) It issues only SELECT and SHOW queries. Output is colourised in a terminal and plain text in a pipe, so it works cleanly under cron. ...