Problem

This article provides the steps for connecting locally to the embedded PostgreSQL database of the New Standard Cluster with PostgreSQL running on Linux.

Resolution

PostgreSQL uses an Access Policy List stored in pg_hba.conf, to delegate access to the database. Because of that some prerequisites must be met before authenticating to the database locally.

Prerequisites

1. Navigate to Setup → Database Settings → Manage Internal Database page of the Admin UI.

2. Create a new Access Policy Under Access Policies List (pg_hba.conf).

3. The newly created access policy must look the following way:

Connection Type: host
Database: all
User: stdbuser
Address: 127.0.0.1/32 (for local access only)
Authentication Method: password

4. Once the new Access Policy is created a restart of the database is required for the new policy to take effect. The database can be restarted by hitting the Restart button on the bottom left of the Database Settingspage.

All actions described above must be done separately for each node in the cluster.

In the New Standard Cluster model, the PostgreSQL database requires an additional non-root user to operate. For this reason, the SecureTransport installation process creates a new non-root user - stdbuser - with UID and GID 9876, thus either a su command must be issued before logging into the database or you need to manually export of the Library path variable, as exaplained in the Connect to the embedded PostgreSQL database without logging into stdbuser section below.

Connect to the embedded PostgreSQL database using stdbuser

1. Issue the following command to log into the stdbuser:

su stdbuser

2. Navigate to <FILEDRIVEHOME>/postgresql/bin.

3. Issue the following command to connect to the SecureTransport database:

./psql --host=localhost --port=54321 -d st

4. When prompted for the password, provide the password that was configured during the installation.

5. Once finished using the database, log out of the stdbuser with the command

exit

Connect to the embedded PostgreSQL database without logging into stdbuser

1. Issue the below command to manually set the LIBRARY PATH to the pgsql libraries:

export LD_LIBRARY_PATH=<FILEDRIVEHOME>//postgresql/lib

2. Navigate to <FILEDRIVEHOME>/postgresql/bin.

3. Issue the following command to connect to the SecureTransport database:

./psql --host=localhost --port=54321 -d st

4. When prompted for the password, provide the password that was configured during the installation.

5. Once finished using the database, log out of the stdbuser with the command

exit