Re: Partitioned tables vs GRANT

On Fri, Apr 7, 2017 at 8:41 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Keith Fiske <keith(at)omniti(dot)com> writes:
> > On Fri, Apr 7, 2017 at 2:46 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> Joe Conway <mail(at)joeconway(dot)com> writes:
> >>> Apparently INSERT and SELECT on the parent partitioned table skip
> normal
> >>> acl checks on the partitions. Is that intended behavior?
>
> >> Yes, this matches normal inheritance behavior.
>
> > Should that really be normal partitioning behavior though?
>
> Yes, it should. Consider the alternatives:
>
> 1. Owner must remember to run around and grant permissions on all child
> tables along with the parent.
>

I'm not following. That's what Joe is saying is happening now. The child
tables are not getting the parent privileges so this is what the owner must
remember to do every time they add a new child if they want to role to be
able to interact directly with the children. They can select, insert, etc
with the parent, but any direct interaction with the child is denied. I
know you're all trying to make the planner work so queries work efficiently
from the parent, but they'll never be as good as being able to hit the
child tables directly if they know where the data they want is. Why even
leave the child tables visible at all they can't be interacted with the
same as the parent? I thought that was supposed to be one of the advantages
to doing partitioning this way vs how Oracle & MySQL do it.

> 2. The system silently(?) doesn't show you some rows that are supposed
> to be visible when scanning the parent table.
>

> If you want RLS, use RLS; this is not that, and is not a good substitute.
>

Agreed. It appears the rows are visible if the role has select privileges
on the parent. But they cannot select directly from children. Not sure what
this has to do with RLS.

>
> (We've been around on this topic before, btw. See the archives.)
>
> regards, tom lane
>