PostgreSQL preparation on Linux - Accounting Service - SSO

This page will describe how to install a very basic PostgreSQL Server RDBMS installation with a database for Ubisecure Accounting Service. In a clustered environment the same PostgreSQL installation is naturally used by all of the nodes.

Many details will vary on a per customer basis, so further tuning is left to the integrator. Clustered installation of PostgreSQL itself, backup, restore etc. are not in the scope of this document.

Following commands are just examples on how to install a basic PostgreSQL database. You might want to use your own commands.

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

Obtain and install 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 Accounting Service with versions listed under System Recommendations and Supported Platforms / Relational Databases.

Create a database user

psql --username=postgres -c "CREATE USER <accounting.datasource.username in unix.config> WITH PASSWORD '<accounting.datasource.password in unix.config>'"

Replace <accounting.datasource.username in unix.config> with your setting (without angle brackets).

Replace <accounting.datasource.password in unix.config> with your setting (without angle brackets).

NOTE: You need to enable md5 type of authentication for the created user. Ident type of authentication won't work. See the instructions provided by your PostgreSQL vendor.

Create the database

psql --username=postgres -c "CREATE DATABASE accountingdb WITH OWNER = <accounting.datasource.username in unix.config> TEMPLATE = template0 ENCODING = 'UTF8' TABLESPACE = pg_default CONNECTION LIMIT = -1;"
psql --username=postgres -c "GRANT CONNECT, TEMPORARY ON DATABASE accountingdb TO public;"
psql --username=postgres -c "GRANT ALL ON DATABASE accountingdb TO <accounting.datasource.username in unix.config>;"

Replace <accounting.datasource.username in unix.config> with your setting (without angle brackets).

You may choose a different database name instead of accountingdb and define it as part of accounting.datasource.url in unix.config.

Add PostgreSQL LC_COLLATE and LC_CTYPE settings if the default settings are not suitable for you. At the moment Accounting Service does not have any language dependent data that would require a specific locale but these settings are not trivial to change for an existing database.

Linux example

For your courtesy an example setup session on a Centos 7 environment with single node PostgreSQL v. 9.6 is provided but Ubisecure does not guarantee that this procedure will work in your environment even with the same Centos 7 version but you must start from PostgreSQL download site and use the latest supported version

# Install statements from https://www.postgresql.org/download/linux/redhat/ as root
sudo su
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install postgresql96
yum install postgresql96-server
/usr/pgsql-9.6/bin/postgresql96-setup initdb
systemctl enable postgresql-9.6
systemctl start postgresql-9.6
exit

# User and DB creation as postgres user
cd /usr/local
sudo su postgres
psql --username=postgres -c "CREATE USER accounting_user WITH PASSWORD 'accounting_password'"
psql --username=postgres -c "CREATE DATABASE accountingdb WITH OWNER = accounting_user TEMPLATE = template0 ENCODING = 'UTF8' TABLESPACE = pg_default CONNECTION LIMIT = -1;"
psql --username=postgres -c "GRANT CONNECT, TEMPORARY ON DATABASE accountingdb TO public;"
psql --username=postgres -c "GRANT ALL ON DATABASE accountingdb TO accounting_user;"
exit

# MD5 authentication
sudo su
nano /var/lib/pgsql/9.6/data/pg_hba.conf
# Have e.g. the following kind of definitions
host    accountingdb     accounting_user  127.0.0.1/32            md5
host    accountingdb     accounting_user  ::1/128                 md5
host    accountingdb     accounting_user  192.168.0.139/32        md5

# Restart PostgreSQL server
systemctl restart postgresql-9.6