Re: Prohibit row-security + inheritance in 9.4?

On Thursday, January 30, 2014, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
>
> > This strikes me as a bit odd- isn't this against how we handle the
> > GRANT system when it comes to inheiritance? That is to say- if
> > you have access to query the parent, then you'll get rows back from
> > the child and I believe everyone feels that makes perfect sense.
>
> Thanks for taking the time to look at this.
>
> I agree that it's odd. The trouble is that there's a conflict between
> two "makes perfect sense"s here.
>
> I expect to get all rows, including inherited rows, filtered by a
> parent's predicate back when you query the parent. Fair enough.
>
> I also expect that when I query a child table, I'll see the same rows
> I see when I query it via the parent table. Especially in the common
> case of an empty parent table.

I don't see where this follows at all- clearly, you already get a subset of
rows from the child than if you queried the parent because there are other
children. If you are first playing with inheritance in PG then it might
seem odd for that to be the case. Ditto for what you describe where the
child returns more rows than the parent, but these things need to simply be
documented as "this is how it works" for those cases where both are
reasonable possibilities and we need to pick one.

Personally, I don't see the suggestion that we filter rows accessed via the
child based on quals of the parent as making any sense. I feel the same
about applying child quals when querying through the parent as we don't
apply GRANT permissions that way. Using the parent and using the child are
two different paths by which to access the data and which you are using is
what drives what you will see.

> One of these has to give, we can't have both.

I agree that we can't do both.

> I'm speaking with an outside party who has an inheritance-based data
> model they want to apply row-security to. Hopefully that'll shed some
> light on practical implications.

Is there a case which can't be implemented if the two are independent as I
am describing? There are cases which can NOT be implemented if we force
the two paths to be handled identically but I feel the approach where we
keep them independently managed is flexible to allow the other cases if
people want them.

> There's another bit of fun too: If you have a policy on a child, and
> query the child via the parent, should the child policy be applied?

No! We do not do that for GRANT and I do not see doing it for row security
either.

> The immediate thought is "obviously" - but then, often the child and
> parent policies are going to be the same, in which case it's a
> duplicate filter step. Here security trumps efficiency; I think we
> just apply both, and leave proving they're identical and skipping the
> child's as an optimization problem for later.

No, if you apply both then you reduce the ability of the user to set it up
to meet their needs. Allowing these paths to be managed independent allows
more flexibility. If it adds a bit of bookkeeping for users who wish to
allow access to both the child and the parent directly then tools can be
written to manage that.

> > ... Just how our existing GRANT system works.
>
> True; it's possible to be able to query the parent, but not its
> children, at present.

It was actually changed not all that long ago to be this way because having
a query against the parent *sometimes* fail when hitting a certain child
table was not sensible.

> Treating row-security checks as permission checks, that'd make this
> consistent. The difference is that you get a nice error telling you
> what's going on currently, not a potentially WTF-y different resultset.

I understand where you're coming from but this strikes me as a
documentation/definition issue and not really a cause for concern or
against POLA. These are complex and important topics that anyone who cares
about security needs to understand.

> > If you want to constrain the children in the same way as the
> > parent, then the user can add to the row-security on the children
> > to match the parent.
>
> Yes, with the caveat mentioned above that this will cause multiple
> nested row-security policies when querying the parent; each child's
> policy will get applied, *and* the parent's policy will get applied.
> Inefficient and ugly if they're the same.

No. You misunderstand. When it comes to querying the parent only the
parents would apply. Yes, this may mean the parent has to have more than it
would otherwise but there would not need to be any redundant evaluation-
perhaps redundant definition, but that's not the same.

> > If the user wants to have one view for the entire inheiritance
> > tree then they need to only implement the row-security on the
> > parent and not grant any access for users on the children (or, if
> > they're paranoid, add row-security to the children which are
> > essentially deny-all).
>
> That works if the children are used for partitioning/inheritance,
> where no direct access (or at least no read access) is required. It
> doesn't work so well when they're actually being used in a "real"
> inheritance model, where they'll have their own additional attributes
> that can only be accessed via the child.
>
> In that case the only option is to apply a policy to each child too.
> If we apply the parent policy when querying via the parent, we get
> multiple nested layers of policy, but it still works.

The policies applied to the children might be different or only a subset of
that on the parent. This model would allow for that while one which
combines the two on behalf of, and without giving any control over the
combination to, the user would not.

> > If we force everything to behave the same between querying the
> > parent and querying the children then we cut off various scenarios
> > of partitioning where users are allowed to query specific children
> > but not the parent or query the parent to get things they're not
> > able to see directly from the children.
>
> That's a fair concern. Note that the second one won't work if we apply
> child policies when querying via the parent though; there'd be no way
> to see rows via the parent that you can't see via the child.

I am not advocating the combination of the two in any case.

> >> 4. attempt to pull quals from parents when querying a child rel
> >> directly.
> >
> > That strikes me as borderline insane. ;)
>
> I'm glad to hear it, because it'd be inefficient and horrifying to
> implement.
>

Agreed. :)

Thanks,

Stephen