Passing variables to RPC for RLS
Does Supabase have a way inbuilt that allows setting session variables when making PGSQL rpc calls?
My goal:
1. Use an RPC call
2. Pass _data to it, but also some other values that are used for authentication
3. RLS checks these auth values and says ya or nay
I found a method that worked by passing raw SQL but am concerned about SQL injection risk.
Le me know if there's a better way to do this? For reference, the auth is with a third party app that stores hashed values in a DB, and we can't use supabase auth to validate this.
Edited to include example RLS:
(account IN (
SELECT table1.account
FROM table1
WHERE ((NOT ((table1.key1)::text IS DISTINCT FROM COALESCE(current_setting('app.authvalue1'::text, true), 'undefined'::text)))
AND ((NOT ((table1.key2)::text IS DISTINCT FROM COALESCE(current_setting('app.authvalue2'::text, true), 'undefined'::text)))
OR (NOT (table1.key3 IS DISTINCT FROM COALESCE(current_setting('app.setting3'::authvalue3, true), 'undefined'::text))))))
My goal:
1. Use an RPC call
2. Pass _data to it, but also some other values that are used for authentication
3. RLS checks these auth values and says ya or nay
I found a method that worked by passing raw SQL but am concerned about SQL injection risk.
Le me know if there's a better way to do this? For reference, the auth is with a third party app that stores hashed values in a DB, and we can't use supabase auth to validate this.
Edited to include example RLS:
(account IN (
SELECT table1.account
FROM table1
WHERE ((NOT ((table1.key1)::text IS DISTINCT FROM COALESCE(current_setting('app.authvalue1'::text, true), 'undefined'::text)))
AND ((NOT ((table1.key2)::text IS DISTINCT FROM COALESCE(current_setting('app.authvalue2'::text, true), 'undefined'::text)))
OR (NOT (table1.key3 IS DISTINCT FROM COALESCE(current_setting('app.setting3'::authvalue3, true), 'undefined'::text))))))
