PostgreSQL preparation on Windows - CustomerID

This page describes 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 may be 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 of how to 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 the command prompt to avoid involuntary character replacement.

Obtaining and Installing PostgreSQL

Ubisecure CustomerID requires a PostgreSQL database which is not included in the distribution.

Ubisecure CustomerID is tested with PostgreSQL 9.6.10.

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 provider, so please follow their instructions to ensure the best possible configuration.

The basic steps are listed here PostgreSQL Windows installation - CustomerID.

Creating a Database User

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

cd /d C:\Program Files\PostgreSQL\9.6\bin  (your PostgreSQL installation directory)
psql --username=postgres -c "CREATE USER customerid_user WITH PASSWORD 'replace with your database.password from win32.config'"

When prompted, enter the password given in the installer wizard window during the PostgreSQL installation process.

Creating the Database

Below are example commands for creating a database for CustomerID usage. By default collation settings will be based on the locale settings of the Windows installation.

psql --username=postgres -c "CREATE DATABASE customeriddb WITH OWNER = customerid_user ENCODING = 'UTF8' TABLESPACE = pg_default 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

cd /d C:\Program Files\PostgreSQL\9.6\bin  (your PostgreSQL installation directory)
psql --dbname=customeriddb --username=customerid_user -f "%PROGRAMFILES%\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 "%PROGRAMFILES%\Ubisecure\customerid\sql\cid_init.sql"
psql --dbname=customeriddb --username=customerid_user -f "%PROGRAMFILES%\Ubisecure\customerid\sql\cid_create_sso_views_and_functions.sql"

When prompted, enter the password from the database.password value of the win32.config file.

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.