Creating and Managing Database Users in RDS, and Avoiding the Master User | AWS & Cloud

Q: How does IAM database authentication work?

IAM database authentication is the tokenbased alternative. You create a database user and mark it to authenticate through IAM, then grant the application's IAM role the rdsdb:connect permission for that user. At connection time the application asks IAM for a shortlived authentication token and uses

Q: How is SQL user management different from IAM authentication?

public class SQLvsIAMAuth { // SQL auth: password stored in Secrets Manager, rotated every 30 days // Good for Oracle and SQL Server which do not support IAM auth // IAM auth: token generated per connection, expires in 15 minutes // Preferred for PostgreSQL and MySQL workloads // Centralized control

Definition

Create least-privilege database users with SQL CREATE USER/GRANT or map them to IAM for short-lived token logins, and never let the application use the all-powerful master user.

SQL User Management – Standard CREATE USER, ALTER USER, and GRANT/REVOKE commands to create least-privilege accounts on the database engine.
IAM Database Authentication – A feature that maps a database user to IAM and authenticates using a short-lived token (about 15 minutes) instead of a stored password.
Master User – The highly privileged administrative account created when an RDS instance is provisioned, meant only for setup and schema migrations.
Secrets Manager – AWS service that stores and automatically rotates database passwords so they never appear in code or config files.
Per-Service Users – The practice of creating one dedicated database user per microservice so permissions and blame are traceable.

Think of the RDS master user as the bank manager's master key that opens every door at National Bank of India – the vault, the HR files, the server room, everything. You do not hand that master key to a teller who only needs to open one counter drawer. Instead you cut specific keys: a teller key that opens just the cash drawer, a read-only auditor key that opens filing cabinets but changes nothing. IAM database authentication is even safer – instead of a permanent key, the teller is handed a temporary paper pass that expires in fifteen minutes.

Understanding the Concepts

What is the RDS master user and why does it exist?

When you create an RDS instance you set a master username and password. That master account is administrative – it can create and drop databases, create other users, and grant any privilege. It is meant for initial setup and schema migrations, not for your application's day-to-day queries.

SQL

-- The master user has full control
-- This is what you use ONCE during setup, then lock away
CREATE DATABASE ledger;
CREATE SCHEMA banking;

How do you create least-privilege database users with SQL?

The standard way to manage users is plain SQL on the engine. You connect once as the master to bootstrap, then create purpose-specific users with only the privileges they need.

SQL

-- Create a least-privilege user for the transfer service (PostgreSQL)
CREATE USER transfer_app WITH PASSWORD 'placeholder-will-be-rotated';
GRANT CONNECT ON DATABASE ledger TO transfer_app;
GRANT USAGE ON SCHEMA banking TO transfer_app;
GRANT SELECT, INSERT, UPDATE ON banking.accounts, banking.transfers TO transfer_app;
-- No DELETE, no DROP, no CREATE TABLE

-- Create a read-only user for reporting – no writes at all
CREATE USER report_ro WITH PASSWORD 'placeholder-will-be-rotated';
GRANT CONNECT ON DATABASE ledger TO report_ro;
GRANT USAGE ON SCHEMA banking TO report_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA banking TO report_ro;
-- Cannot INSERT, UPDATE, or DELETE anything

How does IAM database authentication work?

IAM database authentication is the token-based alternative. You create a database user and mark it to authenticate through IAM, then grant the application's IAM role the rds-db:connect permission for that user. At connection time the application asks IAM for a short-lived authentication token and uses it as the password.

SQL

-- Create a database user mapped to IAM (PostgreSQL)
CREATE USER transfer_app_iam WITH LOGIN;
GRANT rds_iam TO transfer_app_iam;
GRANT SELECT, INSERT, UPDATE ON banking.accounts, banking.transfers TO transfer_app_iam;

JAVA

import software.amazon.awssdk.services.rds.RdsUtilities;
import software.amazon.awssdk.services.rds.model.GenerateAuthenticationTokenRequest;
import software.amazon.awssdk.regions.Region;
import java.sql.DriverManager;
import java.sql.Connection;

public class IAMDatabaseAuth {
    public static void main(String[] args) throws Exception {
        RdsUtilities utilities = RdsUtilities.builder()
                .region(Region.AP_SOUTH_1).build();

        String authToken = utilities.generateAuthenticationToken(
                GenerateAuthenticationTokenRequest.builder()
                        .hostname("nbi-ledger.abcdef.ap-south-1.rds.amazonaws.com")
                        .port(5432)
                        .username("transfer_app_iam")
                        .build());

        // Token expires in about 15 minutes – no stored password
        Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://nbi-ledger.abcdef.ap-south-1.rds.amazonaws.com:5432/ledger",
                "transfer_app_iam", authToken);
        System.out.println("Connected with IAM token authentication");
    }
}

Why must applications avoid the master user?

Four concrete reasons make using the master user for applications unacceptable:

Violates least privilege – The app gets far more power than it needs, including DROP TABLE and DROP DATABASE.
Destroys auditability – Every action looks like the same super-user, so you cannot tell which service did what.
Makes rotation risky – Rotating the master password can break administration everywhere.
One leak equals total compromise – The master can alter or destroy the entire database.

JAVA

public class WhyNotMasterUser {
    // WRONG: application connects as master
    // If compromised, attacker can DROP DATABASE ledger;
    // All services look like the same user in audit logs

    // CORRECT: application connects as transfer_app
    // If compromised, attacker can only SELECT/INSERT/UPDATE
    // on accounts and transfers tables
    // Audit logs clearly show which service did what
}

How is SQL user management different from IAM authentication?

| Aspect | SQL User Management | IAM Database Authentication |
|---|---|---|
| Credentials | Password stored in Secrets Manager | Short-lived IAM token (15 minutes) |
| Rotation | Secrets Manager auto-rotation | No password to rotate; token expires automatically |
| Audit | Database logs show username | CloudTrail + database logs |
| IAM integration | None; separate credential management | Full IAM policy control |
| Supported engines | All RDS engines | PostgreSQL, MySQL, MariaDB |
| Connection limit | No TLS requirement by default | Requires SSL/TLS connection |

JAVA

public class SQLvsIAMAuth {
    // SQL auth: password stored in Secrets Manager, rotated every 30 days
    // Good for Oracle and SQL Server which do not support IAM auth

    // IAM auth: token generated per connection, expires in 15 minutes
    // Preferred for PostgreSQL and MySQL workloads
    // Centralized control through IAM policies
}

How do you store and rotate passwords with Secrets Manager?

When using SQL authentication, store the password in AWS Secrets Manager with automatic rotation enabled. The application retrieves the password at connection time, and Secrets Manager rotates it on a schedule without any code changes.

JAVA

import software.amazon.awssdk.services.secretsmanager.SecretsManagerClient;
import software.amazon.awssdk.services.secretsmanager.model.GetSecretValueRequest;
import com.fasterxml.jackson.databind.ObjectMapper;
import java.sql.DriverManager;
import java.sql.Connection;

public class SecretsManagerConnection {
    public static void main(String[] args) throws Exception {
        SecretsManagerClient sm = SecretsManagerClient.create();
        String secretJson = sm.getSecretValue(GetSecretValueRequest.builder()
                .secretId("nbi/settlement/db-credentials").build())
                .secretString();

        var mapper = new ObjectMapper();
        var creds = mapper.readTree(secretJson);
        String username = creds.get("username").asText();
        String password = creds.get("password").asText();
        String host = creds.get("host").asText();

        Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://" + host + ":5432/ledger",
                username, password);
        System.out.println("Connected using Secrets Manager credentials");
    }
}

What are common mistakes?

The first mistake is using the master user for application connections. Every article and AWS guide warns against this, yet it is the most common shortcut in development environments that leaks into production.

The second mistake is granting all privileges to the application user. Even if you create a separate user, giving it ALL PRIVILEGES defeats the purpose of least privilege.

The third mistake is hardcoding database passwords in application config files or environment variables that end up in Git or container images.

SQL

-- WRONG: application user with all privileges
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA banking TO transfer_app;
-- If compromised, attacker can DROP, TRUNCATE, ALTER

-- CORRECT: only the specific operations needed
GRANT SELECT, INSERT, UPDATE ON banking.accounts, banking.transfers TO transfer_app;
-- If compromised, attacker cannot DROP or TRUNCATE

What should a senior engineer know?

A senior engineer creates a dedicated user per service (transfer_app, report_ro, reconciliation_app), each with the minimum grants, so permissions and blame are traceable per service. They keep the master locked away in Secrets Manager, used only for schema migrations by a controlled CI/CD pipeline.

They also know that IAM database authentication has a connection limit (about 200 new connections per second per instance) and adds latency for token generation, so for high-throughput services with connection pooling, SQL authentication with Secrets Manager rotation is often more practical.

DIAGRAM

Service              DB User           Permissions
──────────────────────────────────────────────────────
Transfer Service     transfer_app      SELECT, INSERT, UPDATE on accounts, transfers
Reporting Service    report_ro         SELECT on all tables (read-only)
Reconciliation Job   recon_app         SELECT, INSERT on reconciliation table
CI/CD Pipeline       master_user       ALL (used only for migrations, locked in Secrets Manager)

Visual Overview

DIAGRAM

┌─────────── Database User Management ───────────┐
│                                                 │
│  Master User (locked in Secrets Manager)         │
│  ├── Used ONLY for: CREATE USER, migrations      │
│  └── NEVER used by: application code             │
│                                                 │
│  SQL Authentication Path:                        │
│  ┌──────────┐    ┌──────────────┐    ┌────────┐ │
│  │ App Code  │──→│ Secrets Mgr   │──→│ RDS DB  │ │
│  │           │   │ (auto-rotate) │   │         │ │
│  └──────────┘    └──────────────┘    └────────┘ │
│                                                 │
│  IAM Authentication Path:                        │
│  ┌──────────┐    ┌──────────────┐    ┌────────┐ │
│  │ App Code  │──→│ IAM (generate │──→│ RDS DB  │ │
│  │ (Role)    │   │ auth token)   │   │ (SSL)   │ │
│  └──────────┘    │ 15-min expiry │   └────────┘ │
│                  └──────────────┘                │
│                                                 │
│  Per-Service Users:                              │
│  transfer_app ──→ SELECT, INSERT, UPDATE         │
│  report_ro    ──→ SELECT only                    │
│  recon_app    ──→ SELECT, INSERT                 │
└─────────────────────────────────────────────────┘

Key Takeaway

In RDS I manage database users exactly as I would on a self-hosted engine, because RDS runs the real database engine. I connect once as the master to bootstrap, then I use standard SQL – CREATE USER, ALTER USER, and GRANT or REVOKE – to create purpose-specific, least-privilege accounts. For example I give the transfer service a user that can only select, insert, and update its own tables, and I give the reporting service a user that can only select, so reporting can never modify money. Each service gets its own user with the minimum grants it needs.

The modern alternative to stored passwords is IAM database authentication. I create a database user mapped to IAM and grant the application's IAM role the connect permission, and at connection time the application requests a short-lived authentication token, valid about fifteen minutes, and uses it instead of a password. The benefit is that there is no long-lived secret to store or leak, access is centrally governed by IAM, and every connection is auditable through CloudTrail. When I do use passwords, I keep them in Secrets Manager with automatic rotation.

Applications should never use the master user, and I can give four concrete reasons. It violates least privilege, because the application would have far more power than it needs, including the ability to drop tables and databases. It destroys auditability, because every action looks like the same super-user and I cannot tell which service did what. It makes rotation dangerous, because rotating the master can break administration everywhere. And it turns a single leaked credential into a total compromise of the whole database.

At National Bank of India I created a dedicated user per service on the ledger database – a read-write transfer user, a read-only reporting user, and a reconciliation user – each with only the grants it required, and I locked the master away in Secrets Manager so it was used only by a controlled migration pipeline. For the services themselves I moved to IAM database authentication so there were no stored passwords to leak and every login expired within minutes.

Remember one sentence: Create one least-privilege database user per service, lock the master in Secrets Manager, and prefer IAM authentication so there is no password to leak.

---