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
