Re: fixing CREATEROLE

On Wed, Nov 23, 2022 at 3:11 PM Mark Dilger
<mark(dot)dilger(at)enterprisedb(dot)com> wrote:
> Ok, so the critical part of this proposal is that auditing tools can tell when Alice circumvents these settings. Without that bit, the whole thing is inane. Why make Alice jump through hoops that you are explicitly allowing her to jump through? Apparently the answer is that you can point a high speed camera at the hoops.

Well put.

Also, it's a bit like 'su', right? Typically you don't just log in as
root and do everything a root, even if you have access to root
privileges. You log in as 'mdilger' or whatever and then when you want
to exercise elevated privileges you use 'su' or 'sudo' or something.
Similarly here you can make an argument that it's a lot cleaner to
give Alice the potential to access all of these privileges than to
make her have them all the time.

But on the flip side, one big advantage of having 'alice' have the
privileges all the time is that, for example, she can probably restore
a database dump that might otherwise be restorable only with superuser
privileges. As long as she has been granted all the relevant roles
with INHERIT TRUE, SET TRUE, the kinds of locutions that pg_dump spits
out should pretty much work fine, whereas if Alice is firewalled from
the privileges of the roles she manages, that is not going to work
well at all. To me, that is a pretty huge advantage, and it's a major
reason why I initially thought that alice should just categorically,
always inherit the privileges of the roles she creates.

But having been burned^Wenlightened by previous community discussion,
I can now see both sides of the argument, which is why I am now
proposing to let people pick the behavior they happen to want.

--
Robert Haas
EDB: http://www.enterprisedb.com