Table permission denied under custom auth hook
I am receiving this error: "error": "ERROR: permission denied for table user_organization (SQLSTATE 42501)".
This is my function:
declare
claims jsonb;
user_roles jsonb;
user_org record;
begin
-- Fetch all user roles in the user_roles table
select jsonb_agg(role) into user_roles
from public.user_roles
where user_id = (event->>'user_id')::uuid;
-- Fetch user organization from user_organization table
select organization_id into user_org
from public.user_organization
where user_id = (event->>'user_id')::uuid
and deleted_at is null
limit 1;
claims := event->'claims'; -- Set use roles if user_roles is not null then -- Set the claims with all roles claims := jsonb_set(claims, '{user_role}', user_roles); else claims := jsonb_set(claims, '{user_role}', 'null'); end if; -- Set organization_id if user_org.organization_id is not null then claims := jsonb_set(claims, '{organization_id}', to_jsonb(user_org.organization_id)); else claims := jsonb_set(claims, '{organization_id}', '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; RLS is disabled for both tables user_organization and user_roles. Any idea why is only user_organization table presenting errors? The custom hook works when only selecting user_roles table but not for user_organization.
claims := event->'claims'; -- Set use roles if user_roles is not null then -- Set the claims with all roles claims := jsonb_set(claims, '{user_role}', user_roles); else claims := jsonb_set(claims, '{user_role}', 'null'); end if; -- Set organization_id if user_org.organization_id is not null then claims := jsonb_set(claims, '{organization_id}', to_jsonb(user_org.organization_id)); else claims := jsonb_set(claims, '{organization_id}', '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; RLS is disabled for both tables user_organization and user_roles. Any idea why is only user_organization table presenting errors? The custom hook works when only selecting user_roles table but not for user_organization.
2 Replies
You did not grant the auth user role access probably...
From the auth hook guide:
totally! I just grant than table to that role and voila, thanks!!