How To Recover PostgreSQL Password on Mounted Disk

Use Case

During a fresh installation of Terraform Enterprise it may be necessary to manually connect to the PostgreSQL database within its own container to reset the password to a known value under the following circumstances:

1. TFE version v202010-1 or later
2. db_pw_migrate migration on Mounted Disk mode
3. The password hasn’t been saved to the TFE configuration

Recovery when the original source instance of the mounted disk contents is still available

• A replicatedctl app-config export --hidden will output a JSON document with the generated PostgreSQL password in generated_postgres_password.
• Set the pg_password in the app config of the new instance to this value and restart.

Recovery when the original source instance of the mounted disk contents is not available

In this case, the password is completely unknown. The PostgreSQL container will have to be connected to directly in order to set the password manually.

Please be aware of the following caveats before continuing.

• Do not access the application console unless directed by HashiCorp Support.
• Only run the commands provided.
• If any command returns unexpected output, do not proceed further, as this process is potentially dangerous.

Process

• Stop the TFE application, but keep Replicated running
• replicatedctl app-config export --hidden will get all the configuration options, find generated_postgres_password from the output.
• Locate the mounted disk path on the host, this will be referred to as ${DISK_PATH}.
• Find the PostgreSQL Docker image ID using docker image ls | grep 'ptfe-postgres'. The below output is an example, it may vary slightly:

# docker image ls | grep 'ptfe-postgres'
10.0.31.249:9874/hashicorp-ptfe-postgres                                                      a6f7616                 ecec249ca541        5 weeks ago         214MB
registry.replicated.com/terraformenterprise/wirqzhu7kinrk.hashicorp-ptfe-postgres             a6f7616                 ecec249ca541        5 weeks ago         214MB

• The image ID will be referred to as ${DOCKER_IMAGE_ID} which is ecec249ca541 in the output above.
• Start a Docker container from this image, mounting the correct volumes and configuring the correct environment variables. Use the following command: docker run -it --user postgres --env PGDATA=/data/pgdata --entrypoint /bin/bash -v ${DISK_PATH}/postgres:/data -v ${DISK_PATH}/postgres-backup:/backup ${DOCKER_IMAGE_ID}. This will start an interactive shell
• From within the container, start PostgreSQL in the background using postgres &.
• Still within the container, connect to the database using psql -U hashicorp -H 127.0.0.1 -d hashicorp.
• While connected to the database, run ALTER ROLE hashicorp WITH PASSWORD 'ABC123'; to set the password to ABC123. Replace ABC123 with the value that was retrieved earlier with generated_postgres_password.
• Run \q to disconnect from the database.
• exit to exit the container
• The last step is to run replicatedctl app start, which will start the TFE application.