From: PG Doc comments form <noreply@postgresql.org>
To: pgsql-docs@lists.postgresql.org
Cc: steven.winfield@cantabcapital.com
Subject: SET ROLE documentation not entirely correct
Date: Tue, 23 Apr 2019 15:52:47 +0000
Message-ID: <155603476764.1372.10888362840885095061@wrigleys.postgresql.org> (raw)
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: noreply@postgresql.org, pgsql-docs@lists.postgresql.org, steven.winfield@cantabcapital.com
Subject: Re: SET ROLE documentation not entirely correct
In-Reply-To: <155603476764.1372.10888362840885095061@wrigleys.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox