Actually - I had the same need so I wrote this backend function to do just that:
$BODY$
DECLARE
v_rec RECORD;
salt_bytes bytea;
salt_str text;
combined text;
salt_kdf_str text;
hash TEXT;
BEGIN
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- Generate a 32-byte random salt
salt_str := (
SELECT string_agg(substring('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
(random() * 62 + 1)::int, 1), '')
FROM generate_series(1, 32)
);
-- Hash the combination of salt and password using sha256
combined := salt_str ||'Sup3rS3cr3tPa55W0rd';
hash := encode(digest(combined, 'sha256'), 'hex');
-- Generate a random 16-character salt_kdf (alphanumeric string)
salt_kdf_str := (
SELECT string_agg(substring('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
(random() * 62 + 1)::int, 1), '')
FROM generate_series(1, 16)
);
-- Create new user
INSERT INTO instance.login (name, salt, hash, salt_kdf, admin, no_auth, limited, active, date_favorites)
VALUES (v_new_username, salt_str, hash, salt_kdf_str, false, false, true, true, 0)
RETURNING * INTO v_rec;
-- Setting login settings for the new user
INSERT INTO instance.login_setting (login_id, dark, date_format, header_captions, hint_update_version, language_code, mobile_scroll_form, font_family, font_size, pattern, spacing, sunday_first_dow, warn_unsaved, tab_remember, login_template_id, borders_squared, color_classic_mode, color_header, color_menu, color_header_single, header_modules, list_colored, list_spaced, number_sep_decimal, number_sep_thousand, bool_as_icon, form_actions_align, shadows_inputs)
VALUES (v_rec.id, false, 'd/m/Y', true, 0, 'en_us', true, 'helvetica', 100, 'cubes', 3, false, true, true, NULL, false, false, '30530F', '30530F', false, true, true, false, '.', ',', true, 'center', true);
-- Assigning role to the new user
INSERT INTO instance.login_role (login_id, role_id)
VALUES (v_rec.id, '7243e54d-e584-45de-9f01-0f82886d5914'::UUID);
RETURN v_rec.id; -- This is to satisfy the function structure for REI3 backend
END;
$BODY$
I made it callable from the frontend and it has the argument v_new_username TEXT
Hope this helps ;)
