PostgreSQL configuration for Vault backend storage

The PostgreSQL storage backend is used to persist Vault's data in a PostgreSQL server or cluster.

• High Availability – the PostgreSQL storage backend supports high availability. Requires PostgreSQL 9.5 or later.

• Community Supported – the PostgreSQL storage backend is supported by the community. While it has undergone review by HashiCorp employees, they may not be as knowledgeable about the technology. If you encounter problems with them, you may be referred to the original author.

storage "postgresql" {
  connection_url = "postgres://user123:secret123!@localhost:5432/vault"
}

The PostgreSQL storage backend does not automatically create the table. Here is some sample SQL to create the schema and indexes.

CREATE TABLE vault_kv_store (
  parent_path TEXT COLLATE "C" NOT NULL,
  path        TEXT COLLATE "C",
  key         TEXT COLLATE "C",
  value       BYTEA,
  CONSTRAINT pkey PRIMARY KEY (path, key)
);

CREATE INDEX parent_path_idx ON vault_kv_store (parent_path);

Store for HAEnabled backend

CREATE TABLE vault_ha_locks (
  ha_key                                      TEXT COLLATE "C" NOT NULL,
  ha_identity                                 TEXT COLLATE "C" NOT NULL,
  ha_value                                    TEXT COLLATE "C",
  valid_until                                 TIMESTAMP WITH TIME ZONE NOT NULL,
  CONSTRAINT ha_key PRIMARY KEY (ha_key)
);

If you're using a version of PostgreSQL prior to 9.5, create the following function:

CREATE FUNCTION vault_kv_put(_parent_path TEXT, _path TEXT, _key TEXT, _value BYTEA) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE vault_kv_store
          SET (parent_path, path, key, value) = (_parent_path, _path, _key, _value)
          WHERE _path = path AND key = _key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO vault_kv_store (parent_path, path, key, value)
              VALUES (_parent_path, _path, _key, _value);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- Do nothing, and loop to try the UPDATE again.
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

postgresql parameters

• connection_url (string: <required>) – Specifies the connection string to use to authenticate and connect to PostgreSQL. The connection URL can also be set using the VAULT_PG_CONNECTION_URL environment variable. A full list of supported parameters can be found in the pgx library and PostgreSQL connection string documentation. For example connection string URLs, see the examples section below.

• table (string: "vault_kv_store") – Specifies the name of the table in which to write Vault data. This table must already exist (Vault will not attempt to create it).

• max_idle_connections (int) - Default not set. Sets the maximum number of connections in the idle connection pool. See golang docs on SetMaxIdleConns for more information. Requires 1.2 or later.

• max_parallel (string: "128") – Specifies the maximum number of concurrent requests to PostgreSQL.

• ha_enabled (string: "true|false") – Default not enabled, requires 9.5 or later.

• ha_table (string: "vault_ha_locks") – Specifies the name of the table to use for storing high availability information. This table must already exist (Vault will not attempt to create it).

• auth_mode (string: "standard|aws_iam|azure|gcp_iam") - Defaults to standard auth. Vault accepts azure_msi as an alias for azure. Refer to the cloud authentication section for mode details.

• aws_db_region (string: "") - Specifies the AWS region where DB is situated. Required when auth_mode is set to aws_iam

• azure_client_id (string: "") - Client ID of a user-assigned managed identity in Azure. Vault uses the system-assigned managed identity if you do not set azure_client_id and auth_mode is set to azure. The azure_client_id parameter applies only to managed identity. Workload identity reads the client ID from the AZURE_CLIENT_ID environment variable.

postgresql examples

Custom SSL verification

This example shows connecting to a PostgreSQL cluster using full SSL verification (recommended).

storage "postgresql" {
  connection_url = "postgres://user:pass@localhost:5432/database?sslmode=verify-full"
}

To disable SSL verification (not recommended), replace verify-full with disable:

storage "postgresql" {
  connection_url = "postgres://user:pass@localhost:5432/database?sslmode=disable"
}

Authentication in cloud via cloud identities

Vault can authenticate to cloud-hosted PostgreSQL instances using the cloud provider's native identity mechanisms. When you set the auth_mode parameter, Vault resolves credentials from the runtime environment, such as Identity and Access Management (IAM) credentials, managed identities, or workload identity federation. section for your cloud provider for the credential sources Vault checks and the order it checks them in.

AWS IAM

You can authenticate to your DB instance using AWS Identity and Access Management (IAM) database authentication. Vault uses AWS default credentials for authentication, which means it automatically picks up IAM roles, instance profiles, or workload identity (such as Amazon Elastic Kubernetes Service (EKS) Pod Identity or IAM Roles for Service Accounts) depending on your environment configuration. For more information on IAM database authentication, see the AWS documentation.

storage "postgresql" {
  connection_url  = "postgres://iam_user@db.xx.us-west-2.rds.amazonaws.com:5432/database?sslmode=require"
  auth_mode       = "aws_iam"
  aws_db_region   = "us-west-2"
}

Azure

You can use workload identity federation (such as on AKS), or system-assigned and user-assigned managed identities, to authenticate to Azure Database for PostgreSQL flexible server. Vault checks for credentials in the following order:

1. Workload identity: Vault uses workload identity when the AZURE_FEDERATED_TOKEN_FILE, AZURE_CLIENT_ID, and AZURE_TENANT_ID environment variables are present, for example, in an AKS pod with workload identity enabled.
2. Managed identity: Vault uses the user-assigned managed identity set by azure_client_id, or the system-assigned managed identity if you do not set azure_client_id.

If your environment provides a workload identity, Vault uses it even when a managed identity is also available. Grant the PostgreSQL role to the identity Vault resolves first. For more information on managed identity authentication, see the Azure documentation.

storage "postgresql" {
  connection_url  = "postgres://msi_user@server.postgres.database.azure.com:5432/database?sslmode=require"
  auth_mode       = "azure"
}

GCP IAM

You can use Google Cloud Identity and Access Management (IAM) to authenticate with Cloud SQL resources. Vault uses Application Default Credentials for authentication, which means it automatically picks up service account keys, attached service accounts, or workload identity federation (such as on GKE) depending on your environment configuration. For more information on IAM authentication, see the Google documentation.

storage "postgresql" {
  connection_url    = "postgres://iam-sa@xxxx.iam@203.0.113.0:5432/database?sslmode=require"
  auth_mode         = "gcp_iam"
}