On 08/01/2019 08:13, B3r3n wrote:
Hello guys,
First of all, Happy New Year 2019 to all !
This question is for Guacamole SQL wizards :-)
We have a central password repository and this must be imported into Guacamole
to avoid manual action. I failed to find some howto explaining the SQL
process.
Can someone please enlight me about :
1- the SQL Query to detect/validate user's permissions, and get target details
(check it matches application)
2- the SQL Query to create connections and associate them with a user.
One important caveat with what I'm about to say: all the queries are
correct for my version of guacamole, but may differ in other versions
(and the database schema may change in the future too, so you'd have to
review your queries when updating guacamole).
I use LDAP for authentication and SQL for storing connection info etc. I
have an external database which contains enough information for me to
answer the question "should user X be allowed to connect to computer
Y?", so the way I manage things is via a webpage (php) where:
1) user logs in to webpage. Because I'm using LDAP for authentication, I
need to ensure there's an entry for the user in the guacamole_user table:
insert into guacamole_user (username, password_hash, password_date)
values ($1, $2, $3)
where I generate a random password_hash because it's never going to
actually be used in my case. If you used the SQL extension for user
authentication too this step would be rather different, I assume!
2) user is presented with a list of guacamole connections they already
have access to:
select connection_name from guacamole_connection natural join
guacamole_connection_permission natural join guacamole_user where
username=$1 and permission='READ' order by connection_name
3) user is given the option to add a new guacamole connection, based on
a check against the out-of-band database I mentioned. This involves
first checking if a suitable row exists in the guacamole_connection
table (NB my convention is that the connection_name field is always the
fully qualified hostname):
select connection_id from guacamole_connection where connection_name=$1
3a) if the guacamole_connection row does not exist, I create a new one:
insert into guacamole_connection (connection_name, protocol) values ($1,
'rdp')
(as you can see, I'm only interested in the rdp protocol - extending
that is left as an exercise!). After inserting that row, you'll need to
set some guacamole_connection_parameter rows, so first you need the
connection_id of the guacamole_connection you just inserted (via the
query in (3) above). My PHP is then
$rdp_params['port'] = '3389';
$rdp_params['hostname'] = $hostname;
# NB I only use guacamole to connect to the local network where I'm
sufficiently trusting to set....
$rdp_params['ignore-cert'] = 'true';
# pass through username/password used to login to guacamole
$rdp_params['username'] = '${GUAC_USERNAME}';
$rdp_params['password'] = '${GUAC_PASSWORD}';
$rdp_params['domain'] = 'MY_DOMAIN';
$rdp_params['security'] = 'nla';
$rdp_params['resize-method'] = 'display-update';
foreach($rdp_params as $k=>$v) {
$sql = "insert into guacamole_connection_parameter (connection_id,
parameter_name, parameter_value) values ($1, $2, $3)";
pg_query_params($guac_dbh, $sql, array($connection_id, $k, $v));
}
but obviously you might want to set different parameters. My methodology
was just to configure a connection through the normal guacamole web
interface and then query the database to see what names/values it had
inserted into guacamole_connection_parameter.
4) lookup the user's ID
select user_id from guacamole_user where username=$1
and grant them permission to use the guacamole_connection
insert into guacamole_connection_permission (user_id, connection_id,
permission) values ($1, $2, 'READ')
5) I also let users delete their own connection permissions
delete from guacamole_connection_permission where user_id=$1 and
connection_id=$2
I don't delete the row from guacamole_connection because other users may
have access to the connection - in principle it wouldn't be hard to
check that by querying guacamole_connection, but I'm not concerned about
leaving behind possibly unused rows in that table.
PS one design detail in all this is that, in my environment, there are
potentially numerous hosts that a user is permitted to rdp to but they
don't want to in practice. I considered populating guacamole_connection
with all valid rdp hosts, and guacamole_connection_permission with all
possible rows (i.e. having steps 2-5 be automatic rather than driven by
user options). But in practice, only a very small fraction of the
possible connection permissions are needed/used, so I went for this
approach so as to not overwhelm users with a long list of connection
options.
Regards,
Adam
