Creating a Secure User Creation Function in PostgreSQL

In this article, we’ll explore how to create a secure user creation function in PostgreSQL that interacts with Supabase’s auth schema. We’ll discuss the implementation, usage, and important security considerations.

The Function

Here’s our create_user_with_metadata function, modified to be a security definer:

CREATE OR REPLACE FUNCTION public.create_user_with_metadata(
  email text,
  password text,
  user_meta_data jsonb
)
RETURNS uuid
SECURITY DEFINER
SET search_path = public
LANGUAGE plpgsql
AS $$
DECLARE
  user_id uuid;
  encrypted_pw text;
  app_metadata jsonb;
  confirmation timestamp;
  firstname text := user_meta_data->>'firstName';
  lastname text := user_meta_data->>'lastName';
BEGIN
  -- set variables
  user_id := gen_random_uuid();
  
  app_metadata := '{
    "provider": "email",
    "providers": [
      "email"
    ]
  }'::jsonb;
  
  IF TRUE THEN -- Replace with actual logic if needed
    confirmation := now();
  ELSE
    confirmation := null;
  END IF;
  
  -- Insert into auth.users
  INSERT INTO auth.users
    (instance_id, id, aud, role, email, encrypted_password, email_confirmed_at, recovery_sent_at, last_sign_in_at,
    confirmation_sent_at, raw_app_meta_data, raw_user_meta_data, created_at, updated_at, confirmation_token,
    email_change, email_change_token_new, recovery_token)
  VALUES 
    ('00000000-0000-0000-0000-000000000000', user_id, 'authenticated', 'authenticated', email, password,
    confirmation, confirmation, confirmation, confirmation, app_metadata, user_meta_data, now(), now(), '', '', '', '');
  
  -- Insert into auth.identities
  INSERT INTO auth.identities
    (provider_id, user_id, identity_data, provider, last_sign_in_at, created_at, updated_at)
  VALUES 
    (gen_random_uuid(), user_id, format('{"sub":"%s","email":"%s"}', user_id::text, email)::jsonb, 'email',
    now(), now(), now());
  
  -- Insert into public.profiles
  -- INSERT INTO public.profiles (id, "firstName", "lastName")
  -- VALUES (user_id, firstname, lastname);
  
  RETURN user_id;
END;
$$;

Usage Example

Here’s how you can use the function:

SELECT * FROM public.create_user_with_metadata(
  'test@email.com',
  '$2a$10$k5mT4h7brMq0rW59cYrhPOYaPh/VFhtZoJ1coHz.nWEtZ39HSDwb2',
  '{"firstName": "Test", "lastName": "User", "phone": "123456719", "email": "test@email.com"}'::jsonb
);

This will create a new user with the provided email, password (which should be pre-hashed), and metadata.

Security Considerations

1. SECURITY DEFINER: The SECURITY DEFINER clause means the function will execute with the privileges of the user who created it, not the user who calls it. This is necessary to allow the function to insert into the auth.users and auth.identities tables.

2. SET search_path: We’ve added SET search_path = public to ensure the function uses the intended schema, preventing potential hijacking.

3. Access Control: Ensure that only trusted roles have EXECUTE privileges on this function. You can restrict access like this:

   REVOKE ALL ON FUNCTION public.create_user_with_metadata(text, text, jsonb) FROM PUBLIC;
   GRANT EXECUTE ON FUNCTION public.create_user_with_metadata(text, text, jsonb) TO authenticated_role;
   

4. Input Validation: The function currently doesn’t validate inputs. Consider adding checks for email format, password strength, and metadata structure.

5. Password Hashing: The function assumes the password is pre-hashed. Ensure your application properly hashes passwords before calling this function.

6. Audit Logging: Consider adding audit logging to track when and by whom this function is called.

7. Deactivation: If you need to temporarily disable user creation, you can revoke execute privileges from all roles:

   REVOKE ALL ON FUNCTION public.create_user_with_metadata(text, text, jsonb) FROM PUBLIC;
   

Remember, exposing database functions, especially those with elevated privileges, always carries some risk. Regularly review your security settings and consider using application-level user management if possible.

By following these guidelines, you can create a more secure user creation process in your PostgreSQL database. Always stay vigilant and keep your security measures up to date!