Amazon RDS Database privileges question
rds_superuser is not the same as a PostgreSQL superuser
From one of the best blogs on PostgreSQL, 2ndquadrant, Andrew Dunstan writes,

The Amazon RDS documentation blithely contains this statement: “When you create a DB instance, the master user system account that you create is assigned to the rds_superuser role. The rds_superuser role is similar to the PostgreSQL superuser role (customarily named postgres in local instances) but with some restrictions.” But just how super is it?
One of the things I came up against recently was that, unlike the usual postgres superuser, this role has no access other than what is explicitly granted to objects owned by other users. From a table and function privileges point of view, it’s just an ordinary user.
So if you’re using more than one user in your RDS database, even if one or even all of them are rds_superusers, you’re going to become very familiar with the GRANT command if you aren’t already. And if your schema has objects owned by more than one user, then the relevant “GRANT .. ON ALL ..” option fails too, since you probably won’t have sufficient privileges on all of them. Perhaps we should have a “GRANT … ON ALL POSSIBLE …” which would skip those things you don’t have GRANT privilege on.

From our own Craig Ringer (also with 2nd Quadrant)

It’s not very super at all. It cannot LOAD libraries, access the file system, override permissions, get an unrestricted view of pg_stat_activity

Another user, Robins, also points out that rds_superuser can't run pg_dumpall so this may be a source of vendor lock-in.