Connect to PostgreSQL
You can connect to your PostgreSQL database and make it available as a resource in Retool. Once complete, your users can write queries that interact with PostgreSQL data.
Requirements
All users for Retool organizations on Free or Team plans have global Edit permissions and can add, edit, and remove resources. If your organization manages user permissions for resources, you must be a member of a group with Edit all permissions.
To create a PostgreSQL resource, you need:
- Authentication credentials for the PostgreSQL database. This can be either a username and password, or AWS IAM authentication (for AWS-hosted PostgreSQL databases).
- Connection details for the PostgreSQL database host. This includes the hostname, port, and database name.
- Firewall rules that allow Retool to access your PostgreSQL host.
PostgreSQL authentication
Retool authenticates with PostgreSQL databases using either a username and password, or with AWS IAM credentials. The authentication method you use determines the configuration settings required.
Username and password
You can provide a username and password for Retool to authenticate with your PostgreSQL host. It is strongly recommended that you create a username and password for this purpose and not reuse an existing user. This provides more granular control over PostgreSQL users and allows you to control the scope of permissions.
AWS IAM
For AWS-hosted PostgreSQL databases, Retool supports IAM authentication that enables authentication using access keys. Use the AWS IAM console to create and manage AWS IAM keys.
SSL connections
Both authentication methods use SSL by default. If required by your database host, you can provide CA certificate, client key, and client certificate credentials that allow Retool to connect using SSL.
Retool automatically configures the necessary SSL credentials and certificates for AWS-hosted databases.
Allow Retool to access the PostgreSQL database
If your PostgreSQL database is behind a firewall, you must allow access from Retool's IP addresses. Add the IP addresses to your firewall's Allowlist before you create the resource.
1. Create a PostgreSQL resource
Retool can connect to almost any API or database, and has built-in integrations for popular data sources. Sign in to your Retool organization and create a new PostgreSQL resource from the Resources tab.
2. Configure the resource
Retool can automatically populate values from a PostgreSQL connection string.
Specify a name and location for the PostgreSQL resource. Retool displays the resource name and type in query editors to help users identify them.
General
| Setting | Description |
|---|---|
| Host | The URL or IP address. |
| Port | The port number. |
| Database name | The name of the database. |
| Connection options | Additional connection options to use with Retool. |
You can also click Import from connection string and enter a database connection string to automatically populate these settings.
Authentication
Retool can connect to a PostgreSQL database using either user and password, or AWS IAM authentication.
User and password authentication
Select User and Password as the authentication method, then provide the following information.
| Setting | Description |
|---|---|
| Database username | The username. |
| Database password | The password. |
AWS IAM authentication
Select AWS IAM Authentication as the authentication method, then provide the following information.
| Setting | Description |
|---|---|
| Database username | The username. |
| AWS Region | The AWS region in which the database is located. |
| Role to assume (ARN) | The role for the IAM user to assume. |
| AWS Access Key ID | The access key for the IAM user. |
| AWS Secret Key | The secret key for the IAM user. |
SSL connections
Retool strongly recommends using SSL and not to skip TLS certificate validation. This ensures the connection is secure and prevents attackers from using invalid server certificates to gain access to your data.
Retool supports SSL connections for both authentication methods using custom certificate credentials. If you're using username and password authentication, check Connect using SSL to display additional fields for this information. SSL is required for AWS IAM authentication and cannot be disabled.
Provide the following information to configure SSL:
| Setting | Description |
|---|---|
| CA Cert | The CA certificate. |
| Client Key | The client key. |
| Client Cert | The client certificate. |
Click Test Connection to verify that Retool can connect to PostgreSQL. If the test fails, check the resource settings and try again. Testing a connection only checks whether Retool can successfully connect to the resource. It cannot check whether the provided credentials have sufficient privileges or can perform every supported action.
3. Save the resource
Click Create resource to complete the setup. You can then click either Create app to immediately start building a Retool app or Back to resources to return to the list of resources.
Wrap up
Your PostgreSQL resource is now ready to use. To interact with PostgreSQL data, select the resource in the query editor. You can read data using SQL queries, write data using GUI queries (if enabled), and use the schema browser to search tables or columns.