Re: v16 roles, SET FALSE, INHERIT FALSE, ADMIN FALSE

On Mon, Jul 8, 2024 at 3:58 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I'd argue that INHERIT TRUE should be required. The point of SET TRUE
> with INHERIT FALSE is that you must *explicitly* do SET ROLE or
> equivalent in order to have access to the privileges of the referenced
> role.

I think that blast radius is too large for v16. Arguable for v17. All
pre-v-16 roles encountered during upgrade will have SET true and those
roles are able to login today but a non-trivial number I would expect to be
unable to if we require INHERIT.

It would be nicer to do away with the default connect grant to public,
expect the group role to have the connect privilege on the desired
database, and let the user role inherit that in lieu of granting it out
explicitly, if we are into v18 feature requests. By the time the newbie
stops using postgres superuser for everything they can add a connect grant
here or there.

There is no opportunity to issue SET ROLE before logging in,
> but ISTM that means you don't get the privilege, not that you
> magically get it without asking. Otherwise, why did we build this
> additional level of grant specificity at all?
>

Mostly for the pre-defined roles, and just general explicitness. So
inherit could be per-role-membership instead per-role. HBA group
specification rules wasn't a motivating drive if it was considered at all.
Unbreaking CREATEROLE.

An analogous case is that having
> some privileges on table(s) in some schema does not give you the right
> to bypass needing USAGE on the schema in order to access those tables.
>

That sounds like the Connect privilege noted above. While +role could be
seen as a sImple text-expander for pg_hba.conf, not a privilege check in
its own right. We don't explicitly exclude users lacking the login
attribute from the 'all' specification. They are allowed to match and then
their login attribute and database-specific connect privilege is checked.

David J.