Re: fixing CREATEROLE

On Wed, Apr 30, 2025 at 4:29 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I confess to not having paid close enough attention when
> these patches went in, or I would have complained about
> createrole_self_grant.  It changes the user-visible behavior
> of SQL commands, specifically CREATE ROLE.  We have learned
> over and over again that GUCs that do that are generally
> a bad idea.

Yeah, that's a fair complaint. I thought it wasn't too bad here
because the cases where it changes the behavior are so narrow, but I
understand why you don't like it. Also, the purpose of the settings is
really to paper over a pretty arbitrary difference between the way
that role management works for superusers and the way it works for
non-superusers. If you want to have a non-superuser administrator, as
every cloud provider does, without this patch, your only real
alternative is to patch the server, which is what everyone was doing
(and maybe they still will, but at least now there's a way to work
around it with just configuration if you want to ship unmodified
PostgreSQL). Consider:

robert.haas=# create role alice;
CREATE ROLE

There is now a role called 'alice' and you have all of alice's
privileges. But now consider:

robert.haas=# create role admin createrole;
CREATE ROLE
robert.haas=# set role admin;
SET
robert.haas=> create role alice;
CREATE ROLE

There is now a role called 'alice' but you do not have alice's
privileges unless you subsequently run "GRANT alice to admin". One
problem, as I say, is that this is confusing and the admin user isn't
likely to understand what they need to do. But maybe the bigger
question is: how do you justify this being randomly different? And if,
hypothetically, you did think it was bad that it was randomly
different, how would you propose fixing it without a behavior-changing
GUC? I guess you could opt for some kind of catalog state someplace
rather than a GUC, but I don't see how else you get around it, unless
you just made a hard behavior change, but that seemed almost certain
to draw objections.

Now I feel like you might object that there's no actual problem here,
but in my opinion, it does nobody any good to refuse to address
problems upstream when multiple large providers are patching around
the issue in more or less the same way. If Microsoft is carrying a
patch to allow for a non-superuser administrator and Amazon is doing
the same thing and EDB is doing the same thing (ok, we're not quite as
big...), to just say "nah, there's no actual problem here" doesn't
really make a lot of sense to me. Besides, even if it were true that
this case wasn't a problem in need of being corrected, surely
sometimes there ARE things we need to correct.
standard_conforming_strings comes to mind as a case when we endured a
lot more pain than I think this will ever cause because the
alternative was to be permanently incompatible with the SQL standard
and we didn't want to do that. And I don't know how we would have
gotten out from under that problem without a behavior-changing GUC,
and I didn't know how to get out from under this one without it,
either.

That's not to say that I feel great about it, though, because I don't.

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