SET ROLE documentation not entirely correct

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: steven(dot)winfield(at)cantabcapital(dot)com
Subject: SET ROLE documentation not entirely correct
Date: 2019-04-23 15:52:47
Message-ID: 155603476764.1372.10888362840885095061@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

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)

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Tom Lane 2019-04-23 16:53:17 Re: Update section on NFS
Previous Message Peter Eisentraut 2019-04-23 15:11:26 Re: Update section on NFS