Supabase/Postgres RLS policy and JavaScript to select all records in public.users if authenticated user's record contains admin: true

Question

In Supabase, I have a table public.users that corresponds to auth.users that contains additional user data.

One of the columns in public.users is is_admin. When the authenticated user's is_admin column equals true, I'd like that user to be able query a list of all records in public.users. I am using the following RLS policy and JavaScript to try to do so, however, an array containing only the record of the authenticated user is returned.

Can this be accomplished and if so, how might I achieve it?

RLS Policy:

CREATE POLICY "Select_By_User"

ON public.users

FOR SELECT

USING(auth.uid() = id OR is_admin = TRUE);

JavaScript:

export const getAllUsers = async () => {
  const supabase = createClient();

  const { data, error } = await supabase.from('users').select();

  console.log(data);
  // Returns an array with only the authenticated user's record, need an array of all records
}

Answer 1

The solution turned out to an RLS policy that calls a Postgres function. The function checks if the record corresponding to the given auth.uid in public.users contains is_admin = true.

FUNCTION:

CREATE OR REPLACE FUNCTION is_admin(user_id uuid)
  returns boolean AS
  $$
  select is_admin from users
  where id = user_id
  $$ stable language sql security definer;

POLICY:

CREATE POLICY "Full access for admins"
  ON users FOR ALL using (is_admin(auth.uid()))