Date: Thu, 2 Jun 2022 13:06:17 -0700
From: Nathan Bossart <nathandbossart@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Joe Conway <mail@joeconway.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	"Bossart, Nathan" <bossartn@amazon.com>,
	Stephen Frost <sfrost@snowman.net>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: replacing role-level NOINHERIT with a grant-level option
Message-ID: <20220602200617.GA2404070@nathanxps13>
References: <0ED10807-9E7F-4BB5-9473-4668EE68AE2D@amazon.com>
 <4117557.1641329799@sss.pgh.pa.us>
 <CAGB+Vh4jWh-KuV6qt0FKC3M=HCZT1X=Kn5E1g68s1+0OSfoCdQ@mail.gmail.com>
 <c81ba78d-ae4f-02bc-9518-61cbe00c1f2e@joeconway.com>
 <730520.1644168290@sss.pgh.pa.us>
 <CA+TgmoYeNB1PxMO-TYv4_Ky7XR3-3Le+fpL87T7QMR=OWc7U1Q@mail.gmail.com>
 <80af9ba7-ec24-9134-8fd9-00bf13f5c494@joeconway.com>
 <CA+Tgmoax3J2D1RoqSqBRuLVifMjinZ5SPvR+rBqBT89u57zOJQ@mail.gmail.com>
 <20220602180755.GA2402942@nathanxps13>
 <CA+TgmobLsD4JOEBZNMb9hYyr3MN6b8tb4LTY=PnmZ+7S5dj6mQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CA+TgmobLsD4JOEBZNMb9hYyr3MN6b8tb4LTY=PnmZ+7S5dj6mQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/20220602200617.GA2404070%40nathanxps13>
Precedence: bulk

On Thu, Jun 02, 2022 at 03:37:34PM -0400, Robert Haas wrote:
> On Thu, Jun 2, 2022 at 2:07 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
>> I think we should also consider replacing role attributes with predefined
>> roles.  I'm not sure that this proposal totally prepares us for such a
>> change, given role attributes apply only to the specific role for which
>> they are set and aren't inherited.  ISTM in order to support that, we'd
>> need even more enhanced functionality.  For example, if I want 'robert' to
>> be a superuser, and I want 'joe' to inherit the privileges of 'robert' but
>> not 'pg_superuser', you'd need some way to specify inheriting only certain
>> privileges possessed by an intermediate role.
> 
> I guess we could think about adding something like an ONLY clause,
> like GRANT ONLY robert TO joe. I feel a little bit uncomfortable about
> that, though, because it assumes that robert is a superuser but his
> own privileges are distinguishable from those of the superuser. Are
> they really? If I can assume robert's identity, I can presumably
> Trojan my way into the superuser account pretty easily. I'll just
> define a little trigger on one of his tables. I don't really see a way
> where we can ever make it safe to grant a non-superuser membership in
> a superuser role.

I was primarily looking at this from the angle of preserving current
behavior when upgrading from a version with role attributes to a version
without them.  If it's alright that a role with privileges of a superuser
role begins being treated like a superuser after an upgrade, then we
probably don't need something like GRANT ONLY.  I bet that's how a lot of
people expect role attributes to work, anyway.  I'm sure I did at some
point.

> But even if there is a way, I think that is a separate patch from what
> I'm proposing here. [NO]INHERIT only has to do with what privileges
> you can exercise without SET ROLE. To solve the problem you're talking
> about here, you'd need a way to control what privileges are conferred
> in any manner, which is related, but different.

I agree that the role-attribute-to-predefined-role stuff needs its own
thread.  I just think it's worth designing this stuff with that in mind.

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