Authorization

Overview

Supasheet uses a two-layer authorization system:

1. Roles & Grants - Real Postgres roles with real GRANT/REVOKE privileges control what resources users can see and operate on (tables, views, charts, reports)
2. Row Level Security (RLS) - Control which specific rows users can access in those resources

There is no permissions table and no permission enum. A user's access is exactly what their Postgres role has been granted — nothing more, nothing less.

How Roles Work

Native Postgres Roles

Supasheet ships three roles, created directly with CREATE ROLE:

create role "x-admin" nologin;
create role "admin" nologin;
create role "user" nologin;

Each is nologin — nobody connects to Postgres as these roles directly. Instead, they're wired into Supabase's connection pooling so PostgREST can assume them per request:

-- The connection-pooling role (authenticator) is allowed to become any of these
grant "x-admin", "admin", "user" to authenticator;

-- Each app role inherits everything already granted to `authenticated`
grant authenticated to "x-admin", "admin", "user";

From JWT to Postgres Role

A Supabase Custom Access Token Hook (supasheet.custom_access_token) copies the role stored on the user's auth.users.raw_app_meta_data->>'role' into the JWT's top-level role claim on every token issuance:

create or replace function supasheet.custom_access_token (event jsonb)
returns jsonb language plpgsql stable
set search_path = '' as $$
declare
  claims jsonb;
  requested_role text;
begin
  claims := event -> 'claims';
  requested_role := event -> 'claims' -> 'app_metadata' ->> 'role';

  if requested_role is not null and exists (select 1 from pg_roles where rolname = requested_role) then
    claims := jsonb_set(claims, '{role}', to_jsonb(requested_role));
  end if;

  return jsonb_set(event, '{claims}', claims);
end;
$$;

PostgREST reads that role claim and issues SET ROLE "<role>" for the request. From that point on, every query — every SELECT, INSERT, UPDATE, DELETE, and every RLS policy evaluation — runs as that Postgres role. Whatever you've GRANTed to "user" is what a user-role request can do; nothing more.

Built-in Roles

user (Default)

• Automatically assigned via a before insert trigger on auth.users (supasheet.assign_default_role) that sets raw_app_meta_data->>'role' to 'user' when nothing else was requested at sign-up
• Has no grants by default — grant access explicitly for each resource
• Recommended for end-users of your application

admin

• An intermediate role with no built-in grants
• Reserved for application admins; grant the resource privileges that match your business rules

x-admin

• The Supasheet super-admin role
• Granted full access to the entire supasheet schema (users, audit logs, notifications, configs, etc.) directly in the base migrations
• The only role whose UI capability checks are hardcoded rather than grant-derived: every admin Edge Function (create/invite/update/delete/ban/generate-link/set-role users) requires x-admin membership specifically, checked via pg_has_role

Helper Functions

-- Check if the current request's role is (or inherits) a given role
select supasheet.has_role('x-admin');

-- Inspect the current session: your user id, JWT role claim, and active Postgres role
select supasheet.whoami();

has_role() is a thin wrapper around Postgres's own pg_has_role(current_user, requested_role, 'member') — it exists for convenience and readability in policies, not because there's any additional bookkeeping behind it.

Granting Access to Resources

Access to a table, view, materialized view, dashboard widget, chart, or report is controlled entirely by standard Postgres grants — there is no separate "permission" to register anywhere else. If a role can SELECT a resource, that role sees it in the UI; if it can't, the resource simply doesn't appear.

Tables

Tables typically need all four operations granted to the roles that should have full CRUD:

revoke all on table public.tasks
from authenticated, service_role;

grant select, insert, update, delete on table public.tasks to "user";
grant select, insert, update, delete on table public.tasks to "x-admin";

Grant only what a role should actually have — for example, a role that can create and read but never delete:

grant select, insert, update on table public.tasks to "editor";

Views, Materialized Views, Dashboards, Charts, Reports

All read-only resource types only ever need SELECT:

revoke all on public.task_status_pie
from authenticated, service_role;

grant select on public.task_status_pie to "user";

How the UI Decides What to Show

The meta layer (supasheet.get_tables(), get_views(), get_materialized_views(), get_permissions(), get_nav_items(), …) computes visibility live, on every request, using Postgres's own has_table_privilege():

-- Simplified shape of what the meta layer checks per resource
select has_table_privilege(current_user, 'public.tasks', 'select');

Because this is computed from the request's actual active role rather than looked up in a table, there's nothing to keep in sync: change a GRANT, and every user on that role sees the effect on their very next request — no cache to invalidate, no metadata refresh needed for the access question (you still need select supasheet.refresh_metadata() after schema changes so the structural catalog — table/column/view lists — picks up new objects at all; see the Complete Example).

No Separate :audit or :comment Permission

Two special-case capabilities from earlier permission-table-based designs are now handled globally instead of per-resource:

• Audit trail visibility needs no per-resource setup — supasheet.audit_logs is read directly (no wrapper function), gated by a table SELECT grant (already held by "user" and "x-admin") plus two RLS policies: x-admin sees every row; everyone else only sees rows where created_by = auth.uid() (their own actions). Add another permissive select policy on the table if a role needs a different rule — don't edit the two existing ones.
• Comment read/write access is simply the table's existing SELECT privilege — if a role can read a table, it can read and post comments on its rows. There's no separate comment grant to add.

Assigning Roles to Users

A user's role lives on auth.users.raw_app_meta_data->>'role' — there is no user_roles table, and a user has exactly one role at a time (not a set of roles).

-- Assign (or change) a user's role
update auth.users
set raw_app_meta_data = raw_app_meta_data || jsonb_build_object('role', 'manager')
where id = 'user-uuid-here';

-- Unassign — falls back to whatever assign_default_role does on next sign-in-adjacent trigger,
-- but for an existing user this simply clears the claim:
update auth.users
set raw_app_meta_data = raw_app_meta_data - 'role'
where id = 'user-uuid-here';

In the UI, an x-admin assigns roles from Core → Users → (a user) → Security, via a role selector that calls the admin-set-user-role Edge Function (which itself calls auth.admin.updateUserById() with the service role key — a user can never set their own role through this path). Assigning a role fires a notification to that user via a trigger on auth.users (trg_user_role_changed_notify, listening for raw_app_meta_data changes).

Adding Custom Roles

Creating a new role is exactly the same recipe used for the built-in ones — there's no enum to extend first:

do $$
begin
  if not exists (select 1 from pg_roles where rolname = 'manager') then
    create role "manager" nologin;
  end if;
end;
$$;

grant "manager" to authenticator;
grant authenticated to "manager";

-- Now grant it access like any other role
grant select, update on table public.tasks to "manager";

Assign it to a user the same way as any built-in role — update auth.users set raw_app_meta_data = raw_app_meta_data || jsonb_build_object('role', 'manager') where id = ....

Row Level Security (RLS)

RLS policies control which rows users can access. Security is enforced at the database level, on top of whatever the role's table-level grant already allows.

The Default: Grants Already Gate the Operation

Since a GRANT already decided whether a role can attempt an operation at all, most RLS policies in a Supasheet schema simply pass everything through:

alter table public.tasks enable row level security;

create policy tasks_select on public.tasks
  for select to authenticated using (true);

create policy tasks_insert on public.tasks
  for insert to authenticated with check (true);

create policy tasks_update on public.tasks
  for update to authenticated using (true) with check (true);

create policy tasks_delete on public.tasks
  for delete to authenticated using (true);

Row-Level Business Rules

RLS is where genuine per-row rules — ownership, status, visibility windows — still live. Combine them freely; there's no permission-string clause to weave in anymore:

-- Users see only their own data
create policy own_data on tasks for select
  to authenticated using (user_id = auth.uid());

create policy create_own on tasks for insert
  to authenticated with check (user_id = auth.uid());

create policy update_own on tasks for update
  to authenticated
  using (user_id = auth.uid())
  with check (user_id = auth.uid());

create policy delete_own on tasks for delete
  to authenticated using (user_id = auth.uid());

Role-Based Overrides

Use pg_has_role() (or the supasheet.has_role() wrapper) directly in a policy when a specific role should bypass an ownership check — this is how x-admin sees every row while everyone else only sees their own:

-- Owners see their own rows; x-admin sees everything
create policy tasks_select on tasks for select
  to authenticated
  using (
    user_id = auth.uid()
    or pg_has_role(current_user, 'x-admin', 'member')
  );

-- Managers see their team's data
create policy manager_access on tasks for select
  to authenticated
  using (
    pg_has_role(current_user, 'manager', 'member')
    and team_id in (
      select team_id from team_managers
      where user_id = auth.uid()
    )
  );

Public + Private Data

-- Anyone signed in can view public tasks
create policy public_tasks on tasks for select
  to authenticated using (is_public = true);

-- Users can additionally view their own private tasks
create policy own_private_tasks on tasks for select
  to authenticated
  using (user_id = auth.uid() and is_public = false);

Policy Operations

• FOR SELECT - Control who can read
• FOR INSERT - Control who can create
• FOR UPDATE - Control who can modify
• FOR DELETE - Control who can remove
• FOR ALL - Shorthand for all operations

Performance Tips

-- Add indexes for policy conditions
create index tasks_user_id_idx on tasks (user_id);
create index tasks_team_id_idx on tasks (team_id);

-- Use EXISTS instead of IN
create policy better_policy on tasks for select
  using (
    exists (
      select 1 from team_members
      where team_id = tasks.team_id
        and user_id = auth.uid()
    )
  );

Notifications Integration

When a notification trigger needs to resolve "which users should see this," it queries roles and grants directly instead of a permissions table:

-- Every user currently holding a given role
select supasheet.get_users_with_role('x-admin');

-- Every user whose role currently holds a given table privilege
select supasheet.get_users_with_table_privilege('public', 'tasks', 'update');

Both are SECURITY DEFINER, restricted to service_role — they're meant to be called from inside your own SECURITY DEFINER notification trigger functions, not from the client. See Notifications for the full trigger pattern.

Complete Workflow

When adding a new resource:

-- 1. Create the table
create table tasks (...);

-- 2. Revoke default grants, then grant exactly what each role needs
revoke all on table tasks from authenticated, service_role;
grant select, insert, update, delete on table tasks to "user";

-- 3. Enable RLS
alter table tasks enable row level security;

-- 4. Create RLS policies — `using (true)` is the default once the grant
--    above already gates the operation; add real row-level conditions
--    only where you actually need them
create policy tasks_select on tasks for select
  to authenticated using (true);

create policy tasks_insert on tasks for insert
  to authenticated with check (true);

Key Takeaways

• Roles and grants control UI visibility (what resources appear) — real CREATE ROLE + GRANT/REVOKE, no permissions table
• RLS controls data access (which rows are accessible), layered on top of whatever the grant already allows
• The JWT's role claim drives SET ROLE — PostgREST runs every request as that native Postgres role
• A user has one role at a time, stored on auth.users.raw_app_meta_data->>'role', not a user_roles join table
• Use pg_has_role() / supasheet.has_role() in policies for role-based overrides; use has_table_privilege() (already wired into the meta layer) to reason about resource visibility

Next Steps

• Complete Example - See full workflow
• Database Schema - Understand schema organization
• User Management - Learn about accounts