Re: table partitioning and access privileges

On Tue, Jan 7, 2020 at 5:15 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
>
> On Fri, Dec 27, 2019 at 4:26 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Fujii Masao <masao(dot)fujii(at)gmail(dot)com> writes:
> > > My customer reported me that the queries through a partitioned table
> > > ignore each partition's SELECT, INSERT, UPDATE, and DELETE privileges,
> > > on the other hand, only TRUNCATE privilege specified for each partition
> > > is applied. I'm not sure if this behavior is expected or not. But anyway
> > > is it better to document that? For example,
> >
> > > Access privileges may be defined and removed separately for each partition.
> > > But note that queries through a partitioned table ignore each partition's
> > > SELECT, INSERT, UPDATE and DELETE privileges, and apply only TRUNCATE one.
> >
> > I believe it's intentional that we only check access privileges on
> > the table explicitly named in the query. So I'd say SELECT etc
> > are doing the right thing, and if TRUNCATE isn't in step with them
> > that's a bug to fix, not something to document.
>
> I tend to agree that TRUNCATE's permission model for inheritance
> should be consistent with that for the other commands. How about the
> attached patch toward that end?

Thanks for the patch!

The patch basically looks good to me.

+GRANT SELECT (f1, fz), UPDATE (fz) ON atestc TO regress_priv_user2;
+REVOKE TRUNCATE ON atestc FROM regress_priv_user2;

These seem not to be necessary for the test.

BTW, I found that LOCK TABLE on the parent table checks the permission
of its child tables. This also needs to be fixed (as a separate patch)?

Regards,

--
Fujii Masao