MIME-Version: 1.0
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>
 <442d7887-645d-0e31-e971-7a8dc8952d2f@joeconway.com>
In-Reply-To: <442d7887-645d-0e31-e971-7a8dc8952d2f@joeconway.com>
From: Robert Haas <robertmhaas@gmail.com>
Date: Fri, 1 Jul 2022 09:05:16 -0400
Message-ID: 
 <CA+Tgmoaw_k2jooV214S=2bOBDPPLo7Nnq27-UGp6MNTfk11x5g@mail.gmail.com>
Subject: Re: replacing role-level NOINHERIT with a grant-level option
To: Joe Conway <mail@joeconway.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>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BTgmoaw_k2jooV214S%3D2bOBDPPLo7Nnq27-UGp6MNTfk11x5g%40mail.gmail.com>
Precedence: bulk

On Fri, Jul 1, 2022 at 8:22 AM Joe Conway <mail@joeconway.com> wrote:
> 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.

Yeah, I anticipate that this would work in the way that you postulate here.

-- 
Robert Haas
EDB: http://www.enterprisedb.com