Configuring Database
Introduction
Policy Control Function (PCF) microservices use MySQL database to store the configuration and run time data. Following microservices require dedicated MySQL databases created in MySQL data tier.- Session Management (SM) Service - To store SM and Policy Authorization (PA) session state
- Access and Mobility (AM) Service - To store AM session state
- User Service - To store User information like Policy Data (from UDR) and Policy Counter information (from CHF)
- Config Server - To store configuration data
- Audit Service - To store session state audit data
Customizing Database Name
Service Name | Default Database Name |
---|---|
SM Service | pcf_smservice |
AM Service | pcf_amservice |
User Service | pcf_userservice |
Config Server Service | ocpm_config_server |
Audit Service | oc5g_audit_service |
Query Service | pcf_smservice |
It is recommended to use unique database name when there are multiple instances of PCF deployed in the network and they share the same data tier (MySQL cluster).
It is recommended to create custom unique database name, by simply prefixing the deployment name of the PCF. This way database name uniqueness can be achieved across all deployments. However, you can use any prefix to create the unique database name. For example, if the OCPCF deployment name is "site1" then the SM Service database can be named as "site1_pcf_smservice". Refer the PCF Customization Parameters section for how to override default database names with custom database names.
Configuring MYSQL Database
Note:
This procedure must be performed only once before initiating the PCF deployment.Note:
Refer Creating a Kubernetes Secret for Storing Database Username and Password for creating Kubernetes Secret for database access.To configure MYSQL database for the different microservices:
- Login to the server where the ssh keys are stored and SQL nodes are accessible.
- Connect to the SQL nodes.
- Login to the database as a root user.
- Create tables for the
different microservices:
Note:
Following are the examples of creating the tables for different microservices. It is administrator responsibility to create user and grant necessary privilege and permissions to the user.SM Service
In the following script default database name "pcf_smservice" is used, if custom database name is used then replace it wherever applicable.CREATE DATABASE IF NOT EXISTS `pcf_smservice`; create table if not exists pcf_smservice.SmPolicyAssociation ( k Varchar(64) primary key not null , v Text not null, SUPI Varchar(128), GPSI Varchar(128), IPV4 Varchar(15), IPV6 Varchar(45), DNN Varchar(128), SNSSAI Varchar(16), IPD Varchar(128), MAC Varchar(17), LASTACCESSTIME DATETIME ); create table if not exists pcf_smservice.AppSession ( k Varchar(1024) primary key not null , v Text not null );
AM Service
In the following script default database name "pcf_amservice" is used, if custom database name is used then replace it wherever applicable.CREATE DATABASE IF NOT EXISTS `pcf_amservice`; CREATE TABLE IF NOT EXISTS `pcf_amservice`.`documents` ( `id` varchar(45) NOT NULL, `projection` varchar(45) DEFAULT NULL, `name` varchar(100) DEFAULT NULL, `data` blob, PRIMARY KEY (`id`) ) DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `pcf_amservice`.`events` ( `id` varchar(45) NOT NULL, `streamId` varchar(45) DEFAULT NULL, `data` blob, `type` varchar(100) DEFAULT NULL, `version` int(11) DEFAULT NULL, `createdDate` datetime(6) DEFAULT NULL, `sequence` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `pcf_amservice`.`projections` ( `name` varchar(100) NOT NULL, `lastEventSequence` int(11) DEFAULT NULL, PRIMARY KEY (`name`) ) DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `pcf_amservice`.`snapshots` ( `id` varchar(45) NOT NULL, `stream` varchar(45) DEFAULT NULL, `data` blob, `lastEventSequence` int(11) DEFAULT NULL, `createDate` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `pcf_amservice`.`streams` ( `id` varchar(45) NOT NULL, `type` varchar(100) DEFAULT NULL, `version` int(11) DEFAULT NULL, `createdDate` datetime(6) DEFAULT NULL, `deletedDate` datetime(6) DEFAULT NULL, PRIMARY KEY (`id`) ) DEFAULT CHARSET=utf8;
User Service
In the following script default database name "pcf_userservice" is used, if custom database name is used then replace it wherever applicable.CREATE DATABASE IF NOT EXISTS `pcf_userservice`; create table if not exists pcf_userservice.userservice_user ( k varchar(128) primary key not null , v blob not null, msisdn varchar(128), imsi varchar(128), nai varchar(128), extid varchar(128), other varchar(128) ); create index idx_msisdn on pcf_userservice.userservice_user (msisdn); create index idx_imsi on pcf_userservice.userservice_user (imsi); create index idx_nai on pcf_userservice.userservice_user (nai); create index idx_extid on pcf_userservice.userservice_user (extid); create index idx_other on pcf_userservice.userservice_user (other); create table if not exists pcf_userservice.chf_user ( k varchar(128) primary key not null, v blob not null, supi varchar(128), gpsi varchar(128) ); create index idx_supi on pcf_userservice.chf_user (supi); create index idx_gpsi on pcf_userservice.chf_user (gpsi);
Config Server Service
In the following script default database name " ocpm_config_server" is used, if custom database name is used then replace it wherever applicable.CREATE DATABASE IF NOT EXISTS `ocpm_config_server`; CREATE TABLE IF NOT EXISTS `ocpm_config_server`.`topic_info` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'Default Topics.', `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `modify_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `version` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `UK_gd6b0a6mdpxc55qbibre2cldc` (`name`) ) AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE IF NOT EXISTS `ocpm_config_server`.`configuration_item` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `cfg_key` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `md5sum` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `cfg_value` mediumtext COLLATE utf8_unicode_ci, `version` int(11) NOT NULL, `topic_info_id` bigint(20) NOT NULL, PRIMARY KEY (`id`), KEY `FKdue8drxn6acrdt63iacireky1` (`topic_info_id`), FOREIGN KEY (`topic_info_id`) REFERENCES `ocpm_config_server`.`topic_info`( `id`) ON DELETE CASCADE ) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Audit Service
In the following script default database name "oc5g_audit_service" is used, if custom database name is used then replace it wherever applicable.CREATE DATABASE IF NOT EXISTS `oc5g_audit_service`; CREATE TABLE IF NOT EXISTS `oc5g_audit_service`.`AuditRegistrations` ( `service_name` varchar(255) COLLATE utf8_unicode_ci , `audit_req_data` mediumtext COLLATE utf8_unicode_ci, PRIMARY KEY (`service_name`) ) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
- Create the user and assign
it to a group having necessary permissions to access the tables on all of the
SQL nodes by executing the following command: :
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
where:username is the username and password is the password for MySQL database user.
For Example: In the below example "pcfusr" is used as username, "pcfpasswd" is used as password and default database names of micro service names
CREATE USER 'pcfusr'@'%' IDENTIFIED BY 'pcfpasswd'; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON pcf_smservice.* TO 'pcfusr'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON pcf_amservice.* TO 'pcfusr'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON pcf_userservice.* TO 'pcfusr'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON ocpm_config_server.* TO 'pcfusr'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON oc5g_audit_service.* TO 'pcfusr'@'%';
- Execute the command,
show databases
, to view database. - Exit from database and logout from MYSQL node.