Re: Row-security writer-side checks proposal - Mailing list pgsql-hackers

On 11/05/2013 09:30 PM, Robert Haas wrote:
>> So really, there are four cases:
>>
>> READ
>> WRITE INSERT
>> WRITE UPDATE
>> WRITE DELETE
> 
> Isn't READ similarly divisible into READ SELECT, READ UPDATE, and READ DELETE?

Not in my opinion. No matter what the command, the read side is all
about having some way to obtain the contents of the tuple.

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

By contrast on the write side it seems routine to need different rules
for different operations. The traditional heriachical mandatory access
model as implemented by Teradata Row Level Security,  Oracle Label Based
Security, etc, can have different rules for each operation. Here's a
synopsis of the example described in the Teradata docs as a typical policy:

- SELECT: Current session security label must be >= the row label

- INSERT: Current session security label must be = the new row label

- UPDATE: Session label must be >= row label to update the row.         New row must be = session security label.

- DELETE: Only permitted for rows with the lowest label set, ensuring         row is reviewed and declassified before
deletion.

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.

I'm looking into use cases and existing examples to put this in a more
concerete context, as much of the RLS discussion has been a hypothetical
one that doesn't look at concrete user problems much.

>> Similarly, saying you can update but not delete seems quite reasonable
>> to me.
> 
> If you simply want to allow UPDATE but not DELETE, you can refrain
> from granting the table-level privilege.  The situation in which you
> need things separate is when you want to allow both UPDATE and DELETE
> but with different RLS quals for each.

That's what I was getting at, yes. Like the example above; the set of
rows you can update might be different to the set of rows you can delete.

>> On the other hand, we might choose to say "if you want to do things with
>> that granularity use your own triggers to enforce it" and provide only
>> READ and WRITE for RLS.
> 
> The funny thing about this whole feature is that it's just syntax
> support for doing things that you can already do in other ways.  If
> you want read-side security, create a security_barrier view and select
> from that instead of hitting the table directly.  If you want
> write-side security, enforce it using triggers.

Right now you can't have both together, though; an UPDATE on the raw
table can observe rows that wouldn't be visible via the view and can
send them to the client via RAISE NOTICE or whatever.

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.

> Now maybe that's fine.  But given that, I think it's pretty important
> that we get the syntax right.  Because if you're adding a feature
> primarily to add a more convenient syntax, then the syntax had better
> actually be convenient.

I completely agree with that.  I don't have a strong opinion on the
current syntax.

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

Oracle VPD has you create a PL/SQL procedure to generate the SQL text of
the desired RLS predicate. It then wants you to create a POLICY (via a
PL/SQL call to a built-in package) that associates the predicate
generation function with a table and sets some options controlling what
statement types it applies to, when the predicate is re-generated, etc.
It also has policy groups, which bundle policies together and control
whether or not they're applied for a given session. The predicates of
different policies are ANDed together.

So to create a single RLS policy on a single table you have to write a
PL/SQL stored procedure that generates an SQL predicate and then create
a RLS policy to apply that procedure to the table.

ALTER TABLE ... SET ROW SECURITY is the equivalent of adding the policy;
Pg RLS doesn't have an equivalent of the predicate generating function,
instead only supporting static predicates. (There might be patent issues
around using a predicate-generator function; I haven't looked, but think
it was mentioned in earlier discussion).



Teradata instead eschews general-purpose row level security. So it's not
really within the scope of the current RLS feature, comparing instead to
whatever we build on top of it (using SEPostgreSQL or otherwise). It
implements label based security directly, with two kinds of labels. You
can create a CONSTRAINT with either a single hirachical compartment
(think "secret", "top secret", etc) or a CONSTRAINT with set of discrete
and isolated security compartment labels. The CONSTRAINT has functions
written in C associated with it to do enforcement, one for each
statement type. The C functions must not execute SQL. A CONSTRAINT can
be assigned to tables and to users in an m:n manner. A user can have
multiple constraints, as can a table. They're ANDed. A user with
non-heirachical "country" constraint set to "uk" can only see rows with
country label "uk"; a user with heirachical "classification" constraint
set to "unclassified (default), classified" can see rows labeled
"unclassified" and, if they elevate their session, "classified", but
cannot see higher rows.




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.


-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services