Обсуждение: Order of execution for permissive RLS policies
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.
I dug into the code in the postgres github repo a bit, but my knowledge of C is pretty limited, so I wasn't able to work out why this is happening. I did, however, note the comment about sorting policies here - https://github.com/postgres/postgres/blob/REL_10_4/src/backend/rewrite/rowsecurity.c#L509 -
"sort_policies_by_name
This is only used for restrictive policies, ensuring that any
WithCheckOptions they generate are applied in a well-defined order.
This is not necessary for permissive policies, since they are all combined
together using OR into a single WithCheckOption check."
I would argue that the claim "This is not necessary for permissive policies" is false. In the case of multiple policies OR'd together, executing the policies from least to most expensive can have a dramatic effect on query speed, since there is the possibility that the more expensive policies will not be executed (when a cheaper policy returns true).
I guess my questions are:
1) Why is order considered unimportant for permissive policies?
2) How come permissive policies are always executed in reverse alphabetical order? (This is mostly for my curiosity)
3) Could the code be changed so that permissive policies are also run through sort_policies_by_name, to make the observed behaviour more sensible (and intentional), without any negative effects?
(Note: I've also observed the same behaviour - reverse alphabetical order of policies - in Postgres 9.6)
Thanks
-- The Wellcome Sanger Institute is operated by Genome Research Limited, a charity registered in England with number 1021457 and a company registered in England with number 2742969, whose registered office is 215 Euston Road, London, NW1 2BE.
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