DocumentationCommerceCommerce Intelligence User Guide

Connect PostgreSQL via SSH Tunnel

Last update: Tue Jul 25 2023 00:00:00 GMT+0000 (Coordinated Universal Time)

CREATED FOR:

  • Beginner
  • Intermediate
  • Admin
  • Developer
  • User

To connect your PostgreSQL database to Commerce Intelligence via an SSH tunnel, you must do a few things:

  1. Retrieve the Commerce Intelligence public key
  2. Allow access to the Commerce Intelligence IP address
  3. Create a Linux user for Commerce Intelligence
  4. Create a PostgreSQL user for Commerce Intelligence
  5. Enter the connection and user info into Commerce Intelligence

Retrieving the Commerce Intelligence public key retrieve

The public key is used to authorize the Commerce Intelligence Linux user. Now, you will create the user and import the key.

  1. Go to Manage Data > Connections and click Add a Data Source.
  2. Click the PostgreSQL icon.
  3. After the PostgreSQL credentials page opens, set the Encrypted toggle to Yes. This displays the SSH setup form.
  4. The public key is located underneath this form.

Leave this page open throughout the tutorial - you will need it in the next section and at the end.

Below demonstrates how to navigate through Commerce Intelligence to retrieve the key:

Retrieving the RJMetrics public key

Allow access to the Commerce Intelligence IP address allowlist

For the connection to be successful, you must configure your firewall to allow access from your IP address. It is 54.88.76.97/32, but it is also on the PostgreSQL credentials page. See the blue box in the GIF above.

Creating a Linux user for Commerce Intelligence linux

This can be a production or secondary machine, as long as it contains real-time (or frequently updated) data. You may restrict this user any way you like, as long as it retains the right to connect to the PostgreSQL server.

  1. To add the new user, run the following commands as root on your Linux server:
        adduser rjmetric -p<password>
        mkdir /home/rjmetric
        mkdir /home/rjmetric/.ssh

  1. Remember the public key you retrieved in the first section? To ensure that the user has access to the database, you need to import the key into authorized\_keys.

    Copy the entire key into the authorized\_keys file as follows:

        touch /home/rjmetric/.ssh/authorized_keys
        "<PASTE KEY HERE>" >> /home/rjmetric/.ssh/authorized_keys
  1. To finish creating the user, alter the permissions on the /home/rjmetric directory to allow access via SSH:
        chown -R rjmetric:rjmetric /home/rjmetric
        chmod -R 700 /home/rjmetric/.ssh
IMPORTANT
If the sshd\_config file associated with the server is not set to the default option, only certain users have server access - this prevents a successful connection to Commerce Intelligence. In these cases, it is necessary to run a command like AllowUsers to allow the rjmetric user access to the server.

Creating an Commerce Intelligence Postgres user postgres

Your organization may require a different process, but the simplest way to create this user is to execute the following query when logged into Postgres as a user with the right to grant privileges. The user should also own the schema that Commerce Intelligence is being granted access to.

    GRANT CONNECT ON DATABASE <database name> TO rjmetric WITH PASSWORD <secure password>;GRANT USAGE ON SCHEMA <schema name> TO rjmetric;GRANT SELECT ON ALL TABLES IN SCHEMA <schema name> TO rjmetric;ALTER DEFAULT PRIVILEGES IN SCHEMA <schema name> GRANT SELECT ON TABLES TO rjmetric;

Replace secure password with your own secure password, which can be different from the SSH password. Also, make sure you replace database name and schema name with the appropriate names in your database.

If you want to connect multiple databases or schemas, repeat this process as necessary.

Entering the connection and user info into Commerce Intelligence finish

To wrap things up, you need to enter the connection and user info into Commerce Intelligence. Did you leave the PostgreSQL credentials page open? If not, go to Manage Data > Connections and click Add a Data Source, then the PostgreSQL icon. Do not forget to set the Encrypted toggle to Yes.

Enter the following info into this page, starting with the Database Connection section:

  • Username: The RJMetrics Postgres username (should be rjmetric)
  • Password: The RJMetrics Postgres password
  • Port: PostgreSQL port on your server (5432 by default)
  • Host: 127.0.0.1

Under SSH Connection:

  • Remote Address: The IP address or hostname of the server you will SSH into
  • Username: Your SSH login name (should be rjmetric)
  • SSH Port: SSH port on your server (22 by default)

When you are finished, click Save & Test to complete the setup.

recommendation-more-help
e1f8a7e8-8cc7-4c99-9697-b1daa1d66dbc