Impersonate in SQL (Become an app user)

[ozum]

We needed impersonation for testing and development in an SQL IDE such as pgAdmin. I implemented an SQL procedure, I would like to share. Maybe someone can use it too. I'm open to suggestions.

SQL Procedure

CREATE OR REPLACE PROCEDURE private."becomeUser" (
  "pUid" uuid,
  "pLocal" boolean = true
)
AS
$body$
DECLARE
  vClaims TEXT;
  vRole VARCHAR(255);
  vId UUID;
BEGIN

  -- Fetch user data. In the web app, this data comes from a JWT token.
  SELECT id, role, FORMAT('{ "aud": "%s", "exp": "%s", "sub": "%s", "email": "%s", "app_metadata": %s, "user_metadata": %s, "role": "%s" }', aud, extract(epoch from "pExp")::INT, id, email, raw_app_meta_data, raw_user_meta_data, role)
    INTO vId, vRole, vClaims FROM auth.users WHERE id = "pUid";
  
  IF vId IS NULL THEN RAISE EXCEPTION 'Cannot become user. User not found: %.', "pUid"; END IF;
  
  -- Add user data to the settings.
  PERFORM set_config('request.jwt.claims', vClaims, "pLocal");
  
  -- Set user role.
  IF "pLocal" THEN EXECUTE FORMAT('set local role %I', vRole);
  ELSE EXECUTE FORMAT('set role %I', vRole); END IF;

END;
$body$
LANGUAGE 'plpgsql'
SECURITY INVOKER;

COMMENT ON PROCEDURE private."becomeUser"("pUid" uuid, "pLocal" boolean)
IS '
Sets your role and JWT data as if you are the given user. By default changes
are local, but can be made non-local by providing false to vlocal parameter.

You can act on behalf of the user. This function is helpful to test user''s data
in an SQL editor such as pgAdmin.

Usage Example:

BEGIN;
  CALL private."becomeUser"(''00000000-0000-0000-0000-000000000011'');
  SELECT auth.uid();
COMMIT;
';

ALTER PROCEDURE private."becomeUser" ("pUid" uuid, "pLocal" boolean)
  OWNER TO postgres;

Usage

BEGIN;
  CALL private."becomeUser"('00000000-0000-0000-0000-000000000011');
  SELECT auth.uid();
COMMIT;

EDIT: I fixed the procedure from time to time.