schemas and search paths
I have run into a problem last few days where I now need to use the FQN for functions and specify search paths, when previsouly they worked OK. Has anything in SupaBase changed recently that might explain this ?
14 Replies
Could you share any logs of the errors and maybe the function in question
It is best practice to use security invoker (which is also the default). If you ever use security definer, you must set the search_path.
If you use an empty search path (search_path = ''), you must explicitly state the schema for every relation in the function body (e.g. from public.table).
This is what the docs say btwThank you, this is very helpful. I am an inexperienced back end dev and this is my first SupaBase project. Most of my functions are definer, I am happy to ensure I conform the the docs/best practice. It was just recent erroneous errors "function get_master_encryption_key() does not exist" when it did in the public schema. I now specify public.get_master_encryption_key and it works, so all good, but I'm not sure what caused the error??
it was very likely the fact that if you are using definer functions the you must set the schema in front of any functions i.e public.
I am just reading the docs, and I'm not sure if I should be using invoker, any advice ?
the default is invoker, if a frontend user is calling the function directly then i would suggest start with invoker and use it with appropriate RLS policies
i personally would probably only set a function invoker if i am calling it from a backend/edge function and i'm already using an adminClient
supabase is being called from a react webapp, triggers support user actions. e.g. encryption/decryption of their data
did you mean definer here ?
yeah my bad, i meant definer
I'n not actually sure with triggers because i think the definer and the invoker might be the same
since it is not the user calling the function really but postgres
Yeah, I can see that.
Actually i think if it is invoker then it still uses permissions of who triggered the row
essentially use invoker for anything in public i would say
I am having all sorts of NOT fun, I'll give this a try 🙂
yeah, give it a okay and keep us informed
Right, I've got to the bottom of my issue:
When SECURITY DEFINER functions call other SECURITY DEFINER functions, the security context can get confused, The innermost function (decrypt_with_tenant_key) may lose access to auth.uid() or RLS policies. PostgreSQL has known issues with deeply nested SECURITY DEFINER contexts. This is a classic PostgreSQL SECURITY DEFINER anti-pattern: Nesting multiple SECURITY DEFINER functions causes the security context (including auth.uid(), session variables, and RLS policies) to become unreliable.
When SECURITY DEFINER functions call other SECURITY DEFINER functions, the security context can get confused, The innermost function (decrypt_with_tenant_key) may lose access to auth.uid() or RLS policies. PostgreSQL has known issues with deeply nested SECURITY DEFINER contexts. This is a classic PostgreSQL SECURITY DEFINER anti-pattern: Nesting multiple SECURITY DEFINER functions causes the security context (including auth.uid(), session variables, and RLS policies) to become unreliable.
That would make sense, was it postgres docs/github thread you found this
No my AI buddy, Alan 🙂