Preparing the PSA Integration Service Database

Important:
1. You must create the PSA Integration service database before deploying the PSA Integration service.
2. The PSA Integration service supports PostgreSQL 9.6 and later.

The PSA Integration service database stores information about user credentials and configuration settings. You can deploy the PSA Integration 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 PSA Integration Service Database to a Self-hosted PostgreSQL Server

To deploy the PSA Integration 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. Add the connect permissions for all the mservice_login group members by adding the following lines to the /var/lib/pgsql/<PostgreSQL_Version>/data/pg_hba.conf configuration file:

    host    all     mservice_login      <idp_app_host_ip>/32       reject
    host    all     +mservice_login      <idp_app_host_ip>/32       md5

    where:

  4. 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 PSA Integration service database user
    • <db_user_password> is the password of the PSA Integration service database user

PSA Integration Service Database Connection Parameters

To connect to the PSA Integration 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 PSA Integration Service Database Using the PostgreSQL Database Service in Azure

Prerequisites

Deployment Procedure

To deploy the PSA Integration 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)

PSA Integration Service Database Connection Parameters

To connect to the PSA Integration 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