The rds_superuser role isn’t that super

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.

This Post Has 3 Comments

  1. craig.ringer says:

    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, …

  2. Tometzky says:

    I’d recommend to do instead (when you use postgres name as rds_superuser):
    grant ordinaryusername to postgres;
    set role ordinaryusername;
    — do what you need to do
    set role postgres; — we’re back

    This allows for creating scripts that run for all databases on the server.

  3. Robins says:

    .. and it can’t do pg_dumpall. Which is quite frustrating if you have 500 Users and want to move-off / have a DR solution to an on-prem setup.

    The best I could do is write a patch to at least create users (sans password). So although pg_dumpall now works, resetting all users to ‘No Password’ isn’t really an ideal solution.

    http://www.thatguyfromdelhi.com/2016/12/custom-pgdumpall-now-works-with-aws.html

Leave A Reply