does this extension allow user to register self?

[matart15]

Problem

I want to use supabase built in email_confirmation + phone_confirmation So i allowed my users to call signup function from frontend

Flow is

1. user sign up
2. user confirm email + phone
3. user update profile information ( here error happens. because profile table have a RLS. but user have no app_metadata.group )

Supabase signup does not have option to write app_metadata https://supabase.com/docs/reference/javascript/auth-signup

Because app_metadata should be edited by admins

This creates user with no group data. which is fine. my app will treat that user as a general group user. ( user without group )

But this line try to read group field into request.groups

then later try to use that as json which results error invalid input syntax for type json.

I tried this

create or replace function public.get_user_claims () 
returns jsonb 
language plpgsql  -- Change language to PLPGSQL
stable 
set search_path = public 
as $function$
declare
  request_groups jsonb;
  app_metadata jsonb;
begin
  RAISE LOG 'request.groups: asdf_111';
  RAISE LOG 'request.groups (raw): %', current_setting('request.groups', true);
  RAISE LOG 'request.groups (is NULL): %', current_setting('request.groups', true) IS NULL;
  RAISE LOG 'request.groups (empty string): %', current_setting('request.groups', true) = '';

  -- Retrieve the value of 'request.groups' if available
  request_groups := current_setting('request.groups', true)::text;
  RAISE LOG 'request.groups: asdf_222';
  RAISE LOG 'request.groups: request_groups: %', request_groups;

  -- Retrieve the app_metadata from the JWT token
  app_metadata := auth.jwt()->'app_metadata';

  -- Log the values for debugging
  RAISE LOG 'request.groups: %', request_groups;
  RAISE LOG 'auth.jwt()->app_metadata: %', app_metadata;

  -- Return the result based on existing logic
  return coalesce(request_groups::json, app_metadata->'groups');
end;
$function$;

which results

2024-11-11 17:25:23 172.19.0.9 2024-11-11 08:25:23.133 UTC [2664] authenticator@postgres LOG:  request.groups (raw): 
2024-11-11 17:25:23 172.19.0.9 2024-11-11 08:25:23.133 UTC [2664] authenticator@postgres CONTEXT:  PL/pgSQL function get_user_claims() line 7 at RAISE
2024-11-11 17:25:23     SQL statement "SELECT public.get_user_claims()"
2024-11-11 17:25:23 172.19.0.9 2024-11-11 08:25:23.133 UTC [2664] authenticator@postgres STATEMENT:  WITH pgrst_source AS (UPDATE "public"."u_profiles" SET "avatar_url" = "pgrst_body"."avatar_url", "created_at" = "pgrst_body"."created_at", "date_of_birth" = "pgrst_body"."date_of_birth", "email" = "pgrst_body"."email", "first_name" = "pgrst_body"."first_name", "group_id" = "pgrst_body"."group_id", "id" = "pgrst_body"."id", "last_name" = "pgrst_body"."last_name", "location" = "pgrst_body"."location", "occupation" = "pgrst_body"."occupation", "phone_number" = "pgrst_body"."phone_number", "review_permission" = "pgrst_body"."review_permission", "status" = "pgrst_body"."status" FROM (SELECT $1 AS json_data) pgrst_payload, LATERAL (SELECT "avatar_url", "created_at", "date_of_birth", "email", "first_name", "group_id", "id", "last_name", "location", "occupation", "phone_number", "review_permission", "status" FROM json_to_record(pgrst_payload.json_data) AS _("avatar_url" text, "created_at" timestamp with time zone, "date_of_birth" date, "email" text, "first_name" text, "group_id" uuid, "id" uuid, "last_name" text, "location" text, "occupation" text, "phone_number" text, "review_permission" text, "status" text) ) pgrst_body  WHERE  "public"."u_profiles"."id" = $2 RETURNING 1) SELECT '' AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total, array[]::text[] AS header, ''::text AS body, nullif(current_setting('response.headers', true), '') AS response_headers, nullif(current_setting('response.status', true), '') AS response_status, '' AS response_inserted FROM (SELECT * FROM pgrst_source) _postgrest_t
2024-11-11 17:25:23 172.19.0.9 2024-11-11 08:25:23.133 UTC [2664] authenticator@postgres LOG:  request.groups (is NULL): f
2024-11-11 17:25:23 172.19.0.9 2024-11-11 08:25:23.133 UTC [2664] authenticator@postgres CONTEXT:  PL/pgSQL function get_user_claims() line 8 at RAISE
2024-11-11 17:25:23     SQL statement "SELECT public.get_user_claims()"
2024-11-11 17:25:23 172.19.0.9 2024-11-11 08:25:23.133 UTC [2664] authenticator@postgres STATEMENT:  WITH pgrst_source AS (UPDATE "public"."u_profiles" SET "avatar_url" = "pgrst_body"."avatar_url", "created_at" = "pgrst_body"."created_at", "date_of_birth" = "pgrst_body"."date_of_birth", "email" = "pgrst_body"."email", "first_name" = "pgrst_body"."first_name", "group_id" = "pgrst_body"."group_id", "id" = "pgrst_body"."id", "last_name" = "pgrst_body"."last_name", "location" = "pgrst_body"."location", "occupation" = "pgrst_body"."occupation", "phone_number" = "pgrst_body"."phone_number", "review_permission" = "pgrst_body"."review_permission", "status" = "pgrst_body"."status" FROM (SELECT $1 AS json_data) pgrst_payload, LATERAL (SELECT "avatar_url", "created_at", "date_of_birth", "email", "first_name", "group_id", "id", "last_name", "location", "occupation", "phone_number", "review_permission", "status" FROM json_to_record(pgrst_payload.json_data) AS _("avatar_url" text, "created_at" timestamp with time zone, "date_of_birth" date, "email" text, "first_name" text, "group_id" uuid, "id" uuid, "last_name" text, "location" text, "occupation" text, "phone_number" text, "review_permission" text, "status" text) ) pgrst_body  WHERE  "public"."u_profiles"."id" = $2 RETURNING 1) SELECT '' AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total, array[]::text[] AS header, ''::text AS body, nullif(current_setting('response.headers', true), '') AS response_headers, nullif(current_setting('response.status', true), '') AS response_status, '' AS response_inserted FROM (SELECT * FROM pgrst_source) _postgrest_t
2024-11-11 17:25:23 172.19.0.9 2024-11-11 08:25:23.133 UTC [2664] authenticator@postgres LOG:  request.groups (empty string): t
2024-11-11 17:25:23 172.19.0.9 2024-11-11 08:25:23.133 UTC [2664] authenticator@postgres CONTEXT:  PL/pgSQL function get_user_claims() line 9 at RAISE
2024-11-11 17:25:23     SQL statement "SELECT public.get_user_claims()"
2024-11-11 17:25:23 172.19.0.9 2024-11-11 08:25:23.133 UTC [2664] authenticator@postgres STATEMENT:  WITH pgrst_source AS (UPDATE "public"."u_profiles" SET "avatar_url" = "pgrst_body"."avatar_url", "created_at" = "pgrst_body"."created_at", "date_of_birth" = "pgrst_body"."date_of_birth", "email" = "pgrst_body"."email", "first_name" = "pgrst_body"."first_name", "group_id" = "pgrst_body"."group_id", "id" = "pgrst_body"."id", "last_name" = "pgrst_body"."last_name", "location" = "pgrst_body"."location", "occupation" = "pgrst_body"."occupation", "phone_number" = "pgrst_body"."phone_number", "review_permission" = "pgrst_body"."review_permission", "status" = "pgrst_body"."status" FROM (SELECT $1 AS json_data) pgrst_payload, LATERAL (SELECT "avatar_url", "created_at", "date_of_birth", "email", "first_name", "group_id", "id", "last_name", "location", "occupation", "phone_number", "review_permission", "status" FROM json_to_record(pgrst_payload.json_data) AS _("avatar_url" text, "created_at" timestamp with time zone, "date_of_birth" date, "email" text, "first_name" text, "group_id" uuid, "id" uuid, "last_name" text, "location" text, "occupation" text, "phone_number" text, "review_permission" text, "status" text) ) pgrst_body  WHERE  "public"."u_profiles"."id" = $2 RETURNING 1) SELECT '' AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total, array[]::text[] AS header, ''::text AS body, nullif(current_setting('response.headers', true), '') AS response_headers, nullif(current_setting('response.status', true), '') AS response_status, '' AS response_inserted FROM (SELECT * FROM pgrst_source) _postgrest_t
2024-11-11 17:25:23 172.19.0.9 2024-11-11 08:25:23.134 UTC [2664] authenticator@postgres ERROR:  invalid input syntax for type json
2024-11-11 17:25:23 172.19.0.9 2024-11-11 08:25:23.134 UTC [2664] authenticator@postgres DETAIL:  The input string ended unexpectedly.
2024-11-11 17:25:23 172.19.0.9 2024-11-11 08:25:23.134 UTC [2664] authenticator@postgres CONTEXT:  JSON data, line 1: 

request.groups: asdf_222 is not printed

looks like it save empty string in request.groups (empty string): t

Question

How this library intend to use groupless users or supabase built in signup function?

[matart15]

For now I am owerwriting db_pre_request function like this

-- Step 2: Override the db_pre_request function
create or REPLACE FUNCTION public.db_pre_request () returns void language plpgsql stable security definer
set
  search_path = public as $$
declare
    groups jsonb;
    default_group_id uuid;
begin
    -- get current groups from auth.users
    select raw_app_meta_data->'groups' from auth.users into groups where id = auth.uid();

    -- Check if groups is null or empty, and set to empty array if true
    if groups is null or groups = '[]'::jsonb then
        groups := '[]'::jsonb; -- set to empty JSON array
    end if;

    -- store it in the request object
    perform set_config('request.groups'::text, groups::text, false /* applies to transaction if true, session if false */);
end;
$$;