PostgreSQL

Prerequisites

Whitelist CData IPs

To establish a connection to PostgreSQL, you need to allow access to PostgreSQL via CData’s IP. When hosting PostgreSQL behind a firewall, you must safelist these IP addresses in your firewall.

• Range: 52.224.0.160 to 52.224.0.175 and 4.154.117.160 to 4.154.117.175.
• CIDR notation: 52.224.0.160/28 and 4.154.117.160/28

Ensure PostgreSQL Is Publicly Accessible

Provide a public facing IP/domain to connect to this data source. The following private IP ranges do not work:

• 10.0.0.0 to 10.255.255.255
• 172.16.0.0 to 172.31.255.255
• 192.168.0.0 to 192.168.255.255
• 127.0.0.1 (aka ‘localhost’)

Setup Guide

Follow these steps to connect PostgreSQL to your Connect AI account:

1. Open the Connections page of the Connect AI dashboard.

2. Click + Add Connection in the upper-right corner.

3. Type PostgreSQL into the search field, then click the data source name.

4. On the Basic Settings tab of the new connection, enter a connection name or keep the default name.

5. If your PostgreSQL connection is behind a firewall, set the Connection Type to SSH Tunnel and follow the instructions in SSH Tunnel Instructions. Then continue with these instructions. Otherwise, leave the Connection Type as Direct.

6. In the Server field, enter the host name or IP address of the PostgreSQL server.

7. Note: If your PostgreSQL database has a self-signed certificate, you must copy the self-signed certificate into the Advanced Settings > SSL Server Cert field.

8. Select the Authentication method, then proceed to the relevant section and follow those instructions.

Authentication Methods

Password

1. In the User field, enter the PostgreSQL username for authentication.

2. Enter the user password in the Password field.

3. (Optional) Enter the name of the PostgreSQL Database. If none is entered, the user’s default database is used.

4. (Optional) Specify the port for connecting to the PostgreSQL server in the Port field. This is set to 5432 by default.

5. In PostgreSQL, add the Connect AI static IP addresses to your connection whitelist.

6. At the top of the Connect AI Add PostgreSQL Connection page, click Save & Test.

   • If the connection test succeeds, a Connection successfully saved message appears, indicating that your connection has been created. The Status on the Edit Connection page also changes to Authenticated. View the data model of your successful connection in the right pane of the Edit Connection page, in the Data Model tab.
   • If the connection test fails, ensure that you entered your login information correctly with no stray spaces or other characters. Connect AI displays error messages under the required fields with missing data. Some data sources require that you sign in directly to the source website. If you did not, an error message appears under the Sign in button. Correct the errors and try again.
   • Unsuccessful connections are saved as drafts and have a Status of Not Authenticated. You can return to the connection and authenticate it later.

AzureAD

1. In the User field, enter the PostgreSQL username for authentication.

2. Enter the Azure Tenant Id to connect to.

3. (Optional) Enter the name of the PostgreSQL Database. If none is entered, the user’s default database is used.

4. (Optional) Specify the port for connecting to the PostgreSQL server in the Port field. This is set to 5432 by default.

5. Click Sign in to connect securely through OAuth. This action opens the PostgreSQL sign-in page in a new tab.

6. Log in to your PostgreSQL account and provide the requested permissions (if applicable).

7. In PostgreSQL, add the Connect AI static IP addresses to your connection whitelist.

8. At the top of the Connect AI Add PostgreSQL Connection page, click Save & Test.

   • If the connection test succeeds, a Connection successfully saved message appears, indicating that your connection has been created. The Status on the Edit Connection page also changes to Authenticated. View the data model of your successful connection in the right pane of the Edit Connection page, in the Data Model tab.
   • If the connection test fails, ensure that you entered your login information correctly with no stray spaces or other characters. Connect AI displays error messages under the required fields with missing data. Some data sources require that you sign in directly to the source website. If you did not, an error message appears under the Sign in button. Correct the errors and try again.
   • Unsuccessful connections are saved as drafts and have a Status of Not Authenticated. You can return to the connection and authenticate it later.

AzurePassword

1. In the User field, enter the PostgreSQL username for authentication.

2. Enter the user password in the Password field.

3. Enter the Azure Tenant Id to connect to.

4. (Optional) Enter the name of the PostgreSQL Database. If none is entered, the user’s default database is used.

5. (Optional) Specify the port for connecting to the PostgreSQL server in the Port field. This is set to 5432 by default.

6. In PostgreSQL, add the Connect AI static IP addresses to your connection whitelist.

7. At the top of the Connect AI Add PostgreSQL Connection page, click Save & Test.

   • If the connection test succeeds, a Connection successfully saved message appears, indicating that your connection has been created. The Status on the Edit Connection page also changes to Authenticated. View the data model of your successful connection in the right pane of the Edit Connection page, in the Data Model tab.
   • If the connection test fails, ensure that you entered your login information correctly with no stray spaces or other characters. Connect AI displays error messages under the required fields with missing data. Some data sources require that you sign in directly to the source website. If you did not, an error message appears under the Sign in button. Correct the errors and try again.
   • Unsuccessful connections are saved as drafts and have a Status of Not Authenticated. You can return to the connection and authenticate it later.

AWSIAMRoles

1. In the User field, enter the PostgreSQL username for authentication.

2. Enter the AWS Access Key associated with the AWS root account.

3. Enter the AWS Secret Key associated with the AWS root account.

4. Enter the AWS Role ARN for the authenticating User Id.

5. (Optional) Enter the AWS External Id only if emulating a different role.

6. (Optional) Enter the name of the PostgreSQL Database. If none is entered, the user’s default database is used.

7. (Optional) Specify the port for connecting to the PostgreSQL server in the Port field. This is set to 5432 by default.

8. In PostgreSQL, add the Connect AI static IP addresses to your connection whitelist.

9. At the top of the Connect AI Add PostgreSQL Connection page, click Save & Test.

   • If the connection test succeeds, a Connection successfully saved message appears, indicating that your connection has been created. The Status on the Edit Connection page also changes to Authenticated. View the data model of your successful connection in the right pane of the Edit Connection page, in the Data Model tab.
   • If the connection test fails, ensure that you entered your login information correctly with no stray spaces or other characters. Connect AI displays error messages under the required fields with missing data. Some data sources require that you sign in directly to the source website. If you did not, an error message appears under the Sign in button. Correct the errors and try again.
   • Unsuccessful connections are saved as drafts and have a Status of Not Authenticated. You can return to the connection and authenticate it later.

SSH Tunnel Instructions

If your PostgreSQL data source is behind a firewall, follow these instructions.

1. Click Connection Type of SSH Tunnel.

2. Enter the following information:

   • SSH Server–enter the name of the SSH server.
   • SSH Port–enter the SSH port. The default value is 22.
   • SSH Auth Mode–enter the authentication mode of Password or Public_Key.
     • If Password, enter the SSH User name and SSH Password.
     • If Public_Key, enter the SSH User name, SSH Client Cert, and SSH Client Cert Password (optional). Only PEMKEY_BLOB is available as the SSH Client Cert Type. The SSH Client Cert is required, and is a valid private key. Enter the SSH Client Cert Password if the SSH client certificate has a password.
3. Continue with the authentication instructions in the Setup Guide.

More Information

For more information about interactions between Connect AI and PostgreSQL, see this information page.