Re: fixing CREATEROLE

On Tue, Jan 3, 2023 at 3:11 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Committed and back-patched 0001 with fixes for the issues that you pointed out.
>
> Here's a trivial rebase of the rest of the patch set.

I committed 0001 and 0002 after improving the commit messages a bit.
Here's the remaining two patches back. I've done a bit more polishing
of these as well, specifically in terms of fleshing out the regression
tests. I'd like to move forward with these soon, if nobody's too
vehemently opposed to that.

Previous feedback, especially from Tom but also others, was that the
role-level properties the final patch was creating were not good. Now
it doesn't create any new role-level properties, and in fact it has
nothing to say about role-level properties in any way. That might not
be the right thing. Right now, if you have CREATEROLE, you can create
new roles with any combination of attributes you like, except that you
cannot set the SUPERUSER, REPLICATION, or BYPASSRLS properties. While
I think it makes sense that a CREATEROLE user can't hand out SUPERUSER
or REPLICATION privileges, it is really not obvious to me why a
CREATEROLE user shouldn't be permitted to hand out BYPASSRLS, at least
if they have it themselves, and right now there's no way to allow
that. On the other hand, I think that some superusers might want to
restrict a CREATEROLE user's ability to hand out CREATEROLE or
CREATEDB to the users they create, and right now there's no way to
prohibit that.

I don't have a great idea about what a system for handling this
problem ought to look like. In a vacuum, I think it would be
reasonable to change CREATEROLE to only allow CREATEDB, BYPASSRLS, and
similar to be given to new users if the creating user possesses them,
but that approach does not work for CREATEROLE, because if you didn't
have that, you couldn't create any new users at all. It's also pretty
weird for, say, CONNECTION LIMIT. I doubt that there's any connection
between the CONNECTION LIMIT of the CREATEROLE user and the values
that they ought to be able to set for users that they create. Probably
you just want to allow setting CONNECTION LIMIT for downstream users,
or not. Or maybe it's not even worth worrying about -- I think there
might be a decent argument that limiting the ability to set CONNECTION
LIMIT just isn't interesting.

If someone else has a good idea what we ought to do about this part of
the problem, I'd be interested to hear it. Absent such a good idea --
or if that good idea is more work to implement that can be done in the
near term -- I think it would be OK to ship as much as I've done here
and revisit the topic at some later point when we've had a chance to
absorb user feedback.

Thanks,

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