postgres-audit

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.

Exit codes are designed for unattended use:

Code	Meaning
0	every check PASS
1	at least one FAIL
2	no FAIL but at least one WARN
3	could not connect

Usage

 1# Download (audit the source first — see "The script" below).
 2curl -fsSL https://stackharden.com/scripts/postgres-audit.sh -o postgres-audit
 3chmod +x postgres-audit
 4
 5# Run as the postgres superuser via the local socket
 6# (needed for the pg_hba.conf rule checks):
 7sudo -u postgres ./postgres-audit
 8
 9# Or via a dedicated auditor role over TCP:
10PSQL="psql -U auditor -h 127.0.0.1 -d postgres" ./postgres-audit

The pg_hba.conf rule queries read the pg_hba_file_rules view, which requires either superuser or membership in pg_read_server_files (PostgreSQL 16+). The script reports “Could not read pg_hba_file_rules” and continues if the role lacks that privilege — the remaining checks still produce useful output.

The script

The source rendered below is the same file served at /scripts/postgres-audit.sh — Hugo reads the downloadable directly at build time, so what you read here is what you would run.

  1#!/usr/bin/env bash
  2#
  3# postgres-audit — Read-only PostgreSQL configuration audit.
  4#
  5# Purpose:    Reports on every hardening recommendation from
  6#             https://stackharden.com/guides/postgresql-hardening/
  7# Companion:  /guides/postgresql-hardening/
  8# Tested on:  Ubuntu 24.04 LTS, PostgreSQL 14 / 15 / 16 / 17
  9# Author:     StackHarden — https://stackharden.com
 10# Date:       2026-05-16
 11# Licence:    MIT
 12#
 13# Usage:
 14#   sudo -u postgres ./postgres-audit                  # local socket as superuser
 15#   PSQL="psql -U auditor -h 127.0.0.1 -d postgres" ./postgres-audit
 16#
 17# Issues only SELECT and SHOW queries. Does NOT modify state.
 18#
 19# Exit codes:
 20#   0 — every check PASS
 21#   1 — at least one FAIL
 22#   2 — no FAIL but at least one WARN
 23#   3 — could not connect
 24
 25set -euo pipefail
 26
 27# psql invocation. -A unaligned, -t tuples-only, -X skip ~/.psqlrc.
 28: "${PSQL:=psql -At -X}"
 29
 30# Colourise when stdout is a terminal; stay plain when piped/redirected.
 31if [ -t 1 ]; then
 32  C_RED=$'\033[31m'; C_YEL=$'\033[33m'; C_GRN=$'\033[32m'; C_OFF=$'\033[0m'
 33else
 34  C_RED=''; C_YEL=''; C_GRN=''; C_OFF=''
 35fi
 36
 37FAILS=0; WARNS=0; PASSES=0
 38pass() { PASSES=$((PASSES+1)); printf '  [%sPASS%s] %s\n' "$C_GRN" "$C_OFF" "$1"; }
 39warn() { WARNS=$((WARNS+1));  printf '  [%sWARN%s] %s\n' "$C_YEL" "$C_OFF" "$1"; }
 40fail() { FAILS=$((FAILS+1));  printf '  [%sFAIL%s] %s\n' "$C_RED" "$C_OFF" "$1"; }
 41hdr()  { printf '\n%s\n' "$1"; }
 42
 43# Run a SQL query and return the scalar result, trimmed.
 44q() {
 45  echo "$1" | $PSQL 2>/dev/null \
 46    | sed -e 's/^[[:space:]]*//' -e 's/[[:space:]]*$//'
 47}
 48
 49printf 'PostgreSQL audit — %s\n' "$(date -u +%Y-%m-%dT%H:%M:%SZ)"
 50printf 'See https://stackharden.com/guides/postgresql-hardening/ for context.\n'
 51
 52# ---------- Connectivity / version ----------
 53VER=$(q "SHOW server_version;")
 54if [ -z "$VER" ]; then
 55  printf '\nERROR: could not connect to PostgreSQL (PSQL=%s)\n' "$PSQL" >&2
 56  exit 3
 57fi
 58printf '\nServer version: %s\n' "$VER"
 59
 60# ---------- Network exposure ----------
 61hdr "Network exposure"
 62LISTEN=$(q "SHOW listen_addresses;")
 63case "$LISTEN" in
 64  localhost|127.0.0.1|::1)
 65    pass "listen_addresses = $LISTEN" ;;
 66  '*')
 67    fail "listen_addresses = '*' — bound to every interface" ;;
 68  *)
 69    warn "listen_addresses = $LISTEN — verify this is a private interface" ;;
 70esac
 71
 72# ---------- Authentication ----------
 73hdr "Authentication"
 74
 75PW_ENC=$(q "SHOW password_encryption;")
 76case "$PW_ENC" in
 77  scram-sha-256) pass "password_encryption = scram-sha-256" ;;
 78  md5)           fail "password_encryption = md5 — switch to scram-sha-256" ;;
 79  *)             warn "password_encryption = $PW_ENC" ;;
 80esac
 81
 82# pg_hba_file_rules requires superuser (or pg_read_server_files on PG 16+).
 83TRUST_COUNT=$(q "SELECT count(*) FROM pg_hba_file_rules WHERE auth_method = 'trust';" 2>/dev/null || true)
 84if [ -z "$TRUST_COUNT" ]; then
 85  warn "Could not read pg_hba_file_rules (need superuser or pg_read_server_files)"
 86else
 87  if [ "$TRUST_COUNT" -eq 0 ]; then
 88    pass "No 'trust' entries in pg_hba.conf"
 89  else
 90    fail "$TRUST_COUNT 'trust' entry/entries in pg_hba.conf — review immediately"
 91  fi
 92
 93  MD5_COUNT=$(q "SELECT count(*) FROM pg_hba_file_rules WHERE auth_method = 'md5';")
 94  if [ "$MD5_COUNT" -eq 0 ]; then
 95    pass "No 'md5' auth entries in pg_hba.conf"
 96  else
 97    warn "$MD5_COUNT 'md5' entry/entries — prefer scram-sha-256"
 98  fi
 99
100  REMOTE_HOST=$(q "SELECT count(*) FROM pg_hba_file_rules WHERE type = 'host' AND address IS NOT NULL AND address NOT IN ('127.0.0.1/32','::1/128');")
101  if [ "$REMOTE_HOST" -eq 0 ]; then
102    pass "No remote cleartext 'host' entries in pg_hba.conf"
103  else
104    warn "$REMOTE_HOST remote 'host' entry/entries — prefer 'hostssl'"
105  fi
106fi
107
108# ---------- TLS ----------
109hdr "TLS"
110SSL=$(q "SHOW ssl;")
111if [ "$SSL" = "on" ]; then
112  pass "ssl = on"
113  MIN_TLS=$(q "SHOW ssl_min_protocol_version;")
114  case "$MIN_TLS" in
115    TLSv1.2|TLSv1.3) pass "ssl_min_protocol_version = $MIN_TLS" ;;
116    *)               warn "ssl_min_protocol_version = $MIN_TLS — set to TLSv1.2 or higher" ;;
117  esac
118else
119  fail "ssl = $SSL — TLS not enabled"
120fi
121
122# ---------- Logging ----------
123hdr "Logging"
124for s in log_connections log_disconnections; do
125  v=$(q "SHOW $s;")
126  if [ "$v" = "on" ]; then
127    pass "$s = on"
128  else
129    warn "$s = $v — recommended: on"
130  fi
131done
132
133LOG_HOSTNAME=$(q "SHOW log_hostname;")
134if [ "$LOG_HOSTNAME" = "off" ]; then
135  pass "log_hostname = off"
136else
137  warn "log_hostname = on — DNS adds latency and may capture PII"
138fi
139
140LOG_STMT=$(q "SHOW log_statement;")
141case "$LOG_STMT" in
142  ddl|none) pass "log_statement = $LOG_STMT" ;;
143  all)      warn "log_statement = all — may capture personal data in literals" ;;
144  mod)      warn "log_statement = mod — verify whether SELECT logging is needed too" ;;
145  *)        warn "log_statement = $LOG_STMT" ;;
146esac
147
148# ---------- Roles & privileges ----------
149hdr "Roles & privileges"
150
151SUPER_NONDEFAULT=$(q "SELECT count(*) FROM pg_roles WHERE rolsuper AND rolname <> 'postgres';")
152if [ "$SUPER_NONDEFAULT" -eq 0 ]; then
153  pass "No non-postgres roles have SUPERUSER"
154else
155  warn "$SUPER_NONDEFAULT non-postgres role(s) have SUPERUSER"
156fi
157
158# PUBLIC schema CREATE — PG 15+ defaults to revoked; older versions need
159# REVOKE CREATE ON SCHEMA public FROM PUBLIC.
160PUBLIC_CREATE=$(q "SELECT has_schema_privilege('public','public','CREATE');")
161case "$PUBLIC_CREATE" in
162  f) pass "PUBLIC has no CREATE on schema public" ;;
163  t) warn "PUBLIC has CREATE on schema public — REVOKE CREATE ON SCHEMA public FROM PUBLIC;" ;;
164  *) warn "Could not determine PUBLIC schema permissions" ;;
165esac
166
167# ---------- Summary ----------
168printf '\nSummary: %sPASS %d%s · %sWARN %d%s · %sFAIL %d%s\n' \
169  "$C_GRN" "$PASSES" "$C_OFF" \
170  "$C_YEL" "$WARNS"  "$C_OFF" \
171  "$C_RED" "$FAILS"  "$C_OFF"
172
173if   [ "$FAILS" -gt 0 ]; then exit 1
174elif [ "$WARNS" -gt 0 ]; then exit 2
175else                          exit 0
176fi

Download postgres-audit.sh · Companion guide

What this script deliberately does not cover

Backup posture — see postgres-backups; a companion script is planned.
Replication and replica permissions — separate scope.
Extension-specific configuration (PostGIS, pgvector, pg_partman, etc.).
Performance tuning — this is a security audit; performance checks are a separate concern with different defensible defaults.

What this script does#

Usage#

The script#

What this script deliberately does not cover#

What this script does

Usage

The script

What this script deliberately does not cover