postgresql Apache guacamole create user using postgres

92vpleto  于 5个月前  发布在  PostgreSQL
关注(0)|答案(4)|浏览(98)

我使用apache guacamole最新从docker hub和postgres 12作为数据库,我想创建一个登录用户使用postgres,但它不工作。
下面是如何从文档创建用户:

-- 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)));

字符串
但是这里第一个命令给了我一个错误:

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


在docs中,他们是这样在postgres中创建defult用户的:“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;


我怎么能翻译这创建新用户,“测试”与密码“123456”,只有读premmision?
下面是相关表的样子:

鳄梨酱_实体:

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

0mkxixxg

0mkxixxg1#

我最终在python中使用POST请求创建了一个用户:第一部分是我们需要为我们想要发送到guacamole API的所有请求获取一个令牌:
导入请求

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']

字符串
使用该令牌,我们可以发送创建用户的请求:使用您的值填充USERNAME和USERPASSWORD。

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)


这里的最后一部分是给你上面创建的用户一个查看连接的权限,如果你不需要,你不必使用它。对于这个请求,我们需要你创建的连接的连接id。我的guacamole是我在这里得到的docker guacamole:https://github.com/boschkundendienst/guacamole-docker-compose
它使用nginx + gouacamole和postgreSql,所以在postgrs中你可以看到guacamole_connection下的连接id。然后你可以添加连接premission到创建的用户:

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)


你也可以使用这个方法添加一个连接到鳄梨酱。你只需要打开F12,看到它在创建连接时发送的值,并使用上面相同的方法。
我会添加另一个很酷的东西-这是你可以看到活动连接的方式:

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


我希望这有助于我有很多麻烦得到所有这些信息。

mbzjlibv

mbzjlibv2#

我从官方的PostgreSQL init脚本中使用了它,它可以工作

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 this user all system permissions
INSERT INTO guacamole_system_permission (entity_id, permission)
SELECT entity_id, permission::guacamole_system_permission_type
FROM (
    VALUES
        ('guacadmin', 'CREATE_CONNECTION'),
        ('guacadmin', 'CREATE_CONNECTION_GROUP'),
        ('guacadmin', 'CREATE_SHARING_PROFILE'),
        ('guacadmin', 'CREATE_USER'),
        ('guacadmin', 'CREATE_USER_GROUP'),
        ('guacadmin', 'ADMINISTER')
) permissions (username, permission)
JOIN guacamole_entity ON permissions.username = guacamole_entity.name 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;

字符串
Ps.我被卡住了,我删除了正常的用户,并使用openID(这是不工作),我需要一个肮脏的方式进入,所以我再次创建了这个用户,请检查所有数据都在数据库内玩之前.

pbossiut

pbossiut3#

你所提到的例子是为MySql做的。密码的生成函数使用了MySql特定的函数,所以你必须在PostgreSQL中找到合适的替代品。
快速搜索发现了以下潜在的替代品:
UNHEX - MySQL's HEX() and UNHEX() equivalent in Postgres?
SHA 2-可能是pgcrypto模块的一些函数,https://www.postgresql.org/docs/8.3/pgcrypto.html
UUID -可能是uuid_generate_v1()或uuid_generate_v4()

4ktjp1zp

4ktjp1zp4#

正如minokolic提到的,你所提到的例子是为MySql制作的,没有真实的需要像MySql那样做。下面是我从Python在PostgreSQL中创建一个新用户所做的:

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

字符串
使用TSQL查询,注意我们使用了decode函数,我们也删除了salt,如果你想使用salt,你也应该在该列中使用decode
然后,散列您的用户密码:

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

  • 边注 *:如果你想使用盐,这应该是连接到密码之前的哈希。

然后,只需执行光标,创建用户:

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


这就足够了

相关问题