Custom Access Token Hook

1
create or replace function public.custom_access_token_hook(event jsonb)
2
returns jsonb
3
language plpgsql
4
as $$
5
  declare
6
    original_claims jsonb;
7
    new_claims jsonb;
8
    claim text;
9
  begin
10
    original_claims = event->'claims';
11
    new_claims = '{}'::jsonb;
12
13
    foreach claim in array array[
14
      -- add claims you want to keep here
15
      'iss',
16
      'aud',
17
      'exp',
18
      'iat',
19
      'sub',
20
      'role',
21
      'aal',
22
      'session_id',
23
      'email',
24
      'phone',
25
      'is_anonymous'
26
   ] loop
27
      if original_claims ? claim then
28
        -- original_claims contains one of the listed claims, set it on new_claims
29
        new_claims = jsonb_set(new_claims, array[claim], original_claims->claim);
30
      end if;
31
    end loop;
32
33
    return jsonb_build_object('claims', new_claims);
34
  end
35
$$;

1
create table profiles (
2
  user_id uuid not null primary key references auth.users (id),
3
  is_admin boolean not null default false
4
);

1
create or replace function public.custom_access_token_hook(event jsonb)
2
returns jsonb
3
language plpgsql
4
as $$
5
  declare
6
    claims jsonb;
7
    is_admin boolean;
8
  begin
9
    -- Check if the user is marked as admin in the profiles table
10
    select is_admin into is_admin from profiles where user_id = (event->>'user_id')::uuid;
11
12
    -- Proceed only if the user is an admin
13
    if is_admin then
14
      claims := event->'claims';
15
16
      -- Check if 'app_metadata' exists in claims
17
      if jsonb_typeof(claims->'app_metadata') is null then
18
        -- If 'app_metadata' does not exist, create an empty object
19
        claims := jsonb_set(claims, '{app_metadata}', '{}');
20
      end if;
21
22
      -- Set a claim of 'admin'
23
      claims := jsonb_set(claims, '{app_metadata, admin}', 'true');
24
25
      -- Update the 'claims' object in the original event
26
      event := jsonb_set(event, '{claims}', claims);
27
    end if;
28
29
    -- Return the modified or original event
30
    return event;
31
  end;
32
$$;
33
34
grant all
35
  on table public.profiles
36
  to supabase_auth_admin;
37
38
revoke all
39
  on table public.profiles
40
  from authenticated, anon, public;

1
create or replace function public.restrict_application_access(event jsonb)
2
 returns jsonb
3
 language plpgsql
4
as $function$
5
declare
6
    authentication_method text;
7
    email_claim text;
8
    allowed_emails text[] := array['myemail@company.com', 'example@company.com'];
9
begin
10
    -- Extract email claim and authentication method
11
    email_claim = event->'claims'->>'email';
12
    authentication_method = event->'authentication_method';
13
    -- Authentication methods come double quoted (e.g. "otp")
14
    authentication_method = replace(authentication_method, '"', '');
15
16
    if email_claim ilike '%@supabase.io' or authentication_method = 'sso/saml' or email_claim = any(allowed_emails) then
17
        return event;
18
    end if;
19
20
    -- If none of the conditions are met, return an error
21
    return jsonb_build_object(
22
        'error', jsonb_build_object(
23
            'http_code', 403,
24
            'message', 'Staging access is only allowed to team members. Please use your @company.com account instead'
25
        )
26
    );
27
end;
28
$function$
29
;
30
-- manually added
31
grant execute
32
  on function public.restrict_application_access
33
  to supabase_auth_admin;
34
35
revoke execute
36
  on function public.restrict_application_access
37
  from authenticated, anon, public;

1
import { readAll } from 'https://deno.land/std/io/read_all.ts'
2
import * as base64 from 'https://denopkg.com/chiefbiiko/base64/mod.ts'
3
import { Webhook } from 'https://esm.sh/standardwebhooks@1.0.0'
4
5
Deno.serve(async (req) => {
6
  const payload = await req.text()
7
  const base64_secret = Deno.env.get('CUSTOM_ACCESS_TOKEN_SECRET').replace('v1,whsec_', '')
8
  const headers = Object.fromEntries(req.headers)
9
  const wh = new Webhook(base64_secret)
10
  try {
11
    const { user_id, claims, authentication_method } = wh.verify(payload, headers)
12
    if (claims.app_metadata && claims.app_metadata.role) {
13
      claims['role'] = claims.app_metadata.role
14
    }
15
    return new Response(
16
      JSON.stringify({
17
        claims,
18
      }),
19
      {
20
        status: 200,
21
        headers: {
22
          'Content-Type': 'application/json',
23
        },
24
      }
25
    )
26
  } catch (error) {
27
    return new Response(
28
      JSON.stringify({
29
        error: `Failed to process the request: ${error}`,
30
      }),
31
      {
32
        status: 500,
33
        headers: {
34
          'Content-Type': 'application/json',
35
        },
36
      }
37
    )
38
  }
39
})

1
import { readAll } from 'https://deno.land/std/io/read_all.ts'
2
import * as base64 from 'https://denopkg.com/chiefbiiko/base64/mod.ts'
3
import { Webhook } from 'https://esm.sh/standardwebhooks@1.0.0'
4
5
Deno.serve(async (req) => {
6
  const payload = await req.text()
7
  const base64_secret = Deno.env.get('CUSTOM_ACCESS_TOKEN_SECRET').replace('v1,whsec_', '')
8
  const headers = Object.fromEntries(req.headers)
9
  const wh = new Webhook(base64_secret)
10
  try {
11
    const { user_id, claims, authentication_method } = wh.verify(payload, headers)
12
13
    // Check the condition
14
    const allowedEmails = ['myemail@company.com', 'example@company.com']
15
    if (authentication_method === 'sso/saml' || allowedEmails.includes(claims.email)) {
16
      return new Response(
17
        JSON.stringify({
18
          claims,
19
        }),
20
        {
21
          status: 200,
22
          headers: {
23
            'Content-Type': 'application/json',
24
          },
25
        }
26
      )
27
    } else {
28
      return new Response(
29
        JSON.stringify({
30
          error: 'Unauthorized',
31
        }),
32
        {
33
          status: 500,
34
          headers: {
35
            'Content-Type': 'application/json',
36
          },
37
        }
38
      )
39
    }
40
  } catch (error) {
41
    return new Response(
42
      JSON.stringify({
43
        error: `Failed to process the request: ${error}`,
44
      }),
45
      {
46
        status: 500,
47
        headers: {
48
          'Content-Type': 'application/json',
49
        },
50
      }
51
    )
52
  }
53
})