On Tue, Apr 3, 2018 at 1:45 PM, Mike Jumper <[email protected]> wrote:
> Hi all, > > I'm currently mid-refactor [1] as I propagate the schema changes for user > groups [2] through the database auth. Before I get too far with this, I > wanted to run the current changes by everyone to make sure things are sane. > > Overall, user groups are represented by the usual set of tables that each > object has within the database schema: > > - guacamole_user_group > - guacamole_user_group_permission > - guacamole_user_group_attribute > > In addition, to link a user group with its set of member users/groups, we > have: > > - guacamole_user_group_member > > As permissions can now be granted to both users and groups, rather than > double the number of permissions tables, I chose to create a base table > common to both users and groups: > > - guacamole_entity > > This table contains simply an integer primary key, a type (either 'USER' or > 'USER_GROUP'), and a name (which must be unique across all entities with > the same type). The guacamole_user and guacamole_user_group tables have a > foreign key into this table, and thus other relations can either be > type-specific (require strictly a user or a group), or can point to both > (this is also why we can have a single "guacamole_user_group_member" table > and not one for user members and another for group members). > > For most queries, this makes little difference, however: > > - Creating a user (or a group) will require two inserts: one for the > entity, and another for the user/group > - Deleting a user/group will involve deleting the entity (and cascading the > delete to the user/group) > - Retrieving the name of a user/group will always involve a join against > the entity table > > The WIP schema changes themselves (I'm working strictly with Postgres until > everything is set in stone): > > https://github.com/mike-jumper/guacamole-client/commit/ > d9afe8a3370ea13228cb8fe001b072648526f66d > > Seem sane so far? > > The schema itself sounds reasonable, my one concern is in the upgrade process for a database that has existing data in it and that needs to move from one schema version to the next. This is going to require inserting the existing guacamole_user objects into the guacamole_entity table and then adding the foreign key constraint to the guacamole_user table. Have you done much in the way of testing that upgrade process? Also, aside from just the back-end logistics of how to redo the schema while maintaining existing data, are there any gotchas that people will need to be aware of once that schema change takes place in how that is shown on the front-end? That is, if I'm running 0.9.14, and upgrade to 1.0.0 (assuming this is in 1.0.0), and I apply the schema update, update my client and server, etc., will I be able to continue to use all of the existing stuff configured in Guacamole prior to the upgrade without making additional changes in the admin UI (e.g. re-assigning permissions, etc.)? Other than making sure the upgrade process is sorted out, it sounds like a great approach to me! Oh, one other thing, presumably after the JDBC module changes are done we will have to go back to some of the other modules (LDAP, primarily, I would guess) and make some changes, there, correct? Are those in scope of what you're doing here for this particular JIRA issue, or would you consider adding group support to the LDAP module a separate issue? -Nick
