Hands-On: PostgreSQL on RDS from EC2 and Node

What You Will Build

A PostgreSQL instance reachable only from your EC2 security group — not from the internet — verified with psql and queried from a Node script using pg. This assumes you already have a running EC2 instance.

Step 1 — Create the Database Security Group First

Creating the security group before the DB instance makes the wizard smoother.

1. EC2 → Security Groups → Create security group.
2. Name: rds-postgres-sg. Description: PostgreSQL access from app servers.
3. Add one inbound rule:

Type	Port	Source
PostgreSQL	5432	The security group ID of your EC2 instance (e.g. sg-0abc123...)

Sourcing the rule from a security group instead of an IP is the key move — any instance in that group can connect, and nothing else can, even as instances come and go with new IPs.

Step 2 — Provision the PostgreSQL Instance

1. RDS → Create database → Standard create.
2. Engine: PostgreSQL, latest minor of the newest major version.
3. Templates: Free tier (this forces sane dev settings and disables Multi-AZ).
4. DB instance identifier: myapp-dev-db.
5. Master username: appadmin. Credentials management: Self managed, set a strong password. For production I'd pick Secrets Manager here, but a plain password keeps this walkthrough focused.
6. Instance configuration: db.t3.micro. Storage: 20 GB gp3, autoscaling on with a 100 GB max.
7. Connectivity:
   • VPC: the same VPC as your EC2 instance — this matters, security groups only work within a VPC.
   • Public access: No.
   • VPC security group: choose existing, select rds-postgres-sg, remove the default.
8. Under Additional configuration, set Initial database name to myapp. If you skip this, RDS creates no database and you'll wonder why myapp doesn't exist later.
9. Click Create database. Provisioning takes 5–10 minutes.

When status reaches Available, copy the Endpoint from the Connectivity tab — something like myapp-dev-db.abc123xyz.us-east-1.rds.amazonaws.com.

Step 3 — Connect with psql from EC2

SSH into your EC2 instance and install the client:

sudo apt update && sudo apt install -y postgresql-client
psql -h myapp-dev-db.abc123xyz.us-east-1.rds.amazonaws.com -U appadmin -d myapp

Enter the master password and you should land at the myapp=> prompt. Prove it works end to end:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO users (email) VALUES ('first@example.com');
SELECT * FROM users;

If psql hangs for 30 seconds and times out instead, skip to the troubleshooting table — it's a security group or VPC mismatch, every time.

Step 4 — Create an App User

Don't let your application connect as the master user. Still in psql:

CREATE USER app_user WITH PASSWORD 'a-different-strong-password';
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT USAGE, CREATE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

The master account stays reserved for migrations and admin work.

Step 5 — Connect from Node with pg

On the EC2 instance (or in your app repo):

mkdir ~/db-test && cd ~/db-test
npm init -y
npm install pg dotenv

Create .env:

DATABASE_URL=postgresql://app_user:a-different-strong-password@myapp-dev-db.abc123xyz.us-east-1.rds.amazonaws.com:5432/myapp

Create db-test.js — a pool, not a single client, since that's what real apps use:

require('dotenv').config();
const { Pool } = require('pg');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30000,
  ssl: { rejectUnauthorized: false },
});

async function main() {
  const { rows } = await pool.query(
    'SELECT id, email, created_at FROM users ORDER BY id'
  );
  console.log(rows);

  const inserted = await pool.query(
    'INSERT INTO users (email) VALUES ($1) RETURNING id',
    ['node@example.com']
  );
  console.log('Inserted id:', inserted.rows[0].id);

  await pool.end();
}

main().catch((err) => {
  console.error('DB error:', err.message);
  process.exit(1);
});

node db-test.js

You should see the row from Step 3 plus the new insert. RDS enforces SSL by default on recent PostgreSQL versions; rejectUnauthorized: false accepts the RDS certificate without a CA bundle — fine for dev, download the RDS CA bundle and verify properly in production.

Step 6 — Confirm the Instance Is Not Public

From your own laptop (not EC2):

psql -h myapp-dev-db.abc123xyz.us-east-1.rds.amazonaws.com -U appadmin -d myapp
# ...hangs, then: Connection timed out

That timeout is the correct behavior. The database has no public IP and its security group only trusts the EC2 group. For local development access, tunnel through the EC2 instance instead:

ssh -i your-key.pem -N -L 5433:myapp-dev-db.abc123xyz.us-east-1.rds.amazonaws.com:5432 ubuntu@YOUR_EC2_IP
# then in another terminal:
psql -h localhost -p 5433 -U appadmin -d myapp

Cleanup

RDS → select instance → Actions → Delete. Untick the final snapshot for a throwaway dev instance, type delete me, confirm. A db.t3.micro left running past the free tier costs about $13/month, so don't forget it.

Common Problems

Symptom	Likely Cause
psql hangs then times out	Security group rule sources the wrong group, or EC2 and RDS are in different VPCs
password authentication failed	Wrong password, or connecting as app_user before Step 4
database "myapp" does not exist	Initial database name was left blank at creation — connect to postgres and CREATE DATABASE myapp;
no pg_hba.conf entry ... no encryption	SSL required — add the ssl option to the pool config