Re: allowing for control over SET ROLE

On Tue, 2022-09-06 at 10:42 -0400, Robert Haas wrote:
> 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.

Interesting. All of those seem like worthwhile use cases to me.

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

By denying the ability to "SET ROLE pg_read_all_settings", I assumed
that we'd deny the ability to create objects owned by that
pg_read_all_settings. But on closer inspection:

grant all privileges on schema public to public;
create user u1;
grant pg_read_all_settings to u1 with set false;
\c - u1
create table foo(i int);
set role pg_read_all_settings;
ERROR: permission denied to set role "pg_read_all_settings"
alter table foo owner to pg_read_all_settings;
\d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------------------
public | foo | table | pg_read_all_settings
(1 row)

Users will reasonably interpret any feature of GRANT to be a security
feature that allows or prevents certain users from causing certain
outcomes. But here, I was initially fooled, and the outcome is still
possible.

So I believe we do need to think in terms of what capabilities we are
really restricting with this feature rather than solely the mechanics.

Regards,
Jeff Davis