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.4.x.
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.
Creating a Database User
Use the following command to create a new user in the PostgreSQL database.
cd /d C:\Program Files\PostgreSQL\9.4\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
psql --username=postgres -c "CREATE DATABASE customeriddb WITH OWNER = customerid_user ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'Finnish_Finland.1252' LC_CTYPE = 'Finnish_Finland.1252' 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;"
Modifying the DDL
The main modifications to the DDL involves setting collations to specific columns. There could be some information that should be interpreted as identical regardless of letter case but on the other hand there could also be some attributes where letter case is significant in uniqueness. In order to make it easier to set collations to columns it is convenient to create named collations that can be referenced by name instead.
psql --username=postgres --dbname=customeriddb -c "CREATE COLLATION custom_collation (locale = 'Finnish_Finland');"
Now that there's a named collation, it can be attached to column definitions by modifying DDL like in example below:
Use the following command to open the cid_create.sql file and do the modifications accordingly.
cd /d %PROGRAMFILES%\Ubisecure\customerid\sql\ notepad cid_create.sql
Original:
CREATE TABLE CIDTUSERS ( CIDCUSERID CHAR(36) NOT NULL CONSTRAINT CIDPK_CIDTUSERS PRIMARY KEY, CIDCREPOUSER VARCHAR(1024), CIDCORGANIZATIONID CHAR(36) CONSTRAINT CIDFK_CIDTUSERS_CIDCORGANIZATIONID REFERENCES CIDTORGANIZATIONS (CIDCORGANIZATIONID), CIDCSTATUS INTEGER NOT NULL DEFAULT 4, CIDCCN VARCHAR(1024), CIDCEMAIL VARCHAR(1024), CIDCMOBILE VARCHAR(1024), CIDCLOCALE CHAR(5), CIDCFIRSTNAME VARCHAR(1024), CIDCSURNAME VARCHAR(1024), CIDCLOGIN VARCHAR(1024), CIDCSSN VARCHAR(1024), CIDCCREATED TIMESTAMP NOT NULL, CIDCLASTMODIFIED TIMESTAMP NOT NULL);
Modified:
CREATE TABLE CIDTUSERS ( CIDCUSERID CHAR(36) NOT NULL CONSTRAINT CIDPK_CIDTUSERS PRIMARY KEY, CIDCREPOUSER VARCHAR(1024), CIDCORGANIZATIONID CHAR(36) CONSTRAINT CIDFK_CIDTUSERS_CIDCORGANIZATIONID REFERENCES CIDTORGANIZATIONS (CIDCORGANIZATIONID), CIDCSTATUS INTEGER NOT NULL DEFAULT 4, CIDCCN VARCHAR(1024) COLLATE custom_collation, CIDCEMAIL VARCHAR(1024) COLLATE custom_collation, CIDCMOBILE VARCHAR(1024), CIDCLOCALE CHAR(5) COLLATE custom_collation, CIDCFIRSTNAME VARCHAR(1024) COLLATE custom_collation, CIDCSURNAME VARCHAR(1024) COLLATE custom_collation, CIDCLOGIN VARCHAR(1024) COLLATE custom_collation, CIDCSSN VARCHAR(1024), CIDCCREATED TIMESTAMP NOT NULL, CIDCLASTMODIFIED TIMESTAMP NOT NULL);
Applying the CustomerID DDL to PostgreSQL
cd /d C:\Program Files\PostgreSQL\9.4\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.