Guide: Using PostgreSQL with Databox

HOW TO

Prepare your PostgreSQL database
Connect PostgreSQL to Databox
Use the Metric Builder for PostgreSQL
Additional Information

PostgreSQL connections are available on the Professional and higher plans. Request a trial of PostgreSQL by following these steps.

How to prepare your PostgreSQL database

How to prepare your PostgreSQL database

To prepare our PostgreSQL database and host, we'll need to allow remote connections from Databox’s public IP (52.4.198.118) to our Database. The necessary steps to take that will depend on your database, server infrastructure, and firewall. Our IP must be able to connect to the database port directly.

For our purposes, we'll assume our PostgreSQL server is up and running, reachable from internet addresses and accepting TCP/IP connections on some TCP port (5432 is the default port).

1. First, we will create a user named "databox_ro" with permissions to remotely access the Database called "mydb"

CREATE USER databox_ro WITH PASSWORD 'secretPassword'; GRANT ALL ON DATABASE mydb to databox_ro; GRANT SELECT ON ALL TABLES IN SCHEMA public TO databox_ro; -- at least 9.0 GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO databox_ro; -- at least 9.0<br>

To keep this simple, we have given this user SELECT permission for all Tables in our database. Permission could be given to select from one Table only, or we could create a custom view and give permissions to select from this view only. You can create a Custom View and give the user permission to select only select from this View only. A basic example of how to achieve this can be found here.

2. By default, our PostgreSQL server will be set to only listen to the local interface. We must configure our PostgreSQL server to listen to all IPs. To do this, we'll open /etc/postgresql/9.5/main/postgresql.conf (default for Ubuntu and Debain). Check to ensure it contains the following:

listen_addresses = '*' # Listen on all host IP addresses available port = 5432 # Listen on this port, default is 5432<br>

3. If you’ve made any changes, restart your PostgreSQL server to make sure it's ready to accept remote connections

service postgresql restart

4. Next, we must give our user access from Databox's IP to our newly created database. We'll add the following to pg_hba.conf and reload the database:

host mydb databox_ro  52.4.198.118   255.255.255.255   md5

5. Find Port 5432/TCP, the default PostgreSQL port, from our VPC IP mentioned above. This must be done on your firewall. Below is an example for Linux iptables:

iptables -A FORWARD -s 52.4.198.118/32 -p tcp --dport 5432 -j ACCEPT<br>

You may have to replace FORWARD with INPUT if your Linux box has a public IP and the Database runs on the Host itself.

Our server is now set up to accept requests from Databox's IP to our Database/ Table.

How to set up your SSL connection

It’s a good practice to secure the connection with an SSL certificate that is generated and installed on the server.

The PostgreSQL server can be configured with SSL enabled by setting the parameter SSL to on in postgresql.conf. The server will listen for both normal and SSL connections on the same TCP port, and will negotiate with any connecting client on whether to use SSL.

How to connect PostgreSQL to Databox

Once our Database is properly prepared, we'll connect it to Databox.

To connect PostgreSQL to Databox, go to Data Sources > + New connection. Type PostgreSQL in the search bar and click on the green Connect button.

This will open the Activation window, which is where you will be prompted to enter your connection data and authorize the Databox app.

If you have set up a SSL certificate, be sure to check the SSL checkbox and paste the certificates in the corresponding fields. SSL CA is not mandatory. If you're using a self-signed certificate, be sure to leave the SSL checkbox un-checked or the connection will fail.

Click the green Activate button to complete the connection process. If PostgreSQL was successfully connected, the Activation window will close shortly and you'll see a Connected notice.

If you see a notice that says Wrong Credentials, doublecheck your user data before re-entering them in the Activation window.

If the Activation window doesn't close for over a minute, there may be difficulty connecting to your Database Host due to firewall, server, or networking issues.

If you have difficulty connecting, be sure the IP or hostname entered is publicly available or access is granted to Databox's public IP (52.4.198.118).

Pro Tip: A connection error can occur if you use some special characters in your PostgreSQL password. Learn more here

How to use the Metric Builder for PostgreSQL

The Metric Builder for PostgreSQL allows you to create Custom Metrics using data from your Database to use in Databoards, Alerts, Goals, etc. Learn more about the Metric Builder for PostgreSQL here.

Additional Information

Make sure your Firewall is open to Databox's public IP and your Port is correctly forwarded to the server
Query results are limited to 10 MB. If more than 10 MB of data is returned in a response to a query, an error message will be displayed in Databox. Please edit your query to successfully sync the data.
The query must contain a column named "Date." If you need to, use AS in your SELECT statement to satisfy this requirement
One column should always contain the Metric Value
"Null" values will not be recognized. Use a COALESCE function when needed. Learn more here.
A connection error can occur if you use some special characters in your PostgreSQL password. We advise you to not use special characters from the list below, as it may cause encoding issues during the connection of the data source. Please check if the following are included in your PostgreSQL password and replace them with other characters:
- , ' " / \ and spaces"