Re: privileges oddity

On 8/7/20 12:40 PM, Adrian Klaver wrote:
> On 8/7/20 12:27 PM, Scott Ribe wrote:
>>> On Aug 7, 2020, at 1:08 PM, Adrian Klaver <adrian.klaver@aklaver.com> 
>>> wrote:
>>>
>>> "Using this command, it is possible to either add privileges or 
>>> restrict one's privileges. If the session user role has the INHERIT 
>>> attribute, then it automatically has all the privileges of every role 
>>> that it could SET ROLE to; in this case SET ROLE effectively drops 
>>> all the privileges assigned directly to the session user and to the 
>>> other roles it is a member of, leaving only the privileges available 
>>> to the named role. On the other hand, if the session user role has 
>>> the NOINHERIT attribute, SET ROLE drops the privileges assigned 
>>> directly to the session user and instead acquires the privileges 
>>> available to the named role.
>>> "
>>
>> So it would only have removed privs if I had used set role in the 
>> session, which I am not.
>>
> 
> See Tom's answer. To confirm do:
> 
> SELECT
>      s.setdatabase,
>      s.setrole,
>      rolname,
>      s.setconfig,
>      rolname        ^^^^^^^ Surplus to requirements
> FROM
>      pg_db_role_setting AS s
>      JOIN pg_roles AS r ON r.oid = s.setrole;
> 

Also log in as 'akanzler' to psql and do:

select session_user;

select current_user;

-- 
Adrian Klaver
adrian.klaver@aklaver.com