Is it possible for an owner to not have table-privileges?-CodePudding

We have been trying to create many roles and users in one of the databases. At one point I came across this issue that I am finding it hard to reproduce.

It is reproducible within the database but when I create a new database and try the same it isn't happening :(

ALTER TABLE public.table_name OWNER TO role_name;

Usually, after we run this query. The table_name will be owned by the role_name role/user.

After running the above query if we run the below query:

select grantee, table_catalog, privilege_type, table_schema, table_name 
from information_schema.table_privileges 
where table_name = 'table_name' 
order by grantee, table_schema, table_name

We will get the below results:

 role_name   | database_name   | INSERT       | public   | table_name
 role_name   | database_name   | DELETE       | public   | table_name
 role_name   | database_name   | SELECT       | public   | table_name
 role_name   | database_name   | UPDATE       | public   | table_name
 role_name   | database_name   | TRUNCATE     | public   | table_name
 role_name   | database_name   | REFERENCES   | public   | table_name
 role_name   | database_name   | TRIGGER      | public   | table_name

However, in a particularly fucked up database (I am sorry about the language, but god knows what I have been through and I am sure he would excuse me this time :P ) after I grant ownership the new role is becoming the owner but it does not have SELECT, INSERT or any privilege for that matter.

So, my question is:

Are there any scenario where when we assign ownership of a table to a role, the role can be the owner and still not have select, insert, update, delete privilege?

If yes, when and why?

CodePudding user response：

the role can be the owner and still not have select, insert, update, delete privilege?

Yes. The manual:

An object's owner can choose to revoke their own ordinary privileges, for example to make a table read-only for themselves as well as others. But owners are always treated as holding all grant options, so they can always re-grant their own privileges.

And a superuser can do anything that the owner can do.

Tricky detail: The owner (or a superuser) can REVOKE privileges from himself (the owner). If ownership then changes hands, the new owner inherits the set of privileges that the previous owner had - plus any privileges the new owner might already have held. So the union of the previous privileges of the role and privileges of the owner. Those are then the new privileges of the owner.

If ownership again changes hands, the whole set of privileges is passed on! The previous owner loses all privileges he has held directly.

But any role can inherit additional privileges via membership in another role or via PUBLIC privileges.