Guacamole SQL queries...

[B3r3n <B3...@argosnet.com>]

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.

Thanks in advance.

Brgrds

Re: Guacamole SQL queries...

[Adam Thorn <al...@cam.ac.uk>]

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

Re: Guacamole SQL queries...

[Nick Couchman <vn...@apache.org>]

On Tue, Jan 8, 2019 at 3:13 AM B3r3n <B3...@argosnet.com> 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.
>

Are you saying you want to import a list of usernames and passwords into
the Guacamole database?  This should be doable, but you should read this
section of the manual and understand the database schema:

http://guacamole.apache.org/doc/gug/jdbc-auth.html#jdbc-auth-schema


>
> 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.
>
>
I'm not sure exactly what you're asking, here.  The SQL queries are not
listed explicitly anywhere in the code - we use the MyBatis framework for
abstracting the interaction with the database, so it is listed in the XML
files.  The closest you can find to the actual query would be the various
mapping files that map database fields to the Java objects that are used
within Guacamole.  For example:

https://github.com/apache/guacamole-client/blob/master/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml

However, based on what you're asking, there isn't any one query that
accomplishes either of the items above - there are multiple SQL queries
that make it happen.  They can be seen or derived from those mapper files.

-Nick