Supabase: Getting "role 'user' does not exist" error only when authenticated user queries product/category tables

Problem Summary

I'm experiencing a strange issue with Supabase where:

• When user is logged out (anonymous): GET requests to /rest/v1/product and /rest/v1/category return 200 OK
• When user is logged in (authenticated): Same requests return 400 Bad Request with error:

{
    "code": "22023",
    "details": null,
    "hint": null,
    "message": "role \"user\" does not exist"
}

Database Schema

I have the following tables in my Supabase project:

-- profiles table (contains user role info)
CREATE TABLE public.profiles (
    id UUID REFERENCES auth.users(id) PRIMARY KEY,
    name TEXT,
    email TEXT,
    role TEXT DEFAULT 'user',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- product table
CREATE TABLE public.product (
    id INT4 PRIMARY KEY,
    name VARCHAR,
    price NUMERIC,
    stock INT4,
    category INT4,
    image_url TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- category table  
CREATE TABLE public.category (
    id INT4 PRIMARY KEY,
    name VARCHAR,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Custom Access Token Hook

I have a custom access token hook that reads user role from profiles:

CREATE OR REPLACE FUNCTION public.custom_access_token_hook(event jsonb)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  user_role text;
  user_name text;
  user_email text;
BEGIN
  SELECT role, name, email 
  INTO user_role, user_name, user_email
  FROM public.profiles
  WHERE id = (event->>'user_id')::uuid;

  IF user_role IS NULL THEN
    user_role := 'user';
  END IF;

  RETURN jsonb_set(
    event,
    '{claims}',
    COALESCE(event->'claims', '{}') || jsonb_build_object(
      'role', user_role,
      'user_name', COALESCE(user_name, ''),
      'user_email', COALESCE(user_email, '')
    )
  );
END;
$$;

Frontend Code

// This works when user is logged out
const { data, error } = await supabase
  .from('product')
  .select('*');

// Same code returns error when user is logged in

What I've Tried

1. Disabled RLS on product and category tables:

   ALTER TABLE public.product DISABLE ROW LEVEL SECURITY;
   ALTER TABLE public.category DISABLE ROW LEVEL SECURITY;
   

2. Removed all RLS policies from these tables

3. Granted permissions to anon and authenticated roles:

   GRANT SELECT ON public.product TO anon;
   GRANT SELECT ON public.product TO authenticated;
   GRANT SELECT ON public.category TO anon;
   GRANT SELECT ON public.category TO authenticated;
   

4. Simplified the custom access token hook to just return the event without modifications

5. Temporarily disabled the custom access token hook entirely

None of these solutions worked. The error persists only when the user is authenticated.

Additional Context

• Supabase project is relatively new
• The error started appearing after implementing the custom access token hook
• User login/logout functionality works correctly
• The error specifically mentions PostgreSQL role "user" which suggests it's trying to use the application role as a database role

Questions

1. Why does the same query work for anonymous users but fail for authenticated users?
2. How is the custom JWT claim role: "user" being interpreted as a PostgreSQL database role?
3. What's the proper way to handle user roles in Supabase without causing this conflict?

Environment

• Supabase: Latest version
• Frontend: Next.js with @supabase/supabase-js
• Database: PostgreSQL (Supabase managed)

Any help would be greatly appreciated!

i want data products and category can be access anyone

You’re getting:

"role \"user\" does not exist"

because your custom_access_token_hook adds this to the JWT:

{ "role": "user" }

Supabase treats role as a PostgreSQL role, and tries to run:

SET ROLE "user";

But your DB has no such role—hence the error.

Fix:

In your hook, rename role to something else, like app_role:

jsonb_build_object(
  'app_role', user_role,
  'user_name', user_name,
  'user_email', user_email
)

Also Make Sure:

• RLS is disabled on product and category:

ALTER TABLE product DISABLE ROW LEVEL SECURITY;

• Both anon and authenticated have SELECT access:

GRANT SELECT ON product TO anon, authenticated;