The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/11/sql-set-role.html
Description:
In the course of trying to sanitise our roles and permissions I found the
notes in the SET ROLE docs a little misleading:
"If the session user role has the INHERITS 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."
This doesn't seem to be true. Consider the following:
CREATE ROLE userrole INHERIT LOGIN;
CREATE ROLE usergroup NOINHERIT NOLOGIN;
GRANT usergroup TO userrole;
CREATE ROLE sensitive;
GRANT sensitive TO usergroup;
SET SESSION AUTHORIZATION userrole;
SELECT session_user, current_user, rolinherit, pg_has_role('sensitive',
'USAGE') as usage, pg_has_role('sensitive', 'MEMBER') as member
FROM pg_roles
WHERE rolname = session_user;
+--------------+--------------+------------+-------+--------+
| session_user | current_user | rolinherit | usage | member |
+--------------+--------------+------------+-------+--------+
| userrole | userrole | True | False | True |
+--------------+--------------+------------+-------+--------+
Here the session role 'userrole' has the INHERITS attribute, and can SET
ROLE to 'sensitive', but contrary to the docs it does not automatically have
the privileges associated with 'sensitive'. The intermediate 'usergroup'
NOINHERIT role blocks automatic inheritance.
That can be demonstrated like this:
SET ROLE sensitive;
SELECT session_user, current_user, rolinherit, pg_has_role('sensitive',
'USAGE') as usage, pg_has_role('sensitive', 'MEMBER') as member
FROM pg_roles
WHERE rolname = session_user;
+--------------+--------------+------------+-------+--------+
| session_user | current_user | rolinherit | usage | member |
+--------------+--------------+------------+-------+--------+
| userrole | sensitive | True | True | True |
+--------------+--------------+------------+-------+--------+
(This is all on v11.2, in case it matters)
