Message-ID: <295e86c7aeafb8e2623f8eccdc846855bf2c7e0c.camel@j-davis.com>
Subject: Re: allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX
From: Jeff Davis <pgsql@j-davis.com>
To: Pavel Luzanov <p.luzanov@postgrespro.ru>, Justin Pryzby
	 <pryzby@telsasoft.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>, pgsql-hackers@postgresql.org
Date: Thu, 15 Dec 2022 10:10:43 -0800
In-Reply-To: <b41c0577-8e16-c0fc-0d98-f17155047354@postgrespro.ru>
References: <20221208183707.GA55474@nathanxps13>
	 <210dc2cc663934f8b463e4effa9af86225e46ce4.camel@j-davis.com>
	 <20221210204109.GA197392@nathanxps13> <20221212200427.GA448418@nathanxps13>
	 <20221212210136.GA449764@nathanxps13>
	 <b19eaf6601d142f96ff4c3c3591f430cd81befbd.camel@j-davis.com>
	 <20221214032332.GA671806@nathanxps13>
	 <b572d238-0de2-9cad-5f34-4741dc627834@postgrespro.ru>
	 <f746293762ae262e321f89153d653173ba759636.camel@j-davis.com>
	 <8f7172da-2b58-3bd0-97ae-5126e2a7970c@postgrespro.ru>
	 <20221214221140.GA1153@telsasoft.com>
	 <cf56dc1a36e5c15e19c4be634038029096d96dab.camel@j-davis.com>
	 <b41c0577-8e16-c0fc-0d98-f17155047354@postgrespro.ru>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.44.4-0ubuntu1 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/295e86c7aeafb8e2623f8eccdc846855bf2c7e0c.camel%40j-davis.com>
Precedence: bulk

On Thu, 2022-12-15 at 12:31 +0300, Pavel Luzanov wrote:
> I think the approach that Nathan implemented [1] for TOAST tables
> in the latest version can be used for partitioned tables as well.
> Skipping the privilege check for partitions while working with
> a partitioned table. In that case we would get exactly the same
> behavior
> as for INSERT, SELECT, etc privileges - the MAINTAIN privilege would=20
> work for
> the whole partitioned table, but not for individual partitions.

There is some weirdness in 15, too:

   create user foo;
   create user su superuser;
   grant all privileges on schema public to foo;

   \c - foo
   create table p(i int) partition by range (i);
   create index p_idx on p (i);
   create table p0 partition of p for values from (0) to (10);

   \c - su
   create table p1 partition of p for values from (10) to (20);

   \c - foo

   -- possibly weird because the 15 inserts into p1 (owned by su)
   insert into p values (5), (15);

   -- all these are as expected:
   select * from p; -- returns 5 & 15
   insert into p1 values(16); -- permission denied
   select * from p1; -- permission denied
  =20
   -- the following commands seem inconsistent to me:
   vacuum p; -- skips p1 with warning
   analyze p; -- skips p1 with warning
   cluster p using p_idx; -- silently skips p1
   reindex table p; -- reindexes p0 and p1 (owned by su)

   -- RLS is also bypassed
   \c - su
   grant select, insert on p1 to foo;
   alter table p1 enable row level security;
   create policy odd on p1 using (i % 2 =3D 1) with check (i % 2 =3D 1);
   \c - foo
   insert into p1 values (16); -- RLS error
   insert into p values (16); -- succeeds
   select * from p1; -- returns only 15
   select * from p; -- returns 5, 15, 16
  =20
The proposal to skip privilege checks for partitions would be
consistent with INSERT, SELECT, REINDEX that flow through to the
underlying partitions regardless of permissions/ownership (and even
RLS). It would be very minor behavior change on 15 for this weird case
of superuser-owned partitions, but I doubt anyone would be relying on
that.

+1.

I do have some lingering doubts about whether we should even allow
inconsistent ownership/permissions. But I don't think we need to settle
that question now.


--=20
Jeff Davis
PostgreSQL Contributor Team - AWS