Re: fixing CREATEROLE

On Wed, Nov 23, 2022 at 2:18 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Wed, Nov 23, 2022 at 3:59 PM David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> > I haven't yet formed a complete thought here but is there any reason we
> cannot convert the permission-like attributes to predefined 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.
>

> Either way, I'm not quite sure what the benefit of converting these
> things to predefined roles is.

Specifically, you gain inheritance/set and "admin option" for free. So
whether I have an ability and whether I can grant it are separate concerns.

> 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.
>

You aren't inheriting a specific password, you are inheriting the right to
have a password stored in the database, with an optional expiration date.

>
> For instance, I'm pretty well unconvinced that merging users and
> groups into a uniformed thing called roles was a good idea.

I agree. No one was interested in the, admittedly complex, psql queries I
wrote the other month but I decided to undo some of that decision there.

David J.