Re: allowing for control over SET ROLE

On Tue, Sep 6, 2022 at 2:45 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> > 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)

Yeah. Please note this paragraph in my original post:

"In order to apply this patch, we'd need to reach a conclusion about
the matters mentioned in
http://postgr.es/m/CA+TgmobhEYYnW9vrHvoLvD8ODsPBJuU9CbK6tms6Owd70hFMTw@mail.gmail.com
-- and thinking about this patch might shed some light on what we'd
want to do over there."

I hadn't quite gotten around to updating that thread based on posting
this, but this scenario was indeed on my mind.

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