On Wed, Sep 6, 2023 at 9:03 PM Robert Haas <robertmh...@gmail.com> wrote: > > On Wed, Sep 6, 2023 at 11:14 AM Ashutosh Sharma <ashu.coe...@gmail.com> wrote: > > In PG-16, I see that we have made a lot of changes in the area roles > > and privileges. I have a question related to this and here is my > > question: > > > > Let's say there is a roleA who creates roleB and then roleB creates > > another role, say roleC. By design, A can administer B and B can > > administer C. But, can A administer C although it has not created C? > > Ultimately, yes, because A can get access to all of B's privileges, > which include administering C. However, A might or might not have B's > privileges by default, depending on the value of createrole_self_grant > in effect at the time when B was created. So, depending on the > situation, A might (or might not) need to do something like GRANT > roleB to roleA or SET ROLE roleB in order to be able to actually > execute the administration commands in question. > > IMHO, it really couldn't reasonably work in any other way. Consider > that A's right to administer B includes the right to change B's > password. If the superuser wants users A and B that can't interfere > with each other, the superuser should create both of those accounts > themselves instead of letting one create the other. >
Thank you for the clarification. This is very helpful. Actually I have one more question. With this new design, assuming that createrole_self_grant is set to 'set, inherit' in postgresql.conf and if roleA creates roleB. So, in this case, roleA will inherit permissions of roleB which means roleA will have access to objects owned by roleB. But what if roleB doesn't want to give roleA access to the certain objects it owns. As an example let's say that roleB creates a table 't' and by default (with this setting) roleA will have access to this table, but for some reason roleB does not want roleA to have access to it. So what's the option for roleB? I've tried running "revoke select on table t from roleA" but that doesn't seem to be working. the only option that works is roleA himself set inherit option on roleB to false - "grant roleB to roleA with inherit false;" -- With Regards, Ashutosh Sharma.