Skip to content

Instantly share code, notes, and snippets.

@ronaldaug

ronaldaug/authorize.sql

Last active April 18, 2025 12:19
Show Gist options
  • Save ronaldaug/62788ee60f140724c26ebe1714dd776e to your computer and use it in GitHub Desktop.
Save ronaldaug/62788ee60f140724c26ebe1714dd776e to your computer and use it in GitHub Desktop.
Download ZIP
authorize method
Raw
authorize.sql
create or replace function authorize(resource_action text) returns boolean as $$
declare
v_user_id uuid;
v_role_id int;
v_resource text;
v_action text;
v_has_permission boolean := false;
v_is_admin boolean := false;
begin
-- Get current user's ID from Supabase Auth
select auth.uid() into v_user_id;
-- Get the user's role
select ur.role_id into v_role_id
from user_roles ur
where ur.id = v_user_id;
-- Check if the role is admin
select exists (
select 1
from roles r
where r.id = v_role_id and r.name = 'admin'
) into v_is_admin;
-- If admin, bypass all checks
if v_is_admin then
return true;
end if;
-- Split 'leaves.delete' into 'leaves' and 'delete'
v_resource := split_part(resource_action, '.', 1);
v_action := split_part(resource_action, '.', 2);
-- Check if the action is valid
if v_action not in ('create', 'read', 'update', 'delete') then
return false;
end if;
-- Dynamically check the permission based on the action
execute format(
'select can_%s from permissions where role_id = $1 and resource = $2 limit 1',
v_action
)
into v_has_permission
using v_role_id, v_resource;
return coalesce(v_has_permission, false);
end;
$$ language plpgsql security definer;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment