Re: allowing for control over SET ROLE

On Sat, Sep 3, 2022 at 3:46 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> > You are now connected to database "rhaas" as user "rhaas".
> > rhaas=# grant pg_read_all_settings to seer with set false;
> > GRANT ROLE
>
> You've defined this in terms of the mechanics -- allow SET ROLE or not
> -- but I assume you intend it as a security feature to allow/forbid
> some capabilities.
>
> Is this only about the capability to create objects owned by a role
> you're a member of? Or are there other implications?

I think there are some other implications, but I don't think they're
anything super-dramatic. For example, you could create a group that's
just for purposes of pg_hba.conf matching and make the grants both SET
FALSE and INHERIT FALSE, with the idea that the members shouldn't have
any access to the role; it's just there for grouping purposes. I
mentioned one other possible scenario, with oncallbot, in the original
post.

I'm not sure whether thinking about this in terms of security
capabilities is the most helpful way to view it. My view was, as you
say, more mechanical. I think sometimes you grant somebody a role and
you want them to be able to use SET ROLE to assume the privileges of
the target role, and sometimes you don't. I think that primarily
depends on the reason why you made the grant. In the case of a
predefined role, you're almost certainly granting membership so that
the privileges of the predefined role can be inherited. In other
cases, you may be doing it so that the member can SET ROLE to the
target role, or you may be doing it so that the member can administer
the role (because you give them ADMIN OPTION), or you may even be
doing it for pg_hba.conf matching.

And because of this, I think it follows that there may be some
capabilities conferred by role membership that you don't really want
to convey in particular cases, so I think it makes sense to have a way
to avoid conveying the ones that aren't necessary for the grant to
fulfill its purpose. I'm not exactly sure how far that gets you in
terms of building a system that is more secure than what you could
build otherwise, but it feels like a useful capability regardless.

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