On 24 July 2018 at 15:25, Simon Brent <sb23@sanger.ac.uk> wrote:
> I've been using postgres for a while now, and have just started looking in
> to row level security. I have found something that I think is a bit strange,
> and wanted to know if anyone knows how/why it is the case.
>
> I have a table with multiple policies, each with a USING statement. When I
> run EXPLAIN ANALYSE SELECT * FROM [table], I see that the policies are OR'd
> together in reverse alphabetical name order. It doesn't matter which order I
> create the policies in - the order they are checked is always (for example)
> zz OR yy OR xx OR ww.
Hmm, the fact that permissive policies sometimes appear to be checked
in reverse alphabetical order looks like an implementation artefact --
that's the order in which RLS policies are read when loading a table's
metadata (see RelationBuildRowSecurity() in
src/backend/commands/policy.c). I don't believe that was intentional,
but any case, PostgreSQL makes no guarantees about the order of
evaluation of clauses under an OR clause, and the query optimiser is
free to re-order them for efficiency, provided that doing so doesn't
affect the query result.
A trivial example where permissive policies won't be evaluated in
reverse alphabetical order would be a policy that said USING (a=1 AND
b=1), and another one that said USING (a=1 AND b=2). The query
optimiser would merge those together to produce a=1 AND (b=1 OR b=2),
and then consider any indexes on 'a' and/or 'b'. So there is, in
general, no well-defined order of evaluation of the policies.
The only case where order can affect the result of a query is multiple
restrictive policies used to check new data inserted into a table
using INSERT or UPDATE. In that case, the error message for new data
violating one of the policies depends on the order in which they are
checked, and it's useful to be able to predict what the error message
will be. This is why restrictive policies are sorted by name. (This is
a little like multiple CHECK constraints on a table, which are also
checked in name order). Again, that name-ordering of restrictive
policies only applies to the checks run on new data; the clauses added
to the WHERE clause to check permission to access existing data may be
rearranged by the query optimiser and evaluated in any order.
Regards,
Dean