While answering this question, I gave some (unfounded) advice on
create policy test_policy on policy for all to public using (
user_id = session_user_id());
Btw, you should schema-qualify the
session_user_id()call to make your policy actually secure, so that the user cannot inject their ownsession_user_idfunction through thesearch_path.
But is this actually the case? I had misremembered the search_path issue with SECURITY DEFINER functions.
How and when are row-level-security policies parsed? Are the references resolved during definition or during evaluation?
It would make sense to have identifiers in them be early-bound not late-bound, but I could not find anything in the docs about this.
Policy definitions are stored in pg_policy, where the USING clause is stored in the polqual column and the WITH CHECK expression is stored in polwithcheck.
Both columns are of data type pg_node_tree, which is a parsed SQL statement. So policies are parsed when they are created, not when they are executed, much like views or standard conforming SQL functions (new in v14). That means that the setting of search_path is only relevant when the policy is created, not when it is executed.