PostgreSQL 12 Upgrade Error: database user hashicorp is not the install user – HashiCorp Help Center

Problem

When upgrading a Terraform Enterprise installation to version v202103-1 or newer, the process may fail with the following error message. This occurs because the upgrade logic from PostgreSQL 9.5 to 12 requires the database install user to be hashicorp.

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

This issue primarily affects Proof of Concept and Mounted Disk installations, particularly those that were previously upgraded to version v202005-1.

Verifying the Install User

You can check the install user for the internally-managed PostgreSQL database with the following commands. The user with an oid of 10 is the install user.

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;'

If the output shows a user other than hashicorp with oid 10, the procedure below is required. This example output shows the install user is postgres.

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

Cause

The PostgreSQL install user may not be hashicorp for one of the following reasons:

A manual database change was previously performed.
Remediation steps were taken during an upgrade to version v202005-1 that altered the install user.

Solution

This procedure details how to correct the database ownership to allow the upgrade to proceed.

Prerequisites

Create a recent backup of your Terraform Enterprise installation.
Ensure the Terraform Enterprise application is running. You may need to restore to the version running before the failed upgrade attempt.
Stop all active use of the Terraform Enterprise application to prevent database writes during this process.

Procedure

Retrieve the PostgreSQL Password.

You will need the current PostgreSQL password. Note its value, which will be referenced as ${PG_PASSWORD}. Use one of the following methods to retrieve it.

Method 1: Inspect the Docker container
- For Proof of Concept installations:
```
$ docker inspect ptfe_postgres | grep POSTGRES_PASSWORD
```
- For Mounted Disk installations:
```
$ docker inspect ptfe_postgres_disk | grep POSTGRES_PASSWORD
```
Method 2: Use replicatedctl
- For TFE v202010-1 and newer, run both commands and use the non-empty value:
```
$ replicatedctl app-config export --hidden --template '{{.pg_password.Value}}'
$ replicatedctl app-config export --hidden --template '{{.generated_postgres_password.Value}}'
```
- For TFE versions before v202010-1:
```
$ replicatedctl app-config export --hidden --template '{{.pg_password.Value}}'
```

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

Identify the current install user.

Run the following SQL command to find the user with oid 10. Note this username, which will be referenced as ${PG_CURRENT_INSTALL_USER}.
```
SELECT rolname, oid FROM pg_roles;
```
Create a temporary superuser.

This user is required because a role cannot be modified while it is in use.
```
CREATE ROLE "temp" WITH SUPERUSER CREATEDB CREATEROLE REPLICATION BYPASSRLS LOGIN PASSWORD 'temp';
```
Connect as the temporary user.
```
\c hashicorp temp
```
Reassign database object ownership.

Transfer ownership from the hashicorp user to the current install user. Substitute ${PG_CURRENT_INSTALL_USER} with the actual username from step 3.
```
REASSIGN OWNED BY "hashicorp" TO "${PG_CURRENT_INSTALL_USER}";
```
Delete the hashicorp user.

Terraform Enterprise will become unavailable after this command executes.
```
DROP ROLE IF EXISTS "hashicorp";
```
Rename the install user to hashicorp.

Substitute ${PG_CURRENT_INSTALL_USER} with the actual username.
```
ALTER ROLE "${PG_CURRENT_INSTALL_USER}" RENAME TO "hashicorp";
```
Update the password for the new hashicorp user.

After renaming, you may see a notice: NOTICE: MD5 password cleared because of role rename. Set the password to the value you retrieved in step 1. Substitute ${PG_PASSWORD} with your actual password.
```
ALTER ROLE "hashicorp" WITH PASSWORD '${PG_PASSWORD}';
```
Reconnect as the hashicorp user.
```
\c hashicorp hashicorp
```
Confirm the hashicorp user is now the install user.

Verify that the hashicorp user now has an oid of 10.
```
SELECT rolname, oid FROM pg_roles;
```
Delete the temporary user.
```
DROP ROLE IF EXISTS "temp";
```
Exit the PostgreSQL prompt.
```
\q
```

Remove the upgrade failure flag file.

This allows the PostgreSQL upgrade to re-run when you install Terraform Enterprise v202103-1.

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, you can safely proceed with the upgrade to Terraform Enterprise v202103-1.

Additional Information

Problem

Verifying the Install User

Cause

Solution

Prerequisites

Procedure

Additional Information

Articles in this section

Related articles