On 24 July 2018 at 15:25, Simon Brent <sb23@xxxxxxxxxxxx> 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
