Apache guacamole create user using postgres

Question

Im using apache guacamole latest from docker hub and postgres 12 as database, I want to create a login user using postgres but its not working.

This is how to create user from docs:

-- Generate salt
SET @salt = UNHEX(SHA2(UUID(), 256));

-- Create user and hash password with salt
INSERT INTO guacamole_user (username, password_salt, password_hash)
     VALUES ('myuser', @salt, UNHEX(SHA2(CONCAT('mypassword', HEX(@salt)), 256)));

But here the first command gives me an error :

guacamole_db=# SET @salt = UNHEX(SHA2(UUID(), 256));
ERROR:  syntax error at or near "@"
LINE 1: SET @salt = UNHEX(SHA2(UUID(), 256));
            ^

From docs this is how they created the defult user in postgres: "guacadmin"

INSERT INTO guacamole_entity (name, type) VALUES ('guacadmin', 'USER');
INSERT INTO guacamole_user (entity_id, password_hash, password_salt, password_date)
SELECT
    entity_id,
    decode('CA458A7D494E3BE824F5E1E175A1556C0F8EEF2C2D7DF3633BEC4A29C4411960', 'hex'),  -- 'guacadmin'
    decode('FE24ADC5E11E2B25288D1704ABE67A79E342ECC26064CE69C5B3177795A82264', 'hex'),
    CURRENT_TIMESTAMP
FROM guacamole_entity WHERE name = 'guacadmin' AND guacamole_entity.type = 'USER';

-- Grant admin permission to read/update/administer self
INSERT INTO guacamole_user_permission (entity_id, affected_user_id, permission)
SELECT guacamole_entity.entity_id, guacamole_user.user_id, permission::guacamole_object_permission_type
FROM (
    VALUES
        ('guacadmin', 'guacadmin', 'READ'),
        ('guacadmin', 'guacadmin', 'UPDATE'),
        ('guacadmin', 'guacadmin', 'ADMINISTER')
) permissions (username, affected_username, permission)
JOIN guacamole_entity          ON permissions.username = guacamole_entity.name AND guacamole_entity.type = 'USER'
JOIN guacamole_entity affected ON permissions.affected_username = affected.name AND guacamole_entity.type = 'USER'
JOIN guacamole_user            ON guacamole_user.entity_id = affected.entity_id;

How can i translate this to create new user, "test" with password "123456" and only READ premmision?

this is how the related tables looks like :

guacamole_entity :

 entity_id |   name    | type
-----------+-----------+------
         1 | guacadmin | USER

guacamole_user:

 user_id | entity_id |                           password_hash                            |                           password_salt                            |        pass
word_date         | disabled | expired | access_window_start | access_window_end | valid_from | valid_until | timezone | full_name | email_address | organization | organiza
tional_role
---------+-----------+--------------------------------------------------------------------+--------------------------------------------------------------------+------------
------------------+----------+---------+---------------------+-------------------+------------+-------------+----------+-----------+---------------+--------------+---------
------------
       1 |         1 | \xca458a7d494e3be824f5e1e175a1556c0f8eef2c2d7df3633bec4a29c4411960 | \xfe24adc5e11e2b25288d1704abe67a79e342ecc26064ce69c5b3177795a82264 | 2020-01-13
09:10:56.73947+00 | f        | f       |                     |                   |            |             |          |           |               |              |

guacamole_user_permission :

 entity_id | affected_user_id | permission
-----------+------------------+------------
         1 |                1 | READ
         1 |                1 | UPDATE
         1 |                1 | ADMINISTER

score 1 · Accepted Answer · answered Feb 20 '20 at 09:22

I ended up creating a user using POST request in python: First part is that we need to get a token for all the requests we want to send to guacamole api:

import requests

url = "http://guacamoleipornginx/api"
headers ={
    'Accept': 'application/json',
    'Content-Type': 'application/x-www-form-urlencoded'}
res = requests.post(url + "/tokens", headers=headers, data={'username': 'guacadmin', 'password': 'guacadmin'})
authToken = res.json()['authToken']

And with that token we can send a request for creating a user: fill USERNAME nad USERPASSWORD with your values.

headers ={
        'Accept': 'application/json',
        'Content-Type': 'application/json;charset=UTF-8'}

uri = "session/data/postgresql/users?token=" + authToken
data = {
        "username": $USERNAME,
        "password": $USERPASSWORD,
        "attributes":{"disabled":"","expired":"","access-window-start":"","access-window-end":"","valid-from":"","valid-until":"","timezone":None}}
res = requests.post(url + "/" + uri, headers=headers, json=data)

The last part here is giving the user you created above a permission to see a connection, you dont have to use it if you dont need to. for this request we will need connection id of a connection you created. my guacamole is docker guacamole that i got here: https://github.com/boschkundendienst/guacamole-docker-compose

And it uses nginx + gouacamole and postgreSql, so in the postgrs you can see connection id under guacamole_connection. and then you can add the connection premission to the created user :

uri = "session/data/postgresql/users/" + $USERNAME+ "/permissions?token=" + authToken
ID = 52 # here is the connection id of some connection created in guoacamole psql db.
data = [
            {"op":"add",
            "path": '/connectionPermissions/' + str(ID),
            "value":"READ"}]
res = requests.patch(url + "/" + uri, headers=headers, json=data)

You can add a connection also to guacamole using this method. you will need simply to open F12 and see the values it sends when it creats a connection and use it with the same methode above.

Ill add another cool thing - this is how you can see active connections :

url = "http://guacamoleipornginx/api"
uri = "/session/data/postgresql/activeConnections?token=" + authToken
res = requests.get(url + "/" + uri)

I hope this helps i had a lot of trouble getting all this information.

score 0 · Answer 2 · answered Jan 26 '20 at 19:07

The example you are referring to is made for MySql. The function for the generation of the password uses MySql specific functions, so you have to find appropriate replacements in PostgreSQL.

A quick search found the following potential replacements:

UNHEX - MySQL's HEX() and UNHEX() equivalent in Postgres?

SHA2 - probably some functions from pgcrypto module, https://www.postgresql.org/docs/8.3/pgcrypto.html

UUID - probably uuid_generate_v1() or uuid_generate_v4()

score 0 · Answer 3 · answered Aug 26 '20 at 16:28

As minokolic mentioned the example you are referring to is made for MySql, and theres no real need to do the same as MySql. Heres what I did in order to create a new user in PostgreSQL, from Python:

CREATE_USER = (
    "INSERT INTO guacamole_user "
    "(entity_id, password_hash, password_date) "
    "VALUES (%(entity_id)s, decode(%(pwd)s, 'hex'), CURRENT_TIMESTAMP)"
)

Use that TSQL query, notice that we use the decode function, and we removed the salt too, if you want to use the salt, you should also use decode in that column.

Then, hash your user password:

hashed_pwd = hashlib.sha256(
    user_password.encode('utf-8')
).hexdigest()

Side Note: if you want to use the salt, this should be concatenated to the password before the hash.

And then, just execute the cursor, for create the user:

cursor.execute(
    CREATE_USER, {'entity_id': entity_id, 'pwd': hashed_pwd},
)

And that should be enough.

Apache guacamole create user using postgres

3 Answers3