This is an internal documentation. There is a good chance you’re looking for something else. See Disclaimer.

Postgres Remote Access

Using SSH Port Forwarding

Forward Port

Note

This requires ssh access to the DB server.

ssh ${USERNAME}@db1.tocco.cust.vshn.net -L 5432:localhost:5432 -N

Hint

${USERNAME} consists of first_name.last_name (e.g. jane.doe)

Now you should be able to connect to the DB server on locahost:5432.

Connect Nice to remote DB

Once port forwarding is established, you can tell Nice to directly connect to a DB on the remote server.

Obtain credentials:

$ oc project nice-${INSTALLATION}
$ oc set env --list dc/nice |grep "^hibernate\.main\."
hibernate.main.databaseName=nice_tocco
hibernate.main.password=************
hibernate.main.serverName=db1.tocco.cust.vshn.net
hibernate.main.user=nice_tocco
hibernate.main.sslMode=require

Copy the necessary properties (colored) from above.

Create or alter customer/${CUSTOMER}/etc/application-development.properties:

hibernate.main.serverName=localhost
hibernate.main.databaseName=nice_tocco
hibernate.main.password=************
hibernate.main.user=nice_tocco

Direct Access

Note

Direct access is only possible from whitelisted addresses.

Important

Postgres doesn’t enforce SSL by default, you must enable it. Take a look at libpq - SSL Support for more details.

Using PSQL

psql 'postgresql://${USER}@db1.tocco.cust.vshn.net/${DB_NAME}?sslmode=verify-full&sslrootcert=${CERT}'

See Certificates for ${CERT}

Using Python

import psycopg2

conn = psycopg2.connect(
    host = "db1.tocco.cust.vshn.net",
    database = DB_NAME,
    user = USER,
    password = PASSWORD,
    sslmode = "verify-full",
    sslrootcert = CERT
)

See Certificates for CERT

Other Means of Accessing Postgres

There are many more libraries and tools that allow you to access a Postgres DB server. But be aware that Postgres doesn’t enable SSL verification by default, you must make sure SSL certificates are verified! Take a look at libpq - SSL Support, most tools and libraries based on libpg. Thus, most of them use the same SSL settings.

Certificates

TLS certificates can be found in roles/tocco/vars/certs.yml/.