Auth helpers in SQL

Every project database has these functions in the auth schema. They read who is calling the current request — Sovaria sets that per request — so you can use them in row-level security policies, defaults, views and your own functions. The names match Supabase's, so policies you bring over keep working.

#auth.uid()

The signed-in user's id — the same id as in the Users page and in sovaria.auth.getUser(). Store it in an owner column and compare against it:

create table notes (
  id bigint generated always as identity primary key,
  owner_id uuid not null default auth.uid(),   -- filled in from the caller
  body text not null
);
alter table notes enable row level security;
alter table notes force row level security;

create policy "own notes" on notes
  for all using (owner_id = auth.uid()) with check (owner_id = auth.uid());

For anonymous requests auth.uid() is null, and owner_id = null is never true in SQL, so visitors see nothing — no extra check needed.

#auth.role()

anon, authenticated or service_role. Use it to separate visitors from signed-in users:

create policy "signed-in users read" on articles
  for select using (auth.role() = 'authenticated');

#auth.has_role()

True when the signed-in user has an app role you assigned on the Users page (or with sovaria.auth.admin…, sov users). Role names compare case-insensitively.

create policy "staff manage products" on products
  for all using (auth.has_role('STAFF')) with check (auth.has_role('STAFF'));

create policy "anyone reads products" on products
  for select using (true);

#auth.email() and auth.jwt()

auth.email() is the user's email from their token. auth.jwt() returns every claim as jsonb — useful for custom claims your auth hook adds (Authentication settings reference):

-- Custom claim "tenant_id" added by your JWT hook
create policy "same tenant" on invoices
  for select using (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);

#Good to know

• The helpers are STABLE: Postgres evaluates them once per statement, so they are cheap even in policies on large tables. Index the columns you compare them with (owner_id).
• They describe the request, not the database user. In a direct database connection (Database: connection, compute, disk and backups) there is no request, so auth.uid() is null.
• Inside a security definer function they still describe the caller, which lets you write helper functions that check ownership.