Re: Prohibit row-security + inheritance in 9.4?

> 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.

One of these has to give, we can't have 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.

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?

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.

> ... Just how our existing GRANT system works.

True; it's possible to be able to query the parent, but not its
children, at present.

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.

> 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.

> 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.

> 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.

>> 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.