Thanks for your response Craig.
Python may be the next step, although there is a learning curve for us. I'll keep this handy.
Thanks
Jim
Sent: Thursday, January 13, 2022 at 11:46 AM
From: "Craig Sawyer" <csaw...@yumaunion.org>
To: user@guacamole.apache.org
Subject: Re: *LONG* Directly using SQL tables
From: "Craig Sawyer" <csaw...@yumaunion.org>
To: user@guacamole.apache.org
Subject: Re: *LONG* Directly using SQL tables
What we do, instead of altering the DB directly, is use the API.
There is some python code running around, like
https://github.com/pschmitt/guacapy
to make changes to Guac. I'm guessing that code will need to be
updated for 1.4.0 by adding
"Content-Type":"application/x-www-form-urlencoded" to the headers.
(This is not tested or checked, just based on my personal observations
with my API code)
-Craig
On Thu, Jan 13, 2022 at 9:38 AM Jim Rx <jimr...@mail.com> wrote:
>
>
> Johnathan,
>
> Thanks for your reply.
>
> I checked and you are right -- the import into the guacamole_entity table was successful, but there are no corresponding entries being created in the guacamole_user table.
>
> We are using LDAP, and we have the auto-create option "mysql-auto-create-accounts: true" configured. I assume that if the account already exists in the database, then it's a push.
>
> Since database authentication is "subordinate" to other authentication extensions, I think I'll try and add to the user table manually with unsalted gibberish passwords and see if it works.
>
> Jim
> Sent: Thursday, January 13, 2022 at 10:45 AM
> From: "Hankins, Jonathan" <jhank...@homewood.k12.al.us>
> To: user@guacamole.apache.org
> Subject: Re: *LONG* Directly using SQL tables
> I am using Postgres, but one thing I noticed that's different with my setup is I am not specifying the entity_id -- it is an auto-increment field in MySQL (serial in Postgres).
>
> I think you don't have anything in the guacamole_user table for the entities you are creating. Have a look at:
>
> https://guacamole.apache.org/doc/gug/jdbc-auth.html#users
>
> Also, since you are using LDAP for auth, you may be able to shortcut all of this, have a look at:
>
> https://guacamole.apache.org/doc/gug/jdbc-auth.html#auto-creating-database-users
>
>
>
>
> On Tue, Jan 11, 2022 at 11:09 AM Jim Rx <jimr...@mail.com> wrote:
>>
>> Greetings, I have a question about updating the database contents directly. Sorry for the length of the email, just trying to cover all the bases.
>>
>> Our environment is using Guacamole 1.3 on a dedicated VM (VMware ESXi, 4 CPU, 16Gb RAM), leveraging SQL and LDAP/Active Directory.
>>
>> We support a training company that will generally bring in 20 students at a time for class, several times over the course of a year.
>>
>> Right now, we use generic "Student123" accounts associated with prebuilt connections and everything runs great. But we want to move to having students use their named logins (which means we'll also have to remove those entries at the end of class). The process of getting everything set up is pretty manual, so I'm looking for a way to script everything.
>>
>> All authentication is pointed at our Windows Domain Controller using LDAP.
>>
>> I have used CSV files and SQL commands in the past to add new connections (entries into the connections and connection_parameter tables). Those always show up in the Web UI just fine. I have not had any luck adding entries into the table with user names (entities) or the table with connections (connection_permissions). The rows are added to the table, but do not appear in the Web UI (this is being done ahead of a class starting, so the users are in AD but they have never logged on any of our systems).
>>
>> When using this method, user login attempts fail with no apparent log entries (I think I checked them all... /var/etc/tomcat9/ catalina.out, localhost_access_log.YYYY-MM-DD.txt, localhost.YYYY-MM-DD.log; /var/log/syslog, /var/log/messages, /var/log/nginx/guac_access.log guac_error.log ).
>> If I only add the connection information (nothing in the entity or permissions tables) then the user can log in successfully, but I have to go back and manually add their connection information. I'd like to keep from doing things manually as possible.
>>
>> Below are the details of the configuration and the SQL commands I used in my attempts.
>>
>> Thank you
>>
>> Jim
>>
>> /etc/guacamole/guacamole.properties looks like this:
>> guacd-hostname: localhost
>> guacd-port: 4822
>> user-mapping: /etc/guacamole/user-mapping.xml
>> auth-provider: net.sourceforge.guacamole.net.auth.ldap.LDAPAuthenticationProvider
>>
>> # MySQL properties
>> mysql-hostname: localhost
>> mysql-port: 3306
>> mysql-database: guacamole_db
>> mysql-username: guacamole_user
>> mysql-password: $UPER$ecret
>> mysql-auto-create-accounts: true
>> # LDAP Properties
>> ldap-hostname: 10.0.0.10
>> ldap-port: 389
>> ldap-encryption-method: none
>> #ldap-user-base-dn: OU=them, DC=our, DC=net
>> ldap-user-base-dn: DC=our,DC=net
>> ldap-username-attribute: samAccountName
>> ldap-search-bind-dn: cn=SVC.GUACAMOLE, ou=Service Accounts, ou=Administration, dc=our, dc=net
>> ldap-search-bind-password: $UPER$ecret
>> #ldap-user-search-filter: (objectClass=user)(!(objectCategory=computer))
>> ldap-user-search-filter: (sAMAccountType=805306368)
>>
>> The process that I attempted to use was as follows:
>> Create the required CSV files-
>> NEW_STUDENT_CONNECTION.csv:
>> 284,NS-Linux-01,\N,rdp,\N,\N,\N,\N,\N,\N,0
>> 285,NS-Linux-02,\N,rdp,\N,\N,\N,\N,\N,\N,0
>> 286,NS-Linux-03,\N,rdp,\N,\N,\N,\N,\N,\N,0
>> 287,NS-Linux-04,\N,rdp,\N,\N,\N,\N,\N,\N,0
>> 314,NS-Windows-01,\N,rdp,\N,\N,\N,\N,\N,\N,0
>> 315,NS-Windows-02,\N,rdp,\N,\N,\N,\N,\N,\N,0
>> 316,NS-Windows-03,\N,rdp,\N,\N,\N,\N,\N,\N,0
>> 317,NS-Windows-04,\N,rdp,\N,\N,\N,\N,\N,\N,0
>>
>>
>> NEW_STUDENT_CONNECTION_PARAMETERS.csv:
>> 284,hostname,10.16.6.100
>> 284,ignore-cert,true
>> 284,port,3389
>> 285,hostname,10.16.6.101
>> 285,ignore-cert,true
>> 285,port,3389
>> 286,hostname,10.16.6.102
>> 286,ignore-cert,true
>> 286,port,3389
>> 287,hostname,10.16.6.103
>> 287,ignore-cert,true
>> 287,port,3389
>> 314,hostname,10.16.6.130
>> 314,ignore-cert,true
>> 314,port,3389
>> 315,hostname,10.16.6.131
>> 315,ignore-cert,true
>> 315,port,3389
>> 316,hostname,10.16.6.132
>> 316,ignore-cert,true
>> 316,port,3389
>> 317,hostname,10.16.6.133
>> 317,ignore-cert,true
>> 317,port,3389
>>
>>
>> NEW_STUDENT_ENTITY.csv:
>> 141,2299shenry,USER
>> 142,2299sjones,USER
>> 143,2299bsmith,USER
>> 144,2299gstetson,USER
>>
>>
>> NEW_STUDENT_CONNECTION_PERMISSIONS.csv:
>> 141,284,READ
>> 141,314,READ
>> 142,285,READ
>> 142,315,READ
>> 143,286,READ
>> 143,316,READ
>> 144,287,READ
>> 144,317,READ
>>
>> Once the files were in place on the server, I ran the following commands:
>>
>> mysql -u root
>> use guacamole_db;
>> LOAD DATA INFILE '/tmp/NEW_STUDENT_CONNECTION.csv' INTO TABLE guacamole_connection FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
>> LOAD DATA INFILE '/tmp/NEW_STUDENT_CONNECTION_PARAMETERS.csv' INTO TABLE guacamole_connection_parameter FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
>> LOAD DATA INFILE '/tmp/NEW_STUDENT_ENTITY.csv' INTO TABLE guacamole_entity FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
>> LOAD DATA INFILE '/tmp/NEW_STUDENT_CONNECTION_PERMISSIONS.csv' INTO TABLE guacamole_connection_permission FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
>> exit;
>>
>> --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscr...@guacamole.apache.org For additional commands, e-mail: user-h...@guacamole.apache.org
>
>
>
> --
> Jonathan Hankins
>
> Homewood City Schools
>
> W: 205-877-4548
>
> This e-mail is intended only for the recipient and may contain confidential or proprietary information. If you are not the intended recipient, the review, distribution, duplication or retention of this message and its attachments are prohibited. Please notify the sender of this error immediately by reply e-mail, and permanently delete this message and its attachments in any form in which they may have been preserved.
> --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscr...@guacamole.apache.org For additional commands, e-mail: user-h...@guacamole.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscr...@guacamole.apache.org
For additional commands, e-mail: user-h...@guacamole.apache.org
There is some python code running around, like
https://github.com/pschmitt/guacapy
to make changes to Guac. I'm guessing that code will need to be
updated for 1.4.0 by adding
"Content-Type":"application/x-www-form-urlencoded" to the headers.
(This is not tested or checked, just based on my personal observations
with my API code)
-Craig
On Thu, Jan 13, 2022 at 9:38 AM Jim Rx <jimr...@mail.com> wrote:
>
>
> Johnathan,
>
> Thanks for your reply.
>
> I checked and you are right -- the import into the guacamole_entity table was successful, but there are no corresponding entries being created in the guacamole_user table.
>
> We are using LDAP, and we have the auto-create option "mysql-auto-create-accounts: true" configured. I assume that if the account already exists in the database, then it's a push.
>
> Since database authentication is "subordinate" to other authentication extensions, I think I'll try and add to the user table manually with unsalted gibberish passwords and see if it works.
>
> Jim
> Sent: Thursday, January 13, 2022 at 10:45 AM
> From: "Hankins, Jonathan" <jhank...@homewood.k12.al.us>
> To: user@guacamole.apache.org
> Subject: Re: *LONG* Directly using SQL tables
> I am using Postgres, but one thing I noticed that's different with my setup is I am not specifying the entity_id -- it is an auto-increment field in MySQL (serial in Postgres).
>
> I think you don't have anything in the guacamole_user table for the entities you are creating. Have a look at:
>
> https://guacamole.apache.org/doc/gug/jdbc-auth.html#users
>
> Also, since you are using LDAP for auth, you may be able to shortcut all of this, have a look at:
>
> https://guacamole.apache.org/doc/gug/jdbc-auth.html#auto-creating-database-users
>
>
>
>
> On Tue, Jan 11, 2022 at 11:09 AM Jim Rx <jimr...@mail.com> wrote:
>>
>> Greetings, I have a question about updating the database contents directly. Sorry for the length of the email, just trying to cover all the bases.
>>
>> Our environment is using Guacamole 1.3 on a dedicated VM (VMware ESXi, 4 CPU, 16Gb RAM), leveraging SQL and LDAP/Active Directory.
>>
>> We support a training company that will generally bring in 20 students at a time for class, several times over the course of a year.
>>
>> Right now, we use generic "Student123" accounts associated with prebuilt connections and everything runs great. But we want to move to having students use their named logins (which means we'll also have to remove those entries at the end of class). The process of getting everything set up is pretty manual, so I'm looking for a way to script everything.
>>
>> All authentication is pointed at our Windows Domain Controller using LDAP.
>>
>> I have used CSV files and SQL commands in the past to add new connections (entries into the connections and connection_parameter tables). Those always show up in the Web UI just fine. I have not had any luck adding entries into the table with user names (entities) or the table with connections (connection_permissions). The rows are added to the table, but do not appear in the Web UI (this is being done ahead of a class starting, so the users are in AD but they have never logged on any of our systems).
>>
>> When using this method, user login attempts fail with no apparent log entries (I think I checked them all... /var/etc/tomcat9/ catalina.out, localhost_access_log.YYYY-MM-DD.txt, localhost.YYYY-MM-DD.log; /var/log/syslog, /var/log/messages, /var/log/nginx/guac_access.log guac_error.log ).
>> If I only add the connection information (nothing in the entity or permissions tables) then the user can log in successfully, but I have to go back and manually add their connection information. I'd like to keep from doing things manually as possible.
>>
>> Below are the details of the configuration and the SQL commands I used in my attempts.
>>
>> Thank you
>>
>> Jim
>>
>> /etc/guacamole/guacamole.properties looks like this:
>> guacd-hostname: localhost
>> guacd-port: 4822
>> user-mapping: /etc/guacamole/user-mapping.xml
>> auth-provider: net.sourceforge.guacamole.net.auth.ldap.LDAPAuthenticationProvider
>>
>> # MySQL properties
>> mysql-hostname: localhost
>> mysql-port: 3306
>> mysql-database: guacamole_db
>> mysql-username: guacamole_user
>> mysql-password: $UPER$ecret
>> mysql-auto-create-accounts: true
>> # LDAP Properties
>> ldap-hostname: 10.0.0.10
>> ldap-port: 389
>> ldap-encryption-method: none
>> #ldap-user-base-dn: OU=them, DC=our, DC=net
>> ldap-user-base-dn: DC=our,DC=net
>> ldap-username-attribute: samAccountName
>> ldap-search-bind-dn: cn=SVC.GUACAMOLE, ou=Service Accounts, ou=Administration, dc=our, dc=net
>> ldap-search-bind-password: $UPER$ecret
>> #ldap-user-search-filter: (objectClass=user)(!(objectCategory=computer))
>> ldap-user-search-filter: (sAMAccountType=805306368)
>>
>> The process that I attempted to use was as follows:
>> Create the required CSV files-
>> NEW_STUDENT_CONNECTION.csv:
>> 284,NS-Linux-01,\N,rdp,\N,\N,\N,\N,\N,\N,0
>> 285,NS-Linux-02,\N,rdp,\N,\N,\N,\N,\N,\N,0
>> 286,NS-Linux-03,\N,rdp,\N,\N,\N,\N,\N,\N,0
>> 287,NS-Linux-04,\N,rdp,\N,\N,\N,\N,\N,\N,0
>> 314,NS-Windows-01,\N,rdp,\N,\N,\N,\N,\N,\N,0
>> 315,NS-Windows-02,\N,rdp,\N,\N,\N,\N,\N,\N,0
>> 316,NS-Windows-03,\N,rdp,\N,\N,\N,\N,\N,\N,0
>> 317,NS-Windows-04,\N,rdp,\N,\N,\N,\N,\N,\N,0
>>
>>
>> NEW_STUDENT_CONNECTION_PARAMETERS.csv:
>> 284,hostname,10.16.6.100
>> 284,ignore-cert,true
>> 284,port,3389
>> 285,hostname,10.16.6.101
>> 285,ignore-cert,true
>> 285,port,3389
>> 286,hostname,10.16.6.102
>> 286,ignore-cert,true
>> 286,port,3389
>> 287,hostname,10.16.6.103
>> 287,ignore-cert,true
>> 287,port,3389
>> 314,hostname,10.16.6.130
>> 314,ignore-cert,true
>> 314,port,3389
>> 315,hostname,10.16.6.131
>> 315,ignore-cert,true
>> 315,port,3389
>> 316,hostname,10.16.6.132
>> 316,ignore-cert,true
>> 316,port,3389
>> 317,hostname,10.16.6.133
>> 317,ignore-cert,true
>> 317,port,3389
>>
>>
>> NEW_STUDENT_ENTITY.csv:
>> 141,2299shenry,USER
>> 142,2299sjones,USER
>> 143,2299bsmith,USER
>> 144,2299gstetson,USER
>>
>>
>> NEW_STUDENT_CONNECTION_PERMISSIONS.csv:
>> 141,284,READ
>> 141,314,READ
>> 142,285,READ
>> 142,315,READ
>> 143,286,READ
>> 143,316,READ
>> 144,287,READ
>> 144,317,READ
>>
>> Once the files were in place on the server, I ran the following commands:
>>
>> mysql -u root
>> use guacamole_db;
>> LOAD DATA INFILE '/tmp/NEW_STUDENT_CONNECTION.csv' INTO TABLE guacamole_connection FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
>> LOAD DATA INFILE '/tmp/NEW_STUDENT_CONNECTION_PARAMETERS.csv' INTO TABLE guacamole_connection_parameter FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
>> LOAD DATA INFILE '/tmp/NEW_STUDENT_ENTITY.csv' INTO TABLE guacamole_entity FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
>> LOAD DATA INFILE '/tmp/NEW_STUDENT_CONNECTION_PERMISSIONS.csv' INTO TABLE guacamole_connection_permission FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
>> exit;
>>
>> --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscr...@guacamole.apache.org For additional commands, e-mail: user-h...@guacamole.apache.org
>
>
>
> --
> Jonathan Hankins
>
> Homewood City Schools
>
> W: 205-877-4548
>
> This e-mail is intended only for the recipient and may contain confidential or proprietary information. If you are not the intended recipient, the review, distribution, duplication or retention of this message and its attachments are prohibited. Please notify the sender of this error immediately by reply e-mail, and permanently delete this message and its attachments in any form in which they may have been preserved.
> --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscr...@guacamole.apache.org For additional commands, e-mail: user-h...@guacamole.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscr...@guacamole.apache.org
For additional commands, e-mail: user-h...@guacamole.apache.org
