Horton

Re: table partitioning and access privileges

Fujii Masao <masao.fujii@gmail.com>

View thread View on postgr.es
From: Fujii Masao <masao.fujii@gmail.com>
To: Amit Langote <amitlangote09@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2020-01-10T01:29:35Z
Lists: pgsql-hackers 
On Tue, Jan 7, 2020 at 5:15 PM Amit Langote <amitlangote09@gmail.com> wrote:
>
> On Fri, Dec 27, 2019 at 4:26 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Fujii Masao <masao.fujii@gmail.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

Commits

  1. Make inherited LOCK TABLE perform access permission checks on parent table only.

  2. Add note about access permission checks by inherited TRUNCATE and LOCK TABLE.

  3. Revert commit 56bc82a511.

  4. Revert commit 606f350de9.

  5. Revert commit 928e755d22.

  6. Revert commit 4b96c03a0a.

  7. Revert commit a5b652f3a0.

  8. Revert commit de0177788b.

  9. Make inherited TRUNCATE perform access permission checks on parent table only.

  10. Regression tests for LOCK TABLE.