On Tue, Nov 15, 2022 at 8:06 AM Mark Li <[email protected]> wrote: > > 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.
I don't see that statement anywhere on that page - I searched for "sha256" and "sha2" on the page, and the only statement that I see that remotely resembles that is: "If you are not using MySQL, or you are using a version of MySQL that lacks the SHA2 function, you will need to calculate the SHA-256 value manually (by using the sha256sum command, for example)." But that doesn't say that sha256 is unavailable in PostgreSQL, it just says that databases other than MySQL may not have the "SHA2" function. Maybe you can clarify where you see that statement? It's quite possible I'm just missing it... > > 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; > You're definitely welcome to contribute this to the documentation - I think having alternatives in there for other databases would be a great idea, particularly as a user of PostgreSQL I like having those options. -Nick
