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
Login to PostgreSQL:
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;
\q
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