Patching vCenter server to 7.0 U3r fails with error: "Error during pre-patch VCDB. See vcdb

Products

VMware vCenter Server 6.0 VMware vCenter Server 7.0 VMware vCenter Server 8.0

Issue/Introduction

Patching the vCenter via VAMI fails with the below error:

While upgrading the vCenter using the CLI you see a similar message where it constantly prompts for user postgres password:

Password for user postges:
Password for user postges:
Password for user postges:
Password for user postges:

Environment

VMware vCenter 6.x.

VMware vCenter 7.x.

VMware vCenter 8.x.

Cause

The issue here is the permission of /root/.pgpass is less strict than requirement.

Technically on Unix systems, the permissions of postgresql password file (here is /root/.pgass) must disallow any access to world or group.

If the permissions are less strict than this, the file will be ignored. That's why we can see the error like 'no password supplied'.

We will see the following log snippets from the vCenter:

From the PatchRunner logs:

[YYYY-MM-DDTHH:MM:SS] vcIntegrity:CollectRequirements ERROR vcIntegrity Failed to query hosts for precheck using psql.
Error: WARNING: password file "/root/.pgpass" has group or world access; permissions should be u=rw (0600) or less
Password for user postgres:
WARNING: password file "/root/.pgpass" has group or world access; permissions should be u=rw (0600) or less psql.bin: error: fe_sendauth: no password supplied

[YYYY-MM-DDTHH:MM:SS] vcdb:CollectRequirements INFO vcdb Calculating DB disk requirements...
[YYYY-MM-DDTHH:MM:SS] vcdb:CollectRequirements ERROR vcdb.vcdb_db_utils ERROR DROP EXTENSION IF EXISTS pg_addons
[YYYY-MM-DDTHH:MM:SS] vcdb:CollectRequirements ERROR vmware_b2b.patching.executor.hook_executor Patch hook 'vcdb:CollectRequirements' failed.
Traceback (most recent call last):
File "/storage/updatemgr/software-updatetb36bcis/stage/scripts/patches/py/vmware_b2b/patching/executor/hook_executor.py", line 74, in executeHook
executionResult = systemExtension(args)
File "/storage/updatemgr/software-updatetb36bcis/stage/scripts/patches/libs/sdk/extensions.py", line 106, in __call__
result = self.extension(*args)
File "/storage/updatemgr/software-updatetb36bcis/stage/scripts/patches/libs/sdk/extensions.py", line 123, in _func
return func(*args)
File "/storage/updatemgr/software-updatetb36bcis/stage/scripts/patches/payload/components-script/vcdb/__init__.py", line 91, in collectRequirements
ret, storage_set, storage_core = pre_check_db()
File "/storage/updatemgr/software-updatetb36bcis/stage/scripts/patches/payload/components-script/vcdb/vcdb_db_utils.py", line 394, in pre_check_db
remove_pg_addons_extension(FILE_OUT, FILE_ERR)
File "/storage/updatemgr/software-updatetb36bcis/stage/scripts/patches/payload/components-script/vcdb/vcdb_db_utils.py", line 383, in remove_pg_addons_extension
raise PermanentError(cause=error, resolution=sugg_action)
patch_errors.PermanentError: Error during pre-patch VCDB. See vcdb_patch.err.
[YYYY-MM-DDTHH:MM:SS] ERROR vmware_b2b.patching.phases.discoverer Could not execute discovery hook in file: /storage/updatemgr/software-updatetb36bcis/stage/scripts/patches/payload/components-script/vcdb

From the microservice logs:

"description": {
"id": "Problem dropping vpg extension",
"translatable": "Error during pre-patch VCDB. See vcdb_patch.err.",
"localized": "Error during pre-patch VCDB. See vcdb_patch.err."
},

From the postgresql logs:

[YYYY-MM-DDTHH:MM:SS] 668495ad.a5fa 0 VCDB postgres [local] 42490 2 FATAL: password authentication failed for user "postgres"
[YYYY-MM-DDTHH:MM:SS] 668495ad.a5fa 0 VCDB postgres [local] 42490 3 DETAIL: Password does not match for user "postgres".
[YYYY-MM-DDTHH:MM:SS] 668496d9.b0f1 0 VCDB postgres [local] 45297 2 FATAL: password authentication failed for user "postgres"
[YYYY-MM-DDTHH:MM:SS] 668496d9.b0f1 0 VCDB postgres [local] 45297 3 DETAIL: Password does not match for user "postgres".
[YYYY-MM-DDTHH:MM:SS] 66849805.bb9c 0 VCDB postgres [local] 48028 2 FATAL: password authentication failed for user "postgres"
[YYYY-MM-DDTHH:MM:SS] 66849805.bb9c 0 VCDB postgres [local] 48028 3 DETAIL: Password does not match for user "postgres".
[YYYY-MM-DDTHH:MM:SS] 66849931.c6bb 0 VCDB postgres [local] 50875 2 FATAL: password authentication failed for user "postgres"

From the vcdb_patch.err log file:

WARNING: password file "/root/.pgpass" has group or world access; permissions should be u=rw (0600) or less
Password for user postgres:
WARNING: password file "/root/.pgpass" has group or world access; permissions should be u=rw (0600) or less
psql.bin: error: fe_sendauth: no password supplied

Resolution

Take a snapshot on the vCenter before implementing the below steps. [Offline snapshot if the vCenter is in ELM].
There should be a .pgpass file residing in the root directory.
The .pgpass file should have read and write permissions only for the owner and not for group and others.
We can run the below command from the root directory to verify if only the owner/user has only Read and Write permissions which is followed by it's output:

root@vcsa01 [ ~ ]# ls -lrtha
total 24K
-rw-r--r-- 1 root root 178 Dec 29 2023 .bash_logout
drwx------ 2 root root 4.0K Dec 29 2023 .ssh
drwxr-xr-x 3 root root 4.0K Dec 29 2023 .ansible
-rw------- 1 root root 418 Sep 9 10:37 .pgpass

If the content is empty in the .pgpass file, we can paste the following content mentioned as follows[cat .pgpass (from the root directory)]:

localhost:5432:replication:replicator:password-1
127.0.0.1:5432:replication:replicator:password-1
/var/run/vpostgres:5432:replication:replicator:password-2
localhost:5432:postgres:postgres:password-2
127.0.0.1:5432:postgres:postgres:password-2
localhost:5432:VCDB:postgres:password-2
127.0.0.1:5432:VCDB:postgres:password-2
/var/run/vpostgres:5432:*:postgres:password-2

Note: Here password-1 and password-2 can be any two unique strings that contains 16 random characters [The passwords can be manually generated with a mix of lower and upper case letters, alphanumeric and special characters].
If the file isn't there, then create a .pgpass file and paste the above content.
If a file is created or pasted the above content then ensure you run the following command to ensure it has only the required permissions: chmod 600 .pgpass
Reboot the vCenter and re-run the upgrade on the vCenter.
The vCenter should be upgraded successfully.

Additional Information

Copy PGUSER_PASSWORD from the /etc/vmware-vpx/embedded_db.cfg file.. and use it in place of 'password-2'
For example:

root@testvcenter [ / ]# cat /etc/vmware-vpx/embedded_db.cfg | grep PGUSER_PASSWORD
PGUSER_PASSWORD='PG-PASSWORD'

Reference Broadcom KB -

https://knowledge.broadcom.com/external/article/316582