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

Karl,

* Karl Czajkowski (karlcz(at)isi(dot)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