PostgreSQL
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 Form](https://files.readme.io/995f579-connector-form.png)
PostgreSQL Connector Form
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.
- Find the public key/certificate for an Amazon AWS RDS hosted database
- Find the public key/certificate for an Azure hosted database
- Find the public key/certificate for a Google Cloud SQL hosted database
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.](https://files.readme.io/2eabdde-crunchy-public-key-download.png)
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. |
Updated 9 months ago