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.
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.
