Granting All Permissions to a user on a table/View

[JimKnicely]

Maybe I'm misunderstanding the question, but isn't it true that If the pseudosuperuser role has been granted to a user and that role is enabled then the user should be able to drop any table in the database...

Example:

The dbadmin user in a test system has the dbadmin and pseudosuperuser roles:

Code: Select all

dbadmin=> select current_user();
 current_user
--------------
 dbadmin
(1 row)

dbadmin=> show available_roles;
      name       |         setting
-----------------+--------------------------
 available roles | dbadmin, pseudosuperuser
(1 row)

I'll create a table that is owned by dbadmin:

Code: Select all

dbadmin=> create table test (col1 varchar);
CREATE TABLE

Next I'll create a new user and grant it the pseudosuperuser role:

Code: Select all

dbadmin=> create user X;
CREATE USER
dbadmin=> grant pseudosuperuser to X;
GRANT ROLE

Now I'll connect as user X and try and drop the test table owned by dbadmin:

Code: Select all

dbadmin=> \c dbadmin X
You are now connected to database "dbadmin" as user "X".
dbadmin=> show available_roles;
      name       |     setting
-----------------+-----------------
 available roles | pseudosuperuser
dbadmin=> drop table test;
ROLLBACK:  must be owner of relation test
(1 row)

User X can't drop the table until the pseudosuperuser is enabled with the SET ROLE statement (unless it's a default ROLE):

Code: Select all

dbadmin=> set role pseudosuperuser;
SET
dbadmin=> drop table test;
DROP TABLE

Have you guys found this to be the case?

This is from the Admin Guide:

Enabling Roles
By default, roles aren't enabled automatically for a user account (see Default Roles (page 158) for a way to make roles enabled automatically). Users must explicitly enable a role using the SET ROLE statement. When users enable a role in their session, they gain all of the privileges assigned to that role. Enabling a role does not affect any other roles that the users have active in their sessions. They can have multiple roles enabled simultaneously, gaining the combined privileges of all the roles they have enabled (plus any of the privileges that have been granted to them directly).