In https://guacamole.apache.org/doc/gug/jdbc-auth.html an sql fragment is given for MySQL but not for Postgres. In addition, the statement, “sha256 is not available in Postgres” is not true.
Feel free to use my sql fragment below to update the guacamole documentation. The difficulty actually arises that the Java tostring() returns uppercase hex text and the Postgres equivalent encode(value::bytea, ‘hex’) returns lowercase hex text. However, Postgres includes a upper(text) function also. I haven’t used SQL (never Postgres sql) in over 35 years so please ignore my ugly SQL. However, this has been tested and does work. --------------------------------------------------------------------------------------------------------------------------------- --generate salt SELECT digest(uuid_send(uuid_generate_v1()), 'sha256') INTO salt; --create temp table salt as select digest(uuid_send(uuid_generate_v1()), 'sha256'); -- add user -- Create base entity entry for user INSERT INTO guacamole_entity (name, type) VALUES (:GUAC_USERID, 'USER') RETURNING entity_id; -- Create user and hash password with salt INSERT INTO guacamole_user ( entity_id, password_salt, password_hash, password_date, full_name) WITH g_user AS (SELECT entity_id FROM guacamole_entity WHERE name = :GUAC_USERID AND type = 'USER'), g_salt AS (SELECT digest FROM salt) SELECT g_user.entity_id, g_salt.digest, digest(concat(:GUAC_PASSWD, upper(encode(g_salt.digest, 'hex'))), 'sha256'), clock_timestamp(), :GUAC_USERID FROM g_user, g_salt; DROP TABLE salt; ---------------------------------------------------------------------------------------------------------------------------------------------- Mark lidd
