Apache guacamole create user using postgres

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

Solution

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.