How to secure a PostgreSQL server

Securing a PostgreSQL server reduces the risk that a single weak authentication rule, exposed listener, or overly privileged role turns into a full database compromise.

PostgreSQL access control is a layered system: network exposure is determined by listen_addresses and firewalling, client authentication is enforced by rules in pg_hba.conf, and in-database permissions are enforced through roles, memberships, and object grants. TLS protects credentials and data in transit, while extensions and logging settings influence what can run inside the database and what activity is recorded.

Configuration paths and defaults vary by packaging, but the running server can report the active file locations and settings through SQL. Apply changes incrementally, because a mistake in pg_hba.conf or TLS settings can lock out remote administration and force console-only recovery.

Steps to secure a PostgreSQL server:

Check the running PostgreSQL server version before applying hardening changes.
```
$ sudo -u postgres psql -Atc "SHOW server_version;"
16.11 (Ubuntu 16.11-0ubuntu0.24.04.1)
```
Plan upgrades with a rollback path and a maintenance window.

Related: How to upgrade PostgreSQL safely
Identify the active postgresql.conf file used by the cluster.
```
$ sudo -u postgres psql -Atc "SHOW config_file;"
/etc/postgresql/16/main/postgresql.conf
```
Runtime settings shown by SHOW reflect the currently loaded configuration, not just file contents.
Identify the pg_hba.conf file used by the current cluster.
```
$ sudo -u postgres psql -Atc "SHOW hba_file;"
/etc/postgresql/16/main/pg_hba.conf
```
A wrong rule in pg_hba.conf can block all remote connections, including administrator access.

Rule order matters in pg_hba.conf, because the first matching entry is used.

Related: How to configure pg_hba.conf in PostgreSQL
Confirm password_encryption is set to scram-sha-256 for new passwords.
```
$ sudo -u postgres psql -Atc "SHOW password_encryption;"
scram-sha-256
```
Changing password_encryption affects newly-set passwords, because existing password hashes remain unchanged until reset.

Related: How to set password encryption method in PostgreSQL
Verify listen_addresses restricts client connections to required interfaces.
```
$ sudo -u postgres psql -Atc "SHOW listen_addresses;"
localhost
```
Related: How to restrict remote access in PostgreSQL
Verify TLS is enabled when accepting connections over a network.
```
$ sudo -u postgres psql -Atc "SHOW ssl;"
on
```
Use \conninfo from a remote psql session to confirm an SSL connection is negotiated.

Related: How to enable SSL for PostgreSQL connections

List roles to identify application accounts with excessive attributes.

$ sudo -u postgres psql -c "\du"
                                List of roles
    Role name    |                         Attributes                         
-----------------+------------------------------------------------------------
 app_owner       | 
 app_runtime     | 
 appowner        | 
 appreadwrite    | Cannot login
 appuser         | 
 pgbouncer_admin | 
 postgres        | Superuser, Create role, Create DB, Replication, Bypass RLS

Prefer separate roles for ownership and runtime access instead of granting SUPERUSER to applications.

Inspect object grants for overly broad privileges granted to PUBLIC or unused roles.

$ sudo -u postgres psql -c "\dp"
                                         Access privileges
 Schema |          Name           | Type |     Access privileges     | Column privileges | Policies 
--------+-------------------------+------+---------------------------+-------------------+----------
 public | pg_stat_statements      | view | postgres=arwdDxt/postgres+|                   | 
        |                         |      | =r/postgres               |                   | 
 public | pg_stat_statements_info | view | postgres=arwdDxt/postgres+|                   | 
        |                         |      | =r/postgres               |                   | 
(2 rows)

Review default privileges for new objects in application schemas.

$ sudo -u postgres psql -c "\ddp"
         Default access privileges
 Owner | Schema | Type | Access privileges 
-------+--------+------+-------------------
(0 rows)

Review installed extensions for unneeded or untrusted modules.

$ sudo -u postgres psql -c "\dx"
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description                               
--------------------+---------+------------+------------------------------------------------------------------------
 pg_stat_statements | 1.10    | public     | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

Check logging settings record connection attempts for audit trails.
```
$ sudo -u postgres psql -Atc "SHOW log_connections;"
on
```
Enabling verbose logging can grow log files quickly and consume disk space.

Related: How to configure PostgreSQL audit logging
Identify the data directory that requires encryption-at-rest controls.
```
$ sudo -u postgres psql -Atc "SHOW data_directory;"
/var/lib/postgresql/16/main
```
Loss of disk-encryption keys permanently blocks access to the data directory and any backups stored on the encrypted volume.

Related: How to encrypt PostgreSQL data at rest
List databases that require backup coverage.
```
$ sudo -u postgres psql -Atc "SELECT datname FROM pg_database WHERE datistemplate = false ORDER BY 1;"
appdb
postgres
```
Backups belong on storage with restricted access and defined retention, not on the same disk as the database.

Related: How to create a PostgreSQL database backup
Test restoring a recent backup to confirm recovery works.

A restore test proves the backup contains everything needed to recover, including roles, permissions, and extension dependencies.

Related: How to restore a PostgreSQL database backup

Author: Mohd Shakir Zakaria
Mohd Shakir Zakaria is a cloud architect with deep roots in software development and open-source advocacy. Certified in AWS, Red Hat, VMware, ITIL, and Linux, he specializes in designing and managing robust cloud and on-premises infrastructures.