To connect your PostgreSQL database to Magento BI via an SSH tunnel, you (or your team, if you're not a techie) will need to do a few things:
It's not as complicated as it might sound. Let's get started.
The public key is used to authorize the Magento BI Linux user. In the next section, we'll create the user and import the key.
Leave this page open throughout the tutorial - you'll need it in the next section and at the end.
If you're a bit lost, here's how to navigate through Magento BI to retrieve the key:
For the connection to be successful, your must configure your firewall to allow access from our IP address. It's 126.96.36.199/32, but it's also on the PostgreSQL credentials page. See the blue box in the GIF above? That's it!
This can be a production or slave 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
touch /home/rjmetric/.ssh/authorized_keys "<PASTE KEY HERE>" >> /home/rjmetric/.ssh/authorized_keys
chown -R rjmetric:rjmetric /home/rjmetric chmod -R 700 /home/rjmetric/.ssh
If the 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 Magento BI. In these cases, it's 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 Magento BI 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 a 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 Magento BI. Did you leave the PostgreSQL credentials page open? If not, go to Manage Data > Connections and click the Add a Data Source button, then the PostgreSQL icon. Don't forget to toggle the Encrypted button to Yes.
Enter the following info into this page, starting with the Database Connection section:
Under "SSH Connection":
That's it! When you're finished, click the Save & Test button to complete the setup.