4

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Surya
  • 2,429
  • 1
  • 21
  • 42
  • yes, "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." – mshabou Nov 15 '21 at 22:56
  • Thanks for the detail. I read the manual too. In my case the privilege isn't revoked. Immediately after granting ownership when I check privileges, select permission is Missing. – Surya Nov 15 '21 at 22:57
  • some one had altred your default privileges https://www.postgresql.org/docs/13/sql-alterdefaultprivileges.html – mshabou Nov 15 '21 at 23:04
  • @mshabou: "Default Privileges" are applied when an object is *created*, not when ownership changes. Different topic, not applicable to this question. – Erwin Brandstetter Nov 15 '21 at 23:08
  • Erwin's answer had given me a nice lead will confirm tomorrow. Thanks @mshabou – Surya Nov 15 '21 at 23:09

1 Answers1

5

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.

Closely related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • In my case the privilege isn't revoked. Immediately after granting ownership when I check privileges, select permission is Missing. – Surya Nov 15 '21 at 22:54
  • @LunaLovegood: Like I wrote: if privileges have been revoked **from the previous owner**, the next owner only inherits the reduced set of privileges. That's effective immediately after `ALTER TABLE OWNER TO role_name;`. – Erwin Brandstetter Nov 15 '21 at 22:58
  • Oh you mean the previous owner of the table. Hmmm maybe that is possible. Let me check. Thanks for patiently pointing it out. – Surya Nov 15 '21 at 23:00
  • @LunaLovegood: That is definitely possible. I verified before posting. – Erwin Brandstetter Nov 15 '21 at 23:01
  • Hmm okay let me confirm this. Away from mac at the moment. First thing tomorrow will check this and properly confirm this answer. Thanks for the help. Now that you point it out I think this is the case. – Surya Nov 15 '21 at 23:03
  • The sad part here is, the previous owner did not the necessary privileges. It did not directly have those privileges it was a member of another role that had these privileges. I am assuming transfer of such privileges does not happen :'( – Surya Nov 16 '21 at 10:10
  • Also, I did run `GRANT ALL PRIVILEGES ON DATABASE "database-name" TO role_name;` So, even if the previous owner did not have privileges the new role should have had the privileges. – Surya Nov 16 '21 at 10:24
  • @LunaLovegood: That's a misunderstanding. You are not the first to fall for that. See: https://stackoverflow.com/questions/22483555/postgresql-give-all-permissions-to-a-user-on-a-postgresql-database/22486012#comment91773324_41556582 – Erwin Brandstetter Nov 16 '21 at 14:11