Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE SCHEMA auth;

CREATE TABLE auth.users (
  id UUID PRIMARY KEY
);

CREATE TABLE public.profiles (
  id UUID NOT NULL  PRIMARY KEY REFERENCES auth.users,
  ROLE text
);

CREATE FUNCTION public.handle_new_user()
  RETURNS trigger
  LANGUAGE plpgsql SECURITY DEFINER AS
$func$
BEGIN
  INSERT INTO public.profiles (id, role)
  VALUES (NEW.id, 'BASE_USER');
  RETURN NEW;
END
$func$;

CREATE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE PROCEDURE public.handle_new_user();

CREATE SCHEMA

CREATE TABLE

CREATE TABLE

CREATE FUNCTION

CREATE TRIGGER

INSERT INTO auth.users VALUES ('4c35abffe1cec5e9b16189fc0ebff34e');

INSERT 0 1

TABLE  public.profiles;

id
role

4c35abff-e1ce-c5e9-b161-89fc0ebff34e
BASE_USER

SELECT 1