Hi Stephen, hi Adrian Thank you a lot! My huge mistake in understanding how policies work was to assume that within a single policy FOR ALL the USING clause would be used *only* for SELECT while WITH CHECK would be used by the modifying commands. Now it is clear why it did not work as I wanted. I just checked it out on my test environment and everything works as you said. I thought I read the documentation carefully but I must have missed that point. I will recheck to see if it really needs improvement, althought I must admit that I am not an English native speaker. And obviously I will continue with some experiments and get back with new questions if any arise. PostgreSQL has really a great community ;-) Enjoy Charles > -----Original Message----- > From: Stephen Frost [mailto:sfrost@xxxxxxxxxxx] > Sent: Samstag, 11. Juli 2015 15:22 > To: Charles Clavadetscher > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: Row level security - notes and questions > > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
