-
Notifications
You must be signed in to change notification settings - Fork 13
User Management in AWS RDS PostgreSQL – 04_21_2025
Below are the logical roles that can be created in PostgreSQL to define levels of access:
Permissions:
a. Superuser - Can manage all DB operations, users, schemas, etc.
b. Full schema access - All tables, views, and functions.
Snippet:
CREATE ROLE admin_role;
GRANT rds_superuser TO admin_role;
Permissions:
a. Connect to DB
b. Read data
c. Write/Update/Delete data
d. Create temp tables, functions (Optional)
e. Cannot manage roles/schemas
Snippet:
CREATE ROLE developer_role;
GRANT CONNECT ON DATABASE your_db TO developer_role;
GRANT USAGE ON SCHEMA public TO developer_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO developer_role;
//For new tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO developer_role;
Permissions:
a. Connect to DB
b. Read data
c. No write/delete privileges
d. Safe for reporting & dashboards
Snippet:
CREATE ROLE readonly_role;
GRANT CONNECT ON DATABASE your_db TO readonly_role;
GRANT USAGE ON SCHEMA public TO readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_role;
In AWS RDS PostgreSQL, user access can be managed in two ways:
- IAM-Based Authentication
AWS IAM users authenticate via temporary tokens and are mapped to database users. This is secure, auditable, and integrates well with AWS-wide access policies.
- PostgreSQL-Native Users
Users are created directly in the database using CREATE USER. It's simpler and faster to set up, but it involves static credentials, making it less secure for large teams.
Will be used to log in to the PostgreSQL database using secure, short-term tokens
To let this IAM user connect to the RDS database securely, you must
a. Attach managed policies -
AmazonRDSFullAccess
AmazonRDSDataFullAccess (if using Data API)
b. Add custom inline policy –
which allows the users to connect to specific DB user inside PostgreSQL.
{
"Effect": "Allow",
"Action": "rds-db:connect",
"Resource": "arn:aws:rds-db:::dbuser:/"
where,
region - us-east-1 or your RDS region
account-id - your AWS account number
dbi-resource-id - unique RDS instance ID (from RDS console)
db-username - the PostgreSQL username you will create (e.g., first_last)
- Go to RDS Console → Databases
- Click on your DB instance
- Under Connectivity & Security, find: Check "IAM DB authentication" and set it to Enabled
- Click Modify → Apply Immediately or during next maintenance
- This tells RDS: “Allow users to log in using AWS IAM tokens.”
The IAM user must be mapped to a PostgreSQL user with the same name.
CREATE USER first_last WITH LOGIN;
GRANT developer_role TO ajith_dev;
Windows: https://docs.aws.amazon.com/cli/latest/userguide/getting-started-install.html
Mac: brew install awscli
Open terminal & run:
aws configure
You will be prompted to enter:
AWS Access Key ID: <Paste here>
AWS Secret Access Key: <Paste here>
Default region name: us-east-1 (or your region)
Default output format: json
Where do I get Access Key ID & Secret Key?
a. If you only have an AWS console username/password:
b. Log into AWS Console with the link provided (e.g., https://123456789012.signin.aws.amazon.com/console)
c. Click your username (top right) > Security credentials
d. Scroll to Access Keys > Click Create access key
e. Save the Access Key ID and Secret Key securely
f. If this section is missing, ask your AWS admin to generate it for you.
aws rds generate-db-auth-token \
--hostname your-db-endpoint.rds.amazonaws.com \
--port 5432 \
--region your-region \
--username your_db_username
This gives a temporary password (token).
Option 1: Log in Using psql (Command Line)
In your terminal, run this full command:
psql "host=your-db-endpoint \
port=5432 \
dbname=your_db_name \
user=ajith_dev \
password=THE_TOKEN_YOU_JUST_GENERATED \
sslmode=require"
where,
your-db-endpoint - Your RDS endpoint (e.g., your-db.abcdefg.us-east-1.rds.amazonaws.com)
your_db_name - Your PostgreSQL database name
THE_TOKEN_YOU_JUST_GENERATED - Paste the full token from the previous step
SSL is required - PostgreSQL won’t accept IAM login without it.
Option 2: Use DBeaver, pgAdmin, or Other DB Client
In DBeaver:
Click New Database Connection → Select PostgreSQL
Fill out:
Host: your-db-endpoint
Port: 5432
Database: your database name
Username: ajith_dev
Password: paste the token
Go to SSL tab → Enable SSL mode: require
Click Test Connection
In pgAdmin:
Right-click Servers → Click Create → Server
Under the General tab:
Name your connection (e.g., RDS IAM PostgreSQL)
Go to the Connection tab
Now fill in the actual database connection details:
Host name/address - your-db-endpoint.rds.amazonaws.com
Port - 5432
Maintenance database - your_db_name
Username - IAM user (e.g., first_last)
Password - Paste the token you generated
Save Password - DO NOT save — token expires in 15 mins
Use SSL - Required (see below)
SSL Configuration
Click the SSL tab:
SSL Mode: require (or verify-ca/verify-full if you're using a certificate)
SSL is mandatory for IAM authentication to work.
Click Save and Connect
Download the RDS Root SSL Certificate (if needed for: verify-full, etc)
You can get it directly from Amazon:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html
Scroll to the section called "Using SSL/TLS to Encrypt a Connection to a DB Instance"
Direct Download Links (Choose based on region/RDS type):
Global/Broadly used (recommended): rds-combined-ca-bundle.pem
Save the file to a location you can access easily (e.g., ~/certs/rds-combined-ca-bundle.pem on Linux/Mac or C:\certs\rds-ca.pem on Windows)
This method involves creating users directly inside the PostgreSQL database and assigning them roles (like readonly, developer, admin) — without involving AWS IAM at all.
- Log in to PostgreSQL (via pgAdmin, psql, DBeaver): Use a superuser like postgres or rdsadmin.
- Create the Database User
CREATE USER first_last WITH LOGIN PASSWORD 'StrongPassword123!';
This password is stored in the DB.
- Assign Role to the User
GRANT readonly_role TO ananya_ro;
- How the User Logs In:
Using psql CLI:
psql -U first_last -h your-db-host.rds.amazonaws.com -d your_database_name
Using GUI tools like pgAdmin, DBeaver, or DataGrip:
Host: your RDS endpoint
Port: 5432
Username: first_last
Password: 'StrongPassword123!'
SSL: require or prefer
Right now,
The IAM authentication is tested on the Ireland Region only.
And, database roles are created for the Virginia region, for developers from different teams to work on.