Re: allowing for control over SET ROLE

On Tue, Sep 6, 2022 at 2:45 PM Jeff Davis <pgsql@j-davis.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