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.
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, …
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.
.. 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
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”.
Can’t run a copy command either!