Search code examples
postgresqlsupabasesupabase-databasesupabase-js

RLS issues in Supabase arise when attempting to filter products based on the `company_id` stored in `auth.user_metadata`

I want to show a list of products to the right employees. Based on the structure below:

  • Tina works at Company A (10), so I want them to see Apple
  • Sam works at Company B (11), so they will see Banana.

Here are my tables:

enter image description here I have a function that is triggered to set auth.usermeta & it works as it should:

const { data:authData, error: authErr } = await supabase.auth.updateUser({
            data: {
                company_id, // company_id is a string I got from the session
                role: 'manager'
            }
        });

Now on the product table on Supabase, I set this RLS on SELECT:

auth.jwt() -> 'user_metadata' ->> 'company_id' = (company_id)::text

Everything seems to be going OK (no error), however - I see the wrong products (and sometimes even no product) on user's dashboard. I have tested different SQL code including converting user_metadata company_id to bigint:

((((auth.jwt() -> 'user_metadata'::text) ->> 'company_id'::text))::bigint = company_id)

But same result, products just don't seem to show correctly :(

I finally gave up tackle this from the FE and it works as expected:

const { data, error: err } = await supabase
    .from('product')
    .select()
    .eq('company_id', session?.user.user_metadata.company_id);

I prefer to solve this using RLS instead of always passing session?.user.user_metadata.company_id. Why don't the products show correctly when I used the SQL expression?

My project uses Supabase(^2.39.1) and Sveltekit(^2.0.0)

Solution

  • Found a solution!

    I used refreshSession after updateUser and then auth.jwt() -> 'user_metadata' ->> 'company_id' = (company_id)::text on RLS SQL check :)