ERROR: "permission denied to create extension "pgcrypto"" when atempting to upgrade VMware Cloud Director

Products

• VMware Cloud Director

Issue/Introduction

• Unable to upgrade the database with the following error:

Backup your database now using the tools provided by your database vendor.
Enter [Y] after the backup is complete. Y
Running 5 upgrade tasks
Executing upgrade task:
Successfully ran upgrade task
Executing upgrade task:
Successfully ran upgrade task
Executing upgrade task:
Successfully ran upgrade task
Executing upgrade task:
..........[10]

org.postgresql.util.PSQLException: ERROR: permission denied to create extension "pgcrypto"
Hint: Must have CREATE privilege on current database to create this extension.

Environment

VMware Cloud Director 10.6.1

Cause

This issue is caused by the fact that the owner of the Cloud Director database named "vcloud" is postgres instead of vcloud.

Resolution

To resolve the issue to apply the following steps:

1. Stop VCD Service on all cells using the below command:
   • /opt/vmware/vcloud-director/bin/cell-management-tool cell -i $(service vmware-vcd pid cell) -s
   • If there are issues, you can use the command
     • systemctl stop vmware-vcd
2. Connect to the postgres database:
   • sudo -i -u postgres psql
3. Verify if the owner of the vcloud database is a user other than vcloud.
   • \l

                                                               List of databases
  Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | C | C | | 8081 kB | pg_default | default administrative connection database
repmgr   | repmgr | UTF8 | C | C | | 8409 kB | pg_default |
vcloud   | postgres | UTF8 | C | C | | 91 MB | pg_default |

## In the above output, the user is postgres not vcloud

5. If found a different owner in vcloud database other that the expected vcloud, update the owner to vcloud.
   • alter database vcloud owner to vcloud;
6. A re-attempt of the upgrade should now be successful