Re: Check old and new tuple in row-level policy?

On Dec 16, Stephen Frost modulated:

> There is still a need to refer back to some kind of state that is
> external to the table under consideration to determine what the session
> level access is, no? Even if the ACLs are in a table somewhere, how do
> you know who the current user is?
>

Yes, I was assuming session variables of some sort.

Right now, I am looking to use the row-security policies for a web
application. We would just have the application server authenticate as
a service role and set session variables holding the web client's
authentication context. The WITH and CHECK conditions would compare
row content against these session variables to enforce web client
authorization decisions underneath our application queries.

The row content includes ownership and ACL-like content as well as
other application content subject to special access rules expressed in
terms of the ownership and ACLs. If we authenticated users to the
database, we would want to consult current_user and something like
current_roles (an array of all roles granted to the
current_user). Instead, we'll be using analogous session context
asserted to us by the web service.

We've done previous systems where we compile all the application
policy checks into the SQL queries generated by the application, but I
think it would be more appropriate to split these out and have
generalized enforcement at the database level. It feels like a close
but not perfect fit already. It's fraught with perile to handle all
the data visibility rules while generating any application-level data
filtering expressions, joins, etc.!

I'm trying to boil out some simple illustrations. Discussing an entire
cohesive system is difficult and probably counter-productive...

The technical idea is to have policies that consider the relationship
between old data, new data, and session context to only allow
particular state transitions for row UPDATE. For INSERT, SELECT, and
DELETE, I think the current policy model is already sufficient.

Just a few possible use cases to illustrate mixed tests of old and new
row values:

1. Interlocks between record states and supplemental access
rights. A community might not allow records to be marked readable
until they have been giving a passing QA grade. A subsequent
consumer might revise the to a failing grade, but not revoke the
current access rights due to transparency rules.

2. State-transition rules for specific values. Enforce that regular
users can only move a workflow state in along normal edges,
while an admin user may be able to intervene and make abnormal
transitions. Or, allow users to fill in "missing" data such as
replacing NULL or other defaults with better values, but only
administrators can erase data back to NULL states.

3. Classification systems or other quasi-monotonic permissions
models where a user may advance the access class of a record in
one direction, but only special administrators can reverse the
direction.

A. A publishing system might make it easy to draft data in
smaller, private groups but once published it is hard to
retract things from the public record.

B. Confidentiality systems might do the opposite, allowing things
to be flagged as sensitive and locked down more easily than
relaxing access restrictions.

C. Community-based delegation systems might make it easy to
"share" records with additional consumers by adding to an ACL
but only the more privileged owner of the row can remove
entries from the ACL to "unshare".

4. Custody or provenance records. Certain unusual state-transitions
of data values may only be allowed if an explantory record is
appended to a small log array stored in the row.

I think that there is significant overlap between authorization, state
transition models, and data integrity constraints once you start
considering collaborative applications with mutable records.

The next big leap beyond considering NEW and OLD values during
condition checks would be to use scalar subqueries to examine the row
within the context of other existing rows in the same or different
tables. I have not looked to see if this is possible in the current
policy system, but I imagine we would try hard to avoid doing this due
to performance implications, even if it is allowed...

Karl