Logo ← PostgreSQL Blog

5 Advanced PostgreSQL Configurations

Setting up a PostgreSQL database is easy; securing it for a high-traffic production environment is the real challenge. PostgreSQL’s default…

5 Advanced PostgreSQL Configurations

Setting up a PostgreSQL database is easy; securing it for a high-traffic production environment is the real challenge. PostgreSQL’s default configurations are designed for compatibility and ease of setup, not for maximum security or isolation. Relying on these defaults in a production environment can leave your database exposed to unnecessary risks, from network scanning to resource exhaustion.

In this article, we move beyond basic tutorials to explore architectural hardening techniques. We will cover how to bind network interfaces at the kernel level, enforce strict access controls using pg_hba.conf, and implement role-based resource isolation to ensure your database is secure, stable, and truly production-ready.

Table of Contents

  1. The Network Layer: The Art of Invisibility & Interface Binding.
  2. Identity Management: Retiring the postgres User & RBAC Architecture.
  3. The Gatekeeper: Mastering “Explicit Reject” in pg_hba.conf.
  4. Advanced Resource Tuning: User-Level Configuration (ALTER ROLE SET).
  5. The Lifeboat: superuser_reserved_connections.

1. The Network Layer: The Art of Invisibility

The first thing an attacker or a malicious bot does is scan ports. Default settings make PostgreSQL scream, “I am here!”

A. The Trap of listen_addresses = '*'

By default, many configs use * (All Interfaces). This is lazy and dangerous. A modern production server has multiple Network Interface Cards (NICs):

  • eth0: Public Internet (The Danger Zone)
  • eth1: Private Network / VPC (The Safe Zone)

PostgreSQL should never listen on the Public IP. You must bind it to a specific private IP in postgresql.conf:

# WRONG: Opens doors to the entire world
listen_addresses = '*'

# CORRECT: Only Private Network and Localhost
listen_addresses = 'localhost, 10.20.**.**'

When you do this, a packet coming from the Public IP is rejected at the Kernel level (Connection Refused) before it even touches the PostgreSQL layer. This saves CPU and keeps your logs clean.

B. Port Obfuscation

Port 5432 is one of the most scanned ports on the internet. Changing this to a non-standard port (e.g., 35432) is "Security by Obscurity," yes, but it eliminates 99% of the "Auth Failed" noise in your logs. A clean log file allows you to spot real threats.

2. Identity Management: Retire the postgres User

If you see user=postgres in your application’s connection string, stop the project. The postgres user is equivalent to root in Linux. It can do anything:

  • DROP DATABASE (Delete everything).
  • COPY PROGRAM (Execute commands on the OS—Remote Code Execution).
  • ALTER SYSTEM (Break your configuration).

Distribute the Power (Least Privilege Principle)

In a real Production environment, roles must be separated by function:

  • app_user: Can only perform DML (SELECT, INSERT, UPDATE, DELETE). Cannot create (CREATE) or drop (DROP) tables.
  • deploy_user: Used only during CI/CD migrations. Has DDL permissions.
  • backup_user: Has REPLICATION and read-only rights. Cannot modify data.
  • monitor_user: Can only read stats views like pg_stat_activity.

In this architecture, even if your application has a SQL Injection vulnerability, the attacker cannot wipe the database or hijack the server.

3. The Gatekeeper: pg_hba.conf and the "Explicit Reject"

pg_hba.conf (Host-Based Authentication) is PostgreSQL's internal Firewall. However, most people misunderstand the "First Match Wins" rule.

PostgreSQL reads the file from top to bottom. Once it finds a matching IP and User rule, it stops looking.

The “Explicit Reject” Strategy

Imagine you allow connections from the 192.168.1.0/24 subnet, but you want to ban a specific compromised or legacy server (192.168.1.50) within that subnet.

If you simply remove its permission, it might still match a broader rule. You must place a reject rule at the very top of the file:

# RULE 1: Explicitly REJECT the suspicious IP immediately.

# This rule drops the connection before even asking for a password.
host    all             all             192.168.1.50/32         reject

# RULE 2: App User can only come from the App Network via SSL
hostssl app_db          app_user        192.168.1.0/24          scram-sha-256

# RULE 3: Replication User can only come from the Backup IP
host    replication     backup_user     10.50.0.5/32            scram-sha-256

Why use reject? The reject method is CPU-friendly. It doesn't waste resources performing a password handshake. It simply kills the connection. This is your best defense against Brute-Force attacks if you don't have access to the OS-level firewall (iptables).

4. Advanced Resource Tuning: User-Level Configuration

We usually set work_mem = 16MB in postgresql.conf and forget it. This is a global setting. However:

  • App User: Sends 1000 tiny queries/sec (Index Scan). Needs 4MB.
  • Report User: Runs one massive analytics query/day (Hash Join). Needs 1GB.

If you set the global default to 1GB, the App User will crash the server (OOM). If you set it to 4MB, the Report User will write to disk (Swap) and be incredibly slow.

The Solution: ALTER ROLE ... SET

Configuration in PostgreSQL is hierarchical. You can override global settings at the User or Database level.

-- Give the Report User more RAM and a longer timeout
ALTER ROLE report_user SET work_mem = '1GB';
ALTER ROLE report_user SET statement_timeout = '30min'; 

-- Restrict the App User for safety
ALTER ROLE app_user SET work_mem = '16MB';
ALTER ROLE app_user SET statement_timeout = '5s';      -- Fail fast!
ALTER ROLE app_user SET temp_file_limit = '100MB';     -- Prevent disk filling

This is True Isolation. The reporting team gets their resources without starving the live application.

5. The Lifeboat: superuser_reserved_connections

Imagine a crisis: Your application enters an infinite loop due to a bug and consumes all available connections (max_connections). You, the Admin, try to log in to fix it, but you get: "FATAL: sorry, too many clients already".

You can’t get in to kill the bad query because the door is jammed. Your only option is to restart the database (Downtime!).

This is why superuser_reserved_connections exists.

max_connections = 500
superuser_reserved_connections = 10

This means: “PostgreSQL, keep the last 10 seats of the 500 capacity empty ONLY for the superuser.” Normal users will get errors at 490, but you can use one of those 10 reserved seats to log in, run pg_terminate_backend, and save the day.

Summary

Database administration isn’t about accepting defaults; it’s about questioning them. By isolating the Network, enforcing reject rules, killing the postgres user, and tuning resources per User Role, you build a system that is resilient to attacks and human error.