To connect your
PostgreSQL database to MBI via an
SSH tunnel, you (or your team, if you are not a techie) will need to do a few things:
It is not as complicated as it might sound. Get started.
public key is used to authorize the MBI Linux user. In the next section, we will create the user and import the key.
PostgreSQL credentialspage opens, set the
Yes. This will display the
public keyis located underneath this form.
Leave this page open throughout the tutorial - you will need it in the next section and at the end.
If you are a bit lost, this is how to navigate through MBI to retrieve the key:
For the connection to be successful, your must configure your firewall to allow access from our IP address. it is
220.127.116.11/32, but it is also on the
PostgreSQL credentials page. See the blue box in the GIF above? That is it!
Linuxuser for MBI
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.
adduser rjmetric -p<password> mkdir /home/rjmetric mkdir /home/rjmetric/.ssh
public key we retrieved in the first section? To ensure the user has access to the database, we need to import the key into
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
/home/rjmetricdirectory to allow access via
chown -R rjmetric:rjmetric /home/rjmetric chmod -R 700 /home/rjmetric/.ssh
sshd\_config file associated with the server is not set to the default option, only certain users will have server access - this will prevent a successful connection to MBI. In these cases, it is necessary to run a command like
AllowUsers to allow the rjmetric user access to the server.
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 MBI 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;
secure password with your own secure password, which can be different than the SSH password. Additionally, 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.
To wrap things up, we need to enter the connection and user info into MBI. 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
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)
Remote Address: The IP address or hostname of the server we will SSH into
Username: Our SSH login name (should be rjmetric)
SSH Port: SSH port on your server (22 by default)
That is it! When you are finished, click Save & Test to complete the setup.