I think you are missing "it". The point of my email was to offer: 1) working Postgres code for password hashing for Guac 2) describe that Java tostring() function returns uppercase hex and the analogous Postgres encode() function returns lowercase hex which needs to converted to uppercase 3) the documentation says, "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)." The phrase "you will need", at least, to me indicates that "maybe" is not an option to calculate the sha256 digest. The statement might be clearer as: " 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)."
Either way, Guacamole remains a great product.. -----Original Message----- From: Nick Couchman <[email protected]> Sent: Tuesday, November 15, 2022 9:58 AM To: [email protected] Subject: Re: Working Postgres sql fragment for updating password hash manually that works for documentation update and documentation correction 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
