Row Level Security with user meta data does not work (example in docs does not work)

[jstdk]

Bug report

I follow the example as explained here: https://supabase.com/docs/guides/auth/row-level-security. It shows the following example:

I try to use this for my own example, whereby I update the organisation table, and the organisationId is in the user meta data

Both the "id" and "organisationId" are bigints, and I have tried all possible type casts, but nothing works. How can I make this work? Once it works, I will make a request to update the documentation with examples that work.

Thanks

[GaryAustin1]

The example is using an array of teams I believe. In works on arrays not sure it will work for what you want to do if those are just bigints. I don't know if casting is needed for that case or not as it might know the array type. I've not tested that.

(auth.jwt() ->'user_metadata' ->> 'org')::bigint should work to cast it to bigint then compare as needed.
It might work with ->'org')::bigint but I've not tried it that way.
Note: ->> retrieves the json as text, -> as json.

Note also you are using user metadata which is not secure as the user can change their organization at anytime.

[jstdk]

Thanks @GaryAustin1, unfortunately it does not work, have tried a gazillion things so maybe it is just not possible. Will have to remove RLS from all tables with "update" procedures whereby the condition cannot use auth.uid() or equivalent (which works). I am stuck on this for about a week.

Will also move to app_metadata or queried data instead of user_metadata (thanks)

[jerocosio]

I had a similar issue and was using arrays as a way to add the info into the app_metadata and this is what ended up working for me:

CREATE POLICY "Only user's companies" 
  ON "public"."companies"
  AS PERMISSIVE FOR SELECT 
  TO authenticated
  USING (id::text = ANY(SELECT jsonb_array_elements_text(auth.jwt() -> 'app_metadata' -> 'companies')));

[Hallidayo]

Hi everyone, due to inactivity on this issue I've moved the issue over to discussions/enhancements.