Configure a PostgreSQL connection

A connection is an authentication mechanism to allow Totango to integrate with another system. Each connector has different requirements for connection. You can reuse connections to support multiple integration jobs.

Data warehouse integrations are a closed system; you cannot reuse data warehouse connections for other integration families.

A PostgreSQL connector is a pure database connection; it is built as a connector and an SQL query powers the connection.

1. Create a PostgreSQL user

We recommend that you create a specific user for fetching data with proper database access permissions (e.g. read-only access to a specific schema or a table).

Connect to your PostgreSQL database.

Create a read-only user for a particular table/tables:

CREATE ROLE 'totango_ro_user' WITH LOGIN PASSWORD password NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL ‘infinity’;

Give the user access to the database from any host:
```
GRANT SELECT ON table_name.* TO 'totango_ro_user';
```
If you know the hostname or IP address of the host that the collector is will be installed on, type the following command:
```
GRANT SELECT ON table_name.* to 'totango_ro_user'@'hostname or IP_address';
```

2. Create a PostgreSQL connection in Totango

In the event that your network is behind a firewall, you will need to whitelist our servers so that we may retrieve information from your data warehouse.

In case your company uses VPN to allow access to your PostgreSQL database, the Totango system will not be able to access it. If you want the Totango system to retrieve data from a PostgreSQL database, please create an accessible PostgreSQL database outside your network and push only the relevant information to this PostgreSQL database in a secure manner.

In Totango, click Settings from the left nav.
Expand Data Management > Customer Data Hub.
From the list of active connectors (left), choose Data Warehouses.
From the list of available connectors, hover over PostgreSQL, and click Connect (or View Integrations).
Enter a name and description for the connection. Because you can have multiple connections with a connector (using different authentication profiles), a name and description helps you identify the connection later.
Add the remaining details for the connection:
- Host name: Host name of your PostgreSQL instance
- Port: Port of the PostgreSQL instance
- Username: Username of the user you plan to give access to your database for Totango
- Password: Password of the user
- Schema: Schema where this connector data should be fetched from
- Database: Database where this connector data should be fetched from
- Create SSH Tunnel: Learn more
Click Connect.

You can now set up an integration using the connection.

All data warehouse connectors support SSL encryption. By default, Totango uses SSL encryption during connecting. If the data warehouse server does not support SSL encryption, the connection will use an unencrypted connection to stream data from the data warehouse.