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
The PCF requires the database administrator to create user in MySQL DB and provide necessary permissions to access the databases. Before installing the PCF it is required that the MySQL user and databases are created.

Customizing Database Name

Each microservice has a default database name assigned as mentioned in below table:
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

The PCF deployment requires the database administrator to create a user in the MYSQL database, and to assign it to the group having necessary permissions to access the tables on all SQL nodes.

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:

  1. Login to the server where the ssh keys are stored and SQL nodes are accessible.
  2. Connect to the SQL nodes.
  3. Login to the database as a root user.
  4. 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;
  5. 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'@'%';
  6. Execute the command, show databases, to view database.
  7. Exit from database and logout from MYSQL node.