Supabase

let { data: members, error } = await supabase
  .from('members')
  .select("*")

  // Filters
  .eq('column', 'Equal to')
  .gt('column', 'Greater than')
  .lt('column', 'Less than')
  .gte('column', 'Greater than or equal to')
  .lte('column', 'Less than or equal to')
  .like('column', '%CaseSensitive%')
  .ilike('column', '%CaseInsensitive%')
  .is('column', null)
  .in('column', ['Array', 'Values'])
  .neq('column', 'Not equal to')

  // Arrays
  .contains('array_column', ['array', 'cxontains'])
  .containedBy('array_column', ['contained', 'by'])

  // Logical operators
  .not('column', 'like', 'Negate filter')
  .or('some_column.eq.Some value, other_column.eq.Other value')

The general idea is we can use certain permissions granted to certain roles for use in RLS to have more fine-grained control over who gets to access what data. To do this, we can create an app_permission enum type to list out all the permissions, and an app_role enum type to list out all of the roles. Then, we can have a user_roles junction table to link a particular UUID with an app_role, and a role_permissions junction table to link specific roles to permissions.

-- CUSTOM TYPES
CREATE TYPE public.app_permission AS ENUM ('members.select', 'members.update', 'members.insert');
CREATE TYPE public.app_role AS ENUM ('admin', 'member');

-- USER ROLES
CREATE TABLE public.user_roles (
  id        BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id   UUID REFERENCES auth.users ON DELETE CASCADE NOT NULL,
  role      app_role NOT NULL,
  UNIQUE (user_id, role)
);
COMMENT ON TABLE public.user_roles IS 'Application roles for each user.';

-- ROLE PERMISSIONS
CREATE TABLE public.role_permissions (
  id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  role        app_role NOT NULL,
  permission  app_permission NOT NULL,
  UNIQUE (role, permission)
);
COMMENT ON TABLE public.role_permissions IS 'Application permissions for each role.';

We can then insert into public.role_permissions the permissions for each role, like so:

INSERT INTO public.role_permissions (role, permission)
VALUES  ('admin', 'members.select'),  
        ('admin', 'members.update'),
        ('admin', 'members.insert');

In order for Supabase Auth to know what role an user is, we can now take advantage of the custom claims feature to add their role as a claim:

-- Create the auth hook function
CREATE OR REPLACE FUNCTION public.custom_access_token_hook(event jsonb)
RETURNS jsonb
LANGUAGE plpgsql
STABLE
AS $$
  DECLARE
    claims jsonb;
    user_role public.app_role;
  BEGIN
    -- Fetch the user role in the user_roles table
    SELECT role INTO user_role FROM public.user_roles WHERE user_id = (event->>'user_id')::uuid;

    claims := event->'claims';

    IF user_role IS NOT NULL THEN
      -- Set the claim
      claims := JSONB_SET(claims, '{user_role}', TO_JSONB(user_role));
    ELSE
      claims := JSONB_SET(claims, '{user_role}', 'null');
    END IF;

    -- Update the 'claims' object in the original event
    event := JSONB_SET(event, '{claims}', claims);

    -- Return the modified or original event
    RETURN event;
  END;
$$;

GRANT USAGE ON SCHEMA public TO supabase_auth_admin;

GRANT EXECUTE
  ON FUNCTION public.custom_access_token_hook
  TO supabase_auth_admin;

REVOKE EXECUTE
  ON FUNCTION public.custom_access_token_hook
  FROM authenticated, anon, public;

GRANT ALL
  ON TABLE public.user_roles
TO supabase_auth_admin;

REVOKE ALL
  ON TABLE public.user_roles
  FROM authenticated, anon, public;

CREATE POLICY "Allow auth admin to read user roles" ON public.user_roles
AS PERMISSIVE FOR SELECT
TO supabase_auth_admin
USING (TRUE);

To enable this function as a hook, in the dashboard, navigate to Authentication > Hooks and select the appropriate Postgres function from the dropdown menu.

Now, in order to use this information in our RLS policies, we can create a function called authorize to see if the user has the required permission:

CREATE OR REPLACE FUNCTION public.authorize(
  requested_permission app_permission
)
RETURNS boolean AS $$
DECLARE
  bind_permissions int;
  user_role public.app_role;
BEGIN
  SELECT (auth.jwt() ->> 'user_role')::public.app_role INTO user_role;

  SELECT COUNT(*)
  INTO bind_permissions
  FROM public.role_permissions
  WHERE role_permissions.permission = requested_permission
    AND role_permissions.role = user_role;

  RETURN bind_permissions > 0;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = '';

We can now use this function in our RLS policies, like so:

CREATE POLICY "Allow authorized select access"
ON "public"."members"
FOR SELECT TO authenticated USING (
  (SELECT authorize('members.select'))
);

CREATE POLICY "Allow authorized update access"
ON "public"."members"
FOR UPDATE TO authenticated USING (
  (SELECT authorize('members.update'))
);

CREATE POLICY "Allow authorized insert access"
ON "public"."members"
FOR INSERT TO authenticated WITH CHECK (
  (SELECT authorize('members.insert'))
);