PostgreSQL 12 Upgrade Error: database user hashicorp is not the install user

Introduction

Terraform Enterprise v202103-1 introduced logic to upgrade the internally-managed PostgreSQL data from PostgreSQL 9.5 to PostgreSQL 12. This change only affects proof of concept and mounted disk installations.

Problem

In certain Terraform Enterprise installations, particularly those that upgraded to v202005-1, the internally-managed PostgreSQL database may be installed as a user other than hashicorp. When that installation is upgraded to v202103-1, operators may see the following error:

Checking database user is the install user
database user "hashicorp" is not the install user
Failure, exiting

The following commands can be used to check the install user for an internally-managed PostgreSQL database.

For proof of concept installations:

docker exec -it ptfe_postgres psql -U hashicorp -c 'SELECT rolname, oid FROM pg_roles;'

For mounted disk installations:

docker exec -it ptfe_postgres_disk psql -U hashicorp -c 'SELECT rolname, oid FROM pg_roles;'

The user with an oid of 10 is the install user. If that user is not hashicorp, then continue to read this article to see how to resolve that.

This example output shows that the install user is postgres.

  rolname  |  oid
-----------+-------
 postgres  |    10
 hashicorp | 16385
(2 rows)

Cause

There are a few reasons why the install user for the internally-managed PostgreSQL database is something other than hashicorp.

• A manual change was executed that changed the install user to something other than hashicorp.
• An installation was upgraded to v202005-1 and remediation steps were taken that changed the install user to something other than hashicorp.

Solution

Before beginning, ensure that there is a recent backup of the Terraform Enterprise installation.

Ensure the Terraform Enterprise installation is running. This may involve restoring to the version of Terraform Enterprise that was running before the upgrade to v202103-1.

Ensure the Terraform Enterprise application is not being actively used. This is to prevent writes to the database while running through this process.

Retrieve the current PostgreSQL password for the Terraform Enterprise installation. Take note of its value as it will be needed later. The remainder of this article will use ${PG_PASSWORD} to refer to this value.

 

Method 1:

• Proof of Concept installation

docker inspect ptfe_postgres | grep POSTGRES_PASSWORD

• Mounted Disk installation

docker inspect ptfe_postgres_disk | grep POSTGRES_PASSWORD

 

Take note of the value of the POSTGRES_PASSWORD key

 

Method 2:

• The current version of TFE is v202010-1 and after
  • When pg_password is set

replicatedctl app-config export --hidden --template '{{.pg_password.Value}}'

• • When pg_password is not set, the above step returns empty value

replicatedctl app-config export --hidden --template '{{.generated_postgres_password.Value}}'

• The current version of TFE is before v202010-1
  • When pg_password is set

replicatedctl app-config export --hidden --template '{{.pg_password.Value}}'

• • When pg_password is not set, the above step returns empty value

The password of the hashicorp user defaults to hashicorp

 

Connect to the PostgreSQL database.

For proof of concept installations:

docker exec -it ptfe_postgres psql -U hashicorp

For mounted disk installations:

docker exec -it ptfe_postgres_disk psql -U hashicorp

Check which user is the current install user. The install user is the user with an oid of 10. Take note of the install user as it will be needed later. The remainder of this article will use ${PG_CURRENT_INSTALL_USER} to refer to this value.

SELECT rolname, oid FROM pg_roles;

This example output shows that the install user is postgres.

  rolname  |  oid
-----------+-------
 postgres  |    10
 hashicorp | 16385
(2 rows)

Create a temporary PostgreSQL user. This temporary user is needed because users cannot be modified while they are in use. Here, a temporary user named temp is created with password temp.

CREATE ROLE "temp" WITH SUPERUSER CREATEDB CREATEROLE REPLICATION BYPASSRLS LOGIN PASSWORD 'temp';

Connect to the hashicorp database as the temp user.

\c hashicorp temp

Reassign ownership of database objects from the hashicorp user to the current install user. Substitute ${PG_CURRENT_INSTALL_USER} with the actual install user retrieved earlier.

REASSIGN OWNED BY "hashicorp" TO "${PG_CURRENT_INSTALL_USER}";

Delete the hashicorp user. As soon as this command is executed, Terraform Enterprise will become unavailable. Please make sure that the Terraform Enterprise instance is not being actively used.

DROP ROLE IF EXISTS "hashicorp";

Rename the current installer user to hashicorp. Substitute ${PG_CURRENT_INSTALL_USER} with the actual install user retrieved earlier.

ALTER ROLE "${PG_CURRENT_INSTALL_USER}" RENAME TO "hashicorp";

After the rename, the warning NOTICE: MD5 password cleared because of role rename may appear if the role "${PG_CURRENT_INSTALL_USER}"has an existing non-empty password. Update the password for the hashicorp user to the value retrieved earlier. Substitute ${PG_PASSWORD} with the actual password retrieved earlier.

ALTER ROLE "hashicorp" WITH PASSWORD '${PG_PASSWORD}';

Connect to the hashicorp database as the hashicorp user.

\c hashicorp hashicorp

Confirm that the hashicorp user has an oid of 10.

SELECT rolname, oid FROM pg_roles;

Delete the temporary PostgreSQL user.

DROP ROLE IF EXISTS "temp";

Exit from the PostgreSQL prompt and go back to the host machine.

\q

Remove the .pg_upgrade_failed file if it exists to tell PostgreSQL to try to upgrade from PostgreSQL 9.5 to PostgreSQL 12 the next time Terraform Enterprise v202103-1 is installed.

For proof of concept installations:

docker_vol_pgdata="$(docker volume inspect --format='{{.Mountpoint}}' postgres)"
pgdata_path="${docker_vol_pgdata}/pgdata"
rm ${pgdata_path}/.pg_upgrade_failed

For mounted disk installations:

mounted_disk_path="$(replicatedctl app-config export --template '{{.disk_path.Value}}' | tr -d '\r')"
pgdata_path="${mounted_disk_path}/postgres/pgdata"
rm ${pgdata_path}/.pg_upgrade_failed

At this point, it is safe to proceed with upgrading to v202103-1.

Additional Information

Here are some related articles.

• How to Manually Backup Internally-Managed PostgreSQL Data
• PostgreSQL 12 Upgrade Error: Timeout waiting for event PostgreSQL Upgraded