Re: Row-security writer-side checks proposal

On Wed, Nov 6, 2013 at 2:27 AM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> Separate "READ DELETE" etc would only be interesting if we wanted to let
> someone DELETE rows they cannot SELECT. Since we have DELETE ...
> RETURNING, and since users can write a predicate function for DELETE
> that leaks the information even if we didn't, in practice if you give
> the user any READ right you've given them all of them. So I don't think
> we can support that (except maybe by column RLS down the track).

Well, we could require SELECT privilege when a a RETURNING clause is present...

> Except for the DELETE, this is actually just two policies, one for reads
> (session label => row label) and one for writes (session label = new row
> label). So this might be an acceptable constraint if necessary, but it'd
> be really good to support per-command rules, and we certainly need
> asymmetric read- and write- rules.

OK.

> Support for automatically updatable security barrier views would take
> care of this issue, at which point I'd agree: RLS becomes mostly
> cosmetic syntactical sugar over existing capabilities. FKs would ignore
> RLS, much like the would if you use explicit SECURITY BARRIER views and
> have FKs between the base tables.
>
> One big difference still remains though: when you add an RLS policy on a
> table, all procedures and views referring to that table automatically
> use the transparent security barrier view over the table instead. That's
> *not* the case when you use views manually; you have to re-create views
> that point to the table so they instead point to a security barrier view
> over the table. Again it's nothing you can't do with updatable security
> barrier views, but it's automatic and transparent with RLS.

That's true, but it's that automatic transparent part that also
introduces a lot of pain, because what do you do when you need to
really get at the real data (e.g. to back it up)? The ad-hoc rule
"superusers are exempt" solves the problem at one level, but it
doesn't do a lot for e.g. database owners.

> I've looked at how some other vendors do it, and I can't say their
> approaches are pretty.

Did you look at Trusted RUBIX?

> Both of these have a concept that Pg RLS doesn't seem to have: multiple
> RLS policies. I think that's actually quite important to consider,
> because we'll need that anyway to support RLS on a subset of columns.
> Both also have the concept of turning particular RLS policies on and off
> on a per-user basis or per-session using privileged on-login triggers,
> so that application A and application B can apply different RLS rules on
> the same data.
>
> I don't think it's important to cover these from the start, but it'd be
> a good idea not to foreclose these possibilities in whatever gets into Pg.

I agree, and I'm not sure we're there yet. Frankly, switching from a
single security policy per table to multiple policies per table
doesn't sound like a good candidate for a follow-on commit; it's
likely to have fundamental ramifications for the syntax, and I'm not
eager to see us implement one syntax now only to overhaul it in the
next release.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company