Connect to an external PostgreSQL database

This topic describes how to configure Terraform Enterprise to connect to an external PostgreSQL database. You only need to complete this task under the following conditions:

You want to operate Terraform Enterprise in active-active or external mode, which instructs Terraform Enterprise to store and retrieve data in an externally-managed database. Refer to Configure operational mode for additional information.
You want to connect to a single PostgreSQL node. To connect Terraform Enterprise to an HA PostgreSQL database cluster, refer to Connect to a highly-available PostgreSQL cluster.

Overview

Complete the following steps to connect Terraform Enterprise to a single PostgreSQL node:

Create extensions for the database schemas.
Specify the connection settings in your deployment configuration. If your server requires additional connection parameters, you must also specify them in the configuration.

Requirements

Before proceeding, verify that you meet the following requirements.

Server version

You can connect to the following versions of many PostgreSQL server distributions:

13.x
14.4 and newer
15.x
16.x
17.x

Server distribution

You can connect to several PostgreSQL database distributions. We have tested and verified Terraform Enterprise with the following services:

Amazon RDS for PostgreSQL
Amazon Aurora PostgreSQL
EnterpriseDB Postgres Advanced Server 16 and 17

User

Create a PostgreSQL user with the following permissions on the database:

Permissions to create, modify, and read all tables and indices on all schemas within the database. Database owners commonly have these permissions.
Permissions to create extensions. If you are unable to create a user with the CREATE EXTENSION privilege, refer to Create extensions for instructions on creating the necessary extensions.
The rails, vault, registry, task_worker, and terraform_enterprise PostgreSQL schemas are required. We recommend allowing the PostgreSQL user to create schemas so that Terraform Enterprise can create them automatically. If the PostgreSQL user does not have permissions to create schemas, they must be created prior to installation.
Note
Note that if you are changing the database user, ensure the new user is the owner of all enum types in the database to avoid failures for any potential migrations that attempt to `ALTER TYPE`.

AWS IAM requirements

You must meet the following requirements to use AWS IAM for authentication with AWS RDS Database for PostgreSQL:

The IAM role must be authorized role must be able to access the database from the virtual machine.
The mapped database role must have required permissions on the database.

Azure Managed Identity requirements

Use Azure Managed Identity to authenticate with Azure Database for PostgreSQL. Here are a few requirements:

The managed identity must be able to access the database from the virtual machine.
The managed identity must have the required permissions on the database.

Note

Terraform Enterprise does not support workload identities for authentication.

Google Default Credentials requirements

You must meet the following requirements to use Google default credentials for authentication with SQL Database for PostgreSQL:

The service account user must be able to access the database from the virtual machine.
The service account user must have the required permissions on the database.

Create extensions

Create extensions for the rails, vault, registry, and task_worker PostgreSQL schemas. The database server automatically creates these schemas if they do not already exist.

Run the following commands on the PostgreSQL server to create the extensions:

CREATE EXTENSION IF NOT EXISTS "hstore" WITH SCHEMA "rails";
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA "rails";
CREATE EXTENSION IF NOT EXISTS "citext" WITH SCHEMA "registry";

Specify PostgreSQL connection settings

Add the following settings to your Terraform Enterprise configuration:

Set the TFE_DATABASE_HOST variable to the location of your PostgreSQL server. Format the location as HOST[:PORT], for example db.example.com or db.example.com:5432. Multi host connection strings are not supported.
Set the TFE_DATABASE_NAME variable to the name of the database you want to store the data in.
Set the TFE_DATABASE_USER variable to the user name you want to use to access the database.
Set the TFE_DATABASE_PASSWORD variable to the password for the user.

Refer to Database settings in the configuration reference for additional information.

Additional connection parameters

Add the TFE_DATABASE_PARAMETERS variable to your configuration and specify any additional connection parameters necessary to connect to the server.

`sslmode`

When providing extra keyword parameters for the database connection, the sslmode parameter only allows the following values:

When operating Terraform Enterprise in external mode, the sslmode parameter is set to require by default. When operating Terraform Enterprise in disk mode, the sslmode parameter is set to disable by default.

Terraform Enterprise provides a certificates file at /etc/ssl/private/terraform-enterprise/bundle.pem, which is required by the verify-full and verify-ca modes.

Refer to the PostgreSQL library documentation for additional information about using sslmode.

Configure authentication settings

You can use the following authentication methods so that Terraform Enterprise can communicate with the database.

Username and password authentication

You can configure Terraform Enterprise to use the database's user and password to authenticate with the server. Set the TFE_DATABASE_USER and TFE_DATABASE_PASSWORD variable for username and password based authentication.

Client certificate authentication

You can configure Terraform Enterprise to use PostgreSQL client certificates to authenticate with the server. When providing client certificates, the database password is not required and the sslmode parameter is set to verify-full. Terraform Enterprise requires PostgreSQL server certificate to use Subject Alternative Names (SANs) rather than relying solely on the legacy Common Name field.

Configure the following settings to use PostgreSQL client certificates:

Set the TFE_DATABASE_USE_MTLS variable to true.
Specify the path to the CA certificate file in the TFE_DATABASE_CA_CERT_FILE variable.
Specify the path to the client certificate file in the TFE_DATABASE_CLIENT_CERT_FILE variable.
Specify the path to the client key in the TFE_DATABASE_CLIENT_KEY_FILE variable.

AWS IAM authentication

You can use AWS Identity and Access Management (IAM) to authenticate to Amazon RDS for PostgreSQL.

Refer to the AWS documentation for detailed guidance on IAM database authentication. When using IAM authentication, a database password is not required. Instead, clients generate temporary authentication tokens signed with AWS credentials.

Ensure that you meet the requirements.

Configure the following settings to use AWS IAM:

Set the TFE_DATABASE_PASSWORDLESS_AWS_USE_INSTANCE_PROFILE variable to true.
Set the TFE_DATABASE_PASSWORDLESS_AWS_REGION to the AWS region of the RDS resource.

Ensure that the IAM role has the required permissions on the database. If you migrate from password-based authentication to IAM, the previous user may own the database and schemas. Reassign ownership or grant the necessary permissions to the IAM-authenticated database user.

Azure MSI authentication

You can use both system-assigned and user-assigned managed identities to authenticate to Azure Database for PostgreSQL flexible server.

Refer to the Azure documentation for more information. When using Azure MSI authentication, the database password is not required.

Ensure that you meet the requirements.

Configure the following settings to use Azure MSI:

Set the TFE_DATABASE_PASSWORDLESS_AZURE_USE_MSI variable to true.
Set the TFE_DATABASE_PASSWORDLESS_AZURE_CLIENT_ID to the client ID of the user-assigned managed identity. Leave it blank to use the system-assigned managed identity.
Set the TFE_DATABASE_USER to the managed identity name.

Ensure that the managed identity has the required permissions on the database. If you migrate from password-based authentication to MSI, the previous user may own the database and schemas. Reassign ownership or grant the necessary permissions to the managed identity user.

Google Default Credentials

Refer to the Google documentation for more information. The scope of the service account needs to be set to Cloud SQL Admin API. When using Google Default Credentials, the database password is not required.

Ensure that you meet the requirements.

Configure the following settings to use Google Default Credentials:

Set the TFE_DATABASE_PASSWORDLESS_GOOGLE_USE_DEFAULT_CREDENTIALS variable to true.
Set the TFE_DATABASE_USER to the username portion of the service account email (the part before .gserviceaccount.com).

Ensure that the service account has the required permissions on the database. If you migrate from password-based authentication to Google Default Credentials, the previous user may own the database and schemas. Reassign ownership or grant the necessary permissions to the service account.