WSO2

Cómo construir un cluster de WSO2 Identity Server con Kubernetes y Raspberry Pi

En este artículo se explicará qué es Kubernetes y cómo podemos construir un cluster de WSO2 Identity Server con Kubernetes y Raspberry Pi.

Seguiremos el siguiente índice:

1. ¿Qué es Kubernetes?

Kubernetes es una plataforma open source escrita en Go que permite administrar cargas de trabajo, despliegues escalado y manejo de aplicaciones basadas en contenedores (docker por defecto). Permite orquestar infraestructura, redes y almacenamiento.

Está compuesto por un administrador central (nodo master) y algunos nodos worker. En el nodo master se ejecuta un servidor API (kube-apiserver), un scheduler, varios controllers y un sistema de almacenamiento que mantiene el estado del clúster, la configuración de los contenedores y la configuración de la red.

Algunos componentes importantes de Kubernetes son:

  • kubectl un cliente desarrollado para consumir el API implementado para gestionar todas las configuraciones kubernetes

  • Pod consta de uno o más contenedores que comparten una dirección IP, acceso al almacenamiento y namespace.

  • Namespace es una segregación de recursos, los pods necesitarían aprovechar los servicios para comunicarse.

  • Controllers son una serie de bucles de vigilancia usados para gestionar la orquestación, también son conocidos como operators. Cada controller interroga al kube-apiserver por el estado de un objeto en particular, luego modifica el objeto hasta que el estado declarado coincide con el estado actual.

  • Deployment es el operador predeterminado para contenedores, permite definir las diferentes funciones y configuraciones. Un deployment no funciona directamente con pods, en su lugar administra ReplicaSets.

  • ReplicaSet es un operador que creará o terminará pods enviando un podSpec. El podSpec se envía al kubelet, que interactúa con el motor del contenedor para generar o terminar un contenedor hasta que se esté ejecutando el número solicitado.

  • K3s: Es una distribución de Lubernetes que permite su ejecución en arquitecturas ARM64 y ARMv7. Otra forma de decirlo es un kubernetes ligero que puede ser ejecutado en una Raspberry Pi.

2. Requisitos para construir un Cluster de WSO2 IS con Kubernetes y Raspberry Pi

Los siguientes elementos son necesarios para la creación del laboratorio:

  • 4 Raspberry Pi modelo 4B de 4 GB.

  • 4 Micro SD de 32 GB.

  • 4 cables utp categoría 6

  • 4 cables usb tipo C a usb tipo A

  • 1 fuente de alimentación Anker Powerport

  • 1 Case acrílico de 4 capaaspberry Pi

  • 1 switch Ubiquiti UniFi US-8

  • 1 cable micro hdmi a hdmi

3. Arquitectura

En este ejemplo con 4 nodos, el nodo 0x00 funcionará como servidor de Base de Datos donde se almacenarán los recursos compartidos y de identidad para Identity Server. El nodo nodeA funcionará como nodo master y su función únicamente será la de orquestar los nodos worker. Los nodos nodeB y nodeC funcionarán como nodos worker es decir donde identity server correrá, en estos nodos se crearán las bases de datos de cada una de las instancias de Identity Server en H2.

4. Instalación y Configuración de Raspberry Pi Os

El primer paso es instalar Raspberry Pi Os LITE usando PiImager. Raspberry Pi tiene un breve vídeo de 40 segundos donde explica muy claramente cómo instalar Raspberry Pi Os en una tarjeta SD y un artículo muy descriptivo desde donde podrás descargar PiImager.

  1. HOW TO INSTALL RASPBERRY PI OS to your Raspberry Pi with ease – Raspberry Pi Imager (Raspbian)

  2. https://www.raspberrypi.org/software/

Al terminar la instalación de Raspberry Pi Os editaremos las opciones de arranque para permitir el uso de ciertas aplicaciones como docker o kubernetes. Para ello se requiere habilitar el soporte para los límites de cgroup modificando el archivo /boot/cmdline.txt y agregar la siguiente línea al final del archivo cgroup_memory=1 cgroup_enable=memory

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

Una vez modificado e instalado Raspberry Pi Os podemos conectar e iniciar los nodos

5. Preconfiguración

Para facilitar la instalación y configuración realizaremos las tareas a través de ssh por lo que primero necesitaremos conectarnos por hdmi a cada uno de los nodos y acceder con el usuario pi y la contraseña raspberry y realizar las siguientes acciones:

1.Habilitamos ssh con los siguientes comandos:

sudo systemctl start ssh
sudo systemctl enable ssh

2. Después de habilitar ssh podremos realizar las siguientes acciones de manera remota.

ssh pi@192.168.1.102

3. Configuramos el hostname, cada nodo tendrá un hostname distinto (nodeA, nodeB, etc.)

sudo raspi-config

4. Seleccionamos la opción “1 System Options”.

5. Seleccionamos la opción “S4 Hostname”.

6. Colocamos el nuevo hostname en cada nodo de acuerdo al diagrama anterior.

Por defecto Raspberry Pi únicamente permite 99 MB de memoria swap lo cuál puede ocasionar problemas de performance entre otros. Para resolverlo aumentaremos los límites de dphys-swapfile editando el archivo /etc/dphys-swapfile y modificando la opción CONF_SWAPSIZE=99 por CONF_SWAPSIZE=2048 para aumentar el límite de 99 MB a 2 GB

7. Apagamos dphys-swapfile:

sudo dphys-swapfile swapoff

8. Modificamos los límites (presiona Ctrl + O para guardar y Ctrl + X para salir).

sudo nano /etc/dphys-swapfile

9. Iniciamos dphys-swapfile:

sudo dphys-swapfile swapon

10. Creamos y configuramos un nuevo usuario wso2 y eliminamos el usuario pi.

11. Crea el usuario wso2 y su directorio home.

sudo useradd -m wso2

12. Coloca una nueva contraseña para el usuario wso2.

sudo passwd wso2

13. Editar el archivo sudoers y agregar la línea wso2 ALL=(ALL:ALL) ALL, para permitir al usuario wso2 ejecutar comandos como superusuario.

sudo nano /etc/sudoers

14. Reiniciamos cada uno de los nodos e iniciamos sesión con el usuario wso2 a través de una consola ssh.

ssh wso2@192.168.1.102

15. Elimina el usuario pi que viene por default en Raspberry Pi Os.

sudo userdel pi

16. Validamos la comunicación entre nodos editando el archivo /etc/hosts en cada uno de los nodos y agregamos los hostname e ip de todos los nodos.

sudo nano /etc/hosts

17. Instalamos dnsutils y validamos la resolución del hostname en cada uno de los nodos.

sudo apt install dnsutils
nslookup nodeA
nslookup nodeB
nslookup nodeC
nslookup 0x00

6. Instalación y Configuración de Kubernetes (nodo master)

En este escenario el nodoA será designado como nodo master, es decir este nodo se enfocará en gestionar los pods y el propio clúster. Para ello nos conectaremos a través de ssh al nodoA e instalaremos k3s:

1.Descargar e instalar k3s.

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

2. Una vez que concluya y visualicemos el mensaje [INFO]  systemd: Starting k3s validamos que k3s esté corriendo correctamente con el comando.

sudo systemctl status k3s

3. Ahora necesitamos obtener el token del nodo master para registrar los nodos worker (K106b0755ffcf7051652038b049db6c9120df4aa13ec98b12072beea4e6e1eb57a3::server:76d986f87ac5d864833e87e3ec2f4164).

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

7. Instalación y Configuración de Kubernetes (nodos worker)

En cada uno de los nodos worker (nodeB y nodeC) nos conectamos a través de ssh e instalaremos k3s-agent y registramos los nodos con el token (K106b0755ffcf7051652038b049db6c9120df4aa13ec98b12072beea4e6e1eb57a3::server:76d986f87ac5d864833e87e3ec2f4164) obtenido del nodo master y la ip del mismo nodo master (192.168.1.102).

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

Validamos que la instalación se realizó correctamente listando los nodos desde el nodo master, debemos visualizar el nodo master (nodeA) y los nodos worker (nodeB y nodeC).

sudo kubectl get nodes

8. Instalación y configuración de MySQ (docker)

El cuarto nodo (0x00) funcionará como servidor de Base de datos MariaDB usando docker, para ello realizaremos algunos de los pasos anteriores en el nodo 0x00:

1.Creamos el usuario wso2 y su directorio home.

sudo useradd -m wso2

2. Colocamos una nueva contraseña para el usuario wso2.

sudo passwd wso2

3. Editamos el archivo sudoers y agregamos la línea wso2 ALL=(ALL:ALL) ALL, para permitir al usuario wso2 ejecutar comandos como superusuario.

sudo nano /etc/sudoers

4. Editamos el archivo /etc/hosts y agregamos los hostname e ip de todos los nodos.

sudo nano /etc/hosts

5. Instalamos docker.

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

6. Al finalizar la instalación agregamos el usuario wso2 al grupo docker para permitirle ejecutar los comandos de docker.

sudo usermod -aG docker wso2

7. Utilizaremos una imagen de docker compatible con arquitecturas ARM (https://hub.docker.com/r/jsurf/rpi-mariadb) hacemos pull a la imagen.

docker pull jsurf/rpi-mariadb

8. Creamos el script mysql.sql basado en el recurso oficial de WSO2 para 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);

Copiamos el script mysql.sql creado anteriormente y lo ejecutamos para crear los esquemas WSO2IS_SHARED_DB y WSO2IS_IDENTITY_DB

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

9. Desplegar Identity Server

Nos conectamos nuevamente al nodo master nodeA y creamos el archivo deployment.yaml

nano deployment.yaml

y colocamos la siguiente configuración basada en el recurso oficial de wso2 para kubernetes (https://github.com/wso2/kubernetes-is/blob/master/simple/deployment-scripts/wso2is-ga.sh ), donde creamos el archivo de configuración deployment.toml que apuntará a la base de datos en el host 0x00 (192.168.1.101) y una sola 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

El script anterior creará el namespace wso2, el service account wso2svc-account, el configmap identity-server-conf, el service wso2is-service y el deployment wso2is-deployment y utilizará la imagen de docker chakray/wso2is-rpi:5.10.0, la cuál está basada en la imagen de docker oficial de wso2 para Identity Server pero pensada para arquitecturas ARM.

Creamos los recursos:

sudo kubectl apply -f .

Listamos los pods en el namespace wso2

sudo kubectl get pods -n wso2

utilizaremos el name del pod y mostraremos los detalles

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

en la parte inferior debe visualizarse el siguiente mensaje: “Successfully assigned wso2/wso2is-deployment-6f66b69947-6n4jw to nodea” lo cuál nos indica en que nodo se está ejecutando la instancia de Identity server

Lo siguiente será esperar a que concluya el proceso de creación esto sucederá cuando se visualice el mensaje: “Started container wso2is“

Volvemos a listar los pods y debe visualizarse el número de pods y el status

sudo kubectl get pods -n wso2

Podemos escalar el número de replicas con el siguiente comando, lo cuál creará nuevos pods de acuerdo al número de replicas que coloquemos:

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

El nombre del deployment se puede obtener listando los deployment en el namespace wso2

sudo kubectl get deployments -n wso2

Para visualizar el proceso de inicio de Identity Server podemos monitorear los logs de IS con el siguiente comando:

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

Al concluir el proceso de inicio de Identity Server se puede validar accediendo a la ip del nodo donde se encuentre la instancia corriendo y el puerto 10443 (configurado en el archivo deployment.yaml) https://192.168.1.103:10443

10. Comandos útiles

Los siguientes comandos pueden ser útiles para depurar algún error durante la creación o para gestionar mejor el cluster:

1.Obtener información del cluster.

sudo kubectl cluster-info

2. Reiniciar k3s en el nodo master.

sudo systemctl restart k3s-agent

3. Reiniciar k3s agent en los nodos worker.

sudo systemctl restart k3s-agent

4. Revisar logs.

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

5. Eliminar el namespace wso2 (donde se crean todos los recursos, pods, services, etc.)

Written By

Ricardo Osorio

WSO2 Specialist