Re: allowing for control over SET ROLE

On Wed, Aug 31, 2022 at 08:56:31AM -0400, Robert Haas wrote:
> In some circumstances, it may be desirable to control this behavior.
> For example, if we GRANT pg_read_all_settings TO seer, we do want the
> seer to be able to read all the settings, else we would not have
> granted the role. But we might not want the seer to be able to do
> this:
>
> You are now connected to database "rhaas" as user "seer".
> rhaas=> set role pg_read_all_settings;
> SET
> rhaas=> create table artifact (a int);
> CREATE TABLE
> rhaas=> \d
> List of relations
> Schema | Name | Type | Owner
> --------+----------+-------+----------------------
> public | artifact | table | pg_read_all_settings
> (1 row)

+1

> The problem here is that if a nasty evil hacker takes over the
> oncallbot role, nothing whatsoever prevents them from executing "grant
> oncall to oncallbot with set true" after which they can then "SET ROLE
> oncall" using the privileges they just granted themselves. And even if
> under some theory we blocked that, they could still maliciously grant
> the sought-after on-call privileges to some other role i.e. "grant
> oncall to accomplice". It's fundamentally difficult to allow people to
> administer a set of privileges without giving them the ability to
> usurp those privileges, and I wouldn't like to pretend that this patch
> is in any way sufficient to accomplish such a thing. Nevertheless, I
> think there's some chance it might be useful to someone building such
> a system, in combination with other safeguards. Or maybe not: this
> isn't the main reason I'm interested in this, and it's just an added
> benefit if it turns out that someone can do something like this with
> it.

Yeah, if you have ADMIN for a role, you would effectively have SET. I'm
tempted to suggest that ADMIN roles should be restricted from granting SET
unless they have it themselves. However, that seems like it'd create a
weird discrepancy. If you have ADMIN but not INHERIT or SET, you'd still
be able to grant membership with or without INHERIT, but you wouldn't be
able to grant SET. In the end, I guess I agree with you that it's
"fundamentally difficult to allow people to administer a set of privileges
without giving them the ability to usurp those privileges..."

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com