Preparing the Native Integrations Service Database

Important:
1. You must create the Native Integrations service database before deploying the Native Integrations service.
2. The Native Integration service supports PostgreSQL 11.5 and later.

The Native Integrations service database stores information about user credentials and configuration settings. You can deploy the Native Integrations service database in one of these ways:

• Scenario 1. Deploy the database to a self-hosted PostgreSQL server.
• Scenario 2. Deploy the database using the PostgreSQL database service in Azure.

Scenario 1. Deploying the Native Integrations Service Database to a Self-hosted PostgreSQL Server

To deploy the Native Integrations service database, complete the following actions:

1. Connect to a PostgreSQL server node under the root user.

2. Create the create_db.sql script file with the commands required for database creation. To do this, run the following command:

   cat << EOF > /tmp/create_db.sql
   DO
   \$\$
   BEGIN
       IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'mservice_login') THEN
          CREATE ROLE mservice_login LOGIN;
          RAISE NOTICE 'Group mservice_login created';
       END IF;
   END
   \$\$;
   CREATE USER :user ENCRYPTED PASSWORD :'password';
   \echo User :user created
   GRANT mservice_login to :user;
   \echo User is a member of mservice_login group
   CREATE DATABASE a8n_:user;
   \echo Database a8n_:user created
   REVOKE ALL ON DATABASE a8n_:user FROM public;
   GRANT CONNECT ON DATABASE a8n_:user TO :user;
   \connect a8n_:user
   GRANT CREATE ON SCHEMA public TO :user;
   GRANT USAGE ON SCHEMA  public TO :user;
   GRANT SELECT,UPDATE,INSERT,DELETE ON ALL TABLES IN SCHEMA public TO :user;
   ALTER DEFAULT PRIVILEGES FOR ROLE :user IN SCHEMA public
   GRANT SELECT,UPDATE,INSERT,DELETE ON TABLES TO :user;
   GRANT SELECT,USAGE ON ALL SEQUENCES IN SCHEMA public TO :user;
   ALTER DEFAULT PRIVILEGES FOR ROLE :user IN SCHEMA public
   GRANT SELECT,USAGE ON SEQUENCES TO :user;
   GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO :user;
   ALTER DEFAULT PRIVILEGES FOR ROLE :user IN SCHEMA public
   GRANT EXECUTE ON FUNCTIONS TO :user;
   \connect postgres
   SELECT pg_reload_conf();
   EOF

3. Create the database by running the following command:

   su - postgres -c "psql -f /tmp/create_db.sql --set ON_ERROR_STOP=on -q -o /dev/null -v user='new_db_owner' -v password='<db_user_password>'"

   where:

   • <new_db_owner> is the name of the Native Integrations service database user
   • <db_user_password> is the password of the Native Integrations service database user

Native Integrations Service Database Connection Parameters

To connect to the Native Integrations service database, provide the following parameters:

Parameter	Value
Database server host	The BackNet IP of the database server host
Database name	a8n_psa
Database user (user)	psa
Database user password (password)	<db_user_password>: the password you specified in step 4

Scenario 2. Deploying the Native Integrations Service Database Using the PostgreSQL Database Service in Azure

Prerequisites

• A corresponding Azure subscription.
• A configured PostgreSQL database service in Azure
• The PostgreSQL client on Operations management node must have access to the PostgreSQL database service in Azure.

Deployment Procedure

To deploy the Native Integrations service database using the PostgreSQL database service in Azure:

1. Connect to the Operations management node under the root user.

2. Create the create_db.sql script file with the commands required for database creation by running the following command:

   cat << EOF > /tmp/create_db.sql
   DO
   \$\$
   BEGIN
       IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'mservice_login') THEN
          CREATE ROLE mservice_login LOGIN;
          RAISE NOTICE 'Group mservice_login created';
       END IF;
   END
   \$\$;
   CREATE USER :user ENCRYPTED PASSWORD :'password';
   \echo User :user created
   GRANT mservice_login to :user;
   \echo User is a member of mservice_login group
   CREATE DATABASE a8n_:user;
   \echo Database a8n_:user created
   REVOKE ALL ON DATABASE a8n_:user FROM public;
   GRANT CONNECT ON DATABASE a8n_:user TO :user;
   \connect a8n_:user
   GRANT CREATE ON SCHEMA public TO :user;
   GRANT USAGE ON SCHEMA  public TO :user;
   GRANT SELECT,UPDATE,INSERT,DELETE ON ALL TABLES IN SCHEMA public TO :user;
   ALTER DEFAULT PRIVILEGES FOR ROLE :user IN SCHEMA public
   GRANT SELECT,UPDATE,INSERT,DELETE ON TABLES TO :user;
   GRANT SELECT,USAGE ON ALL SEQUENCES IN SCHEMA public TO :user;
   ALTER DEFAULT PRIVILEGES FOR ROLE :user IN SCHEMA public
   GRANT SELECT,USAGE ON SEQUENCES TO :user;
   GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO :user;
   ALTER DEFAULT PRIVILEGES FOR ROLE :user IN SCHEMA public
   GRANT EXECUTE ON FUNCTIONS TO :user;
   \connect postgres
   SELECT pg_reload_conf();
   EOF

3. Create the database by running the following command:

   psql \
   -h <db_postgresql_service_name_in_azure> \
   -U <db_admin_login> \
   -d postgres \
   -f /tmp/create_db.sql \
   --set ON_ERROR_STOP=on \
   -v name='psa' \
   -v password='<db_user_password>'

   where:

   • <db_postgresql_service_name_in_azure> is the PostgreSQL database service name in Azure (provided by Azure as a result of the PostgreSQL database service configuration)
   • <db_admin_login> is the admin log-in name to the PostgreSQL database service in Azure (provided by Azure as a result of the PostgreSQL database service configuration)

Native Integrations Service Database Connection Parameters

To connect to the Native Integrations service database in Azure, provide the following parameters:

Parameter	Value
The PostgreSQL database service name in Azure	<db_postgresql_service_name_in_azure>, provided by Azure as a result of the PostgreSQL database service configuration
Database name	a8n_psa
Database user (user)	psa
Database user password (password)	<db_user_password>: the password you specified in step 3