Re: 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