Re: fixing CREATEROLE - Mailing list pgsql-hackers

On Wed, Nov 23, 2022 at 2:28 PM Mark Dilger
<mark.dilger@enterprisedb.com> wrote:
> > On Nov 23, 2022, at 11:02 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> > For me, this
> > clearly falls into the "good" category: it's configuration that you
> > put into the database that makes things happen the way you want, not a
> > behavior-changing setting that comes along and ruins somebody's day.
>
> I had incorrectly imagined that if the bootstrap superuser granted CREATEROLE to Alice with particular settings,
thosesettings would limit the things that Alice could do when creating role Bob, specifically limiting how much she
couldadminister/inherit/set role Bob thereafter.  Apparently, your proposal only configures what happens by default,
andAlice can work around that if she wants to. 

Right.

> But if that's the case, did I misunderstand upthread that these are properties the superuser specifies about Alice?
CanAlice just set these properties about herself, so she gets the behavior she wants?  I'm confused now about who
controlsthese settings. 

Because they are role-level properties, they can be set by whoever has
ADMIN OPTION on the role. That always includes every superuser, and it
never includes Alice herself (except if she's a superuser). It could
include other users depending on the system configuration. For
example, under this proposal, the superuser might create alice and set
her account to CREATEROLE, configuring the INHERITCREATEDROLES and
SETCREATEDROLES properties on Alice's account according to preference.
Then, alice might create another user, say bob, and make him
CREATEROLE as well. In such a case, either the superuser or alice
could set these properties for role bob, because alice enjoys ADMIN
OPTION on role bob.

Somewhat to one side of the question you were asking, but related to
the above, I believe there is an opportunity, and perhaps a need, to
modify the scope of CREATEROLE in terms of what role-level options a
CREATEROLE user can set. For instance, if a CREATEROLE user doesn't
have CREATEDB, they can still create users and give them that
privilege, even with these patches, and likewise these two new
properties. This patch is only concerned about which roles you can
manipulate, not what role-level properties you can set. Somebody might
feel that's a serious problem, and they might even feel that this
patch set ought to something about it. In my view, the issues are
somewhat severable. I don't think you can do anything as evil by
setting role-level properties (except for SUPERUSER, of course) as
what you can do by granting predefined roles, so I don't find
restricting those capabilities to be as urgent as doing something to
restrict role grants.

Also, and this definitely plays into it too, I think there's some
debate about what the model ought to look like there. For instance,
you could simply stipulate that you can't give what you don't have,
but that would mean that every  CREATEROLE user can create additional
CREATEROLE users, and I suspect some people might like to restrict
that. We could add a new CREATECREATEROLE property to decide whether a
user can make CREATEROLE users, but by that argument we'd also need a
new CREATECREATECREATEROLE property to decide whether a role can make
CREATECREATEROLE users, and then it just recurses indefinitely from
there. Similarly for CREATEDB. Also, what if anything should you
restrict about how the new INHERITCREATEDROLES and SETCREATEDROLES
properties should be set? You could argue that they ought to be
superuser-only (because the implicit grant is performed by the
bootstrap superuser) or that it's fine for them to be set by a
CREATEROLE user with ADMIN OPTION (because it's not all that
security-critical how they get set) or maybe even that a user ought to
be able to set those properties on his or her own role.

I'm not very certain about any of that stuff; I don't have a clear
mental model of how it should work, or even what exact problem we're
trying to solve. To me, the patches that I posted make sense as far as
they go, but I'm not under the illusion that they solve all the
problems in this area, or even that I understand what all of the
problems are.

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