PostgreSQL – Administration as Non-Superuser

permissionspostgresqlusers

I have a database on a shared PostgreSQL server. I possess the ownership of the database and the CREATEROLE privilege and would probably be able to get a few other privileges if I request them but I won't be able to get superuser.

The database should be used by different users that I will create. I was wondering if it would be possible for me to actually maintain the system. A few issues I am seeing:

  • Other users can and should be able to create their own tables. Would it be possible for me as the database owner to modify/delete them?
  • Can I create full backups? I.e., would I even be able to see all tables if the creating user doesn't grant me the permissions explicitly? Could I at least set up that privileges are granted to me per default for all users?

As said, I don't have superuser privileges but I might be able to request certain changes to the system and get additional privileges.

Best Answer

That can easily be done if you make your administrative user a member of the users you create:

CREATE ROLE newuser LOGIN;
GRANT newuser TO admin;

Then you inherit all rights from that user and can ALTER and DROP the objects it owns. You can even become that user:

SET ROLE newuser;

The only problem is that newuser can revoke that privilege.

Related Question