Re: Can a role have indirect ADMIN OPTION on another role?
On Thu, Sep 7, 2023 at 3:43 AM David G. Johnston wrote: > > On Wed, Sep 6, 2023 at 1:55 PM Ashutosh Sharma wrote: >> >> But what if roleB doesn't want to give roleA access to >> the certain objects it owns. > > > Not doable - roleA can always pretend they are roleB one way or another since > roleA made roleB. > Okay. It makes sense. thanks.! -- With Regards, Ashutosh Sharma.
Re: Can a role have indirect ADMIN OPTION on another role?
On Thu, Sep 7, 2023 at 12:20 AM Robert Haas wrote: > > On Wed, Sep 6, 2023 at 1:33 PM Ashutosh Sharma wrote: > > 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;" > > It doesn't matter what roleB wants. roleA is strictly more powerful > than roleB and can do whatever they want to roleB or roleB's objects > regardless of how roleB feels about it. > > In the same way, the superuser is strictly more powerful than either > roleA or roleB and can override any security control that either one > of them put in place. > > Neither roleB nor roleA has any right to hide their data from the > superuser, and roleB has no right to hide data from roleA. It's a > hierarchy. If you're on top, you're in charge, and that's it. > > Here again, it can't really meaningfully work in any other way. > Suppose you were to add a feature to allow roleB to hide data from > roleA. Given that roleA has the ability to change roleB's password, > how could that possibly work? When you give one user the ability to > administer another user, that includes the right to change that user's > password, change whether they can log in, drop the role, give the > privileges of that role to themselves or other users, and a whole > bunch of other super-powerful stuff. You can't really give someone > that level of power over another account and, at the same time, expect > the account being administered to be able to keep the more powerful > account from doing stuff. It just can't possibly work. If you want > roleB to be able to resist roleA, you have to give roleA less power. > I agree with you. thank you once again. -- With Regards, Ashutosh Sharma.
Re: Can a role have indirect ADMIN OPTION on another role?
On Wed, Sep 6, 2023 at 1:55 PM Ashutosh Sharma wrote: > But what if roleB doesn't want to give roleA access to > the certain objects it owns. Not doable - roleA can always pretend they are roleB one way or another since roleA made roleB. David J.
Re: Can a role have indirect ADMIN OPTION on another role?
On Wed, Sep 6, 2023 at 1:33 PM Ashutosh Sharma wrote: > 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;" It doesn't matter what roleB wants. roleA is strictly more powerful than roleB and can do whatever they want to roleB or roleB's objects regardless of how roleB feels about it. In the same way, the superuser is strictly more powerful than either roleA or roleB and can override any security control that either one of them put in place. Neither roleB nor roleA has any right to hide their data from the superuser, and roleB has no right to hide data from roleA. It's a hierarchy. If you're on top, you're in charge, and that's it. Here again, it can't really meaningfully work in any other way. Suppose you were to add a feature to allow roleB to hide data from roleA. Given that roleA has the ability to change roleB's password, how could that possibly work? When you give one user the ability to administer another user, that includes the right to change that user's password, change whether they can log in, drop the role, give the privileges of that role to themselves or other users, and a whole bunch of other super-powerful stuff. You can't really give someone that level of power over another account and, at the same time, expect the account being administered to be able to keep the more powerful account from doing stuff. It just can't possibly work. If you want roleB to be able to resist roleA, you have to give roleA less power. -- Robert Haas EDB: http://www.enterprisedb.com
Re: Can a role have indirect ADMIN OPTION on another role?
On Wed, Sep 6, 2023 at 9:03 PM Robert Haas wrote: > > On Wed, Sep 6, 2023 at 11:14 AM Ashutosh Sharma 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.
Re: Can a role have indirect ADMIN OPTION on another role?
On Wed, Sep 6, 2023 at 11:14 AM Ashutosh Sharma 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. -- Robert Haas EDB: http://www.enterprisedb.com
Can a role have indirect ADMIN OPTION on another role?
Hi, 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? -- With Regards, Ashutosh Sharma.
