PostgreSQL automatic secret rotation

PostgreSQL is a popular open-source RDBMS.

HCP Vault Secrets can automatically rotate PostgreSQL user passwords.

PostgreSQL clusters should not be exposed to the internet. Use an integration with a gateway pool to configure HCP Vault Secrets to rotate credentials in the cluster.

Prerequisites

• HCP Vault Secrets standard or plus tier
• hcp binary >= 0.8.0
• Gateway pool created and gateway running somewhere that it can connect to the PostgreSQL cluster
• Environment variable POSTGRES_URL set to a PostgreSQL connection URL that uses a role with permission to modify roles (CREATEROLE)
• Two PostgreSQL users that already exist, whose passwords should be modified on an alternating schedule
• An application in which to create the rotating secret. Here we assume it is named my-app.

Create a PostgreSQL integration

1. Fetch the details of your gateway pool.

   $ hcp vault-secrets gateway-pools read --format=json my-gateway-name > gatewayResponse.json
   

2. Create a config file for the integration.

   $ cat - > integration.hcl <<EOF
   type = "postgres"
   details = {
     static_credential_details = {
       connection_string = "$POSTGRES_URL"
     }
     capabilities = ["ROTATION"]
     gateway_pool_id = "$(jq -r .gateway_pool.resource_id < gatewayResponse.json)"
   }
   EOF
   

3. Create the integration:

   $ hcp vault-secrets integrations create --config-file=integration.hcl my-postgres-integration
   

Create a PostgreSQL rotating secret

Here we assume that the PostgreSQL usernames to rotate are user1 and user2.

1. Create a config file for the rotating secret.

   $ cat - > secret.hcl <<EOF
   type = "postgres"
   details = {
     integration_name = "my-postgres-integration"
     rotation_policy_name = "built-in:30-days-2-active"
     postgres_params = {
       usernames = ["user1", "user2"]
     }
   }
   EOF
   

2. Create the rotating secret.

   $ hcp vault-secrets secrets create --app my-app my-postgres-rotating-secret --secret-type=rotating --data-file=secret.hcl
   

The rotating secret named my-postgres-rotating-secret should now be present in the app my-app, and the first user's password will be rotated imminently. The next user's password will be rotated in 30 days.

Once a password gets rotated, a new version of my-postgres-rotating-secret will be created, containing the username and new password.

Delete a PostgreSQL rotation integration

1. Delete an existing PostgreSQL rotation integration.

   $ hcp vault-secrets integrations delete --type postgres my-postgres-integration
   

Additional resources

• HCP Vault Secrets quickstart tutorial