PostgreSQL preparation on Linux - CustomerID

This page will describe how to install a very basic PostgreSQL Server RDBMS installation with a basic database for Ubisecure CustomerID. Many details will vary on a per customer basis, so further tuning is left to the integrator. One significant detail is to manage the collation rules on a per-column basis, therefore it is most probably necessary to make changes to the provided Database Definition Language (DDL) files before they are used to create the database tables for Ubisecure CustomerID. Collations define how text is compared and sorted and whether searches should behave in a case sensitive or insensitive manner.

Following commands are just examples how install a basic PostgreSQL database. You might want to use your own commands. Don't copy and paste the commands in this chapter. Write them by hand to avoid involuntary character replacement.

NOTE: In case you are using Ident type of authentication with PostgreSQL, run the psql commands as postgres user. Not as root .

Obtaining and installing PostgreSQL

PostgreSQL binaries and instructions on how to use platform specific package managers to download and install PostgreSQL, can be found at the following URL:
https://www.postgresql.org/download/

Having obtained PostgreSQL binaries, run the wizard or relevant binaries to install. The installation process is documented and maintained by your PostgreSQL vendor, so please follow their instructions to ensure the best possible configuration. We have tested Ubisecure CustomerID with PostgreSQL 9.6.10.

Configure PostgreSQL authentication

To allow SQL users to access PostgreSQL, we need to configure authentication methods in pg_hba.conf locate at /var/lib/pgsql/9.6/data/pg_hba.conf.

Example pg_hba.conf

local   postgres         postgres                                 peer
host    postgres         postgres         127.0.0.1/32            ident
host    postgres         postgres         ::1/128                 ident

local   customeriddb     customerid_user                          md5
host    customeriddb     customerid_user  127.0.0.1/32            md5
host    customeriddb     customerid_user  ::1/128                 md5
host    customeriddb     customerid_user  <CustomerID IP/Host>    md5
host    customeriddb     customerid_user  <SSO IP/Host>           md5

Update the listen_addresses in postgresql.conf located at /var/lib/pgsql/9.6/data/postgresql.conf.
```
listen_addresses = '<PostgreSQL IP/Host>'
```

Creating a database user

Use the following command to create a new user in the PostgreSQL database.

psql --username=postgres -c "CREATE USER customerid_user WITH PASSWORD 'replace with your database.password from linux.config'"

Creating the database

Below are example commands for creating a database for CustomerID usage. You should select suitable values for LC_COLLATE and LC_CTYPE based on your data needs. The values presented here are just examples to help you notice the settings.

psql --username=postgres -c "CREATE DATABASE customeriddb WITH OWNER = customerid_user TEMPLATE = template0 ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'fi_FI.utf8' LC_CTYPE = 'fi_FI.utf8' CONNECTION LIMIT = -1;" 
psql --username=postgres -c "GRANT CONNECT, TEMPORARY ON DATABASE customeriddb TO public;" 
psql --username=postgres -c "GRANT ALL ON DATABASE customeriddb TO customerid_user;"

Applying the CustomerID DDL to PostgreSQL

psql --dbname=customeriddb --username=customerid_user -f /usr/local/ubisecure/customerid/sql/cid_create.sql

When the database structure has been created, run the cid_init.sql and create the relevant views for SSO Server

psql --dbname=customeriddb --username=customerid_user -f /usr/local/ubisecure/customerid/sql/cid_init.sql
psql --dbname=customeriddb --username=customerid_user -f /usr/local/ubisecure/customerid/sql/cid_create_sso_views_and_functions.sql

If you notice something wrong with the database at this point, it is possible to drop the tables using the script cid-drop.sql. After this, the DDL can be modified and imported again. Note that all inserted data in the database will be lost when the tables are dropped.