-
Notifications
You must be signed in to change notification settings - Fork 623
/
postgresql.sql
2652 lines (2382 loc) · 99.3 KB
/
postgresql.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
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
BEGIN TRANSACTION;
-- Start of IDN Tables --
DROP TABLE IF EXISTS IDN_BASE_TABLE;
CREATE TABLE IDN_BASE_TABLE (
PRODUCT_NAME VARCHAR(20),
PRIMARY KEY (PRODUCT_NAME)
);
INSERT INTO IDN_BASE_TABLE values ('WSO2 Identity Server');
DROP TABLE IF EXISTS IDN_OAUTH_CONSUMER_APPS;
DROP SEQUENCE IF EXISTS IDN_OAUTH_CONSUMER_APPS_PK_SEQ;
CREATE SEQUENCE IDN_OAUTH_CONSUMER_APPS_PK_SEQ;
CREATE TABLE IDN_OAUTH_CONSUMER_APPS (
ID INTEGER DEFAULT NEXTVAL('IDN_OAUTH_CONSUMER_APPS_PK_SEQ'),
CONSUMER_KEY VARCHAR(255),
CONSUMER_SECRET VARCHAR(2048),
USERNAME VARCHAR(255),
TENANT_ID INTEGER DEFAULT 0,
USER_DOMAIN VARCHAR(50),
APP_NAME VARCHAR(255),
OAUTH_VERSION VARCHAR(128),
CALLBACK_URL VARCHAR(2048),
GRANT_TYPES VARCHAR (1024),
PKCE_MANDATORY CHAR(1) DEFAULT '0',
PKCE_SUPPORT_PLAIN CHAR(1) DEFAULT '0',
APP_STATE VARCHAR (25) DEFAULT 'ACTIVE',
USER_ACCESS_TOKEN_EXPIRE_TIME BIGINT DEFAULT 3600,
APP_ACCESS_TOKEN_EXPIRE_TIME BIGINT DEFAULT 3600,
REFRESH_TOKEN_EXPIRE_TIME BIGINT DEFAULT 84600,
ID_TOKEN_EXPIRE_TIME BIGINT DEFAULT 3600,
CONSTRAINT CONSUMER_KEY_CONSTRAINT UNIQUE (CONSUMER_KEY),
PRIMARY KEY (ID)
);
DROP TABLE IF EXISTS IDN_OAUTH2_SCOPE_VALIDATORS;
CREATE TABLE IDN_OAUTH2_SCOPE_VALIDATORS (
APP_ID INTEGER NOT NULL,
SCOPE_VALIDATOR VARCHAR (128) NOT NULL,
PRIMARY KEY (APP_ID,SCOPE_VALIDATOR),
FOREIGN KEY (APP_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
);
DROP TABLE IF EXISTS IDN_OAUTH1A_REQUEST_TOKEN;
CREATE TABLE IDN_OAUTH1A_REQUEST_TOKEN (
REQUEST_TOKEN VARCHAR(512),
REQUEST_TOKEN_SECRET VARCHAR(512),
CONSUMER_KEY_ID INTEGER,
CALLBACK_URL VARCHAR(2048),
SCOPE VARCHAR(2048),
AUTHORIZED VARCHAR(128),
OAUTH_VERIFIER VARCHAR(512),
AUTHZ_USER VARCHAR(512),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (REQUEST_TOKEN),
FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
);
DROP TABLE IF EXISTS IDN_OAUTH1A_ACCESS_TOKEN;
CREATE TABLE IDN_OAUTH1A_ACCESS_TOKEN (
ACCESS_TOKEN VARCHAR(512),
ACCESS_TOKEN_SECRET VARCHAR(512),
CONSUMER_KEY_ID INTEGER,
SCOPE VARCHAR(2048),
AUTHZ_USER VARCHAR(512),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (ACCESS_TOKEN),
FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
);
DROP TABLE IF EXISTS IDN_OAUTH2_ACCESS_TOKEN;
CREATE TABLE IDN_OAUTH2_ACCESS_TOKEN (
TOKEN_ID VARCHAR (255),
ACCESS_TOKEN VARCHAR(2048),
REFRESH_TOKEN VARCHAR(2048),
CONSUMER_KEY_ID INTEGER,
AUTHZ_USER VARCHAR (100),
TENANT_ID INTEGER,
USER_DOMAIN VARCHAR(50),
USER_TYPE VARCHAR (25),
GRANT_TYPE VARCHAR (50),
TIME_CREATED TIMESTAMP,
REFRESH_TOKEN_TIME_CREATED TIMESTAMP,
VALIDITY_PERIOD BIGINT,
REFRESH_TOKEN_VALIDITY_PERIOD BIGINT,
TOKEN_SCOPE_HASH VARCHAR(32),
TOKEN_STATE VARCHAR(25) DEFAULT 'ACTIVE',
TOKEN_STATE_ID VARCHAR (128) DEFAULT 'NONE',
SUBJECT_IDENTIFIER VARCHAR(255),
ACCESS_TOKEN_HASH VARCHAR(512),
REFRESH_TOKEN_HASH VARCHAR(512),
IDP_ID INTEGER DEFAULT -1 NOT NULL,
TOKEN_BINDING_REF VARCHAR (32) DEFAULT 'NONE',
PRIMARY KEY (TOKEN_ID),
FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE,
CONSTRAINT CON_APP_KEY UNIQUE (CONSUMER_KEY_ID,AUTHZ_USER,TENANT_ID,USER_DOMAIN,USER_TYPE,TOKEN_SCOPE_HASH,
TOKEN_STATE,TOKEN_STATE_ID,IDP_ID,TOKEN_BINDING_REF)
);
DROP TABLE IF EXISTS IDN_OAUTH2_TOKEN_BINDING;
CREATE TABLE IDN_OAUTH2_TOKEN_BINDING (
TOKEN_ID VARCHAR (255),
TOKEN_BINDING_TYPE VARCHAR (32),
TOKEN_BINDING_REF VARCHAR (32),
TOKEN_BINDING_VALUE VARCHAR (1024),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (TOKEN_ID),
FOREIGN KEY (TOKEN_ID) REFERENCES IDN_OAUTH2_ACCESS_TOKEN(TOKEN_ID) ON DELETE CASCADE
);
DROP TABLE IF EXISTS IDN_OAUTH2_ACCESS_TOKEN_AUDIT;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_ACCESS_TOKEN_AUDIT (
TOKEN_ID VARCHAR (255),
ACCESS_TOKEN VARCHAR(2048),
REFRESH_TOKEN VARCHAR(2048),
CONSUMER_KEY_ID INTEGER,
AUTHZ_USER VARCHAR (100),
TENANT_ID INTEGER,
USER_DOMAIN VARCHAR(50),
USER_TYPE VARCHAR (25),
GRANT_TYPE VARCHAR (50),
TIME_CREATED TIMESTAMP NULL,
REFRESH_TOKEN_TIME_CREATED TIMESTAMP NULL,
VALIDITY_PERIOD BIGINT,
REFRESH_TOKEN_VALIDITY_PERIOD BIGINT,
TOKEN_SCOPE_HASH VARCHAR(32),
TOKEN_STATE VARCHAR(25),
TOKEN_STATE_ID VARCHAR (128) ,
SUBJECT_IDENTIFIER VARCHAR(255),
ACCESS_TOKEN_HASH VARCHAR(512),
REFRESH_TOKEN_HASH VARCHAR(512),
INVALIDATED_TIME TIMESTAMP NULL,
IDP_ID INTEGER DEFAULT -1 NOT NULL
);
DROP TABLE IF EXISTS IDN_OAUTH2_AUTHORIZATION_CODE;
CREATE TABLE IDN_OAUTH2_AUTHORIZATION_CODE (
CODE_ID VARCHAR (255),
AUTHORIZATION_CODE VARCHAR(2048),
CONSUMER_KEY_ID INTEGER,
CALLBACK_URL VARCHAR(2048),
SCOPE VARCHAR(2048),
AUTHZ_USER VARCHAR (100),
TENANT_ID INTEGER,
USER_DOMAIN VARCHAR(50),
TIME_CREATED TIMESTAMP,
VALIDITY_PERIOD BIGINT,
STATE VARCHAR (25) DEFAULT 'ACTIVE',
TOKEN_ID VARCHAR(255),
SUBJECT_IDENTIFIER VARCHAR(255),
PKCE_CODE_CHALLENGE VARCHAR (255),
PKCE_CODE_CHALLENGE_METHOD VARCHAR(128),
AUTHORIZATION_CODE_HASH VARCHAR(512),
IDP_ID INTEGER DEFAULT -1 NOT NULL,
PRIMARY KEY (CODE_ID),
FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
);
DROP TABLE IF EXISTS IDN_OAUTH2_AUTHZ_CODE_SCOPE;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_AUTHZ_CODE_SCOPE(
CODE_ID VARCHAR(255),
SCOPE VARCHAR(60),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (CODE_ID, SCOPE),
FOREIGN KEY (CODE_ID) REFERENCES IDN_OAUTH2_AUTHORIZATION_CODE (CODE_ID) ON DELETE CASCADE);
DROP TABLE IF EXISTS IDN_OAUTH2_DEVICE_FLOW;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_DEVICE_FLOW (
CODE_ID VARCHAR(255),
DEVICE_CODE VARCHAR(255),
USER_CODE VARCHAR(25),
CONSUMER_KEY_ID INTEGER,
LAST_POLL_TIME TIMESTAMP NOT NULL,
EXPIRY_TIME TIMESTAMP NOT NULL,
TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
POLL_TIME BIGINT,
STATUS VARCHAR (25) DEFAULT 'PENDING',
AUTHZ_USER VARCHAR (100),
TENANT_ID INTEGER,
USER_DOMAIN VARCHAR(50),
IDP_ID INTEGER,
PRIMARY KEY (DEVICE_CODE),
UNIQUE (CODE_ID),
UNIQUE (USER_CODE),
FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
);
DROP TABLE IF EXISTS IDN_OAUTH2_DEVICE_FLOW_SCOPES;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_DEVICE_FLOW_SCOPES (
ID SERIAL PRIMARY KEY,
SCOPE_ID VARCHAR(255),
SCOPE VARCHAR(255),
FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_DEVICE_FLOW(CODE_ID) ON DELETE CASCADE
);
DROP TABLE IF EXISTS IDN_OAUTH2_ACCESS_TOKEN_SCOPE;
CREATE TABLE IDN_OAUTH2_ACCESS_TOKEN_SCOPE (
TOKEN_ID VARCHAR (255),
TOKEN_SCOPE VARCHAR (100),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (TOKEN_ID, TOKEN_SCOPE),
FOREIGN KEY (TOKEN_ID) REFERENCES IDN_OAUTH2_ACCESS_TOKEN(TOKEN_ID) ON DELETE CASCADE);
DROP TABLE IF EXISTS IDN_OAUTH2_SCOPE;
DROP SEQUENCE IF EXISTS IDN_OAUTH2_SCOPE_PK_SEQ;
CREATE SEQUENCE IDN_OAUTH2_SCOPE_PK_SEQ;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_SCOPE (
SCOPE_ID INTEGER DEFAULT NEXTVAL('IDN_OAUTH2_SCOPE_PK_SEQ'),
NAME VARCHAR(255) NOT NULL,
DISPLAY_NAME VARCHAR(255) NOT NULL,
DESCRIPTION VARCHAR(512),
TENANT_ID INTEGER NOT NULL DEFAULT -1,
SCOPE_TYPE VARCHAR(255) NOT NULL,
PRIMARY KEY (SCOPE_ID),
UNIQUE (NAME, TENANT_ID)
);
DROP TABLE IF EXISTS IDN_OAUTH2_SCOPE_BINDING;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_SCOPE_BINDING (
SCOPE_ID INTEGER NOT NULL,
SCOPE_BINDING VARCHAR(255) NOT NULL,
BINDING_TYPE VARCHAR(255) NOT NULL,
FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_SCOPE(SCOPE_ID) ON DELETE CASCADE,
UNIQUE (SCOPE_ID, SCOPE_BINDING, BINDING_TYPE)
);
DROP TABLE IF EXISTS IDN_OAUTH2_RESOURCE_SCOPE;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_RESOURCE_SCOPE (
RESOURCE_PATH VARCHAR(255) NOT NULL,
SCOPE_ID INTEGER NOT NULL,
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (RESOURCE_PATH),
FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_SCOPE (SCOPE_ID) ON DELETE CASCADE
);
DROP TABLE IF EXISTS IDN_SCIM_GROUP;
DROP SEQUENCE IF EXISTS IDN_SCIM_GROUP_PK_SEQ;
CREATE SEQUENCE IDN_SCIM_GROUP_PK_SEQ;
CREATE TABLE IDN_SCIM_GROUP (
ID INTEGER DEFAULT NEXTVAL('IDN_SCIM_GROUP_PK_SEQ'),
TENANT_ID INTEGER NOT NULL,
ROLE_NAME VARCHAR(255) NOT NULL,
ATTR_NAME VARCHAR(1024) NOT NULL,
ATTR_VALUE VARCHAR(1024),
PRIMARY KEY (ID)
);
DROP TABLE IF EXISTS IDN_OPENID_REMEMBER_ME;
CREATE TABLE IDN_OPENID_REMEMBER_ME (
USER_NAME VARCHAR(255) NOT NULL,
TENANT_ID INTEGER DEFAULT 0,
COOKIE_VALUE VARCHAR(1024),
CREATED_TIME TIMESTAMP,
PRIMARY KEY (USER_NAME, TENANT_ID)
);
DROP TABLE IF EXISTS IDN_OPENID_USER_RPS;
CREATE TABLE IDN_OPENID_USER_RPS (
USER_NAME VARCHAR(255) NOT NULL,
TENANT_ID INTEGER DEFAULT 0,
RP_URL VARCHAR(255) NOT NULL,
TRUSTED_ALWAYS VARCHAR(128) DEFAULT 'FALSE',
LAST_VISIT DATE NOT NULL,
VISIT_COUNT INTEGER DEFAULT 0,
DEFAULT_PROFILE_NAME VARCHAR(255) DEFAULT 'DEFAULT',
PRIMARY KEY (USER_NAME, TENANT_ID, RP_URL)
);
DROP TABLE IF EXISTS IDN_OPENID_ASSOCIATIONS;
CREATE TABLE IDN_OPENID_ASSOCIATIONS (
HANDLE VARCHAR(255) NOT NULL,
ASSOC_TYPE VARCHAR(255) NOT NULL,
EXPIRE_IN TIMESTAMP NOT NULL,
MAC_KEY VARCHAR(255) NOT NULL,
ASSOC_STORE VARCHAR(128) DEFAULT 'SHARED',
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (HANDLE)
);
DROP TABLE IF EXISTS IDN_STS_STORE;
DROP SEQUENCE IF EXISTS IDN_STS_STORE_PK_SEQ;
CREATE SEQUENCE IDN_STS_STORE_PK_SEQ;
CREATE TABLE IDN_STS_STORE (
ID INTEGER DEFAULT NEXTVAL('IDN_STS_STORE_PK_SEQ'),
TOKEN_ID VARCHAR(255) NOT NULL,
TOKEN_CONTENT BYTEA NOT NULL,
CREATE_DATE TIMESTAMP NOT NULL,
EXPIRE_DATE TIMESTAMP NOT NULL,
STATE INTEGER DEFAULT 0,
PRIMARY KEY (ID)
);
DROP TABLE IF EXISTS IDN_IDENTITY_USER_DATA;
CREATE TABLE IDN_IDENTITY_USER_DATA (
TENANT_ID INTEGER DEFAULT -1234,
USER_NAME VARCHAR(255) NOT NULL,
DATA_KEY VARCHAR(255) NOT NULL,
DATA_VALUE VARCHAR(2048),
PRIMARY KEY (TENANT_ID, USER_NAME, DATA_KEY)
);
DROP TABLE IF EXISTS IDN_IDENTITY_META_DATA;
CREATE TABLE IDN_IDENTITY_META_DATA (
USER_NAME VARCHAR(255) NOT NULL,
TENANT_ID INTEGER DEFAULT -1234,
METADATA_TYPE VARCHAR(255) NOT NULL,
METADATA VARCHAR(255) NOT NULL,
VALID VARCHAR(255) NOT NULL,
PRIMARY KEY (TENANT_ID, USER_NAME, METADATA_TYPE,METADATA)
);
DROP TABLE IF EXISTS IDN_THRIFT_SESSION;
CREATE TABLE IDN_THRIFT_SESSION (
SESSION_ID VARCHAR(255) NOT NULL,
USER_NAME VARCHAR(255) NOT NULL,
CREATED_TIME VARCHAR(255) NOT NULL,
LAST_MODIFIED_TIME VARCHAR(255) NOT NULL,
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (SESSION_ID)
);
DROP TABLE IF EXISTS IDN_AUTH_SESSION_STORE;
CREATE TABLE IDN_AUTH_SESSION_STORE (
SESSION_ID VARCHAR(100) NOT NULL,
SESSION_TYPE VARCHAR(100) NOT NULL,
OPERATION VARCHAR(10) NOT NULL,
SESSION_OBJECT BYTEA,
TIME_CREATED BIGINT,
TENANT_ID INTEGER DEFAULT -1,
EXPIRY_TIME BIGINT,
PRIMARY KEY (SESSION_ID, SESSION_TYPE, TIME_CREATED, OPERATION)
);
DROP TABLE IF EXISTS IDN_AUTH_TEMP_SESSION_STORE;
CREATE TABLE IDN_AUTH_TEMP_SESSION_STORE (
SESSION_ID VARCHAR(100) NOT NULL,
SESSION_TYPE VARCHAR(100) NOT NULL,
OPERATION VARCHAR(10) NOT NULL,
SESSION_OBJECT BYTEA,
TIME_CREATED BIGINT,
TENANT_ID INTEGER DEFAULT -1,
EXPIRY_TIME BIGINT,
PRIMARY KEY (SESSION_ID, SESSION_TYPE, TIME_CREATED, OPERATION)
);
DROP TABLE IF EXISTS IDN_AUTH_USER;
CREATE TABLE IDN_AUTH_USER (
USER_ID VARCHAR(255) NOT NULL,
USER_NAME VARCHAR(255) NOT NULL,
TENANT_ID INTEGER NOT NULL,
DOMAIN_NAME VARCHAR(255) NOT NULL,
IDP_ID INTEGER NOT NULL,
PRIMARY KEY (USER_ID),
CONSTRAINT USER_STORE_CONSTRAINT UNIQUE (USER_NAME, TENANT_ID, DOMAIN_NAME, IDP_ID));
DROP TABLE IF EXISTS IDN_AUTH_USER_SESSION_MAPPING;
CREATE TABLE IDN_AUTH_USER_SESSION_MAPPING (
USER_ID VARCHAR(255) NOT NULL,
SESSION_ID VARCHAR(255) NOT NULL,
CONSTRAINT USER_SESSION_STORE_CONSTRAINT UNIQUE (USER_ID, SESSION_ID));
DROP TABLE IF EXISTS IDN_AUTH_SESSION_APP_INFO;
CREATE TABLE IDN_AUTH_SESSION_APP_INFO (
SESSION_ID VARCHAR (100) NOT NULL,
SUBJECT VARCHAR (100) NOT NULL,
APP_ID INTEGER NOT NULL,
INBOUND_AUTH_TYPE VARCHAR (255) NOT NULL,
PRIMARY KEY (SESSION_ID, SUBJECT, APP_ID, INBOUND_AUTH_TYPE)
);
DROP TABLE IF EXISTS IDN_AUTH_SESSION_META_DATA;
CREATE TABLE IDN_AUTH_SESSION_META_DATA (
SESSION_ID VARCHAR (100) NOT NULL,
PROPERTY_TYPE VARCHAR (100) NOT NULL,
VALUE VARCHAR (255) NOT NULL,
PRIMARY KEY (SESSION_ID, PROPERTY_TYPE, VALUE)
);
DROP TABLE IF EXISTS IDN_AUTH_USER;
CREATE TABLE IDN_AUTH_USER (
USER_ID VARCHAR(255) NOT NULL,
USER_NAME VARCHAR(255) NOT NULL,
TENANT_ID INTEGER NOT NULL,
DOMAIN_NAME VARCHAR(255) NOT NULL,
IDP_ID INTEGER NOT NULL,
PRIMARY KEY (USER_ID),
CONSTRAINT USER_STORE_CONSTRAINT UNIQUE (USER_NAME, TENANT_ID, DOMAIN_NAME, IDP_ID));
DROP TABLE IF EXISTS IDN_AUTH_USER_SESSION_MAPPING;
CREATE TABLE IDN_AUTH_USER_SESSION_MAPPING (
USER_ID VARCHAR(255) NOT NULL,
SESSION_ID VARCHAR(255) NOT NULL,
CONSTRAINT USER_SESSION_STORE_CONSTRAINT UNIQUE (USER_ID, SESSION_ID));
DROP TABLE IF EXISTS SP_APP;
DROP SEQUENCE IF EXISTS SP_APP_SEQ;
CREATE SEQUENCE SP_APP_SEQ;
CREATE TABLE SP_APP (
ID INTEGER DEFAULT NEXTVAL('SP_APP_SEQ'),
TENANT_ID INTEGER NOT NULL,
APP_NAME VARCHAR (255) NOT NULL ,
USER_STORE VARCHAR (255) NOT NULL,
USERNAME VARCHAR (255) NOT NULL ,
DESCRIPTION VARCHAR (1024),
ROLE_CLAIM VARCHAR (512),
AUTH_TYPE VARCHAR (255) NOT NULL,
PROVISIONING_USERSTORE_DOMAIN VARCHAR (512),
IS_LOCAL_CLAIM_DIALECT CHAR(1) DEFAULT '1',
IS_SEND_LOCAL_SUBJECT_ID CHAR(1) DEFAULT '0',
IS_SEND_AUTH_LIST_OF_IDPS CHAR(1) DEFAULT '0',
IS_USE_TENANT_DOMAIN_SUBJECT CHAR(1) DEFAULT '1',
IS_USE_USER_DOMAIN_SUBJECT CHAR(1) DEFAULT '1',
ENABLE_AUTHORIZATION CHAR(1) DEFAULT '0',
SUBJECT_CLAIM_URI VARCHAR (512),
IS_SAAS_APP CHAR(1) DEFAULT '0',
IS_DUMB_MODE CHAR(1) DEFAULT '0',
UUID CHAR(36),
IMAGE_URL VARCHAR(1024),
ACCESS_URL VARCHAR(1024),
IS_DISCOVERABLE CHAR(1) DEFAULT '0',
PRIMARY KEY (ID));
ALTER TABLE SP_APP ADD CONSTRAINT APPLICATION_NAME_CONSTRAINT UNIQUE(APP_NAME, TENANT_ID);
ALTER TABLE SP_APP ADD CONSTRAINT APPLICATION_UUID_CONSTRAINT UNIQUE(UUID);
DROP TABLE IF EXISTS SP_METADATA;
DROP SEQUENCE IF EXISTS SP_METADATA_SEQ;
CREATE SEQUENCE SP_METADATA_SEQ;
CREATE TABLE SP_METADATA (
ID INTEGER DEFAULT NEXTVAL('SP_METADATA_SEQ'),
SP_ID INTEGER,
NAME VARCHAR(255) NOT NULL,
VALUE VARCHAR(255) NOT NULL,
DISPLAY_NAME VARCHAR(255),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (ID),
CONSTRAINT SP_METADATA_CONSTRAINT UNIQUE (SP_ID, NAME),
FOREIGN KEY (SP_ID) REFERENCES SP_APP(ID) ON DELETE CASCADE);
DROP TABLE IF EXISTS SP_INBOUND_AUTH;
DROP SEQUENCE IF EXISTS SP_INBOUND_AUTH_SEQ;
CREATE SEQUENCE SP_INBOUND_AUTH_SEQ;
CREATE TABLE SP_INBOUND_AUTH (
ID INTEGER DEFAULT NEXTVAL('SP_INBOUND_AUTH_SEQ'),
TENANT_ID INTEGER NOT NULL,
INBOUND_AUTH_KEY VARCHAR (255),
INBOUND_AUTH_TYPE VARCHAR (255) NOT NULL,
INBOUND_CONFIG_TYPE VARCHAR (255) NOT NULL,
PROP_NAME VARCHAR (255),
PROP_VALUE VARCHAR (1024) ,
APP_ID INTEGER NOT NULL,
PRIMARY KEY (ID));
ALTER TABLE SP_INBOUND_AUTH ADD CONSTRAINT APPLICATION_ID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
DROP TABLE IF EXISTS SP_AUTH_STEP;
DROP SEQUENCE IF EXISTS SP_AUTH_STEP_SEQ;
CREATE SEQUENCE SP_AUTH_STEP_SEQ;
CREATE TABLE SP_AUTH_STEP (
ID INTEGER DEFAULT NEXTVAL('SP_AUTH_STEP_SEQ'),
TENANT_ID INTEGER NOT NULL,
STEP_ORDER INTEGER DEFAULT 1,
APP_ID INTEGER NOT NULL,
IS_SUBJECT_STEP CHAR(1) DEFAULT '0',
IS_ATTRIBUTE_STEP CHAR(1) DEFAULT '0',
PRIMARY KEY (ID));
ALTER TABLE SP_AUTH_STEP ADD CONSTRAINT APPLICATION_ID_CONSTRAINT_STEP FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
DROP TABLE IF EXISTS SP_FEDERATED_IDP;
CREATE TABLE SP_FEDERATED_IDP (
ID INTEGER NOT NULL,
TENANT_ID INTEGER NOT NULL,
AUTHENTICATOR_ID INTEGER NOT NULL,
PRIMARY KEY (ID, AUTHENTICATOR_ID));
ALTER TABLE SP_FEDERATED_IDP ADD CONSTRAINT STEP_ID_CONSTRAINT FOREIGN KEY (ID) REFERENCES SP_AUTH_STEP (ID) ON DELETE CASCADE;
DROP TABLE IF EXISTS SP_CLAIM_DIALECT;
DROP SEQUENCE IF EXISTS SP_CLAIM_DIALECT_SEQ;
CREATE SEQUENCE SP_CLAIM_DIALECT_SEQ;
CREATE TABLE SP_CLAIM_DIALECT (
ID INTEGER DEFAULT NEXTVAL('SP_CLAIM_DIALECT_SEQ'),
TENANT_ID INTEGER NOT NULL,
SP_DIALECT VARCHAR (512) NOT NULL,
APP_ID INTEGER NOT NULL,
PRIMARY KEY (ID));
ALTER TABLE SP_CLAIM_DIALECT ADD CONSTRAINT DIALECTID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
DROP TABLE IF EXISTS SP_CLAIM_MAPPING;
DROP SEQUENCE IF EXISTS SP_CLAIM_MAPPING_SEQ;
CREATE SEQUENCE SP_CLAIM_MAPPING_SEQ;
CREATE TABLE SP_CLAIM_MAPPING (
ID INTEGER DEFAULT NEXTVAL('SP_CLAIM_MAPPING_SEQ'),
TENANT_ID INTEGER NOT NULL,
IDP_CLAIM VARCHAR (512) NOT NULL ,
SP_CLAIM VARCHAR (512) NOT NULL ,
APP_ID INTEGER NOT NULL,
IS_REQUESTED VARCHAR(128) DEFAULT '0',
IS_MANDATORY VARCHAR(128) DEFAULT '0',
DEFAULT_VALUE VARCHAR(255),
PRIMARY KEY (ID));
ALTER TABLE SP_CLAIM_MAPPING ADD CONSTRAINT CLAIMID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
DROP TABLE IF EXISTS SP_ROLE_MAPPING;
DROP SEQUENCE IF EXISTS SP_ROLE_MAPPING_SEQ;
CREATE SEQUENCE SP_ROLE_MAPPING_SEQ;
CREATE TABLE SP_ROLE_MAPPING (
ID INTEGER DEFAULT NEXTVAL('SP_ROLE_MAPPING_SEQ'),
TENANT_ID INTEGER NOT NULL,
IDP_ROLE VARCHAR (255) NOT NULL ,
SP_ROLE VARCHAR (255) NOT NULL ,
APP_ID INTEGER NOT NULL,
PRIMARY KEY (ID));
ALTER TABLE SP_ROLE_MAPPING ADD CONSTRAINT ROLEID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
DROP TABLE IF EXISTS SP_REQ_PATH_AUTH;
DROP SEQUENCE IF EXISTS SP_REQ_PATH_AUTH_SEQ;
CREATE SEQUENCE SP_REQ_PATH_AUTH_SEQ;
CREATE TABLE SP_REQ_PATH_AUTHENTICATOR (
ID INTEGER DEFAULT NEXTVAL('SP_REQ_PATH_AUTH_SEQ'),
TENANT_ID INTEGER NOT NULL,
AUTHENTICATOR_NAME VARCHAR (255) NOT NULL ,
APP_ID INTEGER NOT NULL,
PRIMARY KEY (ID));
ALTER TABLE SP_REQ_PATH_AUTHENTICATOR ADD CONSTRAINT REQ_AUTH_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
DROP TABLE IF EXISTS SP_PROV_CONNECTOR;
DROP SEQUENCE IF EXISTS SP_PROV_CONNECTOR_SEQ;
CREATE SEQUENCE SP_PROV_CONNECTOR_SEQ;
CREATE TABLE SP_PROVISIONING_CONNECTOR (
ID INTEGER DEFAULT NEXTVAL('SP_PROV_CONNECTOR_SEQ'),
TENANT_ID INTEGER NOT NULL,
IDP_NAME VARCHAR (255) NOT NULL ,
CONNECTOR_NAME VARCHAR (255) NOT NULL ,
APP_ID INTEGER NOT NULL,
IS_JIT_ENABLED CHAR(1) NOT NULL DEFAULT '0',
BLOCKING CHAR(1) NOT NULL DEFAULT '0',
RULE_ENABLED CHAR(1) NOT NULL DEFAULT '0',
PRIMARY KEY (ID));
ALTER TABLE SP_PROVISIONING_CONNECTOR ADD CONSTRAINT PRO_CONNECTOR_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
DROP TABLE IF EXISTS SP_AUTH_SCRIPT;
DROP SEQUENCE IF EXISTS SP_AUTH_SCRIPT_SEQ;
CREATE SEQUENCE SP_AUTH_SCRIPT_SEQ;
CREATE TABLE SP_AUTH_SCRIPT (
ID INTEGER NOT NULL DEFAULT NEXTVAL('SP_AUTH_SCRIPT_SEQ'),
TENANT_ID INTEGER NOT NULL,
APP_ID INTEGER NOT NULL,
TYPE VARCHAR(255) NOT NULL,
CONTENT BYTEA DEFAULT NULL,
IS_ENABLED CHAR(1) NOT NULL DEFAULT '0',
PRIMARY KEY (ID)
);
DROP TABLE IF EXISTS SP_TEMPLATE;
DROP SEQUENCE IF EXISTS SP_TEMPLATE_SEQ;
CREATE SEQUENCE SP_TEMPLATE_SEQ;
CREATE TABLE SP_TEMPLATE (
ID INTEGER NOT NULL DEFAULT NEXTVAL('SP_TEMPLATE_SEQ'),
TENANT_ID INTEGER NOT NULL,
NAME VARCHAR(255) NOT NULL,
DESCRIPTION VARCHAR(1023),
CONTENT BYTEA DEFAULT NULL,
PRIMARY KEY (ID),
CONSTRAINT SP_TEMPLATE_CONSTRAINT UNIQUE (TENANT_ID, NAME)
);
DROP TABLE IF EXISTS IDN_AUTH_WAIT_STATUS;
DROP SEQUENCE IF EXISTS IDN_AUTH_WAIT_STATUS_SEQ;
CREATE SEQUENCE IDN_AUTH_WAIT_STATUS_SEQ;
CREATE TABLE IDN_AUTH_WAIT_STATUS (
ID INTEGER NOT NULL DEFAULT NEXTVAL('IDN_AUTH_WAIT_STATUS_SEQ'),
TENANT_ID INTEGER NOT NULL,
LONG_WAIT_KEY VARCHAR(255) NOT NULL,
WAIT_STATUS CHAR(1) NOT NULL DEFAULT '1',
TIME_CREATED TIMESTAMP,
EXPIRE_TIME TIMESTAMP,
PRIMARY KEY (ID),
CONSTRAINT IDN_AUTH_WAIT_STATUS_KEY UNIQUE (LONG_WAIT_KEY)
);
DROP TABLE IF EXISTS IDP;
DROP SEQUENCE IF EXISTS IDP_SEQ;
CREATE SEQUENCE IDP_SEQ;
CREATE TABLE IDP (
ID INTEGER DEFAULT NEXTVAL('IDP_SEQ'),
TENANT_ID INTEGER,
NAME VARCHAR(254) NOT NULL,
IS_ENABLED CHAR(1) NOT NULL DEFAULT '1',
IS_PRIMARY CHAR(1) NOT NULL DEFAULT '0',
HOME_REALM_ID VARCHAR(254),
IMAGE BYTEA,
CERTIFICATE BYTEA,
ALIAS VARCHAR(254),
INBOUND_PROV_ENABLED CHAR(1) NOT NULL DEFAULT '0',
INBOUND_PROV_USER_STORE_ID VARCHAR(254),
USER_CLAIM_URI VARCHAR(254),
ROLE_CLAIM_URI VARCHAR(254),
DESCRIPTION VARCHAR(1024),
DEFAULT_AUTHENTICATOR_NAME VARCHAR(254),
DEFAULT_PRO_CONNECTOR_NAME VARCHAR(254),
PROVISIONING_ROLE VARCHAR(128),
IS_FEDERATION_HUB CHAR(1) NOT NULL DEFAULT '0',
IS_LOCAL_CLAIM_DIALECT CHAR(1) NOT NULL DEFAULT '0',
DISPLAY_NAME VARCHAR(255),
IMAGE_URL VARCHAR(1024),
UUID CHAR(36) NOT NULL,
PRIMARY KEY (ID),
UNIQUE (TENANT_ID, NAME),
UNIQUE (UUID)
);
DROP TABLE IF EXISTS IDP_ROLE;
DROP SEQUENCE IF EXISTS IDP_ROLE_SEQ;
CREATE SEQUENCE IDP_ROLE_SEQ;
CREATE TABLE IDP_ROLE (
ID INTEGER DEFAULT NEXTVAL('IDP_ROLE_SEQ'),
IDP_ID INTEGER,
TENANT_ID INTEGER,
ROLE VARCHAR(254),
PRIMARY KEY (ID),
UNIQUE (IDP_ID, ROLE),
FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE);
DROP TABLE IF EXISTS IDP_ROLE_MAPPING;
DROP SEQUENCE IF EXISTS IDP_ROLE_MAPPING_SEQ;
CREATE SEQUENCE IDP_ROLE_MAPPING_SEQ;
CREATE TABLE IDP_ROLE_MAPPING (
ID INTEGER DEFAULT NEXTVAL('IDP_ROLE_MAPPING_SEQ'),
IDP_ROLE_ID INTEGER,
TENANT_ID INTEGER,
USER_STORE_ID VARCHAR (253),
LOCAL_ROLE VARCHAR(253),
PRIMARY KEY (ID),
UNIQUE (IDP_ROLE_ID, TENANT_ID, USER_STORE_ID, LOCAL_ROLE),
FOREIGN KEY (IDP_ROLE_ID) REFERENCES IDP_ROLE(ID) ON DELETE CASCADE);
DROP TABLE IF EXISTS IDP_CLAIM;
DROP SEQUENCE IF EXISTS IDP_CLAIM_SEQ;
CREATE SEQUENCE IDP_CLAIM_SEQ;
CREATE TABLE IDP_CLAIM (
ID INTEGER DEFAULT NEXTVAL('IDP_CLAIM_SEQ'),
IDP_ID INTEGER,
TENANT_ID INTEGER,
CLAIM VARCHAR(254),
PRIMARY KEY (ID),
UNIQUE (IDP_ID, CLAIM),
FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE);
DROP TABLE IF EXISTS IDP_CLAIM_MAPPING;
DROP SEQUENCE IF EXISTS IDP_CLAIM_MAPPING_SEQ;
CREATE SEQUENCE IDP_CLAIM_MAPPING_SEQ;
CREATE TABLE IDP_CLAIM_MAPPING (
ID INTEGER DEFAULT NEXTVAL('IDP_CLAIM_MAPPING_SEQ'),
IDP_CLAIM_ID INTEGER,
TENANT_ID INTEGER,
LOCAL_CLAIM VARCHAR(253),
DEFAULT_VALUE VARCHAR(255),
IS_REQUESTED VARCHAR(128) DEFAULT '0',
PRIMARY KEY (ID),
UNIQUE (IDP_CLAIM_ID, TENANT_ID, LOCAL_CLAIM),
FOREIGN KEY (IDP_CLAIM_ID) REFERENCES IDP_CLAIM(ID) ON DELETE CASCADE);
DROP TABLE IF EXISTS IDP_AUTHENTICATOR;
DROP SEQUENCE IF EXISTS IDP_AUTHENTICATOR_SEQ;
CREATE SEQUENCE IDP_AUTHENTICATOR_SEQ;
CREATE TABLE IDP_AUTHENTICATOR (
ID INTEGER DEFAULT NEXTVAL('IDP_AUTHENTICATOR_SEQ'),
TENANT_ID INTEGER,
IDP_ID INTEGER,
NAME VARCHAR(255) NOT NULL,
IS_ENABLED CHAR (1) DEFAULT '1',
DISPLAY_NAME VARCHAR(255),
PRIMARY KEY (ID),
UNIQUE (TENANT_ID, IDP_ID, NAME),
FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE);
DROP TABLE IF EXISTS IDP_METADATA;
DROP SEQUENCE IF EXISTS IDP_METADATA_SEQ;
CREATE SEQUENCE IDP_METADATA_SEQ;
CREATE TABLE IDP_METADATA (
ID INTEGER DEFAULT NEXTVAL('IDP_METADATA_SEQ'),
IDP_ID INTEGER,
NAME VARCHAR(255) NOT NULL,
VALUE VARCHAR(255) NOT NULL,
DISPLAY_NAME VARCHAR(255),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (ID),
CONSTRAINT IDP_METADATA_CONSTRAINT UNIQUE (IDP_ID, NAME),
FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE);
DROP TABLE IF EXISTS IDP_AUTHENTICATOR_PROP;
DROP SEQUENCE IF EXISTS IDP_AUTHENTICATOR_PROP_SEQ;
CREATE SEQUENCE IDP_AUTHENTICATOR_PROP_SEQ;
CREATE TABLE IDP_AUTHENTICATOR_PROPERTY (
ID INTEGER DEFAULT NEXTVAL('IDP_AUTHENTICATOR_PROP_SEQ'),
TENANT_ID INTEGER,
AUTHENTICATOR_ID INTEGER,
PROPERTY_KEY VARCHAR(255) NOT NULL,
PROPERTY_VALUE VARCHAR(2047),
IS_SECRET CHAR (1) DEFAULT '0',
PRIMARY KEY (ID),
UNIQUE (TENANT_ID, AUTHENTICATOR_ID, PROPERTY_KEY),
FOREIGN KEY (AUTHENTICATOR_ID) REFERENCES IDP_AUTHENTICATOR(ID) ON DELETE CASCADE);
DROP TABLE IF EXISTS IDP_PROV_CONFIG;
DROP SEQUENCE IF EXISTS IDP_PROV_CONFIG_SEQ;
CREATE SEQUENCE IDP_PROV_CONFIG_SEQ;
CREATE TABLE IDP_PROVISIONING_CONFIG (
ID INTEGER DEFAULT NEXTVAL('IDP_PROV_CONFIG_SEQ'),
TENANT_ID INTEGER,
IDP_ID INTEGER,
PROVISIONING_CONNECTOR_TYPE VARCHAR(255) NOT NULL,
IS_ENABLED CHAR (1) DEFAULT '0',
IS_BLOCKING CHAR (1) DEFAULT '0',
IS_RULES_ENABLED CHAR (1) DEFAULT '0',
PRIMARY KEY (ID),
UNIQUE (TENANT_ID, IDP_ID, PROVISIONING_CONNECTOR_TYPE),
FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE);
DROP TABLE IF EXISTS IDP_PROV_CONFIG_PROP;
DROP SEQUENCE IF EXISTS IDP_PROV_CONFIG_PROP_SEQ;
CREATE SEQUENCE IDP_PROV_CONFIG_PROP_SEQ;
CREATE TABLE IDP_PROV_CONFIG_PROPERTY (
ID INTEGER DEFAULT NEXTVAL('IDP_PROV_CONFIG_PROP_SEQ'),
TENANT_ID INTEGER,
PROVISIONING_CONFIG_ID INTEGER,
PROPERTY_KEY VARCHAR(255) NOT NULL,
PROPERTY_VALUE VARCHAR(2048),
PROPERTY_BLOB_VALUE BYTEA,
PROPERTY_TYPE CHAR(32) NOT NULL,
IS_SECRET CHAR (1) DEFAULT '0',
PRIMARY KEY (ID),
UNIQUE (TENANT_ID, PROVISIONING_CONFIG_ID, PROPERTY_KEY),
FOREIGN KEY (PROVISIONING_CONFIG_ID) REFERENCES IDP_PROVISIONING_CONFIG(ID) ON DELETE CASCADE);
DROP TABLE IF EXISTS IDP_PROV_ENTITY;
DROP SEQUENCE IF EXISTS IDP_PROV_ENTITY_SEQ;
CREATE SEQUENCE IDP_PROV_ENTITY_SEQ;
CREATE TABLE IDP_PROVISIONING_ENTITY (
ID INTEGER DEFAULT NEXTVAL('IDP_PROV_ENTITY_SEQ'),
PROVISIONING_CONFIG_ID INTEGER,
ENTITY_TYPE VARCHAR(255) NOT NULL,
ENTITY_LOCAL_USERSTORE VARCHAR(255) NOT NULL,
ENTITY_NAME VARCHAR(255) NOT NULL,
ENTITY_VALUE VARCHAR(255),
TENANT_ID INTEGER,
ENTITY_LOCAL_ID VARCHAR(255),
PRIMARY KEY (ID),
UNIQUE (ENTITY_TYPE, TENANT_ID, ENTITY_LOCAL_USERSTORE, ENTITY_NAME, PROVISIONING_CONFIG_ID),
UNIQUE (PROVISIONING_CONFIG_ID, ENTITY_TYPE, ENTITY_VALUE),
FOREIGN KEY (PROVISIONING_CONFIG_ID) REFERENCES IDP_PROVISIONING_CONFIG(ID) ON DELETE CASCADE);
DROP TABLE IF EXISTS IDP_LOCAL_CLAIM;
DROP SEQUENCE IF EXISTS IDP_LOCAL_CLAIM_SEQ;
CREATE SEQUENCE IDP_LOCAL_CLAIM_SEQ;
CREATE TABLE IF NOT EXISTS IDP_LOCAL_CLAIM(
ID INTEGER DEFAULT NEXTVAL('IDP_LOCAL_CLAIM_SEQ'),
TENANT_ID INTEGER,
IDP_ID INTEGER,
CLAIM_URI VARCHAR(255) NOT NULL,
DEFAULT_VALUE VARCHAR(255),
IS_REQUESTED VARCHAR(128) DEFAULT '0',
PRIMARY KEY (ID),
UNIQUE (TENANT_ID, IDP_ID, CLAIM_URI),
FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE);
DROP TABLE IF EXISTS IDN_ASSOCIATED_ID;
DROP SEQUENCE IF EXISTS IDN_ASSOCIATED_ID_SEQ;
CREATE SEQUENCE IDN_ASSOCIATED_ID_SEQ;
CREATE TABLE IDN_ASSOCIATED_ID (
ID INTEGER DEFAULT NEXTVAL('IDN_ASSOCIATED_ID_SEQ'),
IDP_USER_ID VARCHAR(255) NOT NULL,
TENANT_ID INTEGER DEFAULT -1234,
IDP_ID INTEGER NOT NULL,
DOMAIN_NAME VARCHAR(255) NOT NULL,
USER_NAME VARCHAR(255) NOT NULL,
ASSOCIATION_ID CHAR(36) NOT NULL,
PRIMARY KEY (ID),
UNIQUE(IDP_USER_ID, TENANT_ID, IDP_ID),
FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
);
DROP TABLE IF EXISTS IDN_USER_ACCOUNT_ASSOCIATION;
CREATE TABLE IDN_USER_ACCOUNT_ASSOCIATION (
ASSOCIATION_KEY VARCHAR(255) NOT NULL,
TENANT_ID INTEGER,
DOMAIN_NAME VARCHAR(255) NOT NULL,
USER_NAME VARCHAR(255) NOT NULL,
PRIMARY KEY (TENANT_ID, DOMAIN_NAME, USER_NAME));
DROP TABLE IF EXISTS FIDO_DEVICE_STORE;
CREATE TABLE FIDO_DEVICE_STORE (
TENANT_ID INTEGER,
DOMAIN_NAME VARCHAR(255) NOT NULL,
USER_NAME VARCHAR(45) NOT NULL,
TIME_REGISTERED TIMESTAMP,
KEY_HANDLE VARCHAR(200) NOT NULL,
DEVICE_DATA VARCHAR(2048) NOT NULL,
PRIMARY KEY (TENANT_ID, DOMAIN_NAME, USER_NAME, KEY_HANDLE));
DROP TABLE IF EXISTS FIDO2_DEVICE_STORE;
CREATE TABLE FIDO2_DEVICE_STORE (
TENANT_ID INTEGER,
DOMAIN_NAME VARCHAR(255) NOT NULL,
USER_NAME VARCHAR(45) NOT NULL,
TIME_REGISTERED TIMESTAMP,
USER_HANDLE VARCHAR(64) NOT NULL,
CREDENTIAL_ID VARCHAR(200) NOT NULL,
PUBLIC_KEY_COSE VARCHAR(1024) NOT NULL,
SIGNATURE_COUNT BIGINT,
USER_IDENTITY VARCHAR(512) NOT NULL,
DISPLAY_NAME VARCHAR(255),
IS_USERNAMELESS_SUPPORTED CHAR(1) DEFAULT '0',
PRIMARY KEY (CREDENTIAL_ID, USER_HANDLE));
DROP TABLE IF EXISTS WF_REQUEST;
CREATE TABLE WF_REQUEST (
UUID VARCHAR (45),
CREATED_BY VARCHAR (255),
TENANT_ID INTEGER DEFAULT -1,
OPERATION_TYPE VARCHAR (50),
CREATED_AT TIMESTAMP,
UPDATED_AT TIMESTAMP,
STATUS VARCHAR (30),
REQUEST BYTEA,
PRIMARY KEY (UUID)
);
DROP TABLE IF EXISTS WF_BPS_PROFILE;
CREATE TABLE WF_BPS_PROFILE (
PROFILE_NAME VARCHAR(45),
HOST_URL_MANAGER VARCHAR(255),
HOST_URL_WORKER VARCHAR(255),
USERNAME VARCHAR(45),
PASSWORD VARCHAR(1023),
CALLBACK_HOST VARCHAR (45),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (PROFILE_NAME, TENANT_ID)
);
DROP TABLE IF EXISTS WF_WORKFLOW;
CREATE TABLE WF_WORKFLOW(
ID VARCHAR (45),
WF_NAME VARCHAR (45),
DESCRIPTION VARCHAR (255),
TEMPLATE_ID VARCHAR (45),
IMPL_ID VARCHAR (45),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (ID)
);
DROP TABLE IF EXISTS WF_WORKFLOW_ASSOCIATION;
DROP SEQUENCE IF EXISTS WF_WORKFLOW_ASSOCIATION_PK_SEQ;
CREATE SEQUENCE WF_WORKFLOW_ASSOCIATION_PK_SEQ;
CREATE TABLE WF_WORKFLOW_ASSOCIATION(
ID INTEGER DEFAULT NEXTVAL('WF_WORKFLOW_ASSOCIATION_PK_SEQ'),
ASSOC_NAME VARCHAR (45),
EVENT_ID VARCHAR(45),
ASSOC_CONDITION VARCHAR (2000),
WORKFLOW_ID VARCHAR (45),
IS_ENABLED CHAR (1) DEFAULT '1',
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY(ID),
FOREIGN KEY (WORKFLOW_ID) REFERENCES WF_WORKFLOW(ID)ON DELETE CASCADE
);
DROP TABLE IF EXISTS WF_WORKFLOW_CONFIG_PARAM;
CREATE TABLE WF_WORKFLOW_CONFIG_PARAM(
WORKFLOW_ID VARCHAR (45),
PARAM_NAME VARCHAR (45),
PARAM_VALUE VARCHAR (1000),
PARAM_QNAME VARCHAR (45),
PARAM_HOLDER VARCHAR (45),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (WORKFLOW_ID, PARAM_NAME, PARAM_QNAME, PARAM_HOLDER),
FOREIGN KEY (WORKFLOW_ID) REFERENCES WF_WORKFLOW(ID)ON DELETE CASCADE
);
DROP TABLE IF EXISTS WF_REQUEST_ENTITY_RELATIONSHIP;
CREATE TABLE WF_REQUEST_ENTITY_RELATIONSHIP(
REQUEST_ID VARCHAR (45),
ENTITY_NAME VARCHAR (255),
ENTITY_TYPE VARCHAR (50),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY(REQUEST_ID, ENTITY_NAME, ENTITY_TYPE, TENANT_ID),
FOREIGN KEY (REQUEST_ID) REFERENCES WF_REQUEST(UUID)ON DELETE CASCADE
);
DROP TABLE IF EXISTS WF_WORKFLOW_REQUEST_RELATION;
CREATE TABLE WF_WORKFLOW_REQUEST_RELATION(
RELATIONSHIP_ID VARCHAR (45),
WORKFLOW_ID VARCHAR (45),
REQUEST_ID VARCHAR (45),
UPDATED_AT TIMESTAMP,
STATUS VARCHAR (30),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (RELATIONSHIP_ID),
FOREIGN KEY (WORKFLOW_ID) REFERENCES WF_WORKFLOW(ID)ON DELETE CASCADE,
FOREIGN KEY (REQUEST_ID) REFERENCES WF_REQUEST(UUID)ON DELETE CASCADE
);
DROP TABLE IF EXISTS IDN_RECOVERY_DATA;
CREATE TABLE IDN_RECOVERY_DATA (
USER_NAME VARCHAR(255) NOT NULL,
USER_DOMAIN VARCHAR(127) NOT NULL,
TENANT_ID INTEGER DEFAULT -1,
CODE VARCHAR(255) NOT NULL,
SCENARIO VARCHAR(255) NOT NULL,
STEP VARCHAR(127) NOT NULL,
TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
REMAINING_SETS VARCHAR(2500) DEFAULT NULL,
PRIMARY KEY(USER_NAME, USER_DOMAIN, TENANT_ID, SCENARIO,STEP),
UNIQUE(CODE)
);
DROP TABLE IF EXISTS IDN_PASSWORD_HISTORY_DATA;
CREATE SEQUENCE IDN_PASSWORD_HISTORY_DATA_PK_SEQ;
CREATE TABLE IDN_PASSWORD_HISTORY_DATA (
ID INTEGER DEFAULT NEXTVAL('IDN_PASSWORD_HISTORY_DATA_PK_SEQ'),
USER_NAME VARCHAR(255) NOT NULL,
USER_DOMAIN VARCHAR(127) NOT NULL,
TENANT_ID INTEGER DEFAULT -1,
SALT_VALUE VARCHAR(255),
HASH VARCHAR(255) NOT NULL,
TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (ID),
UNIQUE (USER_NAME,USER_DOMAIN,TENANT_ID,SALT_VALUE,HASH)
);
DROP TABLE IF EXISTS IDN_CLAIM_DIALECT;
DROP SEQUENCE IF EXISTS IDN_CLAIM_DIALECT_SEQ;
CREATE SEQUENCE IDN_CLAIM_DIALECT_SEQ;
CREATE TABLE IDN_CLAIM_DIALECT (
ID INTEGER DEFAULT NEXTVAL('IDN_CLAIM_DIALECT_SEQ'),
DIALECT_URI VARCHAR (255) NOT NULL,
TENANT_ID INTEGER NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT DIALECT_URI_CONSTRAINT UNIQUE (DIALECT_URI, TENANT_ID)
);
DROP TABLE IF EXISTS IDN_CLAIM;
DROP SEQUENCE IF EXISTS IDN_CLAIM_SEQ;
CREATE SEQUENCE IDN_CLAIM_SEQ;
CREATE TABLE IDN_CLAIM (
ID INTEGER DEFAULT NEXTVAL('IDN_CLAIM_SEQ'),
DIALECT_ID INTEGER NOT NULL,
CLAIM_URI VARCHAR (255) NOT NULL,
TENANT_ID INTEGER NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (DIALECT_ID) REFERENCES IDN_CLAIM_DIALECT(ID) ON DELETE CASCADE,
CONSTRAINT CLAIM_URI_CONSTRAINT UNIQUE (DIALECT_ID, CLAIM_URI, TENANT_ID)
);
DROP TABLE IF EXISTS IDN_CLAIM_MAPPED_ATTRIBUTE;
DROP SEQUENCE IF EXISTS IDN_CLAIM_MAPPED_ATTRIBUTE_SEQ;
CREATE SEQUENCE IDN_CLAIM_MAPPED_ATTRIBUTE_SEQ;
CREATE TABLE IDN_CLAIM_MAPPED_ATTRIBUTE (
ID INTEGER DEFAULT NEXTVAL('IDN_CLAIM_MAPPED_ATTRIBUTE_SEQ'),
LOCAL_CLAIM_ID INTEGER,
USER_STORE_DOMAIN_NAME VARCHAR (255) NOT NULL,
ATTRIBUTE_NAME VARCHAR (255) NOT NULL,
TENANT_ID INTEGER NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (LOCAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
CONSTRAINT USER_STORE_DOMAIN_CONSTRAINT UNIQUE (LOCAL_CLAIM_ID, USER_STORE_DOMAIN_NAME, TENANT_ID)
);
DROP TABLE IF EXISTS IDN_CLAIM_PROPERTY;
DROP SEQUENCE IF EXISTS IDN_CLAIM_PROPERTY_SEQ;
CREATE SEQUENCE IDN_CLAIM_PROPERTY_SEQ;
CREATE TABLE IDN_CLAIM_PROPERTY (
ID INTEGER DEFAULT NEXTVAL('IDN_CLAIM_PROPERTY_SEQ'),
LOCAL_CLAIM_ID INTEGER,
PROPERTY_NAME VARCHAR (255) NOT NULL,
PROPERTY_VALUE VARCHAR (255) NOT NULL,
TENANT_ID INTEGER NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (LOCAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
CONSTRAINT PROPERTY_NAME_CONSTRAINT UNIQUE (LOCAL_CLAIM_ID, PROPERTY_NAME, TENANT_ID)
);
DROP TABLE IF EXISTS IDN_CLAIM_MAPPING;
DROP SEQUENCE IF EXISTS IDN_CLAIM_MAPPING_SEQ;
CREATE SEQUENCE IDN_CLAIM_MAPPING_SEQ;
CREATE TABLE IDN_CLAIM_MAPPING (