This topic describes how to download and install or upgrade the advanced components of the VMware Postgres software distribution. The VMware Postgres advanced components offer the following benefits:

  • VMware Postgres Advanced Server adds extended functionality to open-source PostgreSQL that includes:
    • Database administration
    • Enhanced SQL capabilities
    • Database and application security, and so on.
  • VMware Postgres Advanced Server adds support for Transparent Data Encryption(TDE) which is mising in the Open Source version.

The VMware Postgres software components are provided in 4 downloadable packages for Linux based platforms, and in a single Debian package for Ubuntu platforms.

The following advanced packages are available: - vmware-postgres16-advanced-16.3-1.el8.x86_64.rpm - vmware-postgres16-advanced-clients-16.3-1.el8.x86_64.rpm - vmware-postgres16-advanced-devel-16.3-1.el8.x86_64.rpm - vmware-postgres16-advanced-libs-16.3-1.el8.x86_64.rpm

For Linux based platforms:

  • Postgres Server RPM – installs or upgrades all server and client components. This includes the PostgreSQL database, pgBackRest, pg_auto_failover, psql, the ODBC driver, and more.
  • Postgres Clients RPM – installs or upgrades only the client components, psql and the ODBC driver.
  • JDBC Driver for VMware Postgres – provides only the JDBC driver as a compressed ZIP file. This can be used on any machine as necessary for developing Java clients to PostgreSQL.
  • VMware Postgres Extensions – installs the RPM packages for the optional extensions not already included in the Postgres Server rpm.

For Ubuntu platforms:

  • Postgres and Extensions DEB – installs the Debian package. This includes server, clients, and extensions.

Prerequisites

  • Your deployment systems must meet the system requirements described in Supported Platforms.
  • You must have root permissions to install or upgrade the software.
  • Ensure that you have not installed Red Hat Enterprise Linux PostgreSQL on any system where you want to install VMware Postgres Server or VMware Postgres Clients. VMware Postgres software cannot be installed alongside RHEL PostgreSQL at this time.
  • The VMware Postgres PL/R Extension requires R to be installed before the RPM can be installed. See Installing the VMware Postgres PL/R Extension.

Installing the Postgres Server RPM

Perform this procedure on each host that will run VMware Postgres. Note that this process also installs the psql and ODBC driver client components to each machine:

  1. Download the VMware Postgres Server RPM distribution from VMware Tanzu Network. The Postgres Server download filename has the format: vmware-postgres<version>-<vmware-version>.<platform>.<architecture>.zip, for example vmware-postgres15-15.1.0.el7.x86_64.zip.

  2. Log in to your system as root, or use sudo in the next step to acquire root privileges.

  3. Navigate to the downloaded RPM file and install it using the yum utility. For example:

    cd ~/Downloads/vmware-postgres-16.*
yum install ./vmware-postgres16-advanced-16.3-1.el8.x86_64.rpm

  4. The VMware Postgres RPM installation creates the postgres user and sets the PGDATA and PATH environment variables in ~/.bash_profile. If the .bash_profile exists prior to installation, it will not be modified. It also creates the directory /var/lib/pgsql/data, owned by the postgres user, which you can specify for the data directory when you initialize your PostgreSQL instance. This example initializes the data directory:

    su --login postgres
source ~/.bash_profile
initdb -D /var/lib/pgsql/data

  5. If you are creating a High Availability configuration, you will initialize the Postgres instances as part of the pg_auto_failover, Patroni, or repmgr setup. For more details refer to Creating a High Availability cluster.

  6. If you are creating a single non-HA Postgres server, start it by running:

    postgres -D /var/lib/pgsql/data >/var/lib/pgsql/data/logfile 2>&1 &

  7. Continue to set up and start the PostgreSQL server, as described in Server Setup and Operation in the PostgreSQL documentation.

  8. Follow the instructions in Configuring and Using VMware Postgres to configure PostgreSQL with pgBackRest, psqlODBC, and pgjdbc.

Installing the VMware Postgres Client RPM

Perform this procedure on each host that will act only as a client to the VMware Postgres instance.

Important: Do not install the client package on any machine that has a PostgreSQL server.

  1. Download the Postgres Clients RPM distribution from VMware Tanzu Network. The download filename has the format: vmware-postgres-clients-<postgres-version>.<vmware-version>.<platform>.<architecture>.zip, for example vmware-postgres-clients-15.1.0.el7.x86_64.zip.

  2. Log in to your system as root, or use sudo in the next step to acquire root privileges.

  3. Navigate to the downloaded RPM file and install it using the yum utility. For example:

    cd ~/Downloads/vmware-postgres-16*
yum install ./vmware-postgres16-advanced-clients-16.3-1.el8.x86_64.rpm

Installing the VMware Postgres Client Windows tarball

Perform this procedure on each host that will act only as a client to the VMware Postgres instance.

Important: Do not install the client package on any machine that has a PostgreSQL server.

  1. Download the Postgres Windows Clients tarball distribution from VMware Tanzu Network. The download filename has the format: vmware-postgres-clients-<postgres-version>.<vmware-version>-windows.tar.gz, for example vmware-postgres-clients-14.5.0-windows.tar.gz.

  2. Navigate to the downloaded file and extract the tarball using a file archiver application.

  3. Navigate to the MSI file and install it by double-click the MSI file. You can also use the command to install an MSI file using the Command Prompt or Powershell. For example:

    msiexec /i postgresql-clients-<postgres-version>-x86_64.msi

Installing the VMware Postgres ODBC Driver for Windows

  1. Download the Postgres ODBC Driver for Windows from VMware Tanzu Network. The download filename has the format: vmware-postgres-odbc-driver-<odbc-version>-windows.zip.

  2. Unzip the downloaded file to obtain the driver file. For example:

    unzip vmware-postgres-odbc-driver-<odbc-version>-windows.zip

  3. Navigate to the MSI file and install it by double-click the MSI file. You can also use the command to install an MSI file using the Command Prompt or Powershell. For example:

    msiexec /i psqlodbc_x64.msi

Installing the VMware Postgres JDBC Driver

  1. Download the VMware Postgres Postgres Clients for RHEL7 from VMware Tanzu Network. The JDBC driver file name inside the zip file has the format: vmware-postgres-jdbc-<jdbc-version>.jar.

  2. Unzip the downloaded file to obtain the driver JAR file. For example:

    unzip vmware-postgres-clients-<version>-0.el7.x86_64.zip

  3. The JDBC driver JAR is not installed at the system level; simply include it in your Java CLASSPATH as necessary to connect using the driver. See Setting up the Class Path in the JDBC Driver documentation for more information.

Installing the VMware Postgres Extensions

The VMware Postgres Server and Extensions download from VMware Tanzu Network includes RPM packages for all the VMware Postgres supported extensions.For more information about extensions, see Installing VMware Postgres.

Using Transparent Data Encryption (TDE)

The advanced version of VMware Tanzu PostgreSQL supports transparent data encryption which enables the user to encrypt all underlying files of all tables and indexes (including catalog tables and TOAST tables). The user configuration files and server logs are not encrypted.

TDE encrypts underlying files with a randomly generated key (which is also called as data encryption key or DEK). The data encryption key is encrypted and stored locally in the PostgreSQL data directory as <DATA_DIR>/data_encryption.key. The method for encrypting the data encryption key can be configured by user and it can be protected by a user created password or by a KMS service.

Enable TDE for the Advanced Version of VMware Tanzu PostgreSQL

To enable TDE, an environment variable PG_DATA_ENCRYPTION_KMS_URI is required to export before initializing and starting the database. It is used to enable the TDE support and identify the KMS (Key Management System) that you want to use. Currently, two KMS schemes are supported, namely, password-aes256:// and shell://. The first one allows users to use a password to protect the data encryption key, and the second one allows users to use a customized method to protect the data encryption key.

Using the password-aes256:// Scheme to Enable TDE

The password-aes256:// scheme encodes a plain password in its URI. With URI password-aes256://112233, TDE will protect the data encryption with a plain password 112233. Perform the following steps to protect the database with the password-aes256:// scheme:

  1. Set up the environment variable export PG_DATA_ENCRYPTION_KMS_URI=password-aes256://112233.

  2. Run the following command to initialize the database:

    initdb -D/path/to/data

  3. After initializing the database, the data protection key is stored in the file data_encryption.key under /path/to/data. This key is encrypted with the user specified password 112233. If the special key file data_encryption.key exists, the database is protected by TDE seamlessly.

  4. After initializing the database, user can start the database by running the following command:

    pg_ctl -D /path/to/data -l /path/to/logfile start

Using the shell:// Scheme to Enable TDE

The shell:// scheme identifies an executable that exists in your system. The executable takes either encrypt or decrypt as its argument. The encrypt argument is used for encrypting the data encryption key (DEK), and it reads the key encrypting key (KEK, the key for protecting DEK which is also called as master key) from standard input (stdin) and it prints the encrypted DEK to stardard output (stdout). The decrypt argument is used for decrypting the encrypted DEK, and it reads the KEK for decrypting the encrypted DEK from stdin and it prints the DEK to stdout. With the shell:// scheme, TDE is able to fetch the password (KEK) using some indirect mechanism, like systemd-ask-password, Google Could Key Management Service, and so on.

With systemd-ask-password

  1. User is required to create an encryption script (/path/to/myscript.sh) with the following content.

    #!/usr/bin/env bash

if [[ "$1" == "encrypt" ]]; then
 openssl enc -aes-256-ctr -pbkdf2 -e -pass file:<(sudo systemd-ask-password --keyname pgtde --accept-cached --no-tty)
else
 openssl enc -aes-256-ctr -pbkdf2 -d -pass file:<(sudo systemd-ask-password --keyname pgtde --accept-cached --no-tty)
fi

  2. Add execution permission to chmod +x /path/to/myscript.sh script.

  3. Set up the environment variable export PG_DATA_ENCRYPTION_KMS_URI=shell:///path/to/myscript.sh.

  4. Add an entry to /etc/sudoers by running the following command:

    <USER> ALL = NOPASSWD: /usr/bin/systemd-ask-password

  5. After exporting the environment variable, user can initialize the database with initdb -D/path/to/data. Meanwhile, user should open another terminal to input the password with systemd-tty-ask-password-agent --query.

    Note

    By default, system-ask-password will not look up the user-specific keyring (@u) and the user needs to input password multiple times. The user can link the user-specific keyring with the session-specific keyring to workaround this issue (sudo keyctl link @u @s).

  6. After initializing the database, there is a special key file data_encryption.key under /path/to/data path. The real data encryption key is stored in it and is encrypted with the user provided password. If the special key file data_encryption.key exists, the database is protected by TDE seamlessly.

  7. After initializing the database, user can start by running the following command:

    pg_ctl -D /path/to/data -l /path/to/logfile start

After running this command, you will be prompted to enter the password again.

With Google Could Key Management Service

  1. Create a key on Google cloud by running the following commands:

    # Create a keyring for our key.
gcloud kms keyrings create "my-very-first-keyring" --location="global"
# Create our key on that keyring.
gcloud kms keys create "my-very-first-key" \
   --location="global" --keyring="my-very-first-keyring" \
   --purpose="encryption"
# Verify that our key works and it should prints "FOO".
echo "FOO" | \
 gcloud kms encrypt --location="global" \
   --keyring="my-very-first-keyring" --key="my-very-first-key" \
   --plaintext-file="-" --ciphertext-file="-" | \
 gcloud kms decrypt --location="global" \
   --keyring="my-very-first-keyring" --key="my-very-first-key" \
   --plaintext-file="-" --ciphertext-file="-"

  2. User needs to create an encryption script (/path/to/myscript.sh) with the following commands:

    #!/usr/bin/env bash

if [[ "$1" == "encrypt" ]]; then
 gcloud kms encrypt --location global --keyring my-first-keyring --key my-first-key --version 1 --plaintext-file - --ciphertext-file -
else
 gcloud kms decrypt --location global --keyring my-first-keyring --key my-first-key --plaintext-file - --ciphertext-file -
fi

  3. Add execution permission to chmod +x /path/to/myscript.sh script.

  4. Set up the environment variable export PG_DATA_ENCRYPTION_KMS_URI=shell:///path/to/myscript.sh.

  5. Run the following command to initialize the database:

    initdb -D/path/to/data

  6. After initializing the database, there is a special key file data_encryption.key under /path/to/data. The real data encryption key is stored in it and is encrypted with the KMS service provided by Google. If the special key file data_encryption.key exists, the database is protected by TDE seamlessly.

  7. After initializing the database, user can start it with pg_ctl -D /path/to/data -l /path/to/logfile start.

Limitations of TDE

  • TDE doesn not support encrypting XLOG and the feature will be added in the future release.
  • Using pg_read_binary_file() pg_read_file_v2() pg_read_file() to read table file are not supported by design.
check-circle-line exclamation-circle-line close-line
Scroll to top icon