Manage vCSA Embedded vPostgres Database with pgAdmin

This article explains how to prepare the vCenter Server Appliance to connect with external Postgres Management Tools like pgAdmin. This method works with vCenter Server Appliance version 6.5, 6.7, and 7.0.


Caution: Working with the database can cause issues with the vCenter Service. Make sure that you know what you are doing and double-check that you have a working backup or snapshots of the vCenter Server Appliance. Consider working with VMware Support if you have problems in a critical production environment.

Disclaimer: This is not officially supported by VMware, please use at your own risk.

vCenter Server Appliance Preparation

  1. Connect to the vCenter Servier Appliance with ssh as root user.
  2. Extract the database password from the configuration file in /etc/vmware-vpx/vcdb.properties
    # cat /etc/vmware-vpx/vcdb.properties

  3. Configure the firewall to accept connections on port 5432
    # iptables -I INPUT 1 -p tcp --dport 5432 -j ACCEPT
  4. Configure vPostgres to allow connections from remote hosts. The configuration file is located at /storage/db/vpostgres/pg_hba.conf.
    echo "host all vc 0.0.0.0/0 md5" >> /storage/db/vpostgres/pg_hba.conf

    This entry allows connections from all hosts, but you still need a password to log in. If you use "all" instead of "vc", the postgres user can connect without a password! To limit connections to a specific address, change 0.0.0.0/0 to your local network. Example: 192.168.0.0/24 to allow a subnet or 192.168.0.10/32 to allow a specific IP address.

  5. Reload vPostgres Configuration to activate changes in pg_hba.conf
    # /opt/vmware/vpostgres/current/bin/psql -U postgres -c "SELECT pg_reload_conf();"

pgAdmin Installation and Configuration

  1. Download and Install the latest version of pgAdmin: www.pgadmin.org/download/
  2. Open pgAdmin (Web-based Interface)
  3. Right-Click Servers and navigate to Create > Server...
  4. Enter a Name
  5. Enter Hostname, Username, and Password in the Connection Tab.
  6. Click Save

You are now connected to the vPostgres database and should see two instances: VCDB and postgres.

If you have not worked with a database management tool, this is how you could get started: To get a list of registered Virtual Machines, for example, navigate to Servers > vCenter DB > Databases > VCDB > Schemas > vc > Views > vpxv_vms and click View Data (Table Icon above navigation bar).

 

  1. You mustn't use "trust" authentication method anywhere, because it's a giant hole for security.
    The config line:
    > host all vc 1.2.3.4/5 trust
    means that everyone from the defined subnet can log in without a password, even as a postgres superuser who can also execute shell commands on behalf of its identity.

    The right way is to use md5 method, which requires a password to log in.

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.