Re: row security roadmap proposal

On 12/18/2013 02:21 AM, Simon Riggs wrote:
> On 16 December 2013 14:36, Craig Ringer <craig@2ndquadrant.com> wrote:
> 
>> - Decide on and implement a structure for row-security functionality its
>> self. I'm persuaded by Robert's comments here, that whatever we expose
>> must be significantly more usable than a DIY on top of views (with the
>> fix for updatable security barrier views to make that possible). I
>> outlined the skeleton of a possible design in my earlier post, with the
>> heirachical and non-heirachical access labels. Should be implemented
>> using the same API we expose for extensions (like SEPostgresql RLS).
> 
> That part isn't clear why we "must" do better than that.
> 
> Having declarative security is a huge step forward, in just the same
> way that updateable views were. They save the need for writing scripts
> to implement things, rather than just having a useful default.

In my view the proposed patch doesn't offer a significant improvement in
declarative security, beyond what we can get by just adding update
support to s.b. views and using search_path to control whether a user
sees the view or the base table.

It's a lot like Oracle Virtual Private Database (VPD): A set of low
level building blocks you can build your own flexible row security model
with. One where you have to very carefully write security-sensitive
predicates and define all your security model tables, etc yourself.

That's why I'm now of the opinion that we should make it possible to
achieve the same thing with s.b. views and the search_path (by adding
update support)... then build a declarative row-security system that
doesn't require the user fiddling with delicate queries and sensitive
scripts on top of that.

> Personally, I see no reason not to commit the syntax we have now. So
> people can see what we'll be supporting, whenever that is.

Do you mean commit the syntax without the supporting functionality, so
it doesn't operate / just ERROR's initially? Or are you suggesting that
the project commit the current patch with the known issues with user ID
changes and the concerns about its internal structure and operation,
then clean it up down the track?

> If there is a vision for that, lets see the vision. And then decide
> whether its worth waiting for.

OK, I'll put an example together as a follow-up to this post - the "how
it should look" part. Below I explain the *why* part.

What the current patch does is add something a bit like a simplified and
cut-down version of Oracle VPD.

Oracle's VPD predicates are generated by a PL/SQL procedure that emits
the SQL for a predicate. It has varying levels of caching to mitigate
performance effects. The proposed patch for Pg doesn't do that. We'll
have the predicate fixed and stored in the catalogs. It'll be like VPD
with policy level SHARED_STATIC locked in as the only option.

See
http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm#DBSEG98250 for
details on Oracle VPD.

It's essentially a few tools you can build your own row security model
on, making the job a little easier. The DB designer / DBA still does a
lot of work to define security on each table and do a bunch of procedure
writing and setup to make it work. They have to get potentially security
sensitive queries just right for each table.

IMO it really isn't much different to just having an updatable security
barrier view. Differences arise when you look at VPD-exempt user acess
rights, at policy groups, etc, but we don't have any of that anyway.

The only big differences are that you can apply a VPD policy to an
existing table w/o having to refresh all views/functions that point to
it, and that some users can be made exempt.

(Note: I have not used VPD or Label Security myself; my comments are
intentionally based only on public documentation and on discussions with
others).



Oracle then built Oracle Label Security (their row-security solution)
*on* *top* of VPD. It's much more of a ready-to-go, configure it and use
it product, where you don't have to write procedures and define security
tables and generally build most of it yourself.

See: http://docs.oracle.com/cd/B14117_01/network.101/b10774.pdf

The Teradata implementation Robert pointed out skips the VPD-like layer
entirely. It just implements a fairly simple and flexible label security
model directly.  You don't have to muck about with the details, you
don't have to deal with the guts of the row security implementation, you
just say "here's what I want".

I'd prefer to avoid adding syntax and interfaces for a feature that'll
likely become mostly implementation detail for the feature users
actually want - declarative security.

That's part of why I've been focused on upd. s. b. views. It'll provide
a way for users to do flexible DIY row-security without introducing a
big chunk of new syntax and functionality we'll be stuck with
supporting. It'll also be a useful building block for declarative row
security.






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