Message-ID: <442d7887-645d-0e31-e971-7a8dc8952d2f@joeconway.com>
Date: Fri, 1 Jul 2022 08:22:53 -0400
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.9.1
Content-Language: en-US
To: Robert Haas <robertmhaas@gmail.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
 "Bossart, Nathan" <bossartn@amazon.com>, Stephen Frost <sfrost@snowman.net>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
References: <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>
 <1066202.1654190251@sss.pgh.pa.us>
 <CA+TgmoYmJtLA1aFF2dpGLuqWMuWwrf2SEoT-Ueqd4XCzLL61sA@mail.gmail.com>
 <20220629231939.GA362451@nathanxps13>
 <CA+Tgmobuqgr3XKujMwgsEZUxBHJA8jZSH2tVp7PC1cifePANtw@mail.gmail.com>
 <20220630232931.GA367181@nathanxps13>
 <CA+TgmoZWWqB66t4K6Mqv6TDttO1YJRJhOJO77B5H9Z8f28yc8Q@mail.gmail.com>
 <20220701025830.GA369935@nathanxps13>
 <12e2f0cb-08ff-c65f-d31e-76cea1af70ec@joeconway.com>
 <CA+TgmoZRO3tO_w+ROHW6Xe1fmQfzVyPuZ5OtbYX40w3YBQkMwA@mail.gmail.com>
From: Joe Conway <mail@joeconway.com>
Subject: Re: replacing role-level NOINHERIT with a grant-level option
In-Reply-To: 
 <CA+TgmoZRO3tO_w+ROHW6Xe1fmQfzVyPuZ5OtbYX40w3YBQkMwA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/442d7887-645d-0e31-e971-7a8dc8952d2f%40joeconway.com>
Precedence: bulk

On 7/1/22 07:48, Robert Haas wrote:
> On Fri, Jul 1, 2022 at 6:17 AM Joe Conway <mail@joeconway.com> wrote:
>> Would this allow for an explicit REVOKE to override a default INHERIT
>> along a specific path?
> 
> Can you give an example?
> 
> If you mean that A is granted to B which is granted to C which is
> granted to D and you now want NOINHERIT behavior for the B->C link in
> the chain, this would allow that. You could modify the existing grant
> by saying either "REVOKE INHERIT OPTION FOR B FROM C" or "GRANT B TO C
> WITH INHERIT FALSE".

Hmm, maybe I am misunderstanding something, but what I mean is something 
like:

8<----------------
CREATE TABLE t1(f1 int);
CREATE TABLE t2(f1 int);

CREATE USER A; --defaults to INHERIT
CREATE USER B;
CREATE USER C;

GRANT select ON TABLE t1 TO B;
GRANT select ON TABLE t2 TO C;

GRANT B TO A;
GRANT C TO A;

SET SESSION AUTHORIZATION A;

-- works
SELECT * FROM t1;
-- works
SELECT * FROM t2;

RESET SESSION AUTHORIZATION;
REVOKE INHERIT OPTION FOR C FROM A;
SET SESSION AUTHORIZATION A;

-- works
SELECT * FROM t1;
-- fails
SELECT * FROM t2;
8<----------------

So now A has implicit inherited privs for t1 but not for t2.

-- 
Joe Conway
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com