https://supabase.com/ logo
Run an SQL query on the server for an authenticated user
t

Thomas Allmer

05/25/2023, 10:36 AM
I have a node server that directly connects to the Postgres Database on Superbase. And I know the logged in user and it's token (jwt). There is Policy to only allow reading for the logged in user
sql
CREATE POLICY "Enable select for users based on user_id" ON "public"."rlt-test"
AS PERMISSIVE FOR SELECT
TO public
USING (auth.uid() = user_id)
Data looks something like this (with user_id simplified)
| id  | name        | user_id |
| --- | ----------- | ------- |
| 1   | John Doe    | 1       |
| 2   | Jane Doe    | 1       |
| 3   | Susan Smith | 2       |
so now if I do a select via the api with the user_id 1 logged in I get - John - Jane if I do a
select * from test
then I get - John - Jane - Susan Can I execute an sql query that will give me the same result as the API? e.g. maybe somehow set the values that are used within auth.uid() an idea
BEGIN;
SET LOCAL auth.jwt = '...';

SELECT * from test;
COMMIT;
it sadly does not work as I don't know which values to set... and if it can even be set that way 😅 anyone got any ideas?
s

silentworks

05/25/2023, 11:46 AM
You can set the user via sql with your node server postgres driver.
sql
set local role authenticated;
set local "request.jwt.claim.sub" to 'user_uuid_goes_here';
t

Thomas Allmer

05/25/2023, 1:03 PM
oh nice 👍 that works perfectly 💪 thank you 🙇
v

vick

05/25/2023, 1:40 PM
One of the most useful things I've learned around the authentication features with Supabase is from this discussion comment: https://github.com/orgs/supabase/discussions/12269#discussioncomment-4908791 These functions have helped me debug my RLS by showing how to simulate being another user.
t

Thomas Allmer

05/25/2023, 5:57 PM
@vick that is very useful - thank you for pointing it out 🙏