Обсуждение: 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