Charles, * Charles Clavadetscher (clavadetscher@xxxxxxxxxxxx) wrote: > I have been testing the new row level security feature of 9.5 and I have > some notes and questions on it. Great! Glad to hear it. > My first test is to enable row level security on the table without a policy > in place. According to the documentation this leads to a general deny > access. > > ALTER TABLE testrls.accounts ENABLE ROW LEVEL SECURITY; > > When user john tries to interact with the table he receives an empty result > set, instead of a policy violation error. There is no policy yet, so this > may be acceptable. I find it however confusing. A permissions error would be thrown if the user didn't have access to the table through the GRANT system. If no policy is found for a user (which could happen multiple ways- no policies exist, policies exist but none apply to this user, policies exist but none apply to this command, etc) then a default-deny policy is used which results in an empty set. This is all documented, of course. Specific suggestions for improving the docs to help clarify this would certainly be appreciated. > Since the result set is empty UPDATE and DELETE also do nothing. Right, the default deny policy applies to all commands. > In the case of an INSERT john receives a policy violation error. Still there > is no policy yet for the table. This seems not consistent with the behaviour > for the other commands. INSERTs can fail where SELECTs, UPDATEs, and DELETEs do not- even when policies have been defined on the relation, and so this is consistent within the overall policy system. It would be inconsistent for SELECTs to fail in all cases where INSERTs do. The reason for this is that RLS is about filtering the rows returned, but we suspend that for data which is being added to the system as we don't wish to accept and then throw away data (which is what filtering on an INSERT, or the result of an UPDATE, would do). > For the next example I created a policy that allows users to read all rows, > but only change those "belonging" to them, identified by the column > username. While I appreciate that your goal was to create such a policy, that's not what this command does: > CREATE POLICY accounts_policy ON testrls.accounts > FOR ALL > TO users > USING (true) > WITH CHECK (username = SESSION_USER); This command says "allow all commands to operate on all rows, but new rows being added to the system must have (username = SESSION_USER)". A policy to allow users to read all rows would be: CREATE POLICY accounts_policy ON testrls.accounts FOR SELECT TO users USING (true); The following policy would then allow users to update rows which have (username = SESSION_USER): CREATE POLICY accounts_policy_update ON testrls.accounts FOR UPDATE TO users USING (username = SESSION_USER); -- Note that with no WITH CHECK, the USING clause will be used Further, the "passwd" example in the documentation covers exactly this policy of "read all, modify only same-user". If you wanted to also allow INSERT and DELETE commands on rows which have (username = SESSION_USER), you could create policies for them, as so: CREATE POLICY accounts_policy_insert ON testrls.accounts FOR INSERT TO users WITH CHECK (username = SESSION_USER); CREATE POLICY accounts_policy_delete ON testrls.accounts FOR DELETE TO users USING (username = SESSION_USER); > The policy suggests that users can only modify rows where their name is in > the username field. In the UPDATE case the condition is tested against the > new values for the row, leading to a chance for any user to modify and > delete any row. ... which is what the policy was defined to allow by having a USING clause of "true". > - Why is there not a consistent policy violation message when one would > apply as mentioned above? Hopefully, my answers above explain this. > - Why is the WITH CHECK condition only used on the values in the new record > in the case of an update? Both the USING and WITH CHECK clauses are checked for UPDATE commands- the USING clause is "what *existing* records does this policy allow modification of" while the WITH CHECK clause is "what *new* records are allowed to be added through this policy". Consider a case where you wish to allow users to UPDATE existing rows in the table, but the result of that UPDATE must meet a different condition to be allowed to be added to the table. A simple case of this is "Joe can modify all records, but the result of that modification must update the last-modified-by column to be set to Joe." Thanks! Stephen
Attachment:
signature.asc
Description: Digital signature
