You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@guacamole.apache.org by vn...@apache.org on 2018/10/01 18:08:13 UTC
[12/38] guacamole-client git commit: GUACAMOLE-220: Define base
schema for user groups.
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/master
Commit: e72f88febff5766345fa4a8f9086639ea997a2e9
Parents: d23f88f
Author: Michael Jumper <mj...@apache.org>
Authored: Fri Mar 2 14:45:33 2018 -0800
Committer: Michael Jumper <mj...@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