Developer Guide

PostgreSQL on AWS EC2: A Complete Setup and Security Guide

May 4, 2026

7 min read

FNA Technology

PostgreSQL database setup on AWS EC2 with Ubuntu 22.04 configuration terminal

The short version: PostgreSQL on EC2 installs in minutes. The mistakes that cause production incidents are: opening pg_hba.conf to 0.0.0.0/0 without restricting by IP, skipping SSL, not running a connection pooler, and having no automated backup process. This guide covers all of it with the actual commands.

Running PostgreSQL on EC2 is a common choice when you need more control than RDS provides — custom extensions, specific PostgreSQL versions, or cost control at lower traffic levels. The tradeoff is operational responsibility: backups, connection management, and security configuration are yours to own.

This guide covers a complete production-ready setup on Ubuntu 22.04, including the security mistakes that most "getting started" tutorials skip.

Step 1: Launch and prepare the EC2 instance

Recommended instance type: t3.medium for development and low-traffic production (2 vCPU, 4GB RAM). For production workloads, t3.large or an m6i series instance depending on connection count and query complexity.

Storage: Use a separate EBS volume for PostgreSQL data (/var/lib/postgresql), not the root volume. This lets you snapshot the data volume independently and resize it without rebuilding the instance.

Security Group — initial setup:

Allow port 22 (SSH) from your IP only — not 0.0.0.0/0
Do not open port 5432 yet — we'll configure this properly in a later step

SSH into your instance:

Code

ssh -i your-key.pem ubuntu@<EC2-PUBLIC-IP>

Step 2: Install PostgreSQL

Code

# Update package index
sudo apt update && sudo apt upgrade -y

# Install PostgreSQL (installs the latest version in Ubuntu's repos — 14 on 22.04)
sudo apt install postgresql postgresql-contrib -y

# Verify it's running
sudo systemctl status postgresql

# Enable on boot
sudo systemctl enable postgresql

To install a specific version (e.g., PostgreSQL 16):

Code

# Add PostgreSQL official apt repo
sudo apt install -y gnupg curl
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt install -y postgresql-16

Step 3: Create a database user and database

Code

# Switch to the postgres system user
sudo -u postgres psql

Inside the PostgreSQL shell:

Code

-- Create a dedicated user (not the postgres superuser)
CREATE USER appuser WITH PASSWORD 'use-a-strong-password-here';

-- Create database owned by that user
CREATE DATABASE appdb OWNER appuser;

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;

-- Verify
\l    -- list databases
\du   -- list users
\q    -- quit

Never use the postgres superuser for application connections. A compromised application credential should not have superuser access to the database server.

Step 4: Configure external connections

By default, PostgreSQL only listens on localhost. Two files need editing.

Edit postgresql.conf:

Code

# Find the config file location
sudo -u postgres psql -c "SHOW config_file;"

# Edit it
sudo nano /etc/postgresql/14/main/postgresql.conf

Find and change:

Code

# Before
#listen_addresses = 'localhost'

# After — listen on all interfaces
listen_addresses = '*'

# Or restrict to your private IP (preferred for production)
# listen_addresses = '10.0.1.45'

Edit pg_hba.conf (client authentication):

Code

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

Add at the bottom. Choose the right rule for your setup:

Code

# Option 1: Allow your app server's private IP only (recommended for production)
host    appdb    appuser    10.0.1.100/32    scram-sha-256

# Option 2: Allow all IPs in your VPC subnet
host    appdb    appuser    10.0.0.0/16      scram-sha-256

# Option 3: Allow all IPs — development only, never production
host    appdb    appuser    0.0.0.0/0        scram-sha-256

Use scram-sha-256 not md5 — it's the more secure authentication method and the PostgreSQL 14+ default.

Restart PostgreSQL to apply changes:

Code

sudo systemctl restart postgresql

Step 5: Security Group configuration

In the AWS Console → EC2 → Security Groups, edit the inbound rules for your database instance's Security Group:

Type	Protocol	Port	Source
Custom TCP	TCP	5432	Security Group ID of your app server
SSH	TCP	22	Your IP address /32

Never add a rule with source 0.0.0.0/0 for port 5432 in production. If you need developer access to the database, use an SSH tunnel instead:

Code

# On your local machine — tunnels port 5432 through SSH
ssh -L 5432:localhost:5432 ubuntu@<EC2-PUBLIC-IP> -N

# Then connect with psql as if it were local
psql -h localhost -U appuser -d appdb

The tunnel encrypts the connection and avoids opening port 5432 publicly.

Step 6: Enable SSL

PostgreSQL on Ubuntu 22.04 generates a self-signed certificate by default. Verify SSL is on:

Code

sudo -u postgres psql -c "SHOW ssl;"
# Should return "on"

If not, enable it in postgresql.conf:

Code

ssl = on
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'

For connections that must use SSL, change host to hostssl in pg_hba.conf:

Code

# Require SSL — rejects unencrypted connections
hostssl    appdb    appuser    10.0.1.100/32    scram-sha-256

In your application connection string:

Code

postgresql://appuser:password@ec2-ip:5432/appdb?sslmode=require

Step 7: PgBouncer connection pooling for production

Each PostgreSQL connection uses ~10MB of RAM. An API handling 200 concurrent requests with naive connection handling can open 200+ connections, overwhelming a t3.medium (4GB RAM) quickly.

Install PgBouncer on the same instance (or a separate one for large deployments):

Code

sudo apt install pgbouncer -y

Configure it:

Code

# /etc/pgbouncer/pgbouncer.ini
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction        # best for APIs — connection returned after each transaction
max_client_conn = 500          # max connections from your app
default_pool_size = 20         # max connections to PostgreSQL
min_pool_size = 5
reserve_pool_size = 5
log_connections = 0
log_disconnections = 0

Create the user auth file:

Code

# Generate scram-sha-256 hash
sudo -u postgres psql -c "SELECT pg_shadow.usename, pg_shadow.passwd FROM pg_shadow WHERE usename='appuser';"

# Add to userlist.txt in format: "username" "scram-sha-256$..."
sudo nano /etc/pgbouncer/userlist.txt

Start and enable PgBouncer:

Code

sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer

Your application now connects to port 6432 (PgBouncer) instead of 5432 (PostgreSQL directly). PgBouncer manages the pool.

Step 8: Automated backups to S3

Code

# Install AWS CLI
sudo apt install awscli -y

# Configure with IAM credentials that have s3:PutObject on your backup bucket
aws configure

Create the backup script:

Code

sudo nano /usr/local/bin/backup-postgres.sh

Code

#!/bin/bash
set -e

DB_NAME="appdb"
DB_USER="appuser"
BACKUP_DIR="/tmp/pg_backups"
S3_BUCKET="s3://your-backup-bucket/postgres"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
FILENAME="${DB_NAME}_${TIMESTAMP}.sql.gz"

mkdir -p $BACKUP_DIR

# Dump and compress
PGPASSWORD="your-password" pg_dump -h localhost -U $DB_USER $DB_NAME | gzip > "${BACKUP_DIR}/${FILENAME}"

# Upload to S3
aws s3 cp "${BACKUP_DIR}/${FILENAME}" "${S3_BUCKET}/${FILENAME}"

# Clean up local file
rm "${BACKUP_DIR}/${FILENAME}"

echo "Backup completed: ${FILENAME}"

Code

chmod +x /usr/local/bin/backup-postgres.sh

# Schedule daily at 2am
echo "0 2 * * * ubuntu /usr/local/bin/backup-postgres.sh >> /var/log/pg_backup.log 2>&1" | sudo crontab -

Set an S3 lifecycle rule on your backup bucket to expire objects after 30 days. You get 30 days of recovery points without managing deletion manually.

Verify your setup

Code

# Check PostgreSQL is running
sudo systemctl status postgresql

# Check PgBouncer is running
sudo systemctl status pgbouncer

# Test connection through PgBouncer
psql -h 127.0.0.1 -p 6432 -U appuser -d appdb

# Check active connections and pool stats
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS;"
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW CLIENTS;"

# Verify SSL is active on a connection
psql "postgresql://appuser:password@localhost:5432/appdb?sslmode=require" -c "SELECT ssl_is_used();"

Production checklist

PostgreSQL user created with minimum required privileges (not superuser)
pg_hba.conf restricted to specific IPs — no 0.0.0.0/0
scram-sha-256 used instead of md5
SSL enabled and enforced via hostssl in pg_hba.conf
Security Group port 5432 restricted to app server Security Group ID only
PgBouncer running in transaction mode
Automated daily pg_dump to S3 with lifecycle expiry
SSH key pair access only — no password-based SSH
EC2 IMDSv2 enforced (prevents SSRF credential theft)

For APIs connecting to this PostgreSQL instance via Node.js, the Express and MongoDB scalable API guide covers the connection pool patterns and error handling that apply directly — the concepts translate to PostgreSQL with Prisma or node-postgres.

Frequently Asked Questions

Three common reasons: you didn't restart PostgreSQL after editing the config (sudo systemctl restart postgresql), listen_addresses in postgresql.conf is still set to 'localhost' instead of '*' or your server's private IP, or the AWS Security Group hasn't opened port 5432. All three must be correct simultaneously. Check in that order — config restart first, then listen_addresses, then Security Group.

No, not for production. Opening to 0.0.0.0/0 exposes your database to the entire internet and brute-force attacks. The correct approach depends on your architecture: if your app server is on another EC2 instance or in a VPC, restrict the Security Group to that instance's security group ID or private IP. For developer access, use an SSH tunnel (ssh -L 5432:localhost:5432 ubuntu@ec2-ip) instead of opening port 5432 publicly.

PgBouncer is a connection pooler that sits between your application and PostgreSQL. Each PostgreSQL connection uses ~10MB of memory. Applications that open many short-lived connections — especially serverless functions or high-traffic APIs — can exhaust the connection limit quickly. PgBouncer holds a fixed pool of connections to PostgreSQL and serves many application clients through them. For production APIs with concurrent traffic, it's not optional.

Set ssl = on in postgresql.conf and hostssl in pg_hba.conf instead of host. Then configure your client connection string to include sslmode=require. PostgreSQL on Ubuntu 22.04 generates a self-signed certificate by default at /etc/ssl/certs/ssl-cert-snakeoil.pem — this encrypts the connection but doesn't verify the server identity. For production, replace with a certificate from Let's Encrypt or AWS Certificate Manager.

Two approaches: pg_dump for logical backups (schema + data as SQL), and EBS snapshots for full volume backups. For automated logical backups, run pg_dump via a cron job and upload to S3 with the AWS CLI. A daily pg_dump with 30-day S3 retention gives you point-in-time recovery at the day granularity. For finer recovery windows (transactions), enable WAL archiving to S3 and use pg_basebackup with continuous archiving.

#PostgreSQL AWS EC2 setup#PostgreSQL Ubuntu 22.04 install#PostgreSQL external connections#pg_hba.conf configuration#PgBouncer connection pooling#PostgreSQL SSL configuration#AWS EC2 database setup

Share this article:

Written by

FNA Technology

Team Member at FNA Technology

FNA Technology is a software development company specializing in AI, mobile apps, and web solutions.

Work with us

Developer Guide

PostgreSQL on AWS EC2: A Complete Setup and Security Guide

May 4, 2026

7 min read

FNA Technology

The short version: PostgreSQL on EC2 installs in minutes. The mistakes that cause production incidents are: opening pg_hba.conf to 0.0.0.0/0 without restricting by IP, skipping SSL, not running a connection pooler, and having no automated backup process. This guide covers all of it with the actual commands.

This guide covers a complete production-ready setup on Ubuntu 22.04, including the security mistakes that most "getting started" tutorials skip.

Step 1: Launch and prepare the EC2 instance

Security Group — initial setup:

Allow port 22 (SSH) from your IP only — not 0.0.0.0/0
Do not open port 5432 yet — we'll configure this properly in a later step

SSH into your instance:

Code

ssh -i your-key.pem ubuntu@<EC2-PUBLIC-IP>

Step 2: Install PostgreSQL

Code

# Update package index
sudo apt update && sudo apt upgrade -y

# Install PostgreSQL (installs the latest version in Ubuntu's repos — 14 on 22.04)
sudo apt install postgresql postgresql-contrib -y

# Verify it's running
sudo systemctl status postgresql

# Enable on boot
sudo systemctl enable postgresql

To install a specific version (e.g., PostgreSQL 16):

Code

# Add PostgreSQL official apt repo
sudo apt install -y gnupg curl
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt install -y postgresql-16

Step 3: Create a database user and database

Code

# Switch to the postgres system user
sudo -u postgres psql

Inside the PostgreSQL shell:

Code

-- Create a dedicated user (not the postgres superuser)
CREATE USER appuser WITH PASSWORD 'use-a-strong-password-here';

-- Create database owned by that user
CREATE DATABASE appdb OWNER appuser;

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;

-- Verify
\l    -- list databases
\du   -- list users
\q    -- quit

Never use the postgres superuser for application connections. A compromised application credential should not have superuser access to the database server.

Step 4: Configure external connections

By default, PostgreSQL only listens on localhost. Two files need editing.

Edit postgresql.conf:

Code

# Find the config file location
sudo -u postgres psql -c "SHOW config_file;"

# Edit it
sudo nano /etc/postgresql/14/main/postgresql.conf

Find and change:

Code

# Before
#listen_addresses = 'localhost'

# After — listen on all interfaces
listen_addresses = '*'

# Or restrict to your private IP (preferred for production)
# listen_addresses = '10.0.1.45'

Edit pg_hba.conf (client authentication):

Code

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

Add at the bottom. Choose the right rule for your setup:

Code

# Option 1: Allow your app server's private IP only (recommended for production)
host    appdb    appuser    10.0.1.100/32    scram-sha-256

# Option 2: Allow all IPs in your VPC subnet
host    appdb    appuser    10.0.0.0/16      scram-sha-256

# Option 3: Allow all IPs — development only, never production
host    appdb    appuser    0.0.0.0/0        scram-sha-256

Use scram-sha-256 not md5 — it's the more secure authentication method and the PostgreSQL 14+ default.

Restart PostgreSQL to apply changes:

Code

sudo systemctl restart postgresql

Step 5: Security Group configuration

In the AWS Console → EC2 → Security Groups, edit the inbound rules for your database instance's Security Group:

Type	Protocol	Port	Source
Custom TCP	TCP	5432	Security Group ID of your app server
SSH	TCP	22	Your IP address /32

Never add a rule with source 0.0.0.0/0 for port 5432 in production. If you need developer access to the database, use an SSH tunnel instead:

Code

# On your local machine — tunnels port 5432 through SSH
ssh -L 5432:localhost:5432 ubuntu@<EC2-PUBLIC-IP> -N

# Then connect with psql as if it were local
psql -h localhost -U appuser -d appdb

The tunnel encrypts the connection and avoids opening port 5432 publicly.

Step 6: Enable SSL

PostgreSQL on Ubuntu 22.04 generates a self-signed certificate by default. Verify SSL is on:

Code

sudo -u postgres psql -c "SHOW ssl;"
# Should return "on"

If not, enable it in postgresql.conf:

Code

ssl = on
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'

For connections that must use SSL, change host to hostssl in pg_hba.conf:

Code

# Require SSL — rejects unencrypted connections
hostssl    appdb    appuser    10.0.1.100/32    scram-sha-256

In your application connection string:

Code

postgresql://appuser:password@ec2-ip:5432/appdb?sslmode=require

Step 7: PgBouncer connection pooling for production

Each PostgreSQL connection uses ~10MB of RAM. An API handling 200 concurrent requests with naive connection handling can open 200+ connections, overwhelming a t3.medium (4GB RAM) quickly.

Install PgBouncer on the same instance (or a separate one for large deployments):

Code

sudo apt install pgbouncer -y

Configure it:

Code

# /etc/pgbouncer/pgbouncer.ini
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction        # best for APIs — connection returned after each transaction
max_client_conn = 500          # max connections from your app
default_pool_size = 20         # max connections to PostgreSQL
min_pool_size = 5
reserve_pool_size = 5
log_connections = 0
log_disconnections = 0

Create the user auth file:

Code

# Generate scram-sha-256 hash
sudo -u postgres psql -c "SELECT pg_shadow.usename, pg_shadow.passwd FROM pg_shadow WHERE usename='appuser';"

# Add to userlist.txt in format: "username" "scram-sha-256$..."
sudo nano /etc/pgbouncer/userlist.txt

Start and enable PgBouncer:

Code

sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer

Your application now connects to port 6432 (PgBouncer) instead of 5432 (PostgreSQL directly). PgBouncer manages the pool.

Step 8: Automated backups to S3

Code

# Install AWS CLI
sudo apt install awscli -y

# Configure with IAM credentials that have s3:PutObject on your backup bucket
aws configure

Create the backup script:

Code

sudo nano /usr/local/bin/backup-postgres.sh

Code

#!/bin/bash
set -e

DB_NAME="appdb"
DB_USER="appuser"
BACKUP_DIR="/tmp/pg_backups"
S3_BUCKET="s3://your-backup-bucket/postgres"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
FILENAME="${DB_NAME}_${TIMESTAMP}.sql.gz"

mkdir -p $BACKUP_DIR

# Dump and compress
PGPASSWORD="your-password" pg_dump -h localhost -U $DB_USER $DB_NAME | gzip > "${BACKUP_DIR}/${FILENAME}"

# Upload to S3
aws s3 cp "${BACKUP_DIR}/${FILENAME}" "${S3_BUCKET}/${FILENAME}"

# Clean up local file
rm "${BACKUP_DIR}/${FILENAME}"

echo "Backup completed: ${FILENAME}"

Code

chmod +x /usr/local/bin/backup-postgres.sh

# Schedule daily at 2am
echo "0 2 * * * ubuntu /usr/local/bin/backup-postgres.sh >> /var/log/pg_backup.log 2>&1" | sudo crontab -

Set an S3 lifecycle rule on your backup bucket to expire objects after 30 days. You get 30 days of recovery points without managing deletion manually.

Verify your setup

Code

# Check PostgreSQL is running
sudo systemctl status postgresql

# Check PgBouncer is running
sudo systemctl status pgbouncer

# Test connection through PgBouncer
psql -h 127.0.0.1 -p 6432 -U appuser -d appdb

# Check active connections and pool stats
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS;"
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW CLIENTS;"

# Verify SSL is active on a connection
psql "postgresql://appuser:password@localhost:5432/appdb?sslmode=require" -c "SELECT ssl_is_used();"

Production checklist

PostgreSQL user created with minimum required privileges (not superuser)
pg_hba.conf restricted to specific IPs — no 0.0.0.0/0
scram-sha-256 used instead of md5
SSL enabled and enforced via hostssl in pg_hba.conf
Security Group port 5432 restricted to app server Security Group ID only
PgBouncer running in transaction mode
Automated daily pg_dump to S3 with lifecycle expiry
SSH key pair access only — no password-based SSH
EC2 IMDSv2 enforced (prevents SSRF credential theft)

Frequently Asked Questions

#PostgreSQL AWS EC2 setup#PostgreSQL Ubuntu 22.04 install#PostgreSQL external connections#pg_hba.conf configuration#PgBouncer connection pooling#PostgreSQL SSL configuration#AWS EC2 database setup

Share this article:

Written by

FNA Technology

Team Member at FNA Technology

FNA Technology is a software development company specializing in AI, mobile apps, and web solutions.

Work with us