PostgreSQL Security Hardening with Practical Examples

Intro

PostgreSQL security hardening with practical examples should help operators move from an observed problem to a verified result. This article focuses on PostgreSQL security for developers, DevOps consultants, and technical startup teams. It connects PostgreSQL hardening, access control, secrets management, and permissions to commands, expected output, failure signals, and recovery decisions.

The goal is operational safety: observe before changing, limit the blast radius, use placeholders instead of secrets, verify the result, and document how to recover if the expected state is not reached. Every step includes a concrete command, its expected output, and a failure signal, so you can build a repeatable security baseline.

Version and Environment Inventory

Start by identifying the installed version and deployment topology, because security settings vary across PostgreSQL releases. Capture the current observable state with a read-only command, then define the expected result and failure signal before making a change. Separate observation from intervention: record timestamps, protect credentials, and change one scoped item only when its blast radius and recovery path are understood.

Determine the PostgreSQL version and environment

Run this command to get the exact server version and configuration directory:

postgres --version && pg_config --bindir && pg_config --sysconfdir

Expected output on a typical Debian-based system with PostgreSQL 15:

postgres (PostgreSQL) 15.3
/usr/lib/postgresql/15/bin
/etc/postgresql/15/main

Failure signal: if the command returns "command not found" or a version below your supported baseline (for example, below 14), stop and align with your organization's minimum supported version before applying settings intended for newer releases.

Next, identify the cluster status and data directory location. This is read-only and safe:

pg_lsclusters

Sample output:

Ver Cluster Port Status Owner    Data directory               Log file
15  main    5432 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log

If the cluster is down or not found, do not proceed with configuration changes until the cluster is online and its recovery path is tested.

Safe Configuration Path

For PostgreSQL security, Safe Configuration Path means changing one scoped parameter at a time, with a backup and a tested rollback. Always check the current value first using a read-only query, then apply the smallest justified change.

1. Enforce secure password storage

PostgreSQL stores password hashes in pg_authid. By default, it uses MD5 or SCRAM-SHA-256 depending on password_encryption. To verify the current setting:

SHOW password_encryption;

Expected output for a modern, secure default:

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

If the output is md5 or on (an alias for md5 in older versions), change it:

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

Verification:

SHOW password_encryption;

Expected output:

scram-sha-256

Failure signal: if the ALTER SYSTEM fails due to syntax or permission, review the exact spelling and confirm you are connected as a superuser. The setting takes effect immediately for new passwords; existing passwords remain as they are until changed. To update an existing user's password to use SCRAM:

ALTER USER app_user WITH PASSWORD 'new_strong_password_placeholder';

Recovery: if the new setting causes authentication failures for legacy clients, revert with:

ALTER SYSTEM SET password_encryption = 'md5';
SELECT pg_reload_conf();

2. Restrict client authentication with pg_hba.conf

Edit the pg_hba.conf file found in the configuration directory. Use a backup first:

sudo cp /etc/postgresql/15/main/pg_hba.conf /etc/postgresql/15/main/pg_hba.conf.bak
sudo nano /etc/postgresql/15/main/pg_hba.conf

Replace permissive lines like:

host    all             all             0.0.0.0/0               md5

with a restrictive rule:

# Allow only the application server IP with scram-sha-256
host    app_db          app_user        192.0.2.10/32            scram-sha-256
# Reject all other remote connections
host    all             all             0.0.0.0/0               reject

After saving, reload the configuration:

sudo systemctl reload postgresql@15-main

Verification:

sudo -u postgres psql -c "SELECT * FROM pg_hba_file_rules;"

Expected output includes your new rule with error column null. If the rule contains an error, error will show a description.

Failure signal: if pg_reload_conf() or systemctl reload fails, check the PostgreSQL log for syntax errors. Recovery: restore from backup:

sudo cp /etc/postgresql/15/main/pg_hba.conf.bak /etc/postgresql/15/main/pg_hba.conf
sudo systemctl reload postgresql@15-main

3. Disable unnecessary default privileges

Revoke the default CONNECT privilege from PUBLIC on all databases to prevent unauthorized connection attempts:

REVOKE CONNECT ON DATABASE template1 FROM PUBLIC;
REVOKE CONNECT ON DATABASE postgres FROM PUBLIC;
REVOKE CONNECT ON DATABASE app_db FROM PUBLIC;

Then grant access only to specific roles:

GRANT CONNECT ON DATABASE app_db TO app_user;

Verification:

SELECT datname, datacl FROM pg_database WHERE datname = 'app_db';

Expected output shows datacl containing app_user=c/postgres and no =c/ entry for PUBLIC. If PUBLIC still appears, the revoke did not apply.

Recovery: to restore default, run:

GRANT CONNECT ON DATABASE app_db TO PUBLIC;

Verification and Diagnostics

PostgreSQL security needs continuous verification, not just initial configuration. Use read-only queries and built-in views to detect deviations from your baseline.

Check for insecure authentication methods

Run this query to list all authentication methods from pg_hba_file_rules:

SELECT type, database, user_name, address, auth_method
FROM pg_hba_file_rules
WHERE auth_method IN ('trust', 'password', 'md5');

Expected output if all rules are secure:

 type | database | user_name | address | auth_method
------+----------+-----------+---------+-------------
(0 rows)

If rows are returned, each represents a rule that allows a weak authentication method and must be updated to scram-sha-256 or cert.

Audit superuser roles

List all roles with superuser privileges:

SELECT rolname FROM pg_roles WHERE rolsuper = true;

Expected output in a hardened environment shows only the postgres operating system role or a designated break-glass account. If you see unexpected roles like app_user with superuser, revoke it:

ALTER ROLE app_user WITH NOSUPERUSER;

Check for passwordless roles

Identify roles that can connect without a password (note: this query works in PostgreSQL 14+):

SELECT rolname FROM pg_authid WHERE rolpassword IS NULL AND rolcanlogin = true;

If any roles are returned, assign a password or disable login:

ALTER ROLE reporting_user WITH PASSWORD 'strong_placeholder_password';
-- or
ALTER ROLE reporting_user WITH NOLOGIN;

Failure Modes and Recovery

Even with careful planning, misconfigurations happen. Anticipate common failures and prepare recovery steps that restore service without weakening security.

Failure: Locked out after pg_hba.conf change

If you accidentally restrict access too much, you may be unable to connect remotely or even locally as postgres.

Immediate recovery:

1. Connect via local socket using the system user:

sudo -u postgres psql

1. If local socket is also blocked, edit pg_hba.conf directly as root:

sudo nano /etc/postgresql/15/main/pg_hba.conf

1. Temporarily add a rule to allow local peer authentication for postgres user:

local   all             postgres                                peer

1. Reload:

sudo systemctl reload postgresql@15-main

1. Then fix the original rules and reload again.

Prevention: Always keep a backup of pg_hba.conf before editing and test in a staging environment if possible.

Failure: Password encryption change breaks legacy application

If you switch to scram-sha-256 and an old client only supports MD5, authentication will fail with messages like:

FATAL:  password authentication failed for user "app_user"
DETAIL:  User's password is not a valid SCRAM verifier.

Recovery options:

• Update the client library to one that supports SCRAM (recommended).
• Temporarily set password_encryption = 'md5' and reset the user's password, then plan a migration.
• For a specific user, you can set a MD5 password manually (not recommended for long-term):

ALTER USER app_user WITH PASSWORD 'md5';

Then restore SCRAM later.

Failure: Reload fails due to syntax error

If you introduce a syntax error in postgresql.conf or pg_hba.conf, the reload will fail and PostgreSQL may continue running with the old configuration or refuse to start.

Check the log:

sudo tail -n 50 /var/log/postgresql/postgresql-15-main.log

Look for lines like:

FATAL:  could not load pg_hba.conf

Fix the syntax error in the file, then reload again. If PostgreSQL refuses to start after a restart, you can start it in single-user mode to bypass configuration problems (but this is an emergency step).

Operations Checklist

Use this checklist before and after applying any PostgreSQL security change. It summarizes the workflow from observation to verification.

Pre-Change Checklist

• [ ] Identify PostgreSQL version with postgres --version and confirm it is supported.
• [ ] Determine cluster name and data directory with pg_lsclusters.
• [ ] Backup relevant configuration files (postgresql.conf, pg_hba.conf, pg_ident.conf).
• [ ] Record current setting value using a read-only query, e.g., SHOW password_encryption;.
• [ ] Define the expected new value and the failure signal.
• [ ] Identify who can perform the change and who approves it.

Change Implementation

• [ ] Apply the smallest change: one parameter, one rule, or one grant.
• [ ] Use ALTER SYSTEM for postgresql.conf changes, ALTER ROLE for role changes, and edit pg_hba.conf for host-based rules.
• [ ] Reload configuration with SELECT pg_reload_conf(); or systemctl reload.
• [ ] Do not restart the service unless required; reload is less disruptive.

Post-Change Verification

• [ ] Run the verification query or command from the example.
• [ ] Compare actual output with expected output.
• [ ] Check PostgreSQL logs for errors or warnings:

sudo tail -n 100 /var/log/postgresql/postgresql-15-main.log

• [ ] Attempt to connect as the affected role from an allowed and a denied host to confirm access control.
• [ ] Document the change, timestamp, and rollback steps in your operations runbook.

Rollback Plan

• [ ] For ALTER SYSTEM changes, keep the previous value and the exact ALTER SYSTEM SET ... command to revert.
• [ ] For pg_hba.conf, retain the backup file and know the restore command.
• [ ] For role changes, have the inverse ALTER ROLE command ready.
• [ ] Test the rollback in a non-production environment if possible.

Conclusion

PostgreSQL security hardening with practical examples is useful only when each recommendation is version-scoped, observable, and reversible where the technology permits. Copying a command without checking prerequisites and expected output is not an operations procedure.

As a next step, choose one low-risk verification from this article: check password_encryption, review pg_hba_file_rules, or audit superuser roles. Record the current state, run the documented check, compare the result with the expected signal, and review dependencies such as client drivers and backup procedures. Then apply one hardening change and verify it.

A reliable technical workflow makes failure visible, protects sensitive values, limits changes to the intended resource, and defines recovery verification before an incident forces the decision. By following the patterns here, you can build a PostgreSQL security baseline that is both practical and sustainable.