vCenter Server Appliance /root/.pgpass file does not exist

Products

• VMware vCenter Server

Issue/Introduction

The purpose of this article is to help recreate the hidden pgpass file.

Symptoms:

• When running the below command to connect to the VCDB using the postgres user, instead of automatically logging in, the screen prompts for the postgres password instead.

/opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres

• The hidden file .pgpass is missing from /root/.

Environment

VMware vCenter Server Appliance 6.0.x
VMware vCenter Server Appliance 6.5.x
VMware vCenter Server 7.0.x
VMware vCenter Server Appliance 6.7.x

Resolution

Note: Take valid snapshots of the vCenter Server before proceeding. In the case of linked vCenter Servers, ensure offline snapshots are taken for all vCenters.

1. SSH to the vCSA via root
2. Backup the pg_hba.conf file

cp /storage/db/vpostgres/pg_hba.conf /tmp/pg_hba.conf

3. Edit /storage/db/vpostgres/pg_hba.conf

vi /storage/db/vpostgres/pg_hba.conf

4. Under the # IPv4 local connections, change the last word md5 to trust.

Original

host all all 127.0.0.1/32 md5

Edited
host all all 127.0.0.1/32 trust

Note: you may also need to change
local all all md5
To
local all all trust

5. Restart the vpostgres service

service-control --restart vpostgres

6. Access the VCDB (we will now be able to gain access as we altered the connection to trust all)

/opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres

7. Change the postgres password

alter user postgres with password 'newpassword';

Note: In the above command, change newpassword to a password you'd like to use.

8. Revert the pg_hba.conf file from Step 2-3 to revert trust to the original md5 value
9. Access the VCDB using the same command in Step 6. It will prompt for the postgres password that you set in Step 7.
10. Test and confirm the newly updated password allows access to the VCDB. If so, proceed to the next step.
11. Create the missing .pgpass file under /root, set the proper permissions, edit the file with the below contents with the updated password for the postgres user, and save the file.

touch /root/.pgpass
chmod 600 /root/.pgpass
vi /root/.pgpass

Copy the below contents into the .pgpass file.

/var/run/vpostgres:5432:*:postgres:newpassword
localhost:5432:postgres:postgres:newpassword
127.0.0.1:5432:postgres:postgres:newpassword
localhost:5432:VCDB:postgres:newpassword
127.0.0.1:5432:VCDB:postgres:newpassword
 

Note: There are 5 lines that require updating the newpassword with the password set in Step 7.

Save the file

:wq

12. Access the VCDB again using the command in Step 6. This time instead of being prompted for the postgres password, you'll automatically be logged in.

Additional Information

For Window vCenter Servers:
"Error: pgpass.conf which enables promptless access to PostgreSQL was not found" when upgrading Windows vCenter Server (70861)