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

I've been trying to learn more about the row-security policies but
coming up short in my searches.  Was there any consideration to
allowing access to both old and new row tuples in a POLICY ... CHECK
expression?  This idiom I've seen in the lower level rule and trigger
systems seems like it would allow for much more powerful row-security
policies.

The simple illustrations of row-security policy always seem to
consider an 'owner' field in the tuple compared to current_user.  If
you could consult both old and new values, you could generalize to
storing application ACLs in rows and using those ACLs to decide row
access while also ensuring that ACLs cannot be changed in ways
inconsistent with the privilege level of the current user.

For example, if the current user is in the old ACL value, allow them
to modify the ACL otherwise require that the new ACL value be equal to
the old ACL value.  This would allow a user to be given write access
to some columns while restricting others, but on a row-by-row basis.

Right now, as I understand it, you can only compare the old values to
session state in the WITH condition and new values to the session
state in the CHECK condition, but never consider old and new values
simultaneously.  This excludes a wide and useful gray area between
no trust and full trust to amend row content.


Karl

Karl,

* Karl Czajkowski (karlcz@isi.edu) wrote:
> I've been trying to learn more about the row-security policies but
> coming up short in my searches.  Was there any consideration to
> allowing access to both old and new row tuples in a POLICY ... CHECK
> expression?  This idiom I've seen in the lower level rule and trigger
> systems seems like it would allow for much more powerful row-security
> policies.

It was discussed, albeit rather late in the cycle (this past fall, as I
recall...), and is certainly something we can consider implementing in a
future release.

> The simple illustrations of row-security policy always seem to
> consider an 'owner' field in the tuple compared to current_user.  If
> you could consult both old and new values, you could generalize to
> storing application ACLs in rows and using those ACLs to decide row
> access while also ensuring that ACLs cannot be changed in ways
> inconsistent with the privilege level of the current user.

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?

> For example, if the current user is in the old ACL value, allow them
> to modify the ACL otherwise require that the new ACL value be equal to
> the old ACL value.  This would allow a user to be given write access
> to some columns while restricting others, but on a row-by-row basis.

It's possible to disallow access to that column using column-level
privileges.  Further, how is that column populated?  I would generally
expect it to be populated by consulting some session-level variable (a
custom one, or CURRENT_USER or similar).  If that already exists, then
it can certainly be used in RLS policies.

> Right now, as I understand it, you can only compare the old values to
> session state in the WITH condition and new values to the session
> state in the CHECK condition, but never consider old and new values
> simultaneously.  This excludes a wide and useful gray area between
> no trust and full trust to amend row content.

I'm certainly not against adding that capability, but I do think more
detail around this use-case which you feel it'd be useful for would be
great.  I don't quite see how saying "if the old and new value stay the
same, then you can modify anything" makes sense- you have to consult
some external source to determine if you're the owner of that row,
right?  Otherwise, anyone could change any row, provided that keep that
column the same, and that hardly seems like what you'd want.

Thanks!

Stephen

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

* Karl Czajkowski (karlcz@isi.edu) wrote:
> 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.

Even with OLD/NEW being available to UPDATE, many of the state
transistion checks which you describe may well be better suited to
triggers rather than policies..  I can imagine such complicated
transistions quickly moving beyond SQL expressions and into procedural
logic.  I don't believe using policies for UPDATE which simply end up
calling a function to do a bunch of complicated considerations to be
particularly better than an UPDATE trigger which does the same.

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

Policies are certainly able to have references to other tables through
subqueries.  You are correct that there are performance considerations,
but those are essentially the same considerations you would have if the
application was to perform the same joins and queries as part of the
query, or if you were to include those in a view.

Thanks!

Stephen

I feel that the fundamental question here is about the semantics of
"row" in row security.  The core question is whether UPDATE has a
different semantics with respect to row identity and security context
than a sequence of DELETE, INSERT.

At the moment, the documented solution feels like "tuple security" and
supports an idea of security context encoded into a tuple such that a
policy condition can decide whether its context and data configuration
is consistent with the session context.  This makes sense in a pure
relational calculus, but ignores the practical deviation of SQL where
there are mutable records.

I am hoping for "record security" that supports an idea of security
context encoded into a record such that the context in the existing
record can constrain how the update of the record is formed.  This
includes policies that might differentiate updates to key versus
non-key fields in the record, deciding in application terms whether
the record has an identity and context that carries forward through
the UPDATE or whether it is merely sugar for a DELETE followed by
INSERT.

Ideally, I'd be able to write a policy that has conditions for each
category of operation:

   POLICY FOR SELECT WITH expr1
   POLICY FOR INSERT WITH expr2
   POLICY FOR DELETE WITH expr3
   POLICY FOR UPDATE WITH expr4

where expr1 would always be used to decide whether the current tuple
is visible to the query engine, expr2 would always be used to validate
new rows, expr3 would always be used to authorize row deletion, and
expr4 would be able to authorize row replacement using NEW and OLD
value comparisons.

An actual SQL UPDATE for a row visible according to expr1 could be
authorized if expr4 allows it *or* if expr3 and expr2 would allow a
DELETE followed by INSERT.  Where this becomes interesting is when a
session context with insufficient privilege to do the DELETE and
INSERT sequence is still allowed to do the UPDATE because their
request fits the narrower confines of the expr4 policy.


Karl

Karl,

* Karl Czajkowski (karlcz@isi.edu) wrote:
> Ideally, I'd be able to write a policy that has conditions for each
> category of operation:
>
>    POLICY FOR SELECT WITH expr1
>    POLICY FOR INSERT WITH expr2
>    POLICY FOR DELETE WITH expr3
>    POLICY FOR UPDATE WITH expr4

It's possible to have such policies for a table today.  These would be
independent policies and not all one policy, but I don't see that as
making a practical difference here.

> where expr1 would always be used to decide whether the current tuple
> is visible to the query engine, expr2 would always be used to validate
> new rows, expr3 would always be used to authorize row deletion, and
> expr4 would be able to authorize row replacement using NEW and OLD
> value comparisons.

Any UPDATE which requires SELECT rights on the table will require expr1
to pass AND expr4 (the UPDATE's USING clause) to pass.  This is modeled
directly off of our existing GRANT/ACL system.  The same is true for the
other commands.  Note that we explicitly want an independent USING
clause for expr4 as you may wish to reduce the set of rows which may be
UPDATE'd to be less than the set which are visible via SELECT.

I anticipate adding the ability to have "restrictive" policies also, in
the future.

> An actual SQL UPDATE for a row visible according to expr1 could be
> authorized if expr4 allows it *or* if expr3 and expr2 would allow a
> DELETE followed by INSERT.  Where this becomes interesting is when a
> session context with insufficient privilege to do the DELETE and
> INSERT sequence is still allowed to do the UPDATE because their
> request fits the narrower confines of the expr4 policy.

While allowing an UPDATE if a DELETE/INSERT would be allowed is an
interesting idea, it strikes me as being more complicated to explain and
justify to users than any value it would add.

Thanks!

Stephen

On Dec 18, Stephen Frost modulated:

> Any UPDATE which requires SELECT rights on the table will require expr1
> to pass AND expr4 (the UPDATE's USING clause) to pass.  This is modeled
> directly off of our existing GRANT/ACL system.  The same is true for the
> other commands.  Note that we explicitly want an independent USING
> clause for expr4 as you may wish to reduce the set of rows which may be
> UPDATE'd to be less than the set which are visible via SELECT.
>
> I anticipate adding the ability to have "restrictive" policies also, in
> the future.
>

Right, I think I understand that. However, my argument was (and
remains) that I think the update conditions need to be able to access
OLD and NEW row values to decide whether the existing row security
context allows the new update content.  This update decision is
inherently different from select, insert, and delete decisions.

I don't think that two separate decisions are sufficient:

   1. OLD row can be updated  (the USING condition?)
   2. NEW row is acceptable (the CHECK condition?)

when considering the row lifecycle as having continuity of identity
and security context.  I think that the second decision needs to have
the option to compare OLD and NEW to decide that the new row is an
acceptable transform of the existing row, preserving whatever identity
and/or security context is important in a particular system of
policies.

As I understand this discussion, you are telling me to just use an
update trigger for this.  I can understand that as a workaround given
the current row-security options, but I do think that this kind of
decision is essential to row-security in realistically complex
applications that allow row mutation.  I don't think it is
particularly esoteric to suggest that the existing row security
context should limit possible future configurations of the row by
different parties.  That effect spans security context, row
identities, and regular non-identifying content.

Right now, it seems like the row-security model assumes the only
security context is an "owner" field carrying a user name and that is
never mutated (perhaps by a column-level privilege).  I think security
context can be richer than that, including more abstract roles,
classes, or attributes and having users be able to mutate that context
but only under the control of row-security policy.  It is not as
simple as the context being system managed only, or open to arbitrary
changes by admins and no change by others. Rather, the current context
may grant certain classes of user the ability to make only certain
coherent changes to that context.

Where mutation is different here is that a set of collaborating
parties can interpret one mutable row as a long-lived resource that
has an ongoing identity (or set of identities) and a coherent series
of row states protected by policy.  Without the ability for the OLD
row to contrain how the NEW row develops in an update policy, we lose
that coherence and have a fugue state for the application, with no
ability to trust the identity of content over time.


Karl

Karl,

* Karl Czajkowski (karlcz@isi.edu) wrote:
> On Dec 18, Stephen Frost modulated:
> > Any UPDATE which requires SELECT rights on the table will require expr1
> > to pass AND expr4 (the UPDATE's USING clause) to pass.  This is modeled
> > directly off of our existing GRANT/ACL system.  The same is true for the
> > other commands.  Note that we explicitly want an independent USING
> > clause for expr4 as you may wish to reduce the set of rows which may be
> > UPDATE'd to be less than the set which are visible via SELECT.
> >
> > I anticipate adding the ability to have "restrictive" policies also, in
> > the future.
>
> Right, I think I understand that. However, my argument was (and
> remains) that I think the update conditions need to be able to access
> OLD and NEW row values to decide whether the existing row security
> context allows the new update content.  This update decision is
> inherently different from select, insert, and delete decisions.

I agree that it would be a nice addition, as I've said before.  We
certainly won't be adding it into 9.5 and it's getting pretty late for
9.6 too, but I'm anxious to see just how RLS is used in the field (it's
certainly satisfying the use-cases for which it was developed, but I
anticipate a lot of new and interesting uses will come up).

> I don't think that two separate decisions are sufficient:
>
>    1. OLD row can be updated  (the USING condition?)
>    2. NEW row is acceptable (the CHECK condition?)
>
> when considering the row lifecycle as having continuity of identity
> and security context.  I think that the second decision needs to have
> the option to compare OLD and NEW to decide that the new row is an
> acceptable transform of the existing row, preserving whatever identity
> and/or security context is important in a particular system of
> policies.

Yeah, I tend to think it's more useful in the CHECK condition than the
USING condition.  That would, I expect, also be more practical as it
means we wouldn't be changing anything about how the conditionals are
added to the UPDATE query which is pulling the rows to operate on.

Thanks!

Stephen

On Dec 18, Stephen Frost modulated:

> I agree that it would be a nice addition, as I've said before.

OK, sorry I misunderstood earlier and thought you were dismissing the
idea as redundant with triggers.  Thanks for your patience!


> ... We certainly won't be adding it into 9.5 and it's getting pretty
> late for 9.6 too, but I'm anxious to see just how RLS is used in the
> field

If we want to start testing with 9.5 and emulate an environment
supporting NEW and OLD in the CHECK policy for UPDATE, can you
recommend how we should do that?  E.g., is there a particular trigger
idiom that would most closely replicate the RLS extension we've been
discussing (so that we'd know that a working policy set + triggers
could be translated to just RLS policies if that feature is added)?

We'd be happy to experiment with these sorts of policies in our
applications.  My intuition is that with this extension, we'd be able
to push down nearly all of our important policy enforcement into
PostgreSQL and guard against a slew of potential application
programming errors (compared to handling all this enforcement in our
application data access code)!


Karl