Re: fixing CREATEROLE - Mailing list pgsql-hackers

On Wed, Nov 23, 2022 at 4:18 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> To be clear, I'm not saying that I know a better answer.  But the fact
> that these end up so different from other role-property bits seems to
> me to suggest that making them role-property bits is the wrong thing.
> They aren't privileges in any usual sense of the word --- if they
> were, allowing Alice to flip her own bits would obviously be silly.
> But all the existing role-property bits, with the exception of
> rolinherit, certainly are in the nature of privileges.

I think that's somewhat true, but I don't completely agree. I don't
think that INHERIT, LOGIN, CONNECTION LIMIT, PASSWORD, or VALID UNTIL
are privileges either. I think they're just properties. I would put
these in the same category: properties, not privileges. I think that
SUPERUSER, CREATEDB, CREATEROLE, REPLICATION, and BYPASSRLS are
privileges.

> CREATEDB and CREATEROLE don't particularly bother me.  We've talked before
> about replacing them with memberships in predefined roles, and that would
> be fine.  But the reason nobody's got around to that (IMNSHO) is that it
> won't really add much.

I agree, although I'm not sure that means that we don't need to do
anything about them as we evolve the system.

> The thing that I think is a big wart is
> rolinherit.  I don't know quite what to do about it.

One option is to nuke it from orbit. Now that you can set that
property on a per-grant basis, the per-role basis serves only to set
the default. I think that's of dubious value, and arguably backwards,
because ISTM that in a lot of cases whether you want a role grant to
be inherited will depend on the nature of the role being granted
rather than the role to which it is being granted. The setting we have
works the other way around, and I can never keep in my head what the
use case for that is. I think there must be one, though, because Peter
Eisentraut seemed to like having it around. I don't understand why,
but I respect Peter. :-)

> But these two new
> proposed bits seem to be much the same kind of wart, so I'd rather not
> invent them, at least not in the form of role properties.

I have to admit that when I realized that was the natural place to put
them to make the patch work, my first reaction internally was "well,
that can't possibly be right, role properties suck!". But I didn't and
still don't see where else to put them that makes any sense at all, so
I eventually decided that my initial reaction was misguided. So I
can't really blame you for not liking it either, and would be happy if
we could come up with something else that feels better. I just don't
know what it is: at least as of this moment in time, I believe these
naturally ARE properties of the role, and therefore I'm inclined to
view my initial reluctance to implement it that way as a reflex rather
than a well-considered opinion. That is, the CREATE ROLE syntax is
clunky, and it controls some things that are properties and others
that are permissions, but they're not inherited like regular
permissions, so it stinks, and thus adding things to it also feels
stinky. But if the existing command weren't such a mess I'm not sure
adding this stuff to it would feel bad either.

That might be the wrong view. As I say, I'm open to other ideas, and
it's possible there's some nicer way to do it that I just don't see
right now.

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