OnCallReadyStart learning

AWS Lesson 38.1 · AWS V: Data, Resilience & the Edge · 23 min read

RDS: a managed PostgreSQL you reach by DNS

In plain words

Running PostgreSQL yourself is like owning a car: you change the oil, fix the brakes and keep a spare. RDS is a lease with a service contract. AWS keeps the engine running, replaces parts, and parks a spare car next to yours. You still drive: where the data goes, who gets a key, how hard you push it.

You find the car by a name (the endpoint), never by its parking space, because the car behind the name can change. And the keys AWS hands you open almost every door, but not the bonnet: the master user is not a real superuser.

RDS: a managed PostgreSQL you reach by DNS

The PostgreSQL chapter had you run the database yourself: packages, pg_hba.conf, WAL archiving, replicas, failover. Amazon RDS runs the same PostgreSQL for you. AWS owns the hosts, the operating system, patching, backups, the standby and the failover. You still own the schema, the queries, the users, the parameters, the network path and the decision about what happens when it fails. That split is the shared responsibility model applied to a database, and most RDS incidents happen on your side of the line.

Need to know: an RDS DB instance lives in a DB subnet group (subnets in at least two AZs) behind VPC security groups. You reach it through its endpoint, a DNS name, never an IP. The master user is powerful but not a superuser. Settings live in a parameter group. Some changes apply at once, others wait for a reboot or the maintenance window.

What is there

The lab has a VPC data-vpc (two AZs, private subnets, a NAT gateway), an app instance app-1 in a private subnet, and an RDS for PostgreSQL instance shop-db holding the shop's orders:

$ aws rds describe-db-instances --query 'DBInstances[].[DBInstanceIdentifier,Engine,EngineVersion,DBInstanceClass,DBInstanceStatus,MultiAZ]' --output table
----------------------------------------------------------------------
|                         DescribeDBInstances                        |
+---------+-----------+-------+---------------+-------------+--------+
|  shop-db|  postgres |  18.3 |  db.t4g.micro |  available  |  True  |
+---------+-----------+-------+---------------+-------------+--------+
$ aws rds describe-db-instances --db-instance-identifier shop-db --query 'DBInstances[0].{az: AvailabilityZone, standby: SecondaryAvailabilityZone, storage: AllocatedStorage, type: StorageType, encrypted: StorageEncrypted, public: PubliclyAccessible, subnetGroup: DBSubnetGroup.DBSubnetGroupName, sgs: VpcSecurityGroups[].VpcSecurityGroupId, backups: BackupRetentionPeriod}'
{
    "az": "eu-central-1b",
    "standby": "eu-central-1a",
    "storage": 20,
    "type": "gp3",
    "encrypted": true,
    "public": false,
    "subnetGroup": "data-db",
    "sgs": [
        "sg-0113e62df12292987"
    ],
    "backups": 7
}
$ aws rds describe-db-subnet-groups --db-subnet-group-name data-db --query 'DBSubnetGroups[0].Subnets[].[SubnetIdentifier,SubnetAvailabilityZone.Name]' --output text
subnet-093b5a8079a3fabfe	eu-central-1a
subnet-013213681741cf1fa	eu-central-1b

A DB subnet group must cover two AZs even for a single-AZ instance (RDS refuses one with DBSubnetGroupDoesNotCoverEnoughAZs): RDS needs somewhere to put a standby or a replacement. These are the private subnets. PubliclyAccessible: false means the endpoint resolves to a private address only, so the database is reachable from inside the VPC (or a peered network), never from the internet. That is the default you want.

The endpoint is a name, and it moves

$ dig +noall +answer shop-db.c3a56c7217fa.eu-central-1.rds.amazonaws.com
shop-db.c3a56c7217fa.eu-central-1.rds.amazonaws.com.	5	IN	CNAME	ip-10-30-11-19.eu-central-1.compute.internal.
ip-10-30-11-19.eu-central-1.compute.internal.	60	IN	A	10.30.11.19
$ PGCONNECT_TIMEOUT=5 psql "host=shop-db.c3a56c7217fa.eu-central-1.rds.amazonaws.com user=dbadmin dbname=orders" -c 'select 1'
psql: error: connection to server at "shop-db.c3a56c7217fa.eu-central-1.rds.amazonaws.com" (10.30.11.19), port 5432 failed: Connection timed out
	Is the server running on that host and accepting TCP/IP connections?

Two things to notice. The endpoint is a CNAME with a TTL of 5 seconds, pointing at the private name of whichever host is the primary right now. When RDS fails over, it changes that CNAME, and the 5-second TTL is what lets clients find the new primary quickly. So clients must connect by the endpoint name, never by the IP behind it. Second, from your machine the address resolves but the connection times out: 10.30.x.x is not reachable from outside the VPC. You go in through app-1 with Session Manager (AWS II):

$ APP=$(aws ec2 describe-instances --filters Name=tag:Name,Values=app-1 Name=instance-state-name,Values=running --query 'Reservations[0].Instances[0].InstanceId' --output text)
$ aws ssm start-session --target $APP

Starting session with SessionId: learner-0d94544852da36288
$ export PGPASSWORD='Lab-Passw0rd-2026'
$ psql "host=shop-db.c3a56c7217fa.eu-central-1.rds.amazonaws.com user=dbadmin dbname=orders" -c 'show server_version' -c 'select count(*) as orders from orders'
 server_version
----------------
 18.3
(1 row)

 orders
--------
   2000
(1 row)

(PGPASSWORD in an environment variable is a lab shortcut; the app gets the password from Secrets Manager or uses an IAM token, which the next lesson covers.)

The master user is not a superuser

$ psql "host=shop-db.c3a56c7217fa.eu-central-1.rds.amazonaws.com user=dbadmin dbname=orders" -c "select rolname, rolsuper, rolcreaterole, rolcreatedb from pg_roles where rolname in ('dbadmin', 'rds_superuser', 'rdsadmin')"
    rolname    | rolsuper | rolcreaterole | rolcreatedb
---------------+----------+---------------+-------------
 rds_superuser | f        | f             | f
 rdsadmin      | t        | f             | f
 dbadmin       | f        | t             | t
(3 rows)
$ psql "host=shop-db.c3a56c7217fa.eu-central-1.rds.amazonaws.com user=dbadmin dbname=orders" -Atc "select string_agg(b.rolname, ', ') from pg_auth_members m join pg_roles b on b.oid = m.roleid join pg_roles u on u.oid = m.member where u.rolname = 'dbadmin'"
rds_superuser
$ psql "host=shop-db.c3a56c7217fa.eu-central-1.rds.amazonaws.com user=dbadmin dbname=orders" -c 'alter system set work_mem = 65536'
ERROR:  permission denied to set parameter "work_mem"

The master user (dbadmin here; RDS refuses reserved names like rdsadmin) can create roles and databases and is a member of rds_superuser. The real superuser is rdsadmin, which only AWS uses. So ALTER SYSTEM, file access, and anything that would let you touch the host are closed. Server settings go through the parameter group instead. Create application roles with only what they need, and keep the master password for emergencies.

TLS is on by default

$ psql "host=shop-db.c3a56c7217fa.eu-central-1.rds.amazonaws.com user=dbadmin dbname=orders sslmode=disable" -c 'select 1'
psql: error: connection to server at "shop-db.c3a56c7217fa.eu-central-1.rds.amazonaws.com" (10.30.11.19), port 5432 failed: FATAL:  no pg_hba.conf entry for host "10.30.10.30", user "dbadmin", database "orders", no encryption
$ psql "host=shop-db.c3a56c7217fa.eu-central-1.rds.amazonaws.com user=dbadmin dbname=orders" -Atc 'select ssl, version from pg_stat_ssl where pid = pg_backend_pid()'
t|TLSv1.3
$ exit


Exiting session with sessionId: learner-0d94544852da36288.

Since PostgreSQL 15 on RDS the parameter rds.force_ssl defaults to 1: a connection without TLS gets no pg_hba.conf entry ... no encryption. libpq's default sslmode=prefer already uses TLS, so psql works. A driver configured with ssl=false (old JDBC URLs) breaks right after an upgrade. For full protection a client uses sslmode=verify-full with the RDS CA bundle, which also proves the server is the one you meant.

Parameter groups: static, dynamic, pending-reboot

Every instance uses a DB parameter group. The default one (default.postgres18) cannot be changed, so the first real change is always "create my own group":

$ aws rds describe-db-parameters --db-parameter-group-name default.postgres18 --query "Parameters[?ParameterName=='max_connections' || ParameterName=='shared_buffers' || ParameterName=='log_min_duration_statement' || ParameterName=='rds.force_ssl'].[ParameterName,ParameterValue,ApplyType]" --output table
------------------------------------------------------------------------------------------
|                                  DescribeDBParameters                                  |
+-----------------------------+-----------------------------------------------+----------+
|  max_connections            |  LEAST({DBInstanceClassMemory/9531392},5000)  |  static  |
|  shared_buffers             |  {DBInstanceClassMemory/32768}                |  static  |
|  log_min_duration_statement |  None                                         |  dynamic |
|  rds.force_ssl              |  1                                            |  dynamic |
+-----------------------------+-----------------------------------------------+----------+
$ aws rds create-db-parameter-group --db-parameter-group-name try-pg18 --db-parameter-group-family postgres18 --description 'shop: slow query log'
{
    "DBParameterGroup": {
        "DBParameterGroupName": "try-pg18",
        "DBParameterGroupFamily": "postgres18",
        "Description": "shop: slow query log",
        "DBParameterGroupArn": "arn:aws:rds:eu-central-1:111122223333:pg:try-pg18"
    }
}
$ aws rds modify-db-parameter-group --db-parameter-group-name try-pg18 --parameters 'ParameterName=shared_buffers,ParameterValue=32768,ApplyMethod=immediate'
aws: [ERROR]: An error occurred (InvalidParameterCombination) when calling the ModifyDBParameterGroup operation: cannot use immediate apply method for static parameter
$ aws rds modify-db-parameter-group --db-parameter-group-name try-pg18 --parameters 'ParameterName=log_min_duration_statement,ParameterValue=500,ApplyMethod=immediate' 'ParameterName=shared_buffers,ParameterValue=32768,ApplyMethod=pending-reboot'
{
    "DBParameterGroupName": "try-pg18"
}

max_connections shows a formula (DBInstanceClassMemory / 9531392): it scales with the instance class, so a db.t4g.micro gets fewer than 100 connections. That is a common surprise when a new service with a pool of 20 per pod scales to ten pods. Static parameters (shared_buffers, max_connections) only take effect after a reboot, and RDS refuses immediate for them. Dynamic ones apply at once to every instance using the group.

$ aws rds modify-db-instance --db-instance-identifier shop-db --db-parameter-group-name try-pg18 --db-instance-class db.t4g.small --query 'DBInstance.[DBInstanceClass,PendingModifiedValues]'
[
    "db.t4g.micro",
    {
        "DBInstanceClass": "db.t4g.small"
    }
]
$ aws rds describe-db-instances --db-instance-identifier shop-db --query 'DBInstances[0].[DBParameterGroups[0].DBParameterGroupName,DBParameterGroups[0].ParameterApplyStatus,PreferredMaintenanceWindow]' --output text
try-pg18	pending-reboot	sun:03:00-sun:03:30

Two kinds of waiting. Attaching a new parameter group always leaves it pending-reboot until you reboot. The class change was sent without --apply-immediately, so it sits in PendingModifiedValues until the maintenance window (sun:03:00-sun:03:30 UTC here), where RDS applies it with a short outage. On a Multi-AZ instance RDS changes the standby first and then fails over, so the outage is the failover time. With --apply-immediately it happens now. Choose consciously: "it will apply on Sunday at 03:00" surprises the on-call engineer who did not make the change.

Storage, metrics and what protects you from yourself

$ aws rds describe-db-instances --db-instance-identifier shop-db --query 'DBInstances[0].[AllocatedStorage,MaxAllocatedStorage,StorageType,DeletionProtection]' --output text
20	None	gp3	False
$ aws cloudwatch list-metrics --namespace AWS/RDS --dimensions Name=DBInstanceIdentifier,Value=shop-db --query 'Metrics[].MetricName' --output text
CPUUtilization	DatabaseConnections	DBLoad	FreeableMemory	FreeStorageSpace	ReadIOPS	ReadLatency	WriteIOPS	WriteLatency
$ aws rds modify-db-instance --db-instance-identifier shop-db --max-allocated-storage 100 --deletion-protection --apply-immediately --query 'DBInstance.[MaxAllocatedStorage,DeletionProtection]' --output text
100	True
$ aws rds delete-db-instance --db-instance-identifier shop-db --skip-final-snapshot
aws: [ERROR]: An error occurred (InvalidParameterCombination) when calling the DeleteDBInstance operation: Cannot delete protected DB Instance, please disable deletion protection and try again.
  • Storage is gp3 (20 GiB is the minimum). When it fills, the instance goes to storage-full and writes stop. Storage autoscaling (--max-allocated-storage) grows it by itself when free space stays under 10% for five minutes, at most once every six hours, never down. Alarm on FreeStorageSpace anyway: autoscaling has a ceiling.
  • Metrics: CPUUtilization, DatabaseConnections (against max_connections), FreeableMemory, FreeStorageSpace, Read/WriteLatency, ReplicaLag on replicas, and DBLoad, the Performance Insights metric: average active sessions. DBLoad above the vCPU count means sessions are waiting. Performance Insights breaks it down by wait event and by SQL statement. Enhanced Monitoring adds OS-level metrics from an agent on the host.
  • Deletion protection makes delete-db-instance fail until someone turns it off. And a delete without --skip-final-snapshot must name a --final-db-snapshot-identifier, so the last copy of the data cannot disappear by accident.

On the exam (SOA-C03 1.3, 2.1; SAA-C03 3.3): "the database runs out of storage at night" = storage autoscaling + a FreeStorageSpace alarm; "find the query that causes the load" = Performance Insights; "a static parameter does not take effect" = reboot; "prevent accidental deletion" = deletion protection. Know that RDS is not reachable over SSH and that the master user is not a superuser: changes go through the API and parameter groups.

In an interview: "What do you still own when the database is RDS?" - "The schema, the queries and indexes, users and their privileges, parameter groups, the security groups and subnets, the backup retention and restore tests, the maintenance window choices, client connection settings (pools, timeouts, TLS), and the alarms. AWS owns the hardware, the OS, patching mechanics, the backup machinery and the failover."

You can now: read an RDS instance's network placement, connect through its endpoint from inside the VPC over TLS, explain why the master user is not a superuser, create and attach a parameter group and predict when each change takes effect, and protect an instance with storage autoscaling, deletion protection and the right metrics.

Why it helps

Most RDS incidents happen on your side of the shared responsibility line: a security group that silently drops the new batch host, a connection limit that scales with the instance class, a parameter waiting for a reboot, a class change that lands at 03:00 on Sunday, storage that fills at night.

Knowing what AWS owns and what you own is what lets you find those quickly, and what an interviewer is checking when they ask what changes when the database becomes RDS.

Commands in this lesson

aws dig export psql exit

FAQ

Why can't I run ALTER SYSTEM or SSH into the database host?

RDS gives you no host access. The master user (dbadmin in the lab) can create roles and databases and is a member of rds_superuser, but the real superuser is rdsadmin, which only AWS uses. Server settings go through a DB parameter group instead, and you reach the database only through its endpoint.

Why does my parameter change not take effect?

Static parameters such as shared_buffers and max_connections only apply after a reboot, and RDS refuses ApplyMethod=immediate for them. Dynamic ones apply at once to every instance using the group. Attaching a new parameter group always leaves it pending-reboot until you reboot. The default group cannot be changed, so the first real change is creating your own.

What happens if I modify an instance without --apply-immediately?

The change waits in PendingModifiedValues until the maintenance window (sun:03:00-sun:03:30 UTC in the lab), and describe-db-instances keeps showing the old value until then. RDS applies it there with a short outage; on a Multi-AZ instance it changes the standby first and fails over. Choose consciously: the on-call engineer who did not make the change gets the surprise.

Why does my connection to RDS just time out?

A blocked connection is a silent drop. With PubliclyAccessible false the endpoint resolves to a private address, reachable only from inside the VPC or a connected network. Inside, the database security group decides: in the lab it allows 5432 only from members of the app security group, so a host without that group times out.

Why did an old application stop connecting after an upgrade?

Since PostgreSQL 15 on RDS, rds.force_ssl defaults to 1, so a connection without TLS is refused with a "no encryption" pg_hba error. libpq's default sslmode=prefer already uses TLS, but a driver configured with ssl=false breaks. Use sslmode=verify-full with the RDS CA bundle, which also proves you reached the right server.

In an interview Mid

What do you still own when your database runs on RDS?

That is the shared responsibility model applied to a database. AWS owns the hardware, the operating system, patching mechanics, the backup machinery, the standby and the failover. I own the schema, queries and indexes, users and their privileges (the master user is only in rds_superuser), the parameter group, the security groups and subnets, backup retention and restore tests, maintenance window choices, client connection settings such as pools, timeouts and TLS, and the alarms on things like FreeStorageSpace and DatabaseConnections.

Also asked: Why should applications connect to RDS by its endpoint name rather than an IP address? · How would you stop an RDS instance from running out of storage at night? · What is the difference between static and dynamic parameters in RDS?

Practise this lesson in the terminal Free, in your browser - a real Ubuntu terminal to try it in, with missions that check your work.