Re: Row level security - notes and questions

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(at)snowman(dot)net]
> Sent: Samstag, 11. Juli 2015 15:22
> To: Charles Clavadetscher
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Row level security - notes and questions
>
> Charles,
>
> * Charles Clavadetscher (clavadetscher(at)swisspug(dot)org) 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