GUACAMOLE-220: Define base schema for user groups.
Project: http://git-wip-us.apache.org/repos/asf/guacamole-client/repo Commit: http://git-wip-us.apache.org/repos/asf/guacamole-client/commit/e72f88fe Tree: http://git-wip-us.apache.org/repos/asf/guacamole-client/tree/e72f88fe Diff: http://git-wip-us.apache.org/repos/asf/guacamole-client/diff/e72f88fe Branch: refs/heads/staging/1.0.0 Commit: e72f88febff5766345fa4a8f9086639ea997a2e9 Parents: d23f88f Author: Michael Jumper <mjum...@apache.org> Authored: Fri Mar 2 14:45:33 2018 -0800 Committer: Michael Jumper <mjum...@apache.org> Committed: Wed Sep 19 23:56:51 2018 -0700 ---------------------------------------------------------------------- .../schema/001-create-schema.sql | 233 +++++++++--- .../schema/002-create-admin-user.sql | 27 +- .../schema/upgrade/upgrade-pre-1.0.0.sql | 357 +++++++++++++++++++ 3 files changed, 558 insertions(+), 59 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/e72f88fe/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql index ddd3566..9bcf1c5 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql @@ -27,6 +27,15 @@ CREATE TYPE guacamole_connection_group_type AS ENUM( ); -- +-- Entity types +-- + +CREATE TYPE guacamole_entity_type AS ENUM( + 'USER', + 'USER_GROUP' +); + +-- -- Object permission types -- @@ -46,6 +55,7 @@ CREATE TYPE guacamole_system_permission_type AS ENUM( 'CREATE_CONNECTION_GROUP', 'CREATE_SHARING_PROFILE', 'CREATE_USER', + 'CREATE_USER_GROUP', 'ADMINISTER' ); @@ -132,6 +142,26 @@ CREATE INDEX guacamole_connection_parent_id ON guacamole_connection(parent_id); -- +-- Table of base entities which may each be either a user or user group. Other +-- tables which represent qualities shared by both users and groups will point +-- to guacamole_entity, while tables which represent qualities specific to +-- users or groups will point to guacamole_user or guacamole_user_group. +-- + +CREATE TABLE guacamole_entity ( + + entity_id serial NOT NULL, + name varchar(128) NOT NULL, + type guacamole_entity_type NOT NULL, + + PRIMARY KEY (entity_id), + + CONSTRAINT guacamole_entity_name_scope + UNIQUE (type, name) + +); + +-- -- Table of users. Each user has a unique username and a hashed password -- with corresponding salt. Although the authentication system will always set -- salted passwords, other systems may set unsalted passwords by simply not @@ -141,9 +171,9 @@ CREATE INDEX guacamole_connection_parent_id CREATE TABLE guacamole_user ( user_id serial NOT NULL, + entity_id integer NOT NULL, - -- Username and optionally-salted password - username varchar(128) NOT NULL, + -- Optionally-salted password password_hash bytea NOT NULL, password_salt bytea, password_date timestamptz NOT NULL, @@ -171,8 +201,62 @@ CREATE TABLE guacamole_user ( PRIMARY KEY (user_id), - CONSTRAINT username - UNIQUE (username) + CONSTRAINT guacamole_user_single_entity + UNIQUE (entity_id), + + CONSTRAINT guacamole_user_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) + ON DELETE CASCADE + +); + +-- +-- Table of user groups. Each user group may have an arbitrary set of member +-- users and member groups, with those members inheriting the permissions +-- granted to that group. +-- + +CREATE TABLE guacamole_user_group ( + + user_group_id serial NOT NULL, + entity_id integer NOT NULL, + + -- Group disabled status + disabled boolean NOT NULL DEFAULT FALSE, + + PRIMARY KEY (user_group_id), + + CONSTRAINT guacamole_user_group_single_entity + UNIQUE (entity_id), + + CONSTRAINT guacamole_user_group_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) + ON DELETE CASCADE + +); + +-- +-- Table of users which are members of given user groups. +-- + +CREATE TABLE guacamole_user_group_member ( + + user_group_id integer NOT NULL, + member_entity_id integer NOT NULL, + + PRIMARY KEY (user_group_id, member_entity_id), + + -- Parent must be a user group + CONSTRAINT guacamole_user_group_member_parent + FOREIGN KEY (user_group_id) + REFERENCES guacamole_user_group (user_group_id) ON DELETE CASCADE, + + -- Member may be either a user or a user group (any entity) + CONSTRAINT guacamole_user_group_member_entity + FOREIGN KEY (member_entity_id) + REFERENCES guacamole_entity (entity_id) ON DELETE CASCADE ); @@ -276,6 +360,30 @@ CREATE INDEX guacamole_user_attribute_user_id ON guacamole_user_attribute(user_id); -- +-- Table of arbitrary user group attributes. Each attribute is simply a +-- name/value pair associated with a user group. Arbitrary attributes are +-- defined by other extensions. Attributes defined by this extension will be +-- mapped to properly-typed columns of a specific table. +-- + +CREATE TABLE guacamole_user_group_attribute ( + + user_group_id integer NOT NULL, + attribute_name varchar(128) NOT NULL, + attribute_value varchar(4096) NOT NULL, + + PRIMARY KEY (user_group_id, attribute_name), + + CONSTRAINT guacamole_user_group_attribute_ibfk_1 + FOREIGN KEY (user_group_id) + REFERENCES guacamole_user_group (user_group_id) ON DELETE CASCADE + +); + +CREATE INDEX guacamole_user_group_attribute_user_group_id + ON guacamole_user_group_attribute(user_group_id); + +-- -- Table of arbitrary connection attributes. Each attribute is simply a -- name/value pair associated with a connection. Arbitrary attributes are -- defined by other extensions. Attributes defined by this extension will be @@ -348,141 +456,172 @@ CREATE INDEX guacamole_sharing_profile_attribute_sharing_profile_id ON guacamole_sharing_profile_attribute(sharing_profile_id); -- --- Table of connection permissions. Each connection permission grants a user --- specific access to a connection. +-- Table of connection permissions. Each connection permission grants a user or +-- user group specific access to a connection. -- CREATE TABLE guacamole_connection_permission ( - user_id integer NOT NULL, + entity_id integer NOT NULL, connection_id integer NOT NULL, permission guacamole_object_permission_type NOT NULL, - PRIMARY KEY (user_id,connection_id,permission), + PRIMARY KEY (entity_id, connection_id, permission), CONSTRAINT guacamole_connection_permission_ibfk_1 FOREIGN KEY (connection_id) REFERENCES guacamole_connection (connection_id) ON DELETE CASCADE, - CONSTRAINT guacamole_connection_permission_ibfk_2 - FOREIGN KEY (user_id) - REFERENCES guacamole_user (user_id) ON DELETE CASCADE + CONSTRAINT guacamole_connection_permission_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) ON DELETE CASCADE ); CREATE INDEX guacamole_connection_permission_connection_id ON guacamole_connection_permission(connection_id); -CREATE INDEX guacamole_connection_permission_user_id - ON guacamole_connection_permission(user_id); +CREATE INDEX guacamole_connection_permission_entity_id + ON guacamole_connection_permission(entity_id); -- -- Table of connection group permissions. Each group permission grants a user --- specific access to a connection group. +-- or user group specific access to a connection group. -- CREATE TABLE guacamole_connection_group_permission ( - user_id integer NOT NULL, + entity_id integer NOT NULL, connection_group_id integer NOT NULL, permission guacamole_object_permission_type NOT NULL, - PRIMARY KEY (user_id,connection_group_id,permission), + PRIMARY KEY (entity_id, connection_group_id, permission), CONSTRAINT guacamole_connection_group_permission_ibfk_1 FOREIGN KEY (connection_group_id) REFERENCES guacamole_connection_group (connection_group_id) ON DELETE CASCADE, - CONSTRAINT guacamole_connection_group_permission_ibfk_2 - FOREIGN KEY (user_id) - REFERENCES guacamole_user (user_id) ON DELETE CASCADE + CONSTRAINT guacamole_connection_group_permission_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) ON DELETE CASCADE ); CREATE INDEX guacamole_connection_group_permission_connection_group_id ON guacamole_connection_group_permission(connection_group_id); -CREATE INDEX guacamole_connection_group_permission_user_id - ON guacamole_connection_group_permission(user_id); +CREATE INDEX guacamole_connection_group_permission_entity_id + ON guacamole_connection_group_permission(entity_id); -- -- Table of sharing profile permissions. Each sharing profile permission grants --- a user specific access to a sharing profile. +-- a user or user group specific access to a sharing profile. -- CREATE TABLE guacamole_sharing_profile_permission ( - user_id integer NOT NULL, + entity_id integer NOT NULL, sharing_profile_id integer NOT NULL, permission guacamole_object_permission_type NOT NULL, - PRIMARY KEY (user_id,sharing_profile_id,permission), + PRIMARY KEY (entity_id, sharing_profile_id, permission), CONSTRAINT guacamole_sharing_profile_permission_ibfk_1 FOREIGN KEY (sharing_profile_id) REFERENCES guacamole_sharing_profile (sharing_profile_id) ON DELETE CASCADE, - CONSTRAINT guacamole_sharing_profile_permission_ibfk_2 - FOREIGN KEY (user_id) - REFERENCES guacamole_user (user_id) ON DELETE CASCADE + CONSTRAINT guacamole_sharing_profile_permission_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) ON DELETE CASCADE ); CREATE INDEX guacamole_sharing_profile_permission_sharing_profile_id ON guacamole_sharing_profile_permission(sharing_profile_id); -CREATE INDEX guacamole_sharing_profile_permission_user_id - ON guacamole_sharing_profile_permission(user_id); +CREATE INDEX guacamole_sharing_profile_permission_entity_id + ON guacamole_sharing_profile_permission(entity_id); -- --- Table of system permissions. Each system permission grants a user a --- system-level privilege of some kind. +-- Table of system permissions. Each system permission grants a user or user +-- group a system-level privilege of some kind. -- CREATE TABLE guacamole_system_permission ( - user_id integer NOT NULL, + entity_id integer NOT NULL, permission guacamole_system_permission_type NOT NULL, - PRIMARY KEY (user_id,permission), + PRIMARY KEY (entity_id, permission), - CONSTRAINT guacamole_system_permission_ibfk_1 - FOREIGN KEY (user_id) - REFERENCES guacamole_user (user_id) ON DELETE CASCADE + CONSTRAINT guacamole_system_permission_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) ON DELETE CASCADE ); -CREATE INDEX guacamole_system_permission_user_id - ON guacamole_system_permission(user_id); +CREATE INDEX guacamole_system_permission_entity_id + ON guacamole_system_permission(entity_id); -- --- Table of user permissions. Each user permission grants a user access to --- another user (the "affected" user) for a specific type of operation. +-- Table of user permissions. Each user permission grants a user or user group +-- access to another user (the "affected" user) for a specific type of +-- operation. -- CREATE TABLE guacamole_user_permission ( - user_id integer NOT NULL, + entity_id integer NOT NULL, affected_user_id integer NOT NULL, permission guacamole_object_permission_type NOT NULL, - PRIMARY KEY (user_id,affected_user_id,permission), + PRIMARY KEY (entity_id, affected_user_id, permission), CONSTRAINT guacamole_user_permission_ibfk_1 FOREIGN KEY (affected_user_id) REFERENCES guacamole_user (user_id) ON DELETE CASCADE, - CONSTRAINT guacamole_user_permission_ibfk_2 - FOREIGN KEY (user_id) - REFERENCES guacamole_user (user_id) ON DELETE CASCADE + CONSTRAINT guacamole_user_permission_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) ON DELETE CASCADE ); CREATE INDEX guacamole_user_permission_affected_user_id ON guacamole_user_permission(affected_user_id); -CREATE INDEX guacamole_user_permission_user_id - ON guacamole_user_permission(user_id); +CREATE INDEX guacamole_user_permission_entity_id + ON guacamole_user_permission(entity_id); + +-- +-- Table of user group permissions. Each user group permission grants a user +-- or user group access to a another user group (the "affected" user group) for +-- a specific type of operation. +-- + +CREATE TABLE guacamole_user_group_permission ( + + entity_id integer NOT NULL, + affected_user_group_id integer NOT NULL, + permission guacamole_object_permission_type NOT NULL, + + PRIMARY KEY (entity_id, affected_user_group_id, permission), + + CONSTRAINT guacamole_user_group_permission_affected_user_group + FOREIGN KEY (affected_user_group_id) + REFERENCES guacamole_user_group (user_group_id) ON DELETE CASCADE, + + CONSTRAINT guacamole_user_group_permission_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) ON DELETE CASCADE + +); + +CREATE INDEX guacamole_user_group_permission_affected_user_group_id + ON guacamole_user_group_permission(affected_user_group_id); + +CREATE INDEX guacamole_user_group_permission_entity_id + ON guacamole_user_group_permission(entity_id); -- -- Table of connection history records. Each record defines a specific user's http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/e72f88fe/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/002-create-admin-user.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/002-create-admin-user.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/002-create-admin-user.sql index 47eebd2..c7cd7c9 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/002-create-admin-user.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/002-create-admin-user.sql @@ -17,36 +17,39 @@ -- under the License. -- - -- Create default user "guacadmin" with password "guacadmin" -INSERT INTO guacamole_user (username, password_hash, password_salt, password_date) -VALUES ('guacadmin', +INSERT INTO guacamole_entity (name, type) VALUES ('guacadmin', 'USER'); +INSERT INTO guacamole_user (entity_id, password_hash, password_salt, password_date) +SELECT + entity_id, decode('CA458A7D494E3BE824F5E1E175A1556C0F8EEF2C2D7DF3633BEC4A29C4411960', 'hex'), -- 'guacadmin' decode('FE24ADC5E11E2B25288D1704ABE67A79E342ECC26064CE69C5B3177795A82264', 'hex'), - CURRENT_TIMESTAMP); + CURRENT_TIMESTAMP +FROM guacamole_entity WHERE name = 'guacadmin' AND guacamole_entity.type = 'USER'; -- Grant this user all system permissions -INSERT INTO guacamole_system_permission -SELECT user_id, permission::guacamole_system_permission_type +INSERT INTO guacamole_system_permission (entity_id, permission) +SELECT entity_id, permission::guacamole_system_permission_type FROM ( VALUES ('guacadmin', 'CREATE_CONNECTION'), ('guacadmin', 'CREATE_CONNECTION_GROUP'), ('guacadmin', 'CREATE_SHARING_PROFILE'), ('guacadmin', 'CREATE_USER'), + ('guacadmin', 'CREATE_USER_GROUP'), ('guacadmin', 'ADMINISTER') ) permissions (username, permission) -JOIN guacamole_user ON permissions.username = guacamole_user.username; +JOIN guacamole_entity ON permissions.username = guacamole_entity.name AND guacamole_entity.type = 'USER'; -- Grant admin permission to read/update/administer self -INSERT INTO guacamole_user_permission -SELECT guacamole_user.user_id, affected.user_id, permission::guacamole_object_permission_type +INSERT INTO guacamole_user_permission (entity_id, affected_user_id, permission) +SELECT guacamole_entity.entity_id, guacamole_user.user_id, permission::guacamole_object_permission_type FROM ( VALUES ('guacadmin', 'guacadmin', 'READ'), ('guacadmin', 'guacadmin', 'UPDATE'), ('guacadmin', 'guacadmin', 'ADMINISTER') ) permissions (username, affected_username, permission) -JOIN guacamole_user ON permissions.username = guacamole_user.username -JOIN guacamole_user affected ON permissions.affected_username = affected.username; - +JOIN guacamole_entity ON permissions.username = guacamole_entity.name AND guacamole_entity.type = 'USER' +JOIN guacamole_entity affected ON permissions.affected_username = affected.name AND guacamole_entity.type = 'USER' +JOIN guacamole_user ON guacamole_user.entity_id = affected.entity_id; http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/e72f88fe/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-1.0.0.sql ---------------------------------------------------------------------- diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-1.0.0.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-1.0.0.sql index db115c2..dd341dc 100644 --- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-1.0.0.sql +++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-1.0.0.sql @@ -18,6 +18,339 @@ -- -- +-- Add new system-level permission +-- + +ALTER TYPE guacamole_system_permission_type + ADD VALUE 'CREATE_USER_GROUP' + AFTER 'CREATE_USER'; + +-- +-- Entity types +-- + +CREATE TYPE guacamole_entity_type AS ENUM( + 'USER', + 'USER_GROUP' +); + +-- +-- Table of base entities which may each be either a user or user group. Other +-- tables which represent qualities shared by both users and groups will point +-- to guacamole_entity, while tables which represent qualities specific to +-- users or groups will point to guacamole_user or guacamole_user_group. +-- + +CREATE TABLE guacamole_entity ( + + entity_id serial NOT NULL, + name varchar(128) NOT NULL, + type guacamole_entity_type NOT NULL, + + PRIMARY KEY (entity_id), + + CONSTRAINT guacamole_entity_name_scope + UNIQUE (type, name) + +); + +-- +-- Table of user groups. Each user group may have an arbitrary set of member +-- users and member groups, with those members inheriting the permissions +-- granted to that group. +-- + +CREATE TABLE guacamole_user_group ( + + user_group_id serial NOT NULL, + entity_id integer NOT NULL, + + -- Group disabled status + disabled boolean NOT NULL DEFAULT FALSE, + + PRIMARY KEY (user_group_id), + + CONSTRAINT guacamole_user_group_single_entity + UNIQUE (entity_id), + + CONSTRAINT guacamole_user_group_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) + ON DELETE CASCADE + +); + +-- +-- Table of users which are members of given user groups. +-- + +CREATE TABLE guacamole_user_group_member ( + + user_group_id integer NOT NULL, + member_entity_id integer NOT NULL, + + PRIMARY KEY (user_group_id, member_entity_id), + + -- Parent must be a user group + CONSTRAINT guacamole_user_group_member_parent + FOREIGN KEY (user_group_id) + REFERENCES guacamole_user_group (user_group_id) ON DELETE CASCADE, + + -- Member may be either a user or a user group (any entity) + CONSTRAINT guacamole_user_group_member_entity + FOREIGN KEY (member_entity_id) + REFERENCES guacamole_entity (entity_id) ON DELETE CASCADE + +); + +-- +-- Table of user group permissions. Each user group permission grants a user +-- access to a particular user group for a specific type of operation. +-- + +CREATE TABLE guacamole_user_group_permission ( + + entity_id integer NOT NULL, + affected_user_group_id integer NOT NULL, + permission guacamole_object_permission_type NOT NULL, + + PRIMARY KEY (entity_id, affected_user_group_id, permission), + + CONSTRAINT guacamole_user_group_permission_affected_user_group + FOREIGN KEY (affected_user_group_id) + REFERENCES guacamole_user_group (user_group_id) ON DELETE CASCADE, + + CONSTRAINT guacamole_user_group_permission_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) ON DELETE CASCADE + +); + +CREATE INDEX guacamole_user_group_permission_affected_user_group_id + ON guacamole_user_group_permission(affected_user_group_id); + +CREATE INDEX guacamole_user_group_permission_entity_id + ON guacamole_user_group_permission(entity_id); + +-- +-- Modify guacamole_user table to use guacamole_entity as a base +-- + +-- Add new entity_id column +ALTER TABLE guacamole_user ADD COLUMN entity_id integer; + +-- Create user entities for each guacamole_user entry +INSERT INTO guacamole_entity (name, type) +SELECT username, 'USER' FROM guacamole_user; + +-- Update guacamole_user to point to corresponding guacamole_entity +UPDATE guacamole_user SET entity_id = ( + SELECT entity_id FROM guacamole_entity + WHERE + username = guacamole_entity.name + AND type = 'USER' +); + +-- The entity_id column should now be safely non-NULL +ALTER TABLE guacamole_user + ALTER COLUMN entity_id SET NOT NULL; + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE guacamole_user + ADD CONSTRAINT guacamole_user_single_entity + UNIQUE (entity_id); + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE guacamole_user + ADD CONSTRAINT guacamole_user_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) + ON DELETE CASCADE; + +-- The username column can now safely be removed +ALTER TABLE guacamole_user DROP COLUMN username; + +-- +-- Modify guacamole_connection_permission to use guacamole_entity instead of +-- guacamole_user +-- + +-- Add new entity_id column +ALTER TABLE guacamole_connection_permission ADD COLUMN entity_id integer; + +-- Update guacamole_connection_permission to point to the guacamole_entity +-- that has been granted the permission +UPDATE guacamole_connection_permission SET entity_id = ( + SELECT entity_id FROM guacamole_user + WHERE guacamole_user.user_id = guacamole_connection_permission.user_id +); + +-- The entity_id column should now be safely non-NULL +ALTER TABLE guacamole_connection_permission + ALTER COLUMN entity_id SET NOT NULL; + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE guacamole_connection_permission + ADD CONSTRAINT guacamole_connection_permission_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) + ON DELETE CASCADE; + +CREATE INDEX guacamole_connection_permission_entity_id + ON guacamole_connection_permission(entity_id); + +-- Remove user_id column (implicitly drops associated contraints/keys) +ALTER TABLE guacamole_connection_permission DROP COLUMN user_id; + +-- Add new primary key which uses entity_id +ALTER TABLE guacamole_connection_permission + ADD PRIMARY KEY (entity_id, connection_id, permission); + +-- +-- Modify guacamole_connection_group_permission to use guacamole_entity instead +-- of guacamole_user +-- + +-- Add new entity_id column +ALTER TABLE guacamole_connection_group_permission ADD COLUMN entity_id integer; + +-- Update guacamole_connection_group_permission to point to the guacamole_entity +-- that has been granted the permission +UPDATE guacamole_connection_group_permission SET entity_id = ( + SELECT entity_id FROM guacamole_user + WHERE guacamole_user.user_id = guacamole_connection_group_permission.user_id +); + +-- The entity_id column should now be safely non-NULL +ALTER TABLE guacamole_connection_group_permission + ALTER COLUMN entity_id SET NOT NULL; + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE guacamole_connection_group_permission + ADD CONSTRAINT guacamole_connection_group_permission_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) + ON DELETE CASCADE; + +CREATE INDEX guacamole_connection_group_permission_entity_id + ON guacamole_connection_group_permission(entity_id); + +-- Remove user_id column (implicitly drops associated contraints/keys) +ALTER TABLE guacamole_connection_group_permission DROP COLUMN user_id; + +-- Add new primary key which uses entity_id +ALTER TABLE guacamole_connection_group_permission + ADD PRIMARY KEY (entity_id, connection_group_id, permission); + +-- +-- Modify guacamole_sharing_profile_permission to use guacamole_entity instead +-- of guacamole_user +-- + +-- Add new entity_id column +ALTER TABLE guacamole_sharing_profile_permission ADD COLUMN entity_id integer; + +-- Update guacamole_sharing_profile_permission to point to the guacamole_entity +-- that has been granted the permission +UPDATE guacamole_sharing_profile_permission SET entity_id = ( + SELECT entity_id FROM guacamole_user + WHERE guacamole_user.user_id = guacamole_sharing_profile_permission.user_id +); + +-- The entity_id column should now be safely non-NULL +ALTER TABLE guacamole_sharing_profile_permission + ALTER COLUMN entity_id SET NOT NULL; + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE guacamole_sharing_profile_permission + ADD CONSTRAINT guacamole_sharing_profile_permission_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) + ON DELETE CASCADE; + +CREATE INDEX guacamole_sharing_profile_permission_entity_id + ON guacamole_sharing_profile_permission(entity_id); + +-- Remove user_id column (implicitly drops associated contraints/keys) +ALTER TABLE guacamole_sharing_profile_permission DROP COLUMN user_id; + +-- Add new primary key which uses entity_id +ALTER TABLE guacamole_sharing_profile_permission + ADD PRIMARY KEY (entity_id, sharing_profile_id, permission); + +-- +-- Modify guacamole_user_permission to use guacamole_entity instead of +-- guacamole_user +-- + +-- Add new entity_id column +ALTER TABLE guacamole_user_permission ADD COLUMN entity_id integer; + +-- Update guacamole_user_permission to point to the guacamole_entity +-- that has been granted the permission +UPDATE guacamole_user_permission SET entity_id = ( + SELECT entity_id FROM guacamole_user + WHERE guacamole_user.user_id = guacamole_user_permission.user_id +); + +-- The entity_id column should now be safely non-NULL +ALTER TABLE guacamole_user_permission + ALTER COLUMN entity_id SET NOT NULL; + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE guacamole_user_permission + ADD CONSTRAINT guacamole_user_permission_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) + ON DELETE CASCADE; + +CREATE INDEX guacamole_user_permission_entity_id + ON guacamole_user_permission(entity_id); + +-- Remove user_id column (implicitly drops associated contraints/keys) +ALTER TABLE guacamole_user_permission DROP COLUMN user_id; + +-- Add new primary key which uses entity_id +ALTER TABLE guacamole_user_permission + ADD PRIMARY KEY (entity_id, affected_user_id, permission); + +-- +-- Modify guacamole_system_permission to use guacamole_entity instead of +-- guacamole_user +-- + +-- Add new entity_id column +ALTER TABLE guacamole_system_permission ADD COLUMN entity_id integer; + +-- Update guacamole_system_permission to point to the guacamole_entity +-- that has been granted the permission +UPDATE guacamole_system_permission SET entity_id = ( + SELECT entity_id FROM guacamole_user + WHERE guacamole_user.user_id = guacamole_system_permission.user_id +); + +-- The entity_id column should now be safely non-NULL +ALTER TABLE guacamole_system_permission + ALTER COLUMN entity_id SET NOT NULL; + +-- The entity_id column should now safely point to guacamole_entity entries +ALTER TABLE guacamole_system_permission + ADD CONSTRAINT guacamole_system_permission_entity + FOREIGN KEY (entity_id) + REFERENCES guacamole_entity (entity_id) + ON DELETE CASCADE; + +CREATE INDEX guacamole_system_permission_entity_id + ON guacamole_system_permission(entity_id); + +-- Remove user_id column (implicitly drops associated contraints/keys) +ALTER TABLE guacamole_system_permission DROP COLUMN user_id; + +-- Add new primary key which uses entity_id +ALTER TABLE guacamole_system_permission + ADD PRIMARY KEY (entity_id, permission); + +-- -- Table of arbitrary user attributes. Each attribute is simply a name/value -- pair associated with a user. Arbitrary attributes are defined by other -- extensions. Attributes defined by this extension will be mapped to @@ -42,6 +375,30 @@ CREATE INDEX guacamole_user_attribute_user_id ON guacamole_user_attribute(user_id); -- +-- Table of arbitrary user group attributes. Each attribute is simply a +-- name/value pair associated with a user group. Arbitrary attributes are +-- defined by other extensions. Attributes defined by this extension will be +-- mapped to properly-typed columns of a specific table. +-- + +CREATE TABLE guacamole_user_group_attribute ( + + user_group_id integer NOT NULL, + attribute_name varchar(128) NOT NULL, + attribute_value varchar(4096) NOT NULL, + + PRIMARY KEY (user_group_id, attribute_name), + + CONSTRAINT guacamole_user_group_attribute_ibfk_1 + FOREIGN KEY (user_group_id) + REFERENCES guacamole_user_group (user_group_id) ON DELETE CASCADE + +); + +CREATE INDEX guacamole_user_group_attribute_user_group_id + ON guacamole_user_group_attribute(user_group_id); + +-- -- Table of arbitrary connection attributes. Each attribute is simply a -- name/value pair associated with a connection. Arbitrary attributes are -- defined by other extensions. Attributes defined by this extension will be
