Posted inSecurity

Securing PostgreSQL Server on Linux: Hands-on with pg_hba.conf, pgcrypto, and pgaudit

Security tutorial - IT technology blog
Security tutorial - IT technology blog

Background: Common Mistakes When Deploying PostgreSQL

When setting up PostgreSQL on Ubuntu, many people leave the default configuration untouched. Typical pitfalls include opening listen_addresses = '*', using weak passwords, or storing citizen ID numbers and credit card details in plaintext.

The consequences can be severe. If the server has a public IP or shares a local subnet, a single minor SQL Injection vulnerability is enough to expose all your data.

In production environments, I always build a 3-layer defense:

  • Network & Authentication Layer: Use pg_hba.conf to restrict allowed IP addresses precisely and enforce password hashing via scram-sha-256.
  • Data at Rest Layer: Encrypt sensitive columns using the pgcrypto extension. Even if an attacker steals a multi-gigabyte .sql backup file, the data remains meaningless gibberish.
  • Monitoring & Audit Layer: Install the pgaudit extension to log every query altering the schema or data.

Installing Required Packages and Extensions

I am conducting this lab on Ubuntu 22.04 with PostgreSQL 16. The procedure is identical on versions 14 or 15.

# Update repositories and install PostgreSQL 16 along with extensions
sudo apt update
sudo apt install -y postgresql-16 postgresql-contrib postgresql-16-pgaudit

# Start and enable the service on system boot
sudo systemctl enable postgresql
sudo systemctl start postgresql

The postgresql-contrib package includes pgcrypto, while postgresql-16-pgaudit is the official audit tool maintained by the PostgreSQL community.

Never use passwords like postgres123 or admin@2026. Generate a random string 24–32 characters long containing uppercase and lowercase letters, numbers, and special characters.

To generate one quickly, I often use the Password Generator on ToolCraft. This tool runs entirely in the browser (client-side) and does not transmit data to any server, making it very secure.

# Change the password for the postgres user with the newly generated strong string
sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'k8#M9$xP2@vL7!qZ4*wR1&yN6';"

Implementing 3 Defense Layers for the Database

1. Tightening Network Access via postgresql.conf and pg_hba.conf

PostgreSQL manages connection settings via two primary files located at /etc/postgresql/16/main/.

First, open the postgresql.conf file:

sudo nano /etc/postgresql/16/main/postgresql.conf

Instead of exposing all interfaces with *, explicitly specify the private IP of the database server (e.g., 10.0.1.5) and switch the password hashing algorithm to SCRAM-SHA-256:

# Listen only to local connections from localhost and the private IP
listen_addresses = 'localhost, 10.0.1.5'

# Enable PostgreSQL's most secure password hashing standard
password_encryption = scram-sha-256

Next, configure the IP whitelist in pg_hba.conf:

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

Each line follows the syntax: TYPE DATABASE USER ADDRESS METHOD. Remove any lines using trust or md5 methods, then configure a secure template as shown below:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# Local socket for system user
local   all             postgres                                peer
local   all             all                                     scram-sha-256

# Allow only the Backend Server cluster (10.0.2.0/24 subnet) to access app_production
host    app_production  app_user        10.0.2.0/24             scram-sha-256

# Reject all other IP addresses
host    all             all             all                     reject

When you need to subnet for microservice clusters or backend servers, you can quickly look up the correct CIDR ranges and subnet masks using the Subnet Calculator by ToolCraft.

2. Encrypting Sensitive Data per Column with pgcrypto

If the server’s root account is compromised or a SQL backup file is leaked, full-disk encryption (LUKS) will not help. In such cases, application-/column-level encryption with pgcrypto serves as the ultimate safeguard.

Connect to the application database to enable the extension:

\c app_production
CREATE EXTENSION IF NOT EXISTS pgcrypto;

Create a user profile table with the citizen ID (ID card) column stored in BYTEA binary format:

CREATE TABLE customer_profiles (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    encrypted_id_card BYTEA NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data with AES-128 symmetric encryption using pgp_sym_encrypt
INSERT INTO customer_profiles (full_name, email, encrypted_id_card)
VALUES (
    'Nguyen Van A',
    '[email protected]',
    pgp_sym_encrypt('079199001234', 'SecretKey_2026_Prod_DbApp')
);

Query and decrypt the data:

SELECT 
    id,
    full_name,
    email,
    pgp_sym_decrypt(encrypted_id_card, 'SecretKey_2026_Prod_DbApp') AS id_card_decrypted
FROM customer_profiles
WHERE email = '[email protected]';

The secret key should be stored in backend environment variables (such as HashiCorp Vault or AWS Secrets Manager). Never hardcode the key directly in SQL files.

3. Monitoring and Detailed Logging with pgaudit

The pgaudit extension logs detailed query statements, executing users, and impacted tables. This is mandatory if you need to comply with SOC2 or PCI-DSS certifications.

Add the library preloading configuration to the end of /etc/postgresql/16/main/postgresql.conf:

# Preload pgaudit on PostgreSQL startup
shared_preload_libraries = 'pgaudit'

# Log DDL, ROLE, and write operations (INSERT/UPDATE/DELETE)
pgaudit.log = 'ddl, role, write'
pgaudit.log_catalog = off
pgaudit.log_level = 'log'
pgaudit.log_parameter = on

Restart the service to load the module into RAM:

sudo systemctl restart postgresql

Enable the extension on the database you want to monitor:

\c app_production
CREATE EXTENSION IF NOT EXISTS pgaudit;

Real-World Testing and Operational Notes

1. Testing Connections from Unauthorized IPs

From a server outside the whitelist (e.g., 192.168.1.50), attempt to connect:

psql -h 10.0.1.5 -U app_user -d app_production

The server will drop the connection immediately:

psql: error: connection to server at "10.0.1.5", port 5432 failed: FATAL: no pg_hba.conf entry for host "192.168.1.50", user "app_user", database "app_production", no encryption

2. Checking Audit Logs

Open another terminal window to monitor the log file in real time:

sudo tail -f /var/log/postgresql/postgresql-16-main.log

Execute a query to create and drop a table:

CREATE TABLE test_audit (id INT);
DROP TABLE test_audit;

The log file will record the structured entry clearly:

2026-10-06 10:45:12.312 UTC [12450] app_user@app_production LOG:  AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.test_audit,CREATE TABLE test_audit (id INT);,<not logged>
2026-10-06 10:45:18.891 UTC [12450] app_user@app_production LOG:  AUDIT: SESSION,2,1,DDL,DROP TABLE,TABLE,public.test_audit,DROP TABLE test_audit;,<not logged>

Disk Storage Note: On a system handling around 1,000 transactions/second, pgaudit logs can grow by 2–5 GB daily. Be sure to configure logrotate with gzip compression policies to avoid running out of disk space.

Share:
Tags: