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

@fakhrulnugrohoAugust 29, 2024

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

Ready to set up your PostgreSQL database on AWS RDS? Let's walk through the process together! By the end, you’ll have a new database and a user with just the right permissions.

🎯 What You’ll Need

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

Got everything? Great! Let’s dive in.

Step 1: 🔗 Connect to Your RDS PostgreSQL Instance

First, let’s get connected to your RDS instance. Open your terminal and run this command:

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

✏️ Replace:

your-rds-endpoint with your RDS endpoint (you can find this in the AWS RDS console).
masteruser with your master username.

Hit Enter to connect. You’re now inside your PostgreSQL database!

Step 2: 🛠️ Create a New Database

Now, let’s create a database where your data will live. Type this command:

SQL
CREATE DATABASE mydatabase;

✏️ Tip: Replace mydatabase with your preferred name. Maybe something meaningful like inventoryDB?

Hit Enter and voilà! Your new database is ready.

Step 3: 👤 Create a New User

Next up, let’s create a user who will interact with this database:

SQL
CREATE USER myuser WITH PASSWORD 'mypassword';

✏️ Personalize:

Replace myuser with your desired username (like dbadmin or data_analyst).
Replace mypassword with a strong password.

Pro Tip: Keep that password safe!

Step 4: 🎛️ Grant the Right Permissions

Now, let’s give your new user some powers—but not too many! Here’s how:

Step 4a: Allow the User to Connect to the Database

First, let’s make sure your user can actually connect:

SQL
GRANT CONNECT ON DATABASE mydatabase TO myuser;

Hit Enter. Now your user can access the database!

Step 4b: Let the User Create Tables

Now, let’s give the user permission to create tables:

SQL
\\c mydatabase  -- switch to your new database
GRANT CREATE ON SCHEMA public TO myuser;

✨ Magic: Switching to your new database ensures you’re giving permissions in the right place.

Step 4c: Give Permissions to Read, Add, Update, and Delete Data

Next, let’s enable your user to work with the data:

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

🛡️ Safety Check: This gives your user the ability to read and manipulate data, but only in the specified tables.

Step 4d: Set Default Permissions for Future Tables

Finally, make sure your user gets the right permissions on any new tables or sequences created in the future:

SQL
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;

📌 Remember: These commands make it easier to manage permissions as your database grows.

Step 5: 🏁 Exit PostgreSQL

Once you’re done, exit the PostgreSQL prompt by typing:

SQL
\\q

You’re all set!

🎉 Summary

Here’s a quick summary of the steps:

Connect to your RDS instance:

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

Create a new database:

SQL
CREATE DATABASE mydatabase;

Create a new user:

SQL
CREATE USER myuser WITH PASSWORD 'mypassword';

Grant specific permissions:

SQL
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;

Exit PostgreSQL:
```
SQL
\\q
```

Now you have a brand new PostgreSQL database and a user with just the right permissions on your AWS RDS instance! 🎉