Tuesday, October 24

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.

4 Comments

  • Tometzky

    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.

  • MichaelDBA

    Wow, broke all my access control design when it comes time to import a dump from non-rds source. So I use an RDS_SUPERUSER to do the database import. It creates a schema, whatever. It then assigns ownership of that schema to another non-login account, but then when the session rds_superuser tries to create objects under that schema, it fails since the superuser is no longer the owner of the schema! So I have to manually edit the dump file to not change ownership of objects until after all the objects are created. Jeeeeeesh!
    All i got to say is “oh my gosh”.

Leave a Reply

Your email address will not be published. Required fields are marked *