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.
