Connect a database to GPTree

You can connect a database to GPTree and ask questions about its numbers in ordinary words. GPTree reads the database's structure (its tables, views and columns), prepares a read-only query when a question asks for a number, and runs it only after someone approves it. GPTree never changes data in the database.

Connecting a database and asking it questions are included on the Pro and Team plans.

Before you start

You need three things.

• A supported database. PostgreSQL, MySQL or MariaDB. No others can be connected.
• A database user that can only read. GPTree refuses to connect with a user that can change data, so ask whoever looks after the database for a read-only user made for this.
• A decision about what that user can see. Everyone who can use a connection sees what its database user can see. GPTree does not add row or column restrictions of its own. To limit what someone sees, connect with a database user or views made for them.

Open the form

1. Open Settings, then Knowledge sources.
2. In the "Databases" section, select "Connect a database".

Fill in the form

Field	What to enter
Name	Optional.
Engine	PostgreSQL, MySQL or MariaDB.
Host, Port, Database	Where the database is, and which one to read.
Schema	PostgreSQL only, and optional.
User, Password	The read-only database user.
Encryption	One of two choices, covered below.
What GPTree may read	One of three choices, covered below.
Time zone	The time zone GPTree uses to turn a period such as "last month" into exact dates.

If you administer a workspace, you also choose whether the connection is for just you or for the workspace.

The read-only user

GPTree only connects with a database user that can read and nothing else.

The detail: GPTree checks this every time it connects, not only at setup, so a user that is later given permission to change data is refused as well. On top of that, every query runs inside a read-only transaction, with a time limit the database server itself enforces, and is rolled back afterwards.

Encryption

The connection has to be encrypted. There are two choices:

• "Require encryption" is the default.
• "Require encryption and verify the server's certificate" also verifies the certificate. It needs the certificate authority pasted in PEM format.

With either choice, GPTree asks the server what encryption the session actually has, and refuses a connection that is not encrypted.

Using MySQL or MariaDB? Read this before you turn verification on. On MySQL and MariaDB, verification only succeeds if the server's certificate lists the server's IP address, because GPTree connects to the address it resolved rather than to the name. Many hosted MySQL services issue certificates for a hostname only. Those work with "Require encryption" and are refused with verification on. PostgreSQL does not have this limitation.

What GPTree may read

This setting decides what GPTree reads and who approves a query before it runs.

• "Views only" is the default. GPTree reads views. The person asking approves their own query.
• "Defined metrics only". GPTree answers only from metrics the organisation has defined and checked. A one-off query cannot run.
• "Tables". GPTree reads tables directly. An admin of the connection approves each query.

"Tables" comes with one more safeguard. If the server is the live database rather than a read replica, reading it is off until an admin turns on "Allow reading the live database" and confirms "I understand queries will run on our live database." While that is on, queries run under tighter limits and one at a time.

After you save

The connection shows "Checking the connection". During that time GPTree connects from a background worker, proves the user is read-only, proves the session is encrypted, and reads the structure.

Then it shows one of two things:

• "Connected", with the number of tables read.
• "Needs attention", with the reason in plain words.

GPTree re-reads the structure every hour.

If it says "Needs attention"

"Needs attention" always comes with the reason, written in plain words on the connection. For example:

This database user can change data. GPTree only connects with a user that can read and nothing else.

Fix the cause, in the database or with "Edit" if a detail in the form was wrong, then select "Check now".

Manage a connection

Each connection has these buttons:

• "Check now" has GPTree check the connection straight away.
• "Pause" puts the connection on hold, and "Resume" brings it back.
• "Edit" changes its details.
• "Disconnect" removes it for good.

Change the password or the certificate

The password is stored encrypted and is never shown again after saving. To replace it, select "Edit" and type the new one. Leaving the box blank keeps the current one. The same is true of the certificate.

Disconnect

"Disconnect" asks for confirmation. When you confirm, GPTree deletes the saved password, removes everything it read from that database (the structure and saved query results), and withdraws answers that relied on it. It cannot be undone.

Good to know

• SSH tunnels are not available.
• Once a database is connected, a question that asks for a number waits for approval before anything runs. See Approve a database query in GPTree.
• For what protects the database while a query runs, see How GPTree runs queries on your data safely.