Hardening and Configuring PostgreSQL on Ubuntu for Production

Managed database instances on AWS RDS or DigitalOcean are convenient, but running your own PostgreSQL instance on an Ubuntu server is often the right move when you want predictable infrastructure costs, custom extensions, or total control over storage tuning. Getting the packages onto the server is quick, but preparing the instance for actual production traffic requires deliberate configuration around access rules, connection management, and role isolation.

Installing the Latest Stable Engine

Ubuntu's default upstream repositories typically package an older release of PostgreSQL. While those builds are tested, I prefer pointing directly to the official PostgreSQL Apt repository so security patches and major version upgrades are directly manageable without OS version jumps.

sudo apt-get update
sudo apt-get install -y curl ca-certificates gnupg

sudo install -d /etc/apt/keyrings
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/keyrings/postgresql.gpg

echo "deb [signed-by=/etc/apt/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

sudo apt-get update
sudo apt-get install -y postgresql-16 postgresql-contrib
sudo systemctl enable postgresql
sudo systemctl status postgresql

Securing Access and Authentication

Default installations bind PostgreSQL strictly to localhost and rely on the peer authentication method for local users. That works well for running local management utilities, but production applications need isolated roles and explicit network limits.

The first security mistake teams make is running their web backend as the superuser postgres account. You should create dedicated application roles with minimum required privileges, enforce strong passwords, and map out precisely which CIDR blocks are allowed to talk to port 5432.

-- Switch to postgres user via sudo -u postgres psql
CREATE ROLE app_user WITH LOGIN PASSWORD 'use-a-strong-random-secret';
CREATE DATABASE production_db OWNER app_user;

-- Revoke default public schema creation to restrict privileges
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
\c production_db
GRANT ALL ON SCHEMA public TO app_user;

Network Isolation with pg_hba.conf and UFW

If your application runs on a separate app server or container cluster within the same VPC, PostgreSQL must listen on the private network interface rather than just 127.0.0.1. You configure this in postgresql.conf by specifying your internal IP, then restrict access in pg_hba.conf using scram-sha-256 instead of older md5 hashing.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# Local UNIX domain socket
local   all             postgres                                peer
local   production_db   app_user                                scram-sha-256

# Private VPC application subnet only
host    production_db   app_user        10.0.2.0/24             scram-sha-256

Pair this configuration with the OS firewall. Never leave port 5432 exposed to the public internet. Use UFW to ensure only explicitly designated private hosts can initiate a connection to the port.

# Allow incoming traffic from your backend application server only
sudo ufw allow from 10.0.2.15 to any port 5432 proto tcp
sudo ufw reload

Base Tuning for Production Workloads

Out of the box, PostgreSQL is configured conservatively to run on systems with minimal RAM. If your virtual machine has 8 GB or 16 GB of memory, leaving shared_buffers at the default 128 MB leaves the majority of your hardware idle.

• shared_buffers: Set this to approximately 25 percent of system RAM to allow PostgreSQL to cache frequently read pages effectively.
• effective_cache_size: Set this to roughly 50 to 75 percent of total memory to help the query planner estimate how much data fits in OS caches.
• work_mem: Allocate around 16 MB to 64 MB per operation, depending on connection limits, to prevent complex queries and joins from spilling to temporary disk files.
• maintenance_work_mem: Increase this to 512 MB or 1 GB to speed up vacuuming, index builds, and schema alterations.

Once you apply these settings in postgresql.conf and restart the service, test your connectivity using your application connection string. Monitoring disk I/O and query latency after deployment will quickly reveal whether your memory allocation matches your actual read-to-write ratio.