I have created a database and have my username which is part of the rds_superuser group. I granted a friend access to my database and whenever he creates a new table I am unable to view his table and get an access denied error code [42501].

Also, If I try to change any privileges to my user account, I get an error that I don't have permission on his tables. How do I fix this?

  • 1
    I've removed any connection to PostgreSQL because the rds_superuser is central to your question and it's not a PostgreSQL thing. – Evan Carroll Jul 8 '17 at 2:58

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.