PostgreSQL on VMware Data Services Manager

Learn about specifics of using PostgreSQL on VMware Data Services Manager.

Supported PostgreSQL Versions

For information about PostgreSQL versions that this release of VMware Data Services Manager supports, see VMware Data Services Manager Release Notes.

Configuration

When you create a PostgreSQL database with VMware Data Services Manager, you configure certain PostgreSQL properties.

Database Basic Information

The Basic Information properties identify the database version, the name of the default database, the replica mode, and the database administrator user credentials.

For a full list of properties, see Database Configuration Reference.

Note: VMware Data Services Manager 2.1 does not support five-node topology (3 replicas) for PostgreSQL database clusters. Only clusters with three nodes (1 replica) or a single node (0 replicas) are supported by VMware Data Services Manager 2.1. If you have any existing five-node clusters in your environment created with VMware Data Services Manager 2.0.3, they will continue to be functional after upgrading to 2.1, but you cannot edit them. You can clone or restore your five-node clusters into one-node (0 replicas) or three-node (1 replica) clusters if you want them to be modifiable in 2.1.

Property Name	Description
Database Version	Use the dropdown menu to select from the available template versions of the database. Note: Availability of template versions depends on whether your data service is enabled in full or limited preview mode. For more information, see Activate Data Services in VMware Data Services Manager.
Instance Name	Enter a unique instance name.
Topology	Specify configuration for the database nodes appropriate for the replica mode you selected.
Database Name	Name of the database engine to be created.
Admin Username	The database administrator user name.

Database Advanced Settings

The Advanced Settings properties control certain runtime characteristics of the PostgreSQL database. The following table lists several of the settings you can configure, but there can be more. For additional details, see the PostgreSQL Documentation page at https://www.postgresql.org/docs/.

Property Name	Description	Default Value	Restart Required on Update?
TimeZone	The time zone for the PostgreSQL server. Run the following PostgreSQL query to obtain the possible time zone values: SELECT name FROM pg_timezone_names;	UTC	No
max_connections	The maximum number of concurrent connections allowed to the PostgreSQL server. Choose a suitable value based on CPU, memory, and application requirements. In a High Availability cluster of PostgreSQL databases, you cannot configure the max_connections of a Replica database lower than that of the Primary database.	100	Yes
log_connections	Activate or deactivate logging of connection attempts and successful completion of authentication.	off	No
locale	The language, sorting, number formatting, date/time, currency, etc. preference for the PostgreSQL server. Note: You cannot change the default parameter.	C.UTF-8	N/A
shared_buffers	The amount of memory the database server can use for shared memory buffers. Be sure to consult the PostgreSQL documentation before you set the shared_buffers option.	25% of total RAM	Yes

Note: If you want to set a parameter to the PostgreSQL server default, you must remove/clear the entry.

Using TLS with a PostgreSQL Database

Refer to SSL Support in the PostgreSQL documentation for more information on using SSL/TLS with PostgreSQL.

Note: VMware Data Services Manager does not support verify-full TLS configuration for client applications connecting to a database.

The default SSL/TLS configuration for PostgreSQL on VMware Data Services Manager does not require secure client connections.

About the Permissions Assigned to the Default Database User

When you create a database, the default PostgreSQL database user (role) name is pgadmin, and it has a SUPERUSER privileges.

Working with PostgreSQL Extensions

PostgreSQL extensions are built-in modules that provide additional functionality to a database. The extensions that are available to a VMware Data Services Manager database depend on the PostgreSQL version provisioned for the database.

When you create a PostgreSQL database, VMware Data Services Manager enables the postgis extension by default.

An extension must be explicitly enabled in each database in which it is to be used. Only PostgreSQL SUPERUSERs can enable an extension.

Extensions in a PostgreSQL database

DSM PostgreSQL databases are provisioned with several extensions in addition to the extensions that are supplied with Postgres by default. For information, see Additional Supplied Modules and Extensions.

You can review the full list of extensions below.

• pgAudit https://github.com/pgaudit/pgaudit
• Orafce https://github.com/orafce/orafce
• PostGIS https://postgis.net
• pgvector https://github.com/pgvector/pgvector

To install any of the default or additional extensions, use the following CREATE EXTENSION command. For more details, see CREATE EXTENSION.

CREATE EXTENSION IF NOT EXISTS extension_name

Connecting to a PostgreSQL

This section provides information on how to connect to a PostgreSQL database. If needed, you can learn how to create an LDAP user for the PostgreSQL database.

Connect to a PostgreSQL Database

Use this task to connect to a PostgreSQL database.

Prerequisites

• The host on which the client application is running must have connectivity to the Application Network configured for the database. You can use any SQL client application to connect to a database in your PostgreSQL database.
• To connect the host, you must be able to identify the IP or FQDN of the PostgreSQL server host, the port on which the server is running, and the database name.

  To be able to connect to a database using its FQDN, add the name when creating the database. See Creating a Database.

  If you don't specify the FQDN at the database creation, you can add it later. See Edit Basic Information of the Database in VMware Data Services Manager.

• You must also have the PostgreSQL admin credentials on hand.

Procedure

1. Install psql or other tool, such as DBeaver, pgAdmin4, Toad, and so on, on your system.
2. In the DSM console, navigate to Databases > Postgres tab, and click the database you want to access.
3. On the Summary tab, click COPY CONNECTION STRING.
   
4. To connect to the database, provide the connection string to the tool you are using.

Connect to a PostgreSQL Database using SSL

You can also connect to a PostgreSQL database using DSM API Kubeconfig.

Prerequisites

Download the CA certificate for the database.

Procedure

1. Download the DSM API Kubeconfig.
   1. Login to the DSM console.
      See Accessing the VMware Data Services Manager Console.
   2. Click on the Person icon on the top right, and click Download DSM API Kubeconfig.
      
2. Use the following kubectl command to extract the CA certificate for the database:

   KUBECONFIG=kubeconfig-gateway.yaml kubectl get secret pg-<dbname> -o jsonpath="{.data.ca\.crt}" | base64 -d > ca.crt

   Where:

   kubeconfig-gateway.yaml is the DSM API Kubeconfig file downloaded in the previous step.

   <dbname> is the name of the database.

   ca.crt is the name of the file where you put the CA certificate.

3. Modify the connection parameter to include the required sslmode and a reference to the downloaded certificate:

   psql "host=<host> port=<port> dbname=<dbname> user=<adminuser> password=<password> sslmode=verify-ca sslrootcert=<path-to-ca.crt>" -c 'select 1'

   Replace the <host>, <port>, <dbname>, <adminuser>, and <password> with corresponding values from the original connection string. Set <path-to-ca.crt> to the path of the ca.crt file generated in the previous step.
   Note: Access to the database secrets is restricted only to the owner of the database.

Create an LDAP User for a PostgreSQL Database

Configure LDAP authentication with PostgreSQL in VMware Data Services Manager environment.

As a DB administrator, connect to the database using the database connection string.

Perform the following steps for each LDAP user that needs to have access to the database.

You can use your favorite SQL client to perform these steps. This example uses psql.

Prerequisites

• Configure user directories. For information, see Configuring a Directory Service in VMware Data Services Manager.
• When you create a user, keep in mind that username depends on the User Search Attribute of the Directory Service. Common attributes are:
  • userPrincipalName – Default in Active Directory. Typically, it is [email protected].
  • sAMAccountName or uid – Typically, it is the username user.

Procedure

1. Create a database role corresponding to the LDAP user.
   psql -c "CREATE ROLE <user> login"
2. Grant needed privileges to the database user.
   For example,
   psql -c GRANT ALL PRIVILEGES ON DATABASE <database> TO <user>
   psql -c GRANT ALL ON SCHEMA public TO <user>

What to do next

To connect as the LDAP user, enter the following:

psql -h <host> -p <port> -U <user> -d <database>