Re: fixing CREATEROLE - Mailing list pgsql-hackers

On Wed, Nov 23, 2022 at 3:59 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> I haven't yet formed a complete thought here but is there any reason we cannot convert the permission-like attributes
topredefined roles?
 
>
> pg_login
> pg_replication
> pg_bypassrls
> pg_createdb
> pg_createrole
> pg_haspassword (password and valid until)
> pg_hasconnlimit
>
> Presently, attributes are never inherited, but having that be controlled via the INHERIT property of the grant seems
desirable.

I think that something like this might be possible, but I'm not
convinced that it's a good idea. I've always felt that the role-level
properties seemed kind of like warts, but in studying these issues
recently, I've come to the conclusion that in some ways that's just a
visual impression. The syntax LOOKS outdated and clunky, whereas
granting someone a predefined role feels clean and modern. But the
reality is that the predefined roles system is full of really
unpleasant warts. For example, in talking through the now-committed
patch to allow control over SET ROLE, we had some fairly extensive
discussion of the fact that there was previously no way to avoid
having a user who has been granted the pg_read_all_stats predefined
role to create objects owned by pg_read_all_stats, or to alter
existing objects. That's really pretty grotty. We now have a way to
prevent that, but perhaps we should have something even better. I'm
also not really sure that's the only problem here, but maybe it is.

Either way, I'm not quite sure what the benefit of converting these
things to predefined roles is. I think actually the strongest argument
would be to do this for the superuser property! Make the bootstrap
superuser the only real superuser, and anyone else who wants to be a
superuser has to inherit that from that role. It's really unclear to
me why inheriting a lot of permissions is allowable, but inheriting
all of them is not allowable. Doing it for something like
pg_hasconnlimit seems pretty unappealing by contrast, because that's
an integer-valued property, not a Boolean, and it's not at all clear
to me why that should be inherited or what the semantics ought to be.
Really, I'm not that tempted to try to rejigger this kind of stuff
around because it seems like a lot of work for not a whole lot of
benefit. I think there's a perfectly reasonable case for setting some
things on a per-role basis that are actually per-role and not
inherited. A password is a fine example of that. You should never
inherit someone else's password. Whether we've chosen the right set of
things to treat as per-role properties rather than predefined roles is
very much debatable, though, as are a number of other aspects of the
role system.

For instance, I'm pretty well unconvinced that merging users and
groups into a uniformed thing called roles was a good idea. I think it
makes all of this machinery a LOT harder to understand, which may be
part of the reason why this area doesn't seem to have had much TLC in
quite a long time. But I think it's too late to revisit that decision,
and I also think it's too late to revisit the question of having
predefined roles at all. For better or for worse, that's what we did,
and what remains now is to find a way to make the best of it in light
of those decisions.

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