PostgreSQL Setup on AWS EC2: Complete Developer Guide

This guide will walk you through setting up PostgreSQL on AWS EC2 with Ubuntu 22.04 LTS, including configuration for external connections and security best practices.

1Launch EC2 Instance

•Use Ubuntu 22.04 LTS (or Amazon Linux if you prefer)
•Choose a security group that you can edit (important for allowing access later)
•Assign a public IP to the instance

2Install PostgreSQL

SSH into your EC2 instance:

sudo apt update && sudo apt upgrade -y

sudo apt install postgresql postgresql-contrib -y

Check PostgreSQL service:

sudo systemctl status postgresql

3Configure PostgreSQL to Allow External Connections

a) Edit postgresql.conf

Find the config file:

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

Look for:

#listen_addresses = 'localhost'

Change it to:

listen_addresses = '*'

b) Edit pg_hba.conf

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

Add at the end:

host all all 0.0.0.0/0 md5

Note: This allows all IPs. For production, restrict to your specific IP range for security.

Restart PostgreSQL:

sudo systemctl restart postgresql

4Create User & Database

sudo -u postgres psql

Inside psql:

CREATE USER myuser WITH PASSWORD 'mypassword';

CREATE DATABASE mydb OWNER myuser;

GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

5Security Group Setup (VERY IMPORTANT)

Go to AWS Console → EC2 → Security Groups.

Edit inbound rules:

•Add Custom TCP Rule
•Port: 5432
•Source: 0.0.0.0/0 (or restrict to your IP for safety)

6Test Connection

From your local machine:

psql -h <EC2-Public-IP> -U myuser -d mydb

It will ask for the password → connect.

✅ Now PostgreSQL is running and accessible publicly.

⚠️ Security Note

Don't keep it open to the whole internet (0.0.0.0/0). Better:

•Allow only your office/home IP in the security group
•Or use an SSH tunnel for secure access