Re: Prohibit row-security + inheritance in 9.4?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/30/2014 10:41 PM, Stephen Frost wrote:
>> Earlier discussions seemed to settle on each relation having its
>> own row-security quals applied independently. So quals on a
>> parent rel did not cascade down to child rels.
>
> 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.

>> It's what you'd expect to happen when querying a parent rel with
>> row-security, too. Parent quals are applied to children. But
>> that then gives you an inconsistent view of a rel's contents
>> based on whether you query it via a parent or directly.
>
> ... 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.

- --
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.15 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJS6u+5AAoJELBXNkqjr+S2QkEH/iXg4qww3KfcKLW/dDHWUL/g
33T/168ZGgeAvgFgTQdrgZmM8bUDsNnCD/GdH4PBmNWMlxwTeHmdANBEsKgDCL7r
Fu4HuF0JFEQMqHPtZSKUIXxW1KYEnWjISd+4YDQqor3aH03OV3z4vFEgAi73truR
kcqOe/xyeuPQDPe/9UTtiyIT2g/sQaeXhNqQx+queKYwjgYTIZgkUs0y46lH4pAK
nvWcWCscPIJ4bFpMr3joJQiFwegRaVIcAac89uZHL5iuMKPzp5lfEfWHUmTreZLu
1gPjRxWTcOhNZoaVpVCBA+Gsqw0255IxWKKD8I2RYPp0bK88t42cCB3aHejAjzo=
=cB2U
-----END PGP SIGNATURE-----