Create a PostgreSQL Database and User on AWS RDS: A Step-by-Step Guide

Every time I provision a new service, one of the first chores is creating a dedicated PostgreSQL database with a user that can only touch that database. Running application traffic through the master user is asking for trouble — a leaked credential or a buggy migration suddenly has access to everything on the instance.

This is the exact sequence I run on AWS RDS.

📋 What you'll need

• Access to your AWS RDS PostgreSQL instance
• The master username and password (created when you set up the RDS instance)

🔗 Step 1: Connect to the RDS instance

Connect with psql, pointing it at the RDS endpoint:

BASH
copy
psql -h your-rds-endpoint -U masteruser -d postgres

Replace your-rds-endpoint with the endpoint from the AWS RDS console, and masteruser with your master username.

🗄️ Step 2: Create the database

SQL
copy
CREATE DATABASE mydatabase;

Name it after the service that owns it — one database per service keeps the blast radius small when something goes wrong.

👤 Step 3: Create the user

SQL
copy
CREATE USER myuser WITH PASSWORD 'mypassword';

Use a strong generated password and keep it in whatever secret manager you use — not in the repository.

🔐 Step 4: Grant the right permissions

The goal here is least privilege: the user gets what the application needs, and nothing else.

Allow the user to connect

SQL
copy
GRANT CONNECT ON DATABASE mydatabase TO myuser;

Allow the user to create tables

Switch to the new database first — the grant applies to the schema inside that database:

SQL
copy
\c mydatabase
GRANT CREATE ON SCHEMA public TO myuser;

Grant data access on existing tables

SQL
copy
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;

Note what's missing: no TRUNCATE, no DROP, no ownership. The application doesn't need those, so the user doesn't get them.

Set default privileges for future tables

GRANT ... ON ALL TABLES only covers tables that exist right now. Without default privileges, every migration that adds a table would need a manual grant afterwards:

SQL
copy
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO myuser;

The sequence grant matters too — without USAGE on sequences, inserts into tables with SERIAL or IDENTITY columns will fail with a confusing permission error.

🏁 Step 5: Exit

SQL
copy
\q

📌 Summary

The whole thing, end to end:

SQL
copy
-- as the master user
CREATE DATABASE mydatabase;
CREATE USER myuser WITH PASSWORD 'mypassword';
GRANT CONNECT ON DATABASE mydatabase TO myuser;
\c mydatabase
GRANT CREATE ON SCHEMA public TO myuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO myuser;
\q

🔚 Conclusion

You now have a database and a user scoped to exactly what the application needs — and the master credentials stay out of your application config. It takes two minutes and saves you from the class of incident where one compromised service takes down every database on the instance.

Happy coding 🚀