Enterprise feature. Contact us to learn more.

Using the PostgreSQL data connector, connect and query against PostgreSQL/PostGIS databases.

For more information, please refer to the data connector user guide.

Configuring a PostgreSQL Connector

When adding a PostgreSQL Data Connector you are presented with a form allowing you to configure your connector. To correctly populate this form, you may need to retrieve connection information from your remote database hosting provider.

PostgreSQL connector fields:

Field	Description
Name	An arbitrary name for your data connector.
Description	(Optional) An arbitrary description for your data connector.
Host Server	The server host name/address. You can retrieve the host server name/address from your hosting provider.
Port	The port on which your database is listening for requests. You can retrieve the port from your hosting provider. (The default PostgreSQL port is 5432.)
Database name	The name of the database to connect to.
User name	The login name for the Postgres user.
Password	The password for the Postgres user.
Accept self-signed certificate	Enable to force acceptance of a self-signed SSL certificate. (Not recommended for secure connections.)
Server Public Key	Postgres server public key/certificate for SSL connection.

Once all required fields are correctly populated, click Confirm to create a new connector.

SSL Connection

With SSL enabled by default, the server public key is needed to verify the connection between Studio and the expected Postgres server. This creates a secure, encrypted connection to the specified database protected against network eavesdropping, while also providing protection against impersonation (also known as Man in the Middle) attacks.

The server public key should be readily available and easily accessible, but how to access differs across hosts.

Self-Signed SSL Certificates

Studio allows for connecting to Postgres instances that are using a self-signed certificate for SSL encryption. This option provides additional convenience if the SSL certificate for your Postgres instance is not CA signed.

Enable the Accept self-signed certificate option to accept a self-signed certificate in lieu of providing Studio a public certificate/key.

Caution: We recommend that you do not rely on using self-signed certificates for connections to confidential data.

Find Public Key Hosted on Crunchy Data

Retrieve Key via Crunchy Dashboard

You can access the public key for your Crunchy Data Postgres host from the Crunchy Dashboard:

1. Log in to your Crunchy Data dashboard.

2. Use the drop-down menu in the upper left corner to select the desired team.

3. From the dashboard, select Settings >> Certificate.

4. Click the Download Certificate button to download the public key for your Postgres server.

The download certificate page on the Crunchy Dashboard.

Retrieve Key via Crunchy CLI

You can also access the public key for your Crunchy Data Postgres server using the Crunchy REST API or the Crunchy CLI.

Follow these instructions to access the public key via the Crunchy CLI:

1. Install the Crunchy CLI. Crunchy installation documentation.

2. Use the following command to log in with CLI:
cb login <api-secret-key>

You can verify that the CLI is connected to your Crunchy account with cb whoami

3. To access the public key for your database, execute the following command:
cb teamcert <team-id> > /path/to/save/file/cb.pem

Note: This command requires your team ID.

Geospatial Queries

In the current release, users must cast the geometry column to GeoJSON or WKT to properly complete a geospatial query. Geometry columns in a projection other than EPSG 4326 must be projected to it to be understood in Studio. Users can use ST_Transform to reproject a column.

Examples (where geom is the name of the geometry column):

Casting to GeoJSON:

SELECT ST_AsGeoJSON(geom) from public.earthquakes;

Reprojecting to EPSG 4326 and casting to GeoJSON:

SELECT ST_AsGeoJSON(ST_Transform(geom, 4326)) from public.earthquakes;

Casting to WKT:

SELECT ST_AsText(geom) from public.earthquakes;

Error Responses

This section provides reference information for Studio's PostgreSQL Connector errors.

Note: If you are getting an error response not listed below, feel free to reach out in the Studio Community Slack or contact us directly via email.

Error Response	Description
`Error: self signed certificate in certificate chain`	This error indicates that your database is using a non-trusted self-signed certificate. To fix your connection, edit the connection configuration and either select the `Accept self-signed certificates` checkbox, or provide the public key from your database server.
`Error: The server does not support SSL connections`	This error indicates that your database does not support encrypted SSL connections. For your security, we don't allow unencrypted connections to be established with your database. To address this, enable SSL connection on database. Once enabled, fix your data connection in Studio by editing the connection configuration and specifying the public key from your database server.