On Thu, Sep 7, 2023 at 12:20 AM Robert Haas <robertmh...@gmail.com> wrote: > > On Wed, Sep 6, 2023 at 1:33 PM Ashutosh Sharma <ashu.coe...@gmail.com> 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.
