-
Notifications
You must be signed in to change notification settings - Fork 0
/
sample_initdb.sql
792 lines (583 loc) · 23.9 KB
/
sample_initdb.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements. See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership. The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
--
-- Connection group types
--
CREATE TYPE guacamole_connection_group_type AS ENUM(
'ORGANIZATIONAL',
'BALANCING'
);
--
-- Entity types
--
CREATE TYPE guacamole_entity_type AS ENUM(
'USER',
'USER_GROUP'
);
--
-- Object permission types
--
CREATE TYPE guacamole_object_permission_type AS ENUM(
'READ',
'UPDATE',
'DELETE',
'ADMINISTER'
);
--
-- System permission types
--
CREATE TYPE guacamole_system_permission_type AS ENUM(
'CREATE_CONNECTION',
'CREATE_CONNECTION_GROUP',
'CREATE_SHARING_PROFILE',
'CREATE_USER',
'CREATE_USER_GROUP',
'AUDIT',
'ADMINISTER'
);
--
-- Guacamole proxy (guacd) encryption methods
--
CREATE TYPE guacamole_proxy_encryption_method AS ENUM(
'NONE',
'SSL'
);
--
-- Table of connection groups. Each connection group has a name.
--
CREATE TABLE guacamole_connection_group (
connection_group_id serial NOT NULL,
parent_id integer,
connection_group_name varchar(128) NOT NULL,
type guacamole_connection_group_type
NOT NULL DEFAULT 'ORGANIZATIONAL',
-- Concurrency limits
max_connections integer,
max_connections_per_user integer,
enable_session_affinity boolean NOT NULL DEFAULT FALSE,
PRIMARY KEY (connection_group_id),
CONSTRAINT connection_group_name_parent
UNIQUE (connection_group_name, parent_id),
CONSTRAINT guacamole_connection_group_ibfk_1
FOREIGN KEY (parent_id)
REFERENCES guacamole_connection_group (connection_group_id)
ON DELETE CASCADE
);
CREATE INDEX guacamole_connection_group_parent_id
ON guacamole_connection_group(parent_id);
--
-- Table of connections. Each connection has a name, protocol, and
-- associated set of parameters.
-- A connection may belong to a connection group.
--
CREATE TABLE guacamole_connection (
connection_id serial NOT NULL,
connection_name varchar(128) NOT NULL,
parent_id integer,
protocol varchar(32) NOT NULL,
-- Concurrency limits
max_connections integer,
max_connections_per_user integer,
-- Connection Weight
connection_weight integer,
failover_only boolean NOT NULL DEFAULT FALSE,
-- Guacamole proxy (guacd) overrides
proxy_port integer,
proxy_hostname varchar(512),
proxy_encryption_method guacamole_proxy_encryption_method,
PRIMARY KEY (connection_id),
CONSTRAINT connection_name_parent
UNIQUE (connection_name, parent_id),
CONSTRAINT guacamole_connection_ibfk_1
FOREIGN KEY (parent_id)
REFERENCES guacamole_connection_group (connection_group_id)
ON DELETE CASCADE
);
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
-- providing the salt.
--
CREATE TABLE guacamole_user (
user_id serial NOT NULL,
entity_id integer NOT NULL,
-- Optionally-salted password
password_hash bytea NOT NULL,
password_salt bytea,
password_date timestamptz NOT NULL,
-- Account disabled/expired status
disabled boolean NOT NULL DEFAULT FALSE,
expired boolean NOT NULL DEFAULT FALSE,
-- Time-based access restriction
access_window_start time,
access_window_end time,
-- Date-based access restriction
valid_from date,
valid_until date,
-- Timezone used for all date/time comparisons and interpretation
timezone varchar(64),
-- Profile information
full_name varchar(256),
email_address varchar(256),
organization varchar(256),
organizational_role varchar(256),
PRIMARY KEY (user_id),
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
);
--
-- Table of sharing profiles. Each sharing profile has a name, associated set
-- of parameters, and a primary connection. The primary connection is the
-- connection that the sharing profile shares, and the parameters dictate the
-- restrictions/features which apply to the user joining the connection via the
-- sharing profile.
--
CREATE TABLE guacamole_sharing_profile (
sharing_profile_id serial NOT NULL,
sharing_profile_name varchar(128) NOT NULL,
primary_connection_id integer NOT NULL,
PRIMARY KEY (sharing_profile_id),
CONSTRAINT sharing_profile_name_primary
UNIQUE (sharing_profile_name, primary_connection_id),
CONSTRAINT guacamole_sharing_profile_ibfk_1
FOREIGN KEY (primary_connection_id)
REFERENCES guacamole_connection (connection_id)
ON DELETE CASCADE
);
CREATE INDEX guacamole_sharing_profile_primary_connection_id
ON guacamole_sharing_profile(primary_connection_id);
--
-- Table of connection parameters. Each parameter is simply a name/value pair
-- associated with a connection.
--
CREATE TABLE guacamole_connection_parameter (
connection_id integer NOT NULL,
parameter_name varchar(128) NOT NULL,
parameter_value varchar(4096) NOT NULL,
PRIMARY KEY (connection_id,parameter_name),
CONSTRAINT guacamole_connection_parameter_ibfk_1
FOREIGN KEY (connection_id)
REFERENCES guacamole_connection (connection_id) ON DELETE CASCADE
);
CREATE INDEX guacamole_connection_parameter_connection_id
ON guacamole_connection_parameter(connection_id);
--
-- Table of sharing profile parameters. Each parameter is simply
-- name/value pair associated with a sharing profile. These parameters dictate
-- the restrictions/features which apply to the user joining the associated
-- connection via the sharing profile.
--
CREATE TABLE guacamole_sharing_profile_parameter (
sharing_profile_id integer NOT NULL,
parameter_name varchar(128) NOT NULL,
parameter_value varchar(4096) NOT NULL,
PRIMARY KEY (sharing_profile_id, parameter_name),
CONSTRAINT guacamole_sharing_profile_parameter_ibfk_1
FOREIGN KEY (sharing_profile_id)
REFERENCES guacamole_sharing_profile (sharing_profile_id) ON DELETE CASCADE
);
CREATE INDEX guacamole_sharing_profile_parameter_sharing_profile_id
ON guacamole_sharing_profile_parameter(sharing_profile_id);
--
-- 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
-- properly-typed columns of a specific table.
--
CREATE TABLE guacamole_user_attribute (
user_id integer NOT NULL,
attribute_name varchar(128) NOT NULL,
attribute_value varchar(4096) NOT NULL,
PRIMARY KEY (user_id, attribute_name),
CONSTRAINT guacamole_user_attribute_ibfk_1
FOREIGN KEY (user_id)
REFERENCES guacamole_user (user_id) ON DELETE CASCADE
);
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
-- mapped to properly-typed columns of a specific table.
--
CREATE TABLE guacamole_connection_attribute (
connection_id integer NOT NULL,
attribute_name varchar(128) NOT NULL,
attribute_value varchar(4096) NOT NULL,
PRIMARY KEY (connection_id, attribute_name),
CONSTRAINT guacamole_connection_attribute_ibfk_1
FOREIGN KEY (connection_id)
REFERENCES guacamole_connection (connection_id) ON DELETE CASCADE
);
CREATE INDEX guacamole_connection_attribute_connection_id
ON guacamole_connection_attribute(connection_id);
--
-- Table of arbitrary connection group attributes. Each attribute is simply a
-- name/value pair associated with a connection 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_connection_group_attribute (
connection_group_id integer NOT NULL,
attribute_name varchar(128) NOT NULL,
attribute_value varchar(4096) NOT NULL,
PRIMARY KEY (connection_group_id, attribute_name),
CONSTRAINT guacamole_connection_group_attribute_ibfk_1
FOREIGN KEY (connection_group_id)
REFERENCES guacamole_connection_group (connection_group_id) ON DELETE CASCADE
);
CREATE INDEX guacamole_connection_group_attribute_connection_group_id
ON guacamole_connection_group_attribute(connection_group_id);
--
-- Table of arbitrary sharing profile attributes. Each attribute is simply a
-- name/value pair associated with a sharing profile. 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_sharing_profile_attribute (
sharing_profile_id integer NOT NULL,
attribute_name varchar(128) NOT NULL,
attribute_value varchar(4096) NOT NULL,
PRIMARY KEY (sharing_profile_id, attribute_name),
CONSTRAINT guacamole_sharing_profile_attribute_ibfk_1
FOREIGN KEY (sharing_profile_id)
REFERENCES guacamole_sharing_profile (sharing_profile_id) ON DELETE CASCADE
);
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 or
-- user group specific access to a connection.
--
CREATE TABLE guacamole_connection_permission (
entity_id integer NOT NULL,
connection_id integer NOT NULL,
permission guacamole_object_permission_type NOT NULL,
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_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_entity_id
ON guacamole_connection_permission(entity_id);
--
-- Table of connection group permissions. Each group permission grants a user
-- or user group specific access to a connection group.
--
CREATE TABLE guacamole_connection_group_permission (
entity_id integer NOT NULL,
connection_group_id integer NOT NULL,
permission guacamole_object_permission_type NOT NULL,
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_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_entity_id
ON guacamole_connection_group_permission(entity_id);
--
-- Table of sharing profile permissions. Each sharing profile permission grants
-- a user or user group specific access to a sharing profile.
--
CREATE TABLE guacamole_sharing_profile_permission (
entity_id integer NOT NULL,
sharing_profile_id integer NOT NULL,
permission guacamole_object_permission_type NOT NULL,
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_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_entity_id
ON guacamole_sharing_profile_permission(entity_id);
--
-- 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 (
entity_id integer NOT NULL,
permission guacamole_system_permission_type NOT NULL,
PRIMARY KEY (entity_id, permission),
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);
--
-- 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 (
entity_id integer NOT NULL,
affected_user_id integer NOT NULL,
permission guacamole_object_permission_type NOT NULL,
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_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_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
-- session, including the connection used, the start time, and the end time
-- (if any).
--
CREATE TABLE guacamole_connection_history (
history_id serial NOT NULL,
user_id integer DEFAULT NULL,
username varchar(128) NOT NULL,
remote_host varchar(256) DEFAULT NULL,
connection_id integer DEFAULT NULL,
connection_name varchar(128) NOT NULL,
sharing_profile_id integer DEFAULT NULL,
sharing_profile_name varchar(128) DEFAULT NULL,
start_date timestamptz NOT NULL,
end_date timestamptz DEFAULT NULL,
PRIMARY KEY (history_id),
CONSTRAINT guacamole_connection_history_ibfk_1
FOREIGN KEY (user_id)
REFERENCES guacamole_user (user_id) ON DELETE SET NULL,
CONSTRAINT guacamole_connection_history_ibfk_2
FOREIGN KEY (connection_id)
REFERENCES guacamole_connection (connection_id) ON DELETE SET NULL,
CONSTRAINT guacamole_connection_history_ibfk_3
FOREIGN KEY (sharing_profile_id)
REFERENCES guacamole_sharing_profile (sharing_profile_id) ON DELETE SET NULL
);
CREATE INDEX guacamole_connection_history_user_id
ON guacamole_connection_history(user_id);
CREATE INDEX guacamole_connection_history_connection_id
ON guacamole_connection_history(connection_id);
CREATE INDEX guacamole_connection_history_sharing_profile_id
ON guacamole_connection_history(sharing_profile_id);
CREATE INDEX guacamole_connection_history_start_date
ON guacamole_connection_history(start_date);
CREATE INDEX guacamole_connection_history_end_date
ON guacamole_connection_history(end_date);
CREATE INDEX guacamole_connection_history_connection_id_start_date
ON guacamole_connection_history(connection_id, start_date);
--
-- User login/logout history
--
CREATE TABLE guacamole_user_history (
history_id serial NOT NULL,
user_id integer DEFAULT NULL,
username varchar(128) NOT NULL,
remote_host varchar(256) DEFAULT NULL,
start_date timestamptz NOT NULL,
end_date timestamptz DEFAULT NULL,
PRIMARY KEY (history_id),
CONSTRAINT guacamole_user_history_ibfk_1
FOREIGN KEY (user_id)
REFERENCES guacamole_user (user_id) ON DELETE SET NULL
);
CREATE INDEX guacamole_user_history_user_id
ON guacamole_user_history(user_id);
CREATE INDEX guacamole_user_history_start_date
ON guacamole_user_history(start_date);
CREATE INDEX guacamole_user_history_end_date
ON guacamole_user_history(end_date);
CREATE INDEX guacamole_user_history_user_id_start_date
ON guacamole_user_history(user_id, start_date);
--
-- User password history
--
CREATE TABLE guacamole_user_password_history (
password_history_id serial NOT NULL,
user_id integer NOT NULL,
-- Salted password
password_hash bytea NOT NULL,
password_salt bytea,
password_date timestamptz NOT NULL,
PRIMARY KEY (password_history_id),
CONSTRAINT guacamole_user_password_history_ibfk_1
FOREIGN KEY (user_id)
REFERENCES guacamole_user (user_id) ON DELETE CASCADE
);
CREATE INDEX guacamole_user_password_history_user_id
ON guacamole_user_password_history(user_id);
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements. See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership. The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- Create default user "guacadmin" with password "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
FROM guacamole_entity WHERE name = 'guacadmin' AND guacamole_entity.type = 'USER';
-- Grant this user all system permissions
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_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 (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_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;