Skip to main content

Security Hardening

Learning Focus

Use this lesson to harden PostgreSQL in production: tighten pg_hba.conf, require SCRAM and TLS, remove unsafe PUBLIC defaults, and add safe operational guardrails (timeouts, logging, and auditing).

Concept Overview​

Security hardening is layered:

  1. network exposure and TLS
  2. authentication policy (pg_hba.conf)
  3. least-privilege roles and grants
  4. audit and monitoring

Why is it important?​

  • Prevent unauthorized access: restrict who can even attempt to connect.
  • Protect credentials and data in transit: TLS prevents sniffing.
  • Reduce blast radius: least privilege limits what compromised credentials can do.
  • Detect incidents early: connection/audit logging gives you evidence and alerts.

Where does it fit?​

Hardening complements SQL-level grants:

  • pg_hba.conf and TLS decide who can connect and how
  • roles/grants decide what they can do once connected
  • logging and timeouts decide how failures are detected and contained

Syntax & Rules​

pg_hba.conf rule format​

Each line in pg_hba.conf is a rule:

TYPE  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
FieldMeaning
TYPEconnection type: local, host, hostssl, hostnossl
DATABASEdatabase(s) matched by rule
USERrole(s) matched by rule
ADDRESSclient IP/CIDR (host rules)
METHODauthentication method: scram-sha-256, cert, peer, etc.

Common methods:

MethodTypical useNotes
scram-sha-256password auth over networkrecommended modern password auth
md5legacy password authavoid for new deployments
peerlocal unix socket authcommon for local admin workflows
certclient certificate authstrong, but requires cert management

TLS essentials (server-side)​

TLS is configured in postgresql.conf (file paths vary by distro):

# postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_min_protocol_version = 'TLSv1.2'

To force TLS for remote clients, use hostssl rules in pg_hba.conf.

Operational guardrails (SQL-level)​

You can set safe defaults at the role level:

ALTER ROLE app_user SET statement_timeout = '15s';
ALTER ROLE app_user SET lock_timeout = '2s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';

Key rules and considerations​

  • Harden from the outside in: network rules, then authentication, then authorization.
  • Prefer scram-sha-256 over md5 for password authentication.
  • Use hostssl + TLS in production for any remote connections.
  • Revoke unsafe default privileges from PUBLIC to reduce accidental access.
  • Keep app roles non-superuser and non-owner whenever possible.

Step-by-Step Examples​

Example 1: Remove risky PUBLIC defaults (SQL)​

Many outages and incidents start with overly broad defaults. These revokes are a common baseline.

-- Database-level: prevent everyone from connecting by default
REVOKE ALL ON DATABASE pg_lab FROM PUBLIC;

-- Schema-level: public schema is often too permissive for shared environments
REVOKE ALL ON SCHEMA public FROM PUBLIC;

-- Optional: prevent creating objects in public schema
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Expected output:

REVOKE
REVOKE
REVOKE

Explanation:

  • PUBLIC is a pseudo-role that includes all roles.
  • Tightening PUBLIC defaults is most important on shared clusters.

Example 2: Require SCRAM for new passwords​

Ensure PostgreSQL stores new passwords as SCRAM hashes.

SHOW password_encryption;

Expected output (recommended):

 password_encryption
---------------------
 scram-sha-256
(1 row)

If you need to change it (superuser action):

ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();
SHOW password_encryption;

Expected output:

ALTER SYSTEM
 pg_reload_conf
---------------
 t
(1 row)

 password_encryption
---------------------
 scram-sha-256
(1 row)

Explanation:

  • Existing passwords are not re-hashed automatically; users must change passwords to pick up the new method.

Example 3: Set per-role timeouts to prevent lock hoarding​

Role-level defaults are an effective safety net.

ALTER ROLE app_user SET statement_timeout = '15s';
ALTER ROLE app_user SET lock_timeout = '2s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';

Expected output:

ALTER ROLE
ALTER ROLE
ALTER ROLE

Verify (new session for app_user is the simplest; shown here as conceptual checks):

SHOW statement_timeout;
SHOW lock_timeout;
SHOW idle_in_transaction_session_timeout;

Expected output:

 statement_timeout
-------------------
 15s
(1 row)

 lock_timeout
--------------
 2s
(1 row)

 idle_in_transaction_session_timeout
------------------------------------
 30s
(1 row)

Explanation:

  • These settings reduce the chance that a single stuck request blocks many others.

Example 4: Tighten pg_hba.conf (configuration)​

Example: allow only app servers on a private subnet, require TLS and SCRAM.

# pg_hba.conf
# TYPE    DATABASE  USER       ADDRESS         METHOD
hostssl   pg_lab    app_user   10.0.1.0/24     scram-sha-256

# Local admin via unix socket
local     all       postgres                    peer

# Reject everything else (place at end)
host      all       all        0.0.0.0/0       reject

Expected outcome:

- Connections from 10.0.1.x must use TLS and SCRAM
- Connections from other networks are rejected

Explanation:

  • hostssl enforces TLS.
  • Rule order matters: PostgreSQL uses the first matching rule.

Practical Use Cases​

1) Production remote access​

Require TLS + SCRAM and restrict allowed subnets.

hostssl app_db app_user 10.0.1.0/24 scram-sha-256

2) CI/CD migrations​

Use a migration role with elevated DDL privileges; keep runtime roles limited.

CREATE ROLE app_migrator LOGIN PASSWORD 'use-a-secret-manager';
GRANT app_owner TO app_migrator;

3) Compliance auditing​

Enable connection logging and keep named accounts (no shared logins).

SHOW log_connections;

4) Multi-tenant SaaS​

RLS prevents cross-tenant reads even when application bugs occur.

ALTER TABLE app.invoices ENABLE ROW LEVEL SECURITY;

Common Mistakes & Troubleshooting​

Mistake 1: Wide-open pg_hba.conf​

Wrong (too permissive):

host all all 0.0.0.0/0 md5

Risk: anyone on the internet can attempt password guessing.

Fix: restrict networks and require TLS + SCRAM:

hostssl app_db app_user 10.0.1.0/24 scram-sha-256
host all all 0.0.0.0/0 reject

Mistake 2: Apps running as superuser​

Bad outcome:

  • full compromise blast radius

Fix:

  • create least-privilege runtime roles
  • reserve superuser for DBAs and automation that truly needs it

Mistake 3: No TLS for remote connections​

Bad outcome:

  • credentials and data can be intercepted on the network

Fix:

  • enable TLS in postgresql.conf
  • use hostssl rules

Mistake 4: Logging secrets​

Bad outcome:

  • statement logging can capture sensitive parameters

Fix:

  • log what you need (connections, slow queries) and avoid verbose statement logging for sensitive workloads

Debugging tips (numbered)​

  1. Identify config locations:
SHOW config_file;
SHOW hba_file;
  1. Validate TLS status:
SHOW ssl;
  1. Confirm auth method being used by matching the client IP/user/db against pg_hba.conf in order.

Best Practices​

✅ Restrict network access (firewall + listen_addresses + pg_hba)
❌ Don’t expose PostgreSQL to the public internet

✅ Require TLS for remote connections (hostssl)
❌ Don’t send credentials or data in plaintext

✅ Prefer SCRAM (scram-sha-256)
❌ Avoid MD5 for new deployments

✅ Use least-privilege runtime roles
❌ Don’t run apps as superuser or as the object owner

✅ Add safety rails: timeouts + lock wait logging
❌ Don’t allow unbounded lock waits or idle-in-transaction sessions

Hands-On Practice​

Exercise 1 (Easy): Revoke risky PUBLIC privileges​

Task: revoke database connect and public schema create privileges from PUBLIC.

Solution:

REVOKE ALL ON DATABASE pg_lab FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Exercise 2 (Medium): Add per-role timeouts​

Task: set statement_timeout=10s and lock_timeout=1s for app_user.

Solution:

ALTER ROLE app_user SET statement_timeout = '10s';
ALTER ROLE app_user SET lock_timeout = '1s';

Exercise 3 (Advanced): Draft a hardened pg_hba.conf policy​

Task: write three pg_hba.conf lines:

  1. allow TLS + SCRAM for app_user from 10.0.1.0/24 to pg_lab
  2. allow local postgres via peer
  3. reject everything else

Solution:

hostssl pg_lab app_user 10.0.1.0/24 scram-sha-256
local   all    postgres             peer
host    all    all      0.0.0.0/0   reject

Connection to Other Concepts​

Related conceptWhy it matters
Users, Roles, and PrivilegesGrants define what connected roles can do; hardening defines who can connect.
Transactions and ConcurrencyTimeouts and lock wait logging influence concurrency safety and incident response.
Performance OptimizationLogging and TLS have performance tradeoffs; tune with evidence.
Backup and recoverySecurity includes protecting backups and verifying restores.

Visual Learning Diagram​

flowchart TD
  A["Client"] --> B["Network controls\nFirewall + listen_addresses"]
  B --> C["TLS (ssl=on)\nEncryption in transit"]
  C --> D["pg_hba.conf\nAuth rules"]
  D --> E["Authentication\nSCRAM / cert / peer"]
  E --> F["Authorization\nRoles + GRANT"]
  F --> G["Audit + monitoring\nLogs, pgaudit"]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G allNodes
  class D,F highlight

Common Pitfalls​

PitfallConsequencePrevention
pg_hba.conf allows 0.0.0.0/0broad attack surfacerestrict CIDRs; reject by default
No TLS on remote connectionssniffing / MITM riskuse hostssl + TLS config
Apps run as superuserfull compromise impactleast-privilege runtime roles
Logging too muchleaked secrets / high overheadlog connections + slow queries; avoid full statement logs
No restore testingbackups may be unusabletest restore regularly and secure backup storage

Quick Reference​

SHOW config_file;
SHOW hba_file;
SHOW ssl;
REVOKE ALL ON DATABASE pg_lab FROM PUBLIC;
ALTER ROLE app_user SET statement_timeout = '15s';

What's Next​