Skip to Main Content
  • Home
  • Articles
  • How to Build a WSO2 Identity Server Cluster With Kubernetes and Raspberry Pi

How to Build a WSO2 Identity Server Cluster With Kubernetes and Raspberry Pi

In this article, you will learn what Kubernetes is and how to build a WSO2 Identity Server cluster with Kubernetes and Raspberry Pi.

We will focus on the following index:

  1. What is Kubernetes?
  2. Requirements
  3. Architecture
  4. Raspberry Pi OS Installation and Configuration
  5. Preconfiguration
  6. Kubernetes (master node) Installation and Configuration
  7. Kubernetes (worker nodes) Installation and Configuration
  8. MySQL (docker) Installation and Configuration
  9. Deploying the Identity Server
  10. Useful Commands

1. What is Kubernetes?

Kubernetes is an open-source platform written in Go that facilitates the management of workloads, scalable deployments, and container-based applications (docker by default). It allows you to orchestrate infrastructure, networks, and storage.

It is made up of a central administrator (master node) and a number of worker nodes. In the master node, Kubernetes runs on an API server (kube-apiserver), a scheduler, several controllers, and a storage system that maintains the state of the cluster and the configuration of the containers and the network.

Some important Kubernetes components include:

  • kubectl a client developed to consume the implemented API in order to manage all the kubernetes configurations.
  • Pod consists of one or more containers that share an IP address, storage access, and
  • Namespaces are used to divide cluster resources, since the pods need to make use of the services in order to communicate.
  • Controllers (are also known as operators) are a series of monitoring loops used to manage orchestration. Each controller examines the kubeapiserver for the status of a specific object and then modifies said object until the declared state coincides with the current state.
  • Deployment is the predetermined operator for containers and allows you to define the different functions and configurations. A deployment does not work directly with pods, instead it manages
  • ReplicaSet is an operator that will create or terminate pods by sending a podSpec. The podSpec is sent to the kubelet, which then interacts with the container engine to spawn or terminate a container until the requested number is running.
  • K3s is a Kubernetes distribution that supports both ARM64 and ARMv7 In other words, it is a lightweight edition of Kubernetes that can be deployed on Raspberry Pi.

2. Requirements for Building a WSO2 IS Cluster With Kubernetes and Raspberry Pi

The following elements are necessary to create the lab:

  • 4 Raspberry Pi 4 Model B 4GB.
  • 4 MicroSD 32GB.
  • 4 UTP CAT6 cables.
  • 4 USB Type-C to USB-A cables.
  • 1 Anker PowerPort power supply unit.
  • 1 Raspberry Pi 4 acrylic case.
  • 1 Ubiquiti UniFi US-8 switch.
  • 1 micro-HDMI to HDMI cable.

3. Architecture

In this example with 4 nodes, the 0x00 node will act as a database server. Here, all shared and identity resources for the Identity Server will be stored. nodeA will act as the master node and its sole function will be to manage the worker nodes. nodeB and nodeC will act as worker nodes, that is, where identity server will run. The databases of each of the Identity Server instances in H2 will be created in these nodes.

4. Raspberry Pi OS Installation and Configuration

The first step is to install Raspberry Pi OS LITE using PiImager. Raspberry Pi has a short 40 second video which clearly explains how to install Raspberry Pi OS on a SD card and a very descriptive article from which you can download Pilmager.

  1. HOW TO INSTALL RASPBERRY PI OS to your Raspberry Pi with ease – Raspberry Pi Imager (Raspbian)
  2. https://www.raspberrypi.org/software/

Once the Raspberry Pi OS installation is complete, we must edit the boot options to enable the use of certain applications such as Docker or Kubernetes. This requires enabling support for cgroup limits by modifying the /boot/cmdline.txt file and adding cgroup_memory=1 cgroup_enable=memory at the end of the file.

E.g. console=serial0,115200 console=tty1 root=/dev/mmcblk0p7 rootfstype=ext4 elevator=deadline fsck.repair=yes rootwait cgroup_memory=1 cgroup_enable=memory

Once the Raspberry Pi OS has been modified and installed we can connect and run the nodes.

5. Preconfiguration

In order to facilitate the installation and configuration process the tasks will be carried out via SSH. First, we must connect to each of the nodes through HDMI. We must then gain access with the username pi and the password raspberry to perform the following actions:

1.Enable SSH with the following commands:

sudo systemctl start ssh

sudo systemctl enable ssh

2. After enabling SSH we will be able to perform the following actions remotely.

ssh pi@192.168.1.102

3. Configure the hostname, each node will have a different hostname (nodeA, nodeB, etc.).

sudo raspi-config

4. Select the option “1 System Options”.

5. Select the option “S4 Hostname”.

6. Insert the new hostname for each node as per the previous diagram.

By default, Raspberry Pi only allows 99 MB of swap memory. This can cause performance problems, among other issues. To resolve this, increase the limits of dphys-swapfile by editing the /etc/dphys-swapfile file and modifying the CONF_SWAPSIZE=99 option to CONF_SWAPSIZE=2048. This will increase the limit from 99 MB to 2 GB.

7. Switch off dphys-swapfile:

sudo dphys-swapfile swapoff

8. Modify the limits (press Ctrl + O to save and Ctrl + X to exit).

sudo nano /etc/dphys-swapfile

9. Launch dphys-swapfile:

sudo dphys-swapfile swapon

10. Create and configure a new WSO2 user and remove the pi user.

11. Create the WSO2 user and its home directory.

sudo useradd -m WSO2

12. Set a new password for the WSO2 user.

sudo passwd WSO2

13. Edit the sudoers file and add the line WSO2 ALL=(ALL:ALL) ALL to enable the WSO2 user to execute commands as a superuser.

sudo nano /etc/sudoers

14. Restart each of the nodes and log in using the WSO2 user through via a SSH console.

ssh WSO2@192.168.1.102

15. Remove the default pi user in Raspberry Pi OS.

sudo userdel pi

16. Validate communication between nodes by editing the /etc/hosts file in each of them and adding their respective hostnames and IP addresses.

sudo nano /etc/hosts

17. Install dnsutils and validate the hostname resolution in each of the nodes.

sudo apt install dnsutils

nslookup nodeA

nslookup nodeB

nslookup nodeC

nslookup 0x00

6. Kubernetes (Master Node) Installation and Configuration

In this context, nodeA will be designated as the master node. That is to say, this node will focus on managing the pods and the cluster itself. To do this, connect to nodeA via SSH and install K3s:

1.Download and install K3s.

curl -sfL https://get.k3s.io | sh -

2. Once the process is complete and the message [INFO] systemd: Starting k3s appears, check that K3s is running correctly using the command

sudo systemctl status k3s

3. Now, we must obtain the master node token in order to register the worker nodes K106b0755ffcf7051652038b049db6c9120df4aa13ec98b12072beea4e6e1eb57a3::server:76d986f87ac5d864833e87e3ec2f4164).

sudo cat /var/lib/rancher/k3s/server/node-token

7. Kubernetes (Worker Nodes) Installation and Configuration

In each of the worker nodes (nodeB and nodeC), connect via SSH and install k3s-agent. Register the nodes with the token (K106b0755ffcf7051652038b049db6c9120df4aa13ec98b12072beea4e6e1eb57a3::server:76d986f87ac5d864833e87e3ec2f4164)  and IP address obtained from the master node (192.168.1.102).

curl -sfL http://get.k3s.io | K3S_URL=https://192.168.1.102:6443 K3S_TOKEN=K106b0755ffcf7051652038b049db6c9120df4aa13ec98b12072beea4e6e1eb57a3::server:76d986f87ac5d864833e87e3ec2f4164 sh -

Check that the installation has been successfully carried out by listing the nodes from the master node. We should see the master node (nodeA) and the worker nodes (nodeB and nodeC).

sudo kubectl get nodes

8. MySQL (Docker) Installation and Configuration

The fourth node (0x00) will act as a MariaDB database server using Docker. For this, you must carry out some of the previous steps in node 0x00:

1.Create theWSO2 user and its home directory.

sudo useradd -m WSO2

2. Set a new password for the WSO2 user.

sudo passwd WSO2

3. Edit the sudoers file and add the line WSO2 ALL=(ALL:ALL) ALL to enable the WSO2 user to execute commands as a superuser.

sudo nano /etc/sudoers

4. Edit the /etc/hosts file and add the hostname and ID for each of the nodes.

sudo nano /etc/hosts

5. Install Docker.

curl -fsSL https://get.docker.com | sh

6. Once the installation is complete, add the WSO2 user to the Docker group to enable it execute Docker commands.

sudo usermod -aG docker WSO2

7. Use one Docker image compatible with ARM architectures (https://hub.docker.com/r/jsurf/rpi-mariadb) and pull the image.

docker pull jsurf/rpi-mariadb

8. Create the mysql.sql script based on the WSO2 official resource for Kubernetes (https://github.com/wso2/kubernetes-is/blob/master/simple/deployment-scripts/wso2is-ga.sh).

DROP DATABASE IF EXISTS WSO2IS_SHARED_DB;
DROP DATABASE IF EXISTS WSO2IS_IDENTITY_DB;
CREATE DATABASE WSO2IS_SHARED_DB;
CREATE DATABASE WSO2IS_IDENTITY_DB;
GRANT ALL ON WSO2IS_SHARED_DB.* TO 'wso2carbon'@'%' IDENTIFIED BY 'wso2carbon';
GRANT ALL ON WSO2IS_IDENTITY_DB.* TO 'wso2carbon'@'%' IDENTIFIED BY 'wso2carbon';
USE WSO2IS_SHARED_DB;
CREATE TABLE IF NOT EXISTS REG_CLUSTER_LOCK (
REG_LOCK_NAME VARCHAR (20),
REG_LOCK_STATUS VARCHAR (20),
REG_LOCKED_TIME TIMESTAMP,
REG_TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY (REG_LOCK_NAME)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS REG_LOG (
REG_LOG_ID INTEGER AUTO_INCREMENT,
REG_PATH VARCHAR (750),
REG_USER_ID VARCHAR (31) NOT NULL,
REG_LOGGED_TIME TIMESTAMP NOT NULL,
REG_ACTION INTEGER NOT NULL,
REG_ACTION_DATA VARCHAR (500),
REG_TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY (REG_LOG_ID, REG_TENANT_ID)
)ENGINE INNODB;
CREATE INDEX REG_LOG_IND_BY_REGLOG USING HASH ON REG_LOG(REG_LOGGED_TIME, REG_TENANT_ID);
-- The REG_PATH_VALUE should be less than 767 bytes, and hence was fixed at 750.
-- See CARBON-5917.
CREATE TABLE IF NOT EXISTS REG_PATH(
REG_PATH_ID INTEGER NOT NULL AUTO_INCREMENT,
REG_PATH_VALUE VARCHAR(750) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
REG_PATH_PARENT_ID INTEGER,
REG_TENANT_ID INTEGER DEFAULT 0,
CONSTRAINT PK_REG_PATH PRIMARY KEY(REG_PATH_ID, REG_TENANT_ID),
CONSTRAINT UNIQUE_REG_PATH_TENANT_ID UNIQUE (REG_PATH_VALUE,REG_TENANT_ID)
)ENGINE INNODB;
CREATE INDEX REG_PATH_IND_BY_PATH_PARENT_ID USING HASH ON REG_PATH(REG_PATH_PARENT_ID, REG_TENANT_ID);
CREATE TABLE IF NOT EXISTS REG_CONTENT (
REG_CONTENT_ID INTEGER NOT NULL AUTO_INCREMENT,
REG_CONTENT_DATA LONGBLOB,
REG_TENANT_ID INTEGER DEFAULT 0,
CONSTRAINT PK_REG_CONTENT PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS REG_CONTENT_HISTORY (
REG_CONTENT_ID INTEGER NOT NULL,
REG_CONTENT_DATA LONGBLOB,
REG_DELETED   SMALLINT,
REG_TENANT_ID INTEGER DEFAULT 0,
CONSTRAINT PK_REG_CONTENT_HISTORY PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS REG_RESOURCE (
REG_PATH_ID         INTEGER NOT NULL,
REG_NAME            VARCHAR(256),
REG_VERSION         INTEGER NOT NULL AUTO_INCREMENT,
REG_MEDIA_TYPE      VARCHAR(500),
REG_CREATOR         VARCHAR(31) NOT NULL,
REG_CREATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
REG_LAST_UPDATOR    VARCHAR(31),
REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
REG_DESCRIPTION     VARCHAR(1000),
REG_CONTENT_ID      INTEGER,
REG_TENANT_ID INTEGER DEFAULT 0,
REG_UUID VARCHAR(100) NOT NULL,
CONSTRAINT PK_REG_RESOURCE PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
)ENGINE INNODB;
ALTER TABLE REG_RESOURCE ADD CONSTRAINT REG_RESOURCE_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
ALTER TABLE REG_RESOURCE ADD CONSTRAINT REG_RESOURCE_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT (REG_CONTENT_ID, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_IND_BY_NAME USING HASH ON REG_RESOURCE(REG_NAME, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_IND_BY_PATH_ID_NAME USING HASH ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_IND_BY_UUID USING HASH ON REG_RESOURCE(REG_UUID);
CREATE INDEX REG_RESOURCE_IND_BY_TENAN USING HASH ON REG_RESOURCE(REG_TENANT_ID, REG_UUID);
CREATE INDEX REG_RESOURCE_IND_BY_TYPE USING HASH ON REG_RESOURCE(REG_TENANT_ID, REG_MEDIA_TYPE);
CREATE TABLE IF NOT EXISTS REG_RESOURCE_HISTORY (
REG_PATH_ID         INTEGER NOT NULL,
REG_NAME            VARCHAR(256),
REG_VERSION         INTEGER NOT NULL,
REG_MEDIA_TYPE      VARCHAR(500),
REG_CREATOR         VARCHAR(31) NOT NULL,
REG_CREATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
REG_LAST_UPDATOR    VARCHAR(31),
REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
REG_DESCRIPTION     VARCHAR(1000),
REG_CONTENT_ID      INTEGER,
REG_DELETED         SMALLINT,
REG_TENANT_ID INTEGER DEFAULT 0,
REG_UUID VARCHAR(100) NOT NULL,
CONSTRAINT PK_REG_RESOURCE_HISTORY PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
)ENGINE INNODB;
ALTER TABLE REG_RESOURCE_HISTORY ADD CONSTRAINT REG_RESOURCE_HIST_FK_BY_PATHID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
ALTER TABLE REG_RESOURCE_HISTORY ADD CONSTRAINT REG_RESOURCE_HIST_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT_HISTORY (REG_CONTENT_ID, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_NAME USING HASH ON REG_RESOURCE_HISTORY(REG_NAME, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_PATH_ID_NAME USING HASH ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID);
CREATE TABLE IF NOT EXISTS REG_COMMENT (
REG_ID        INTEGER NOT NULL AUTO_INCREMENT,
REG_COMMENT_TEXT      VARCHAR(500) NOT NULL,
REG_USER_ID           VARCHAR(31) NOT NULL,
REG_COMMENTED_TIME    TIMESTAMP NOT NULL,
REG_TENANT_ID INTEGER DEFAULT 0,
CONSTRAINT PK_REG_COMMENT PRIMARY KEY(REG_ID, REG_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS REG_RESOURCE_COMMENT (
REG_COMMENT_ID          INTEGER NOT NULL,
REG_VERSION             INTEGER,
REG_PATH_ID             INTEGER,
REG_RESOURCE_NAME       VARCHAR(256),
REG_TENANT_ID INTEGER DEFAULT 0
)ENGINE INNODB;
ALTER TABLE REG_RESOURCE_COMMENT ADD CONSTRAINT REG_RESOURCE_COMMENT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
ALTER TABLE REG_RESOURCE_COMMENT ADD CONSTRAINT REG_RESOURCE_COMMENT_FK_BY_COMMENT_ID FOREIGN KEY (REG_COMMENT_ID, REG_TENANT_ID) REFERENCES REG_COMMENT (REG_ID, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_COMMENT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_VERSION USING HASH ON REG_RESOURCE_COMMENT(REG_VERSION, REG_TENANT_ID);
CREATE TABLE IF NOT EXISTS REG_RATING (
REG_ID     INTEGER NOT NULL AUTO_INCREMENT,
REG_RATING        INTEGER NOT NULL,
REG_USER_ID       VARCHAR(31) NOT NULL,
REG_RATED_TIME    TIMESTAMP NOT NULL,
REG_TENANT_ID INTEGER DEFAULT 0,
CONSTRAINT PK_REG_RATING PRIMARY KEY(REG_ID, REG_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS REG_RESOURCE_RATING (
REG_RATING_ID           INTEGER NOT NULL,
REG_VERSION             INTEGER,
REG_PATH_ID             INTEGER,
REG_RESOURCE_NAME       VARCHAR(256),
REG_TENANT_ID INTEGER DEFAULT 0
)ENGINE INNODB;
ALTER TABLE REG_RESOURCE_RATING ADD CONSTRAINT REG_RESOURCE_RATING_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
ALTER TABLE REG_RESOURCE_RATING ADD CONSTRAINT REG_RESOURCE_RATING_FK_BY_RATING_ID FOREIGN KEY (REG_RATING_ID, REG_TENANT_ID) REFERENCES REG_RATING (REG_ID, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_RATING_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_RATING(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_RATING_IND_BY_VERSION USING HASH ON REG_RESOURCE_RATING(REG_VERSION, REG_TENANT_ID);
CREATE TABLE IF NOT EXISTS REG_TAG (
REG_ID         INTEGER NOT NULL AUTO_INCREMENT,
REG_TAG_NAME       VARCHAR(500) NOT NULL,
REG_USER_ID        VARCHAR(31) NOT NULL,
REG_TAGGED_TIME    TIMESTAMP NOT NULL,
REG_TENANT_ID INTEGER DEFAULT 0,
CONSTRAINT PK_REG_TAG PRIMARY KEY(REG_ID, REG_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS REG_RESOURCE_TAG (
REG_TAG_ID              INTEGER NOT NULL,
REG_VERSION             INTEGER,
REG_PATH_ID             INTEGER,
REG_RESOURCE_NAME       VARCHAR(256),
REG_TENANT_ID INTEGER DEFAULT 0
)ENGINE INNODB;
ALTER TABLE REG_RESOURCE_TAG ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
ALTER TABLE REG_RESOURCE_TAG ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_TAG_ID FOREIGN KEY (REG_TAG_ID, REG_TENANT_ID) REFERENCES REG_TAG (REG_ID, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_TAG_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_TAG(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_TAG_IND_BY_VERSION USING HASH ON REG_RESOURCE_TAG(REG_VERSION, REG_TENANT_ID);
CREATE TABLE IF NOT EXISTS REG_PROPERTY (
REG_ID         INTEGER NOT NULL AUTO_INCREMENT,
REG_NAME       VARCHAR(100) NOT NULL,
REG_VALUE        VARCHAR(1000),
REG_TENANT_ID INTEGER DEFAULT 0,
CONSTRAINT PK_REG_PROPERTY PRIMARY KEY(REG_ID, REG_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS REG_RESOURCE_PROPERTY (
REG_PROPERTY_ID         INTEGER NOT NULL,
REG_VERSION             INTEGER,
REG_PATH_ID             INTEGER,
REG_RESOURCE_NAME       VARCHAR(256),
REG_TENANT_ID INTEGER DEFAULT 0
)ENGINE INNODB;
ALTER TABLE REG_RESOURCE_PROPERTY ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
ALTER TABLE REG_RESOURCE_PROPERTY ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_TAG_ID FOREIGN KEY (REG_PROPERTY_ID, REG_TENANT_ID) REFERENCES REG_PROPERTY (REG_ID, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_PROPERTY(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_VERSION USING HASH ON REG_RESOURCE_PROPERTY(REG_VERSION, REG_TENANT_ID);
-- CREATE TABLE IF NOT EXISTS REG_ASSOCIATIONS (
-- SRC_PATH_ID     INTEGER,
-- SRC_RESOURCE_NAME    VARCHAR(256),
-- SRC_VERSION     INTEGER,
-- TGT_PATH_ID     INTEGER,
-- TGT_RESOURCE_NAME    VARCHAR(256),
-- TGT_VERSION     INTEGER
-- )ENGINE INNODB;
--
-- ALTER TABLE REG_ASSOCIATIONS ADD CONSTRAINT REG_ASSOCIATIONS_FK_BY_SRC_PATH_ID FOREIGN KEY (SRC_PATH_ID) REFERENCES REG_PATH (PATH_ID);
-- ALTER TABLE REG_ASSOCIATIONS ADD CONSTRAINT REG_ASSOCIATIONS_FK_BY_TGT_PATH_ID FOREIGN KEY (TGT_PATH_ID) REFERENCES REG_PATH (PATH_ID);
-- CREATE INDEX REG_ASSOCIATIONS_IND_BY_SRC_VERSION ON REG_ASSOCIATIONS(SRC_VERSION);
-- CREATE INDEX REG_ASSOCIATIONS_IND_BY_TGT_VERSION ON REG_ASSOCIATIONS(TGT_VERSION);
-- CREATE INDEX REG_ASSOCIATIONS_IND_BY_SRC_RESOURCE_NAME ON REG_ASSOCIATIONS(SRC_RESOURCE_NAME);
-- CREATE INDEX REG_ASSOCIATIONS_IND_BY_TGT_RESOURCE_NAME ON REG_ASSOCIATIONS(TGT_RESOURCE_NAME);
CREATE TABLE IF NOT EXISTS REG_ASSOCIATION (
REG_ASSOCIATION_ID INTEGER AUTO_INCREMENT,
REG_SOURCEPATH VARCHAR (750) NOT NULL,
REG_TARGETPATH VARCHAR (750) NOT NULL,
REG_ASSOCIATION_TYPE VARCHAR (2000) NOT NULL,
REG_TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY (REG_ASSOCIATION_ID, REG_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS REG_SNAPSHOT (
REG_SNAPSHOT_ID     INTEGER NOT NULL AUTO_INCREMENT,
REG_PATH_ID            INTEGER NOT NULL,
REG_RESOURCE_NAME      VARCHAR(255),
REG_RESOURCE_VIDS     LONGBLOB NOT NULL,
REG_TENANT_ID INTEGER DEFAULT 0,
CONSTRAINT PK_REG_SNAPSHOT PRIMARY KEY(REG_SNAPSHOT_ID, REG_TENANT_ID)
)ENGINE INNODB;
CREATE INDEX REG_SNAPSHOT_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_SNAPSHOT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
ALTER TABLE REG_SNAPSHOT ADD CONSTRAINT REG_SNAPSHOT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
-- ################################
-- USER MANAGER TABLES
-- ################################
CREATE TABLE UM_TENANT (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_TENANT_UUID VARCHAR(36) NOT NULL,
UM_DOMAIN_NAME VARCHAR(255) NOT NULL,
UM_EMAIL VARCHAR(255),
UM_ACTIVE BOOLEAN DEFAULT FALSE,
UM_CREATED_DATE TIMESTAMP NOT NULL,
UM_USER_CONFIG LONGBLOB,
PRIMARY KEY (UM_ID),
UNIQUE(UM_DOMAIN_NAME),
UNIQUE(UM_TENANT_UUID)
)ENGINE INNODB;
CREATE TABLE UM_DOMAIN(
UM_DOMAIN_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_DOMAIN_NAME VARCHAR(255) NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY (UM_DOMAIN_ID, UM_TENANT_ID),
UNIQUE(UM_DOMAIN_NAME,UM_TENANT_ID)
)ENGINE INNODB;
CREATE UNIQUE INDEX INDEX_UM_TENANT_UM_DOMAIN_NAME
ON UM_TENANT (UM_DOMAIN_NAME);
CREATE TABLE UM_USER (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_USER_ID VARCHAR(255) NOT NULL,
UM_USER_NAME VARCHAR(255) NOT NULL,
UM_USER_PASSWORD VARCHAR(255) NOT NULL,
UM_SALT_VALUE VARCHAR(31),
UM_REQUIRE_CHANGE BOOLEAN DEFAULT FALSE,
UM_CHANGED_TIME TIMESTAMP NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY (UM_ID, UM_TENANT_ID),
UNIQUE(UM_USER_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_SYSTEM_USER (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_USER_NAME VARCHAR(255) NOT NULL,
UM_USER_PASSWORD VARCHAR(255) NOT NULL,
UM_SALT_VALUE VARCHAR(31),
UM_REQUIRE_CHANGE BOOLEAN DEFAULT FALSE,
UM_CHANGED_TIME TIMESTAMP NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY (UM_ID, UM_TENANT_ID),
UNIQUE(UM_USER_NAME, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_ROLE (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_ROLE_NAME VARCHAR(255) NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
UM_SHARED_ROLE BOOLEAN DEFAULT FALSE,
PRIMARY KEY (UM_ID, UM_TENANT_ID),
UNIQUE(UM_ROLE_NAME, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_MODULE(
UM_ID INTEGER  NOT NULL AUTO_INCREMENT,
UM_MODULE_NAME VARCHAR(100),
UNIQUE(UM_MODULE_NAME),
PRIMARY KEY(UM_ID)
)ENGINE INNODB;
CREATE TABLE UM_MODULE_ACTIONS(
UM_ACTION VARCHAR(255) NOT NULL,
UM_MODULE_ID INTEGER NOT NULL,
PRIMARY KEY(UM_ACTION, UM_MODULE_ID),
FOREIGN KEY (UM_MODULE_ID) REFERENCES UM_MODULE(UM_ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE UM_PERMISSION (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_RESOURCE_ID VARCHAR(255) NOT NULL,
UM_ACTION VARCHAR(255) NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
UM_MODULE_ID INTEGER DEFAULT 0,
UNIQUE(UM_RESOURCE_ID,UM_ACTION, UM_TENANT_ID),
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE INDEX INDEX_UM_PERMISSION_UM_RESOURCE_ID_UM_ACTION ON UM_PERMISSION (UM_RESOURCE_ID, UM_ACTION, UM_TENANT_ID);
CREATE TABLE UM_ROLE_PERMISSION (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_PERMISSION_ID INTEGER NOT NULL,
UM_ROLE_NAME VARCHAR(255) NOT NULL,
UM_IS_ALLOWED SMALLINT NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
UM_DOMAIN_ID INTEGER,
UNIQUE (UM_PERMISSION_ID, UM_ROLE_NAME, UM_TENANT_ID, UM_DOMAIN_ID),
FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE,
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
-- REMOVED UNIQUE (UM_PERMISSION_ID, UM_ROLE_ID)
CREATE TABLE UM_USER_PERMISSION (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_PERMISSION_ID INTEGER NOT NULL,
UM_USER_NAME VARCHAR(255) NOT NULL,
UM_IS_ALLOWED SMALLINT NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
-- REMOVED UNIQUE (UM_PERMISSION_ID, UM_USER_ID)
CREATE TABLE UM_USER_ROLE (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_ROLE_ID INTEGER NOT NULL,
UM_USER_ID INTEGER NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
UNIQUE (UM_USER_ID, UM_ROLE_ID, UM_TENANT_ID),
FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_ROLE(UM_ID, UM_TENANT_ID),
FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID),
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_SHARED_USER_ROLE(
UM_ROLE_ID INTEGER NOT NULL,
UM_USER_ID INTEGER NOT NULL,
UM_USER_TENANT_ID INTEGER NOT NULL,
UM_ROLE_TENANT_ID INTEGER NOT NULL,
UNIQUE(UM_USER_ID,UM_ROLE_ID,UM_USER_TENANT_ID, UM_ROLE_TENANT_ID),
FOREIGN KEY(UM_ROLE_ID,UM_ROLE_TENANT_ID) REFERENCES UM_ROLE(UM_ID,UM_TENANT_ID) ON DELETE CASCADE,
FOREIGN KEY(UM_USER_ID,UM_USER_TENANT_ID) REFERENCES UM_USER(UM_ID,UM_TENANT_ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE UM_ACCOUNT_MAPPING(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_USER_NAME VARCHAR(255) NOT NULL,
UM_TENANT_ID INTEGER NOT NULL,
UM_USER_STORE_DOMAIN VARCHAR(100),
UM_ACC_LINK_ID INTEGER NOT NULL,
UNIQUE(UM_USER_NAME, UM_TENANT_ID, UM_USER_STORE_DOMAIN, UM_ACC_LINK_ID),
FOREIGN KEY (UM_TENANT_ID) REFERENCES UM_TENANT(UM_ID) ON DELETE CASCADE,
PRIMARY KEY (UM_ID)
)ENGINE INNODB;
CREATE TABLE UM_USER_ATTRIBUTE (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_ATTR_NAME VARCHAR(255) NOT NULL,
UM_ATTR_VALUE VARCHAR(1024),
UM_PROFILE_ID VARCHAR(255),
UM_USER_ID INTEGER,
UM_TENANT_ID INTEGER DEFAULT 0,
FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID),
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE INDEX UM_USER_ID_INDEX ON UM_USER_ATTRIBUTE(UM_USER_ID);
--CREATE INDEX UM_ATTR_NAME_VALUE_INDEX ON UM_USER_ATTRIBUTE(UM_ATTR_NAME, UM_ATTR_VALUE);
CREATE TABLE UM_DIALECT(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_DIALECT_URI VARCHAR(255) NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
UNIQUE(UM_DIALECT_URI, UM_TENANT_ID),
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_CLAIM(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_DIALECT_ID INTEGER NOT NULL,
UM_CLAIM_URI VARCHAR(255) NOT NULL,
UM_DISPLAY_TAG VARCHAR(255),
UM_DESCRIPTION VARCHAR(255),
UM_MAPPED_ATTRIBUTE_DOMAIN VARCHAR(255),
UM_MAPPED_ATTRIBUTE VARCHAR(255),
UM_REG_EX VARCHAR(255),
UM_SUPPORTED SMALLINT,
UM_REQUIRED SMALLINT,
UM_DISPLAY_ORDER INTEGER,
UM_CHECKED_ATTRIBUTE SMALLINT,
UM_READ_ONLY SMALLINT,
UM_TENANT_ID INTEGER DEFAULT 0,
UNIQUE(UM_DIALECT_ID, UM_CLAIM_URI, UM_TENANT_ID,UM_MAPPED_ATTRIBUTE_DOMAIN),
FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID),
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_PROFILE_CONFIG(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_DIALECT_ID INTEGER NOT NULL,
UM_PROFILE_NAME VARCHAR(255),
UM_TENANT_ID INTEGER DEFAULT 0,
FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID),
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS UM_CLAIM_BEHAVIOR(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_PROFILE_ID INTEGER,
UM_CLAIM_ID INTEGER,
UM_BEHAVIOUR SMALLINT,
UM_TENANT_ID INTEGER DEFAULT 0,
FOREIGN KEY(UM_PROFILE_ID, UM_TENANT_ID) REFERENCES UM_PROFILE_CONFIG(UM_ID,UM_TENANT_ID),
FOREIGN KEY(UM_CLAIM_ID, UM_TENANT_ID) REFERENCES UM_CLAIM(UM_ID,UM_TENANT_ID),
PRIMARY KEY(UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_HYBRID_ROLE(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_ROLE_NAME VARCHAR(255) NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY (UM_ID, UM_TENANT_ID),
UNIQUE(UM_ROLE_NAME,UM_TENANT_ID)
)ENGINE INNODB;
CREATE INDEX UM_ROLE_NAME_IND ON UM_HYBRID_ROLE(UM_ROLE_NAME);
CREATE TABLE UM_HYBRID_USER_ROLE(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_USER_NAME VARCHAR(255),
UM_ROLE_ID INTEGER NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
UM_DOMAIN_ID INTEGER,
UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID, UM_DOMAIN_ID),
FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_HYBRID_ROLE(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE,
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_HYBRID_GROUP_ROLE(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_GROUP_NAME VARCHAR(255),
UM_ROLE_ID INTEGER NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
UM_DOMAIN_ID INTEGER,
UNIQUE (UM_GROUP_NAME, UM_ROLE_ID, UM_TENANT_ID, UM_DOMAIN_ID),
FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_HYBRID_ROLE(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE,
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_SYSTEM_ROLE(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_ROLE_NAME VARCHAR(255) NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY (UM_ID, UM_TENANT_ID),
UNIQUE(UM_ROLE_NAME,UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_SYSTEM_USER_ROLE(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_USER_NAME VARCHAR(255),
UM_ROLE_ID INTEGER NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID),
FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_SYSTEM_ROLE(UM_ID, UM_TENANT_ID),
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_HYBRID_REMEMBER_ME(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_USER_NAME VARCHAR(255) NOT NULL,
UM_COOKIE_VALUE VARCHAR(1024),
UM_CREATED_TIME TIMESTAMP,
UM_TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS UM_UUID_DOMAIN_MAPPER (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_USER_ID VARCHAR(255) NOT NULL,
UM_DOMAIN_ID INTEGER NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY (UM_ID),
UNIQUE (UM_USER_ID),
FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE INDEX UUID_DM_UID_TID ON UM_UUID_DOMAIN_MAPPER(UM_USER_ID, UM_TENANT_ID);
USE WSO2IS_IDENTITY_DB;
CREATE TABLE IF NOT EXISTS IDN_BASE_TABLE (
PRODUCT_NAME VARCHAR(20),
PRIMARY KEY (PRODUCT_NAME)
)ENGINE INNODB;
INSERT INTO IDN_BASE_TABLE values ('WSO2 Identity Server');
CREATE TABLE IF NOT EXISTS IDN_OAUTH_CONSUMER_APPS (
ID INTEGER NOT NULL AUTO_INCREMENT,
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)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS 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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH1A_REQUEST_TOKEN (
REQUEST_TOKEN VARCHAR(255),
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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH1A_ACCESS_TOKEN (
ACCESS_TOKEN VARCHAR(255),
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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS 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 NOT NULL DEFAULT CURRENT_TIMESTAMP,
REFRESH_TOKEN_TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_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)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS 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
)ENGINE INNODB;
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
);
CREATE TABLE IF NOT EXISTS 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 NOT NULL DEFAULT CURRENT_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
)ENGINE INNODB;
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
)ENGINE INNODB;
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 DEFAULT CURRENT_TIMESTAMP,
EXPIRY_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_DEVICE_FLOW_SCOPES (
ID INTEGER NOT NULL AUTO_INCREMENT,
SCOPE_ID VARCHAR(255),
SCOPE VARCHAR(255),
PRIMARY KEY (ID),
FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_DEVICE_FLOW(CODE_ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_ACCESS_TOKEN_SCOPE (
TOKEN_ID VARCHAR (255),
TOKEN_SCOPE VARCHAR (60),
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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_SCOPE (
SCOPE_ID INTEGER NOT NULL AUTO_INCREMENT,
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)
)ENGINE INNODB;
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)
)ENGINE INNODB;
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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_SCIM_GROUP (
ID INTEGER AUTO_INCREMENT,
TENANT_ID INTEGER NOT NULL,
ROLE_NAME VARCHAR(255) NOT NULL,
ATTR_NAME VARCHAR(1024) NOT NULL,
ATTR_VALUE VARCHAR(1024),
PRIMARY KEY (ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS 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)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS 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)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS 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)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_STS_STORE (
ID INTEGER AUTO_INCREMENT,
TOKEN_ID VARCHAR(255) NOT NULL,
TOKEN_CONTENT BLOB(1024) NOT NULL,
CREATE_DATE TIMESTAMP NOT NULL,
EXPIRE_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
STATE INTEGER DEFAULT 0,
PRIMARY KEY (ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS 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)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS 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)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS 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)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_AUTH_SESSION_STORE (
SESSION_ID VARCHAR (100) NOT NULL,
SESSION_TYPE VARCHAR(100) NOT NULL,
OPERATION VARCHAR(10) NOT NULL,
SESSION_OBJECT BLOB,
TIME_CREATED BIGINT,
TENANT_ID INTEGER DEFAULT -1,
EXPIRY_TIME BIGINT,
PRIMARY KEY (SESSION_ID, SESSION_TYPE, TIME_CREATED, OPERATION)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_AUTH_TEMP_SESSION_STORE (
SESSION_ID VARCHAR (100) NOT NULL,
SESSION_TYPE VARCHAR(100) NOT NULL,
OPERATION VARCHAR(10) NOT NULL,
SESSION_OBJECT BLOB,
TIME_CREATED BIGINT,
TENANT_ID INTEGER DEFAULT -1,
EXPIRY_TIME BIGINT,
PRIMARY KEY (SESSION_ID, SESSION_TYPE, TIME_CREATED, OPERATION)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS 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));
CREATE TABLE IF NOT EXISTS 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));
CREATE TABLE IF NOT EXISTS 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)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS 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)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS SP_APP (
ID INTEGER NOT NULL AUTO_INCREMENT,
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)
)ENGINE INNODB;
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);
CREATE TABLE IF NOT EXISTS SP_METADATA (
ID INTEGER AUTO_INCREMENT,
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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS SP_INBOUND_AUTH (
ID INTEGER NOT NULL AUTO_INCREMENT,
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)
)ENGINE INNODB;
ALTER TABLE SP_INBOUND_AUTH ADD CONSTRAINT APPLICATION_ID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
CREATE TABLE IF NOT EXISTS SP_AUTH_STEP (
ID INTEGER NOT NULL AUTO_INCREMENT,
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)
)ENGINE INNODB;
ALTER TABLE SP_AUTH_STEP ADD CONSTRAINT APPLICATION_ID_CONSTRAINT_STEP FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
CREATE TABLE IF NOT EXISTS SP_FEDERATED_IDP (
ID INTEGER NOT NULL,
TENANT_ID INTEGER NOT NULL,
AUTHENTICATOR_ID INTEGER NOT NULL,
PRIMARY KEY (ID, AUTHENTICATOR_ID)
)ENGINE INNODB;
ALTER TABLE SP_FEDERATED_IDP ADD CONSTRAINT STEP_ID_CONSTRAINT FOREIGN KEY (ID) REFERENCES SP_AUTH_STEP (ID) ON DELETE CASCADE;
CREATE TABLE IF NOT EXISTS SP_CLAIM_DIALECT (
ID INTEGER NOT NULL AUTO_INCREMENT,
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;
CREATE TABLE IF NOT EXISTS SP_CLAIM_MAPPING (
ID INTEGER NOT NULL AUTO_INCREMENT,
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)
)ENGINE INNODB;
ALTER TABLE SP_CLAIM_MAPPING ADD CONSTRAINT CLAIMID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
CREATE TABLE IF NOT EXISTS SP_ROLE_MAPPING (
ID INTEGER NOT NULL AUTO_INCREMENT,
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)
)ENGINE INNODB;
ALTER TABLE SP_ROLE_MAPPING ADD CONSTRAINT ROLEID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
CREATE TABLE IF NOT EXISTS SP_REQ_PATH_AUTHENTICATOR (
ID INTEGER NOT NULL AUTO_INCREMENT,
TENANT_ID INTEGER NOT NULL,
AUTHENTICATOR_NAME VARCHAR (255) NOT NULL ,
APP_ID INTEGER NOT NULL,
PRIMARY KEY (ID)
)ENGINE INNODB;
ALTER TABLE SP_REQ_PATH_AUTHENTICATOR ADD CONSTRAINT REQ_AUTH_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
CREATE TABLE IF NOT EXISTS SP_PROVISIONING_CONNECTOR (
ID INTEGER NOT NULL AUTO_INCREMENT,
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)
)ENGINE INNODB;
ALTER TABLE SP_PROVISIONING_CONNECTOR ADD CONSTRAINT PRO_CONNECTOR_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
CREATE TABLE SP_AUTH_SCRIPT (
ID         INTEGER AUTO_INCREMENT NOT NULL,
TENANT_ID  INTEGER                NOT NULL,
APP_ID     INTEGER                NOT NULL,
TYPE       VARCHAR(255)           NOT NULL,
CONTENT    BLOB    DEFAULT NULL,
IS_ENABLED CHAR(1) NOT NULL DEFAULT '0',
PRIMARY KEY (ID));
CREATE TABLE IF NOT EXISTS SP_TEMPLATE (
ID         INTEGER AUTO_INCREMENT NOT NULL,
TENANT_ID  INTEGER                NOT NULL,
NAME VARCHAR(255) NOT NULL,
DESCRIPTION VARCHAR(1023),
CONTENT BLOB DEFAULT NULL,
PRIMARY KEY (ID),
CONSTRAINT SP_TEMPLATE_CONSTRAINT UNIQUE (TENANT_ID, NAME));
CREATE TABLE IF NOT EXISTS IDN_AUTH_WAIT_STATUS (
ID              INTEGER AUTO_INCREMENT NOT NULL,
TENANT_ID       INTEGER                NOT NULL,
LONG_WAIT_KEY   VARCHAR(255)           NOT NULL,
WAIT_STATUS     CHAR(1) NOT NULL DEFAULT '1',
TIME_CREATED    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
EXPIRE_TIME     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (ID),
CONSTRAINT IDN_AUTH_WAIT_STATUS_KEY UNIQUE (LONG_WAIT_KEY));
CREATE TABLE IF NOT EXISTS IDP (
ID INTEGER AUTO_INCREMENT,
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 MEDIUMBLOB,
CERTIFICATE BLOB,
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)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_ROLE (
ID INTEGER AUTO_INCREMENT,
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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_ROLE_MAPPING (
ID INTEGER AUTO_INCREMENT,
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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_CLAIM (
ID INTEGER AUTO_INCREMENT,
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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_CLAIM_MAPPING (
ID INTEGER AUTO_INCREMENT,
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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_AUTHENTICATOR (
ID INTEGER AUTO_INCREMENT,
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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_METADATA (
ID INTEGER AUTO_INCREMENT,
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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_AUTHENTICATOR_PROPERTY (
ID INTEGER AUTO_INCREMENT,
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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_PROVISIONING_CONFIG (
ID INTEGER AUTO_INCREMENT,
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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_PROV_CONFIG_PROPERTY (
ID INTEGER AUTO_INCREMENT,
TENANT_ID INTEGER,
PROVISIONING_CONFIG_ID INTEGER,
PROPERTY_KEY VARCHAR(255) NOT NULL,
PROPERTY_VALUE VARCHAR(2048),
PROPERTY_BLOB_VALUE BLOB,
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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_PROVISIONING_ENTITY (
ID INTEGER AUTO_INCREMENT,
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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_LOCAL_CLAIM (
ID INTEGER AUTO_INCREMENT,
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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_ASSOCIATED_ID (
ID INTEGER AUTO_INCREMENT,
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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS 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)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS 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)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS 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)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS WF_REQUEST (
UUID VARCHAR (45),
CREATED_BY VARCHAR (255),
TENANT_ID INTEGER DEFAULT -1,
OPERATION_TYPE VARCHAR (50),
CREATED_AT TIMESTAMP,
UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
STATUS VARCHAR (30),
REQUEST BLOB,
PRIMARY KEY (UUID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS 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),
CALLBACK_USERNAME VARCHAR (45),
CALLBACK_PASSWORD VARCHAR (255),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (PROFILE_NAME, TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS 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)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS WF_WORKFLOW_ASSOCIATION(
ID INTEGER NOT NULL AUTO_INCREMENT,
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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS 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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS 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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS 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
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS 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)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_PASSWORD_HISTORY_DATA (
ID INTEGER NOT NULL AUTO_INCREMENT,
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)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_CLAIM_DIALECT (
ID INTEGER NOT NULL AUTO_INCREMENT,
DIALECT_URI VARCHAR (255) NOT NULL,
TENANT_ID INTEGER NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT DIALECT_URI_CONSTRAINT UNIQUE (DIALECT_URI, TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_CLAIM (
ID INTEGER NOT NULL AUTO_INCREMENT,
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)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_CLAIM_MAPPED_ATTRIBUTE (
ID INTEGER NOT NULL AUTO_INCREMENT,
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)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_CLAIM_PROPERTY (
ID INTEGER NOT NULL AUTO_INCREMENT,
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)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_CLAIM_MAPPING (
ID INTEGER NOT NULL AUTO_INCREMENT,
EXT_CLAIM_ID INTEGER NOT NULL,
MAPPED_LOCAL_CLAIM_ID INTEGER NOT NULL,
TENANT_ID INTEGER NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (EXT_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
FOREIGN KEY (MAPPED_LOCAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
CONSTRAINT EXT_TO_LOC_MAPPING_CONSTRN UNIQUE (EXT_CLAIM_ID, TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS  IDN_SAML2_ASSERTION_STORE (
ID INTEGER NOT NULL AUTO_INCREMENT,
SAML2_ID  VARCHAR(255) ,
SAML2_ISSUER  VARCHAR(255) ,
SAML2_SUBJECT  VARCHAR(255) ,
SAML2_SESSION_INDEX  VARCHAR(255) ,
SAML2_AUTHN_CONTEXT_CLASS_REF  VARCHAR(255) ,
SAML2_ASSERTION  VARCHAR(4096) ,
ASSERTION BLOB ,
PRIMARY KEY (ID)
)ENGINE INNODB;
CREATE TABLE IDN_SAML2_ARTIFACT_STORE (
ID INT(11) NOT NULL AUTO_INCREMENT,
SOURCE_ID VARCHAR(255) NOT NULL,
MESSAGE_HANDLER VARCHAR(255) NOT NULL,
AUTHN_REQ_DTO BLOB NOT NULL,
SESSION_ID VARCHAR(255) NOT NULL,
EXP_TIMESTAMP TIMESTAMP NOT NULL,
INIT_TIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
ASSERTION_ID VARCHAR(255),
PRIMARY KEY (`ID`)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OIDC_JTI (
JWT_ID VARCHAR(255) NOT NULL,
EXP_TIME TIMESTAMP NOT NULL ,
TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY (JWT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS  IDN_OIDC_PROPERTY (
ID INTEGER NOT NULL AUTO_INCREMENT,
TENANT_ID  INTEGER,
CONSUMER_KEY  VARCHAR(255) ,
PROPERTY_KEY  VARCHAR(255) NOT NULL,
PROPERTY_VALUE  VARCHAR(2047) ,
PRIMARY KEY (ID),
FOREIGN KEY (CONSUMER_KEY) REFERENCES IDN_OAUTH_CONSUMER_APPS(CONSUMER_KEY) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OIDC_REQ_OBJECT_REFERENCE (
ID INTEGER NOT NULL AUTO_INCREMENT,
CONSUMER_KEY_ID INTEGER ,
CODE_ID VARCHAR(255) ,
TOKEN_ID VARCHAR(255) ,
SESSION_DATA_KEY VARCHAR(255),
PRIMARY KEY (ID),
FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE,
FOREIGN KEY (TOKEN_ID) REFERENCES IDN_OAUTH2_ACCESS_TOKEN(TOKEN_ID) ON DELETE CASCADE,
FOREIGN KEY (CODE_ID) REFERENCES IDN_OAUTH2_AUTHORIZATION_CODE(CODE_ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OIDC_REQ_OBJECT_CLAIMS (
ID INTEGER NOT NULL AUTO_INCREMENT,
REQ_OBJECT_ID INTEGER,
CLAIM_ATTRIBUTE VARCHAR(255) ,
ESSENTIAL CHAR(1) NOT NULL DEFAULT '0' ,
VALUE VARCHAR(255) ,
IS_USERINFO CHAR(1) NOT NULL DEFAULT '0',
PRIMARY KEY (ID),
FOREIGN KEY (REQ_OBJECT_ID) REFERENCES IDN_OIDC_REQ_OBJECT_REFERENCE (ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OIDC_REQ_OBJ_CLAIM_VALUES (
ID INTEGER NOT NULL AUTO_INCREMENT,
REQ_OBJECT_CLAIMS_ID INTEGER ,
CLAIM_VALUES VARCHAR(255) ,
PRIMARY KEY (ID),
FOREIGN KEY (REQ_OBJECT_CLAIMS_ID) REFERENCES  IDN_OIDC_REQ_OBJECT_CLAIMS(ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_CERTIFICATE (
ID INTEGER NOT NULL AUTO_INCREMENT,
NAME VARCHAR(100),
CERTIFICATE_IN_PEM BLOB,
TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY(ID),
CONSTRAINT CERTIFICATE_UNIQUE_KEY UNIQUE (NAME, TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OIDC_SCOPE_CLAIM_MAPPING (
ID INTEGER NOT NULL AUTO_INCREMENT,
SCOPE_ID INTEGER NOT NULL,
EXTERNAL_CLAIM_ID INTEGER NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_SCOPE(SCOPE_ID) ON DELETE CASCADE,
FOREIGN KEY (EXTERNAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
UNIQUE (SCOPE_ID, EXTERNAL_CLAIM_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_FUNCTION_LIBRARY (
NAME VARCHAR(255) NOT NULL,
DESCRIPTION VARCHAR(1023),
TYPE VARCHAR(255) NOT NULL,
TENANT_ID INTEGER NOT NULL,
DATA BLOB NOT NULL,
PRIMARY KEY (TENANT_ID,NAME)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_CIBA_AUTH_CODE (
AUTH_CODE_KEY CHAR (36),
AUTH_REQ_ID CHAR (36),
ISSUED_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSUMER_KEY VARCHAR(255),
LAST_POLLED_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
POLLING_INTERVAL INTEGER,
EXPIRES_IN  INTEGER,
AUTHENTICATED_USER_NAME VARCHAR(255),
USER_STORE_DOMAIN VARCHAR(100),
TENANT_ID INTEGER,
AUTH_REQ_STATUS VARCHAR (100) DEFAULT 'REQUESTED',
IDP_ID INTEGER,
UNIQUE(AUTH_REQ_ID),
PRIMARY KEY (AUTH_CODE_KEY),
FOREIGN KEY (CONSUMER_KEY) REFERENCES IDN_OAUTH_CONSUMER_APPS(CONSUMER_KEY) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_CIBA_REQUEST_SCOPES (
AUTH_CODE_KEY CHAR (36),
SCOPE VARCHAR (255),
FOREIGN KEY (AUTH_CODE_KEY) REFERENCES IDN_OAUTH2_CIBA_AUTH_CODE(AUTH_CODE_KEY) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_FED_AUTH_SESSION_MAPPING (
IDP_SESSION_ID VARCHAR(255) NOT NULL,
SESSION_ID VARCHAR(255) NOT NULL,
IDP_NAME VARCHAR(255) NOT NULL,
AUTHENTICATOR_ID VARCHAR(255),
PROTOCOL_TYPE VARCHAR(255),
TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (IDP_SESSION_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_CONFIG_TYPE (
ID VARCHAR(255) NOT NULL,
NAME VARCHAR(255) NOT NULL,
DESCRIPTION VARCHAR(1023) NULL,
PRIMARY KEY (ID),
CONSTRAINT TYPE_NAME_CONSTRAINT UNIQUE (NAME)
)ENGINE INNODB;
INSERT INTO IDN_CONFIG_TYPE (ID, NAME, DESCRIPTION) VALUES
('9ab0ef95-13e9-4ed5-afaf-d29bed62f7bd', 'IDP_TEMPLATE', 'Template type to uniquely identify IDP templates'),
('3c4ac3d0-5903-4e3d-aaca-38df65b33bfd', 'APPLICATION_TEMPLATE', 'Template type to uniquely identify Application templates'),
('8ec6dbf1-218a-49bf-bc34-0d2db52d151c', 'CORS_CONFIGURATION', 'A resource type to keep the tenant CORS configurations');
CREATE TABLE IF NOT EXISTS IDN_CONFIG_RESOURCE (
ID VARCHAR(255) NOT NULL,
TENANT_ID INT NOT NULL,
NAME VARCHAR(255) NOT NULL,
CREATED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
LAST_MODIFIED TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
HAS_FILE tinyint(1) NOT NULL,
HAS_ATTRIBUTE tinyint(1) NOT NULL,
TYPE_ID VARCHAR(255) NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT NAME_TENANT_TYPE_CONSTRAINT UNIQUE (NAME, TENANT_ID, TYPE_ID)
)ENGINE INNODB;
ALTER TABLE IDN_CONFIG_RESOURCE ADD CONSTRAINT TYPE_ID_FOREIGN_CONSTRAINT FOREIGN KEY (TYPE_ID) REFERENCES
IDN_CONFIG_TYPE (ID) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE IF NOT EXISTS IDN_CONFIG_ATTRIBUTE (
ID VARCHAR(255) NOT NULL,
RESOURCE_ID VARCHAR(255) NOT NULL,
ATTR_KEY VARCHAR(255) NOT NULL,
ATTR_VALUE VARCHAR(1023) NULL,
PRIMARY KEY (ID),
CONSTRAINT RESOURCE_KEY_VAL_CONSTRAINT UNIQUE (RESOURCE_ID(64), ATTR_KEY(255))
)ENGINE INNODB;
ALTER TABLE IDN_CONFIG_ATTRIBUTE ADD CONSTRAINT RESOURCE_ID_ATTRIBUTE_FOREIGN_CONSTRAINT FOREIGN KEY (RESOURCE_ID)
REFERENCES IDN_CONFIG_RESOURCE (ID) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE IF NOT EXISTS IDN_CONFIG_FILE (
ID VARCHAR(255) NOT NULL,
VALUE BLOB NULL,
RESOURCE_ID VARCHAR(255) NOT NULL,
NAME VARCHAR(255) NULL,
PRIMARY KEY (ID)
)ENGINE INNODB;
ALTER TABLE IDN_CONFIG_FILE ADD CONSTRAINT RESOURCE_ID_FILE_FOREIGN_CONSTRAINT FOREIGN KEY (RESOURCE_ID) REFERENCES
IDN_CONFIG_RESOURCE (ID) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE IDN_REMOTE_FETCH_CONFIG (
ID VARCHAR(255) NOT NULL,
TENANT_ID INT NOT NULL,
IS_ENABLED CHAR(1) NOT NULL,
REPO_MANAGER_TYPE VARCHAR(255) NOT NULL,
ACTION_LISTENER_TYPE VARCHAR(255) NOT NULL,
CONFIG_DEPLOYER_TYPE VARCHAR(255) NOT NULL,
REMOTE_FETCH_NAME VARCHAR(255),
REMOTE_RESOURCE_URI VARCHAR(255) NOT NULL,
ATTRIBUTES_JSON MEDIUMTEXT NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT UC_REMOTE_RESOURCE_TYPE UNIQUE (TENANT_ID, CONFIG_DEPLOYER_TYPE)
)ENGINE INNODB;
CREATE TABLE IDN_REMOTE_FETCH_REVISIONS (
ID VARCHAR(255) NOT NULL,
CONFIG_ID VARCHAR(255) NOT NULL,
FILE_PATH VARCHAR(255) NOT NULL,
FILE_HASH VARCHAR(255),
DEPLOYED_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
LAST_SYNC_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
DEPLOYMENT_STATUS VARCHAR(255),
ITEM_NAME VARCHAR(255),
DEPLOY_ERR_LOG MEDIUMTEXT,
PRIMARY KEY (ID),
FOREIGN KEY (CONFIG_ID) REFERENCES IDN_REMOTE_FETCH_CONFIG(ID) ON DELETE CASCADE,
CONSTRAINT UC_REVISIONS UNIQUE (CONFIG_ID, ITEM_NAME)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_USER_FUNCTIONALITY_MAPPING (
ID VARCHAR(255) NOT NULL,
USER_ID VARCHAR(255) NOT NULL,
TENANT_ID INTEGER NOT NULL,
FUNCTIONALITY_ID VARCHAR(255) NOT NULL,
IS_FUNCTIONALITY_LOCKED BOOLEAN NOT NULL,
FUNCTIONALITY_UNLOCK_TIME BIGINT NOT NULL,
FUNCTIONALITY_LOCK_REASON VARCHAR(1023),
FUNCTIONALITY_LOCK_REASON_CODE VARCHAR(255),
PRIMARY KEY (ID),
CONSTRAINT IDN_USER_FUNCTIONALITY_MAPPING_CONSTRAINT UNIQUE (USER_ID, TENANT_ID, FUNCTIONALITY_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_USER_FUNCTIONALITY_PROPERTY (
ID VARCHAR(255) NOT NULL,
USER_ID VARCHAR(255) NOT NULL,
TENANT_ID INTEGER NOT NULL,
FUNCTIONALITY_ID VARCHAR(255) NOT NULL,
PROPERTY_NAME VARCHAR(255),
PROPERTY_VALUE VARCHAR(255),
PRIMARY KEY (ID),
CONSTRAINT IDN_USER_FUNCTIONALITY_PROPERTY_CONSTRAINT UNIQUE (USER_ID, TENANT_ID, FUNCTIONALITY_ID, PROPERTY_NAME)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_CORS_ORIGIN (
ID                INT           NOT NULL AUTO_INCREMENT,
TENANT_ID         INT           NOT NULL,
ORIGIN            VARCHAR(2048) NOT NULL,
UUID              CHAR(36)      NOT NULL,
PRIMARY KEY (ID),
UNIQUE (UUID)
) ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_CORS_ASSOCIATION (
IDN_CORS_ORIGIN_ID  INT NOT NULL,
SP_APP_ID           INT NOT NULL,
PRIMARY KEY (IDN_CORS_ORIGIN_ID, SP_APP_ID),
FOREIGN KEY (IDN_CORS_ORIGIN_ID) REFERENCES IDN_CORS_ORIGIN (ID) ON DELETE CASCADE,
FOREIGN KEY (SP_APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE
) ENGINE INNODB;
-- --------------------------- INDEX CREATION -----------------------------
-- IDN_OAUTH2_ACCESS_TOKEN --
CREATE INDEX IDX_TC ON IDN_OAUTH2_ACCESS_TOKEN(TIME_CREATED);
CREATE INDEX IDX_ATH ON IDN_OAUTH2_ACCESS_TOKEN(ACCESS_TOKEN_HASH);
CREATE INDEX IDX_AT_CK_AU ON IDN_OAUTH2_ACCESS_TOKEN(CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_STATE, USER_TYPE);
CREATE INDEX IDX_AT_TI_UD ON IDN_OAUTH2_ACCESS_TOKEN(AUTHZ_USER, TENANT_ID, TOKEN_STATE, USER_DOMAIN);
CREATE INDEX IDX_AT_AU_TID_UD_TS_CKID ON IDN_OAUTH2_ACCESS_TOKEN(AUTHZ_USER, TENANT_ID, USER_DOMAIN, TOKEN_STATE, CONSUMER_KEY_ID);
--CREATE INDEX IDX_AT_AT ON IDN_OAUTH2_ACCESS_TOKEN(ACCESS_TOKEN);
CREATE INDEX IDX_AT_AU_CKID_TS_UT ON IDN_OAUTH2_ACCESS_TOKEN(AUTHZ_USER, CONSUMER_KEY_ID, TOKEN_STATE, USER_TYPE);
CREATE INDEX IDX_AT_RTH ON IDN_OAUTH2_ACCESS_TOKEN(REFRESH_TOKEN_HASH);
--CREATE INDEX IDX_AT_RT ON IDN_OAUTH2_ACCESS_TOKEN(REFRESH_TOKEN);
CREATE INDEX IDX_AT_CKID_AU_TID_UD_TSH_TS ON IDN_OAUTH2_ACCESS_TOKEN(CONSUMER_KEY_ID, AUTHZ_USER, TENANT_ID, USER_DOMAIN, TOKEN_SCOPE_HASH, TOKEN_STATE);
-- IDN_OAUTH2_AUTHORIZATION_CODE --
CREATE INDEX IDX_AUTHORIZATION_CODE_HASH ON IDN_OAUTH2_AUTHORIZATION_CODE (AUTHORIZATION_CODE_HASH, CONSUMER_KEY_ID);
CREATE INDEX IDX_AUTHORIZATION_CODE_AU_TI ON IDN_OAUTH2_AUTHORIZATION_CODE (AUTHZ_USER, TENANT_ID, USER_DOMAIN, STATE);
CREATE INDEX IDX_AC_CKID ON IDN_OAUTH2_AUTHORIZATION_CODE(CONSUMER_KEY_ID);
CREATE INDEX IDX_AC_TID ON IDN_OAUTH2_AUTHORIZATION_CODE(TOKEN_ID);
--CREATE INDEX IDX_AC_AC_CKID ON IDN_OAUTH2_AUTHORIZATION_CODE(AUTHORIZATION_CODE, CONSUMER_KEY_ID);
-- IDN_SCIM_GROUP --
CREATE INDEX IDX_IDN_SCIM_GROUP_TI_RN ON IDN_SCIM_GROUP (TENANT_ID, ROLE_NAME);
CREATE INDEX IDX_IDN_SCIM_GROUP_TI_RN_AN ON IDN_SCIM_GROUP (TENANT_ID, ROLE_NAME, ATTR_NAME(500));
-- IDN_AUTH_SESSION_STORE --
CREATE INDEX IDX_IDN_AUTH_SESSION_TIME ON IDN_AUTH_SESSION_STORE (TIME_CREATED);
-- IDN_AUTH_TEMP_SESSION_STORE --
CREATE INDEX IDX_IDN_AUTH_TMP_SESSION_TIME ON IDN_AUTH_TEMP_SESSION_STORE (TIME_CREATED);
-- IDN_OIDC_SCOPE_CLAIM_MAPPING --
CREATE INDEX IDX_AT_SI_ECI ON IDN_OIDC_SCOPE_CLAIM_MAPPING(SCOPE_ID, EXTERNAL_CLAIM_ID);
-- IDN_OAUTH2_SCOPE --
CREATE INDEX IDX_SC_TID ON IDN_OAUTH2_SCOPE(TENANT_ID);
-- IDN_OAUTH2_SCOPE_BINDING --
CREATE INDEX IDX_SB_SCPID ON IDN_OAUTH2_SCOPE_BINDING(SCOPE_ID);
-- IDN_OIDC_REQ_OBJECT_REFERENCE --
CREATE INDEX IDX_OROR_TID ON IDN_OIDC_REQ_OBJECT_REFERENCE(TOKEN_ID);
-- IDN_OAUTH2_ACCESS_TOKEN_SCOPE --
CREATE INDEX IDX_ATS_TID ON IDN_OAUTH2_ACCESS_TOKEN_SCOPE(TOKEN_ID);
-- SP_TEMPLATE --
CREATE INDEX IDX_SP_TEMPLATE ON SP_TEMPLATE (TENANT_ID, NAME);
-- IDN_AUTH_USER --
CREATE INDEX IDX_AUTH_USER_UN_TID_DN ON IDN_AUTH_USER (USER_NAME, TENANT_ID, DOMAIN_NAME);
CREATE INDEX IDX_AUTH_USER_DN_TOD ON IDN_AUTH_USER (DOMAIN_NAME, TENANT_ID);
-- IDN_AUTH_USER_SESSION_MAPPING --
CREATE INDEX IDX_USER_ID ON IDN_AUTH_USER_SESSION_MAPPING (USER_ID);
CREATE INDEX IDX_SESSION_ID ON IDN_AUTH_USER_SESSION_MAPPING (SESSION_ID);
-- IDN_OAUTH_CONSUMER_APPS --
CREATE INDEX IDX_OCA_UM_TID_UD_APN ON IDN_OAUTH_CONSUMER_APPS(USERNAME,TENANT_ID,USER_DOMAIN, APP_NAME);
-- IDX_SPI_APP --
CREATE INDEX IDX_SPI_APP ON SP_INBOUND_AUTH(APP_ID);
-- IDN_OIDC_PROPERTY --
CREATE INDEX IDX_IOP_TID_CK ON IDN_OIDC_PROPERTY(TENANT_ID,CONSUMER_KEY);
-- IDN_FIDO2_PROPERTY --
CREATE INDEX IDX_FIDO2_STR ON FIDO2_DEVICE_STORE(USER_NAME, TENANT_ID, DOMAIN_NAME, CREDENTIAL_ID, USER_HANDLE);
-- IDN_ASSOCIATED_ID --
CREATE INDEX IDX_AI_DN_UN_AI ON IDN_ASSOCIATED_ID(DOMAIN_NAME, USER_NAME, ASSOCIATION_ID);
-- IDN_OAUTH2_TOKEN_BINDING --
CREATE INDEX IDX_IDN_AUTH_BIND ON IDN_OAUTH2_TOKEN_BINDING (TOKEN_BINDING_REF);
-- IDN_FED_AUTH_SESSION_MAPPING --
CREATE INDEX IDX_FEDERATED_AUTH_SESSION_ID ON IDN_FED_AUTH_SESSION_MAPPING (SESSION_ID);
-- IDN_REMOTE_FETCH_REVISIONS --
CREATE INDEX IDX_REMOTE_FETCH_REVISION_CONFIG_ID ON IDN_REMOTE_FETCH_REVISIONS (CONFIG_ID);
-- IDN_CORS_ASSOCIATION --
CREATE INDEX IDX_CORS_SP_APP_ID ON IDN_CORS_ASSOCIATION (SP_APP_ID);
-- IDN_CORS_ASSOCIATION --
CREATE INDEX IDX_CORS_ORIGIN_ID ON IDN_CORS_ASSOCIATION (IDN_CORS_ORIGIN_ID);

9. We create a new MySQL called wso2is-mariadb with username root and password 123456  to execute in port 3306

docker run -dti --name wso2is-mariadb -p 3306:3306 \
    -e MYSQL_ROOT_PASSWORD=123456 \
    jsurf/rpi-mariadb:latest \
    --collation_server=utf8_general_ci \
    --character_set_server=utf8

10. Copy the previously created mysql.sql script and run it to create the WSO2IS_SHARED_DB and WSO2IS_IDENTITY_DB schemas.

docker cp mysql.sql wso2is-mariadb:.
docker exec -ti wso2is-mariadb bash
mysql -u root -p123456 < /mysql.sql

9. Deploying the Identity Server

1.Connect to the master node (nodeA) once again, create the deployment.yaml file

nano deployment.yaml

and insert the following configuration based on the official WS02 resource for Kubernetes ( https://github.com/wso2/kubernetes-is/blob/master/simple/deployment-scripts/wso2is-ga.sh ). Here, we create the deployment.toml configuration file that will point to the database on the 0x00 (192.168.1.101) host and a single replica:

apiVersion: v1
kind: Namespace
metadata:
name: wso2
spec:
finalizers:
- kubernetes
---
apiVersion: v1
kind: ServiceAccount
metadata:
name: wso2svc-account
namespace : wso2
---
apiVersion: v1
kind: ConfigMap
metadata:
name: identity-server-conf
namespace : wso2
data:
deployment.toml: |-
[server]
hostname = "$env{HOST_NAME}"
node_ip = "$env{NODE_IP}"
# base_path = "https://$ref{server.hostname}:${carbon.management.port}"
[super_admin]
username = "admin"
password = "admin"
create_admin_account = true
[user_store]
type = "read_write_ldap_unique_id"
connection_url = "ldap://localhost:${Ports.EmbeddedLDAP.LDAPServerPort}"
connection_name = "uid=admin,ou=system"
connection_password = "admin"
base_dn = "dc=wso2,dc=org"      #refers the base dn on which the user and group search bases will be generated
[database.identity_db]
type = "mysql"
url = "jdbc:mysql://192.168.1.101:3306/WSO2IS_IDENTITY_DB?autoReconnect=true&amp;useSSL=false"
username = "root"
password = "123456"
driver = "com.mysql.cj.jdbc.Driver"
[database.identity_db.pool_options]
validationQuery = "SELECT 1"
[database.shared_db]
type = "mysql"
url = "jdbc:mysql://192.168.1.101:3306/WSO2IS_SHARED_DB?autoReconnect=true&amp;useSSL=false"
username = "root"
password = "123456"
driver = "com.mysql.cj.jdbc.Driver"
[database.shared_db.pool_options]
validationQuery = "SELECT 1"
[keystore.primary]
file_name = "wso2carbon.jks"
password = "wso2carbon"
---
apiVersion: v1
kind: Service
metadata:
name: wso2is-service
namespace : wso2
labels:
deployment: wso2is
app: wso2is
monitoring: jmx
pod: wso2is
spec:
selector:
deployment: wso2is
app: wso2is
type: NodePort
ports:
- name: servlet-http
port: 9763
targetPort: 9763
protocol: TCP
- name: servlet-https
port: 9443
targetPort: 9443
protocol: TCP
nodePort: 30443
---
apiVersion: apps/v1
kind: Deployment
metadata:
name: wso2is-deployment
namespace : wso2
spec:
replicas: 1
minReadySeconds: 600
strategy:
rollingUpdate:
maxSurge: 1
maxUnavailable: 0
type: RollingUpdate
selector:
matchLabels:
deployment: wso2is
app: wso2is
monitoring: jmx
pod: wso2is
template:
metadata:
labels:
deployment: wso2is
app: wso2is
monitoring: jmx
pod: wso2is
spec:
hostAliases:
- ip: "127.0.0.1"
hostnames:
- "wso2is"
containers:
- name: wso2is
image: chakray/wso2is-rpi:5.10.0
livenessProbe:
exec:
command:
- /bin/sh
- -c
- nc -z localhost 9443
initialDelaySeconds: 250
periodSeconds: 10
readinessProbe:
exec:
command:
- /bin/sh
- -c
- nc -z localhost 9443
initialDelaySeconds: 250
periodSeconds: 10
imagePullPolicy: Always
resources:
requests:
memory: "1Gi"
cpu: "2000m"
limits:
memory: "2Gi"
cpu: "4000m"
lifecycle:
preStop:
exec:
command:  ['sh', '-c', '${WSO2_SERVER_HOME}/bin/wso2server.sh stop']
securityContext:
runAsUser: 802
env:
- name: NODE_IP
valueFrom:
fieldRef:
fieldPath: status.podIP
- name: HOST_NAME
value: wso2is
ports:
- containerPort: 9763
protocol: TCP
- containerPort: 9443
protocol: TCP
volumeMounts:
- name: identity-server-conf
mountPath: /home/wso2carbon/wso2-config-volume/repository/conf/deployment.toml
subPath: deployment.toml
serviceAccountName: "wso2svc-account"
imagePullSecrets:
- name: wso2is-deployment-creds
volumes:
- name: identity-server-conf
configMap:
name: identity-server-conf

The previous script will create the WS02 namespace, wso2svc-account service account, configmap identity-server-conf, wso2is-service service, and the wso2is-deployment deployment. It will also use the Docker image chakray/wso2is-rpi:5.10.0. This is based on the official WS02 Docker image for the Identity Server but is designed for ARM architectures.

2. Create the resources:

sudo kubectl apply -f .

3. List the pods in the WS02 namespace.

sudo kubectl get pods -n wso2

4. Use the name of the pod and show the details

sudo kubectl describe pod wso2is-deployment-6f66b69947-6n4jw -n wso2

The following message should be displayed at the bottom: “Successfully assigned wso2/wso2is-deployment-6f66b69947-6n4jw to nodea” which indicates the node on which the Identity Server instance is running.

5. Next, we must wait for the creation process to finish. We will be advised of this when the following message is displayed: “Started container wso2is“

6. We list the pods again. Both the number of pods and the status should be displayed.

sudo kubectl get pods -n wso2

7. We can scale the number of replicas using the following command. This will create new pods according to the number of replicas we install:

sudo  kubectl scale --current-replicas=1 --replicas=2 deployment/wso2is-deployment -n wso2

8. The deployment name can be obtained by listing the deployments in the WS02 namespace

sudo kubectl get deployments -n wso2

9. In order to visualize the Identity Server startup process we can monitor the IS logs using the following command:

sudo kubectl logs wso2is-deployment-6f66b69947-9kkbm -n wso2 -f

10. At the end of the Identity Server startup process, we can check the exercise has been successful by accessing the IP of the node where the instance is running and the port 10443 (configured in the deployment.yaml file) https://192.168.1.103:10443

10. Useful Commands

The following commands may be useful for dealing with any bugs that may appear during cluster creation or for better cluster management:

1.Get information from the cluster.

sudo systemctl restart k3s

2. Reboot K3s on the master node.

sudo systemctl restart k3s

3. Reboot K3s agent on the worker nodes.

sudo systemctl restart k3s-agent

4. Check logs.

# Revisar logs de k3s
sudo journalctl -u k3s -r
# Revisar logs generales
sudo tail -f /var/log/syslog
# Revisar logs de un pod especifico
sudo kubectl logs wso2is-deployment-6f66b69947-9kkbm -n wso2 -f
# Revisar logs de un deployment
sudo kubectl logs deploy/wso2is-deployment -f

5. Remove the WS02 namespace (where all resources, pods, services, etc. are created).

sudo kubectl delete namespace wso2

11. Links

Ricardo Osorio
WSO2 Specialist

Related Articles

Catch up on the latest news, articles, guides and opinions from Chakray.

WSO2 Identity Server
IAM

WSO2 Identity Server 7.0: Analysis of the new version’s capabilities and functionalities

Jesús Navarro
Technical Director
IAM

How to configure single sign-on in Camunda with Keycloak

Nestor Fernandez
Senior Integration Consultant
IAM

What is adaptive authentication and what are its challenges?

Trishan Fernando
Head of Sales