PostgreSQL on Data Management for VMware Tanzu

Supported PostgreSQL Versions

Data Management for VMware Tanzu supports provisioning databases running PostgreSQL versions 10.x, 11.x, 12.x, and 13.x.

Configuration

When you create a PostgreSQL database with Data Management for VMware Tanzu, you configure certain PostgreSQL properties.

Database Configuration

The Database Configuration properties identify the name of the default database, the database server port number, and the database administrator user credentials.

Property Name	Description
Database Name	The name of the default database.
Database Port No	The port number on which the database server runs. (The default is 5432.) (Read-only)
Username	The database administrator user name.
Password	The password for the database administrator. The special characters allowed are @, #, %, &, and *.

Note: The default values for these properties are provided through the user interface, but you should change them as required. Data Management for VMware Tanzu does not support changing the Password from the console or API. You must change this password using a database client. If you change the password in this manner, the new password is not reflected in Data Management for VMware Tanzu.

DB Options Configuration

The DB Options Configuration properties control certain runtime characteristics of the PostgreSQL database. These are:

Property Name	Description	Default Value	Restart Required on Update?
TimeZone	The time zone for the PostgreSQL server.1	GMT	Yes
max_connections	The maximum number of concurrent connections allowed to the PostgreSQL server. Choose a suitable value based on CPU, memory, and application requirements.	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.2 Note: You can set the locale only at database creation time; you cannot set this parameter after you create the database.	Unset The following PostgreSQL locales are available in this release of Data Management for VMware Tanzu: C, de_DE.utf8, en_US.utf8, en_US.UTF-8, es_ES.utf8, fr_FR.utf8, ja_JP.utf8, ko_KR.utf8, POSIX, zh_CN.utf8, zh_TW.utf8	N/A

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

¹ Run the following PostgreSQL query to obtain the possible time zone values: SELECT name FROM pg_timezone_names;

² Be sure to consult the PostgreSQL documentation before you set the locale or related lc_xxx options.

Connecting to a PostgreSQL Database

Prerequisites

To access a PostgreSQL database:

• 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 FQDN of the PostgreSQL server host, the port on which the server is running, and the database name.
• You must also have the PostgreSQL admin credentials on hand. You can obtain this information from the Data Management for VMware Tanzu console as described in Locating the Database Connection Parameters.

Connecting to a PostgreSQL Database Through PSQL Command Line Interface

To provide access to a PostgresSQL database through a command line interface(CLI):

1. You must enable SSH access. This creates the db-admin OS user with the capability to use the native PostgreSQL tools, such as, psql, pg_dump, and pg_restore; and allow the import or export data directly to or from the database.

2. Log into the host OS as the db-admin user.

3. To connect to the database, create a .db-client-env file in the db-admin user's $HOME directory.

   printf "PGHOST=localhost\nPGDATABASE=dbaas\nPGUSER=dbaas\nPGPASSWORD=<default-password>" > $HOME/.db-client.env
   

   Note: Replace with password of your choice.

4. The .db-client-env file must contain the PostgreSQL connection parameters to the default values set by Data Management for VMware Tanzu when a database is provisioned. Environment variables that you can set in this file are as follows:

Environment Variable	Description
PGHOST	The host on which the PostgreSQL server is running.
PGDATABASE	The name of the PostgreSQL database to which you want to connect.
PGUSER	The PostgreSQL user name.
PGPASSWORD	The password of the PostgreSQL user that abides by the PostgreSQL password restrictions.

With these .db-client-env settings in place, running the psql command (without adding any other options) attempts to connect the user dbaas to the dbaas database on the PostgreSQL server.

Using TLS with a PostgreSQL Database

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

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

Requiring TLS

Note: The default SSL/TLS configuration for PostgreSQL on Data Management for VMware Tanzu does not require secure client connections.

You can configure a PostgreSQL database to require SSL/TLS client connections during creation or after provisioning as follows:

• You specify Require Client TLS when you create the database.
• You require TLS by configuring the database after it is provisioned as described in Requiring TLS-Secured Client Connections.

About the Permissions Assigned to the Default Database User

When you create a database, the default PostgreSQL database user (role) name is dbaas. You can change this name, if you choose, before you provision the database.

While the default user is not a PostgreSQL SUPERUSER, Data Management for VMware Tanzu assigns this user the privileges, roles, and function execution permissions that allow them to create other roles and databases, import and export data from the database, execute the PostgreSQL server signalling functions, and manage PostgreSQL extensions.

Privileges

Data Management for VMware Tanzu assigns the following PostgreSQL privileges to the default user:

Privilege Name	Description
CREATEDB	Allows the user to create a database.
CREATEROLE	Allows the user to create, alter, and drop a (non-superuser) role.
REPLICATION	Allows the user to connect in replication mode.
BYPASSRLS	Instructs PostgreSQL to bypass every row-level security policy for the user.

Roles

Data Management for VMware Tanzu grants the default user membership in the following PostgreSQL default roles. Granting role membership conveys the privileges assigned to the role.

Role Name	Description
pg_monitor	Allows the user to read monitoring views, execute monitoring functions.
pg_signal_backend	Allows the user to signal a server process to cancel a query or terminate a session.
pg_read_server_files	Allows the user to read files from any accessible location on the database file system using COPY or other file access functions.
pg_write_server_files	Allows the user to write files to any accessible location on the database file system using COPY or other file access functions.

Note: The pg_read_server_files and pg_write_server_files default roles are not available to the default user in PostgreSQL version 10.x.

Functions

Data Management for VMware Tanzu grants the default user the permission to execute the PostgreSQL Server Signaling Functions:

Function Name	Description
pg_reload_conf()	Sends a signal to reload configuration files.
pg_cancel_backend()	Sends a signal to cancel a query.
pg_rotate_logfile()	Immediately rotates the PostgreSQL server's log file.
pg_terminate_backend()	Sends a signal to terminate a client session.

Extension Management

Data Management for VMware Tanzu grants the default user the permissions required to execute extension management functions as described in Working with PostgreSQL Extensions.

Working with PostgreSQL Extensions

PostgreSQL extensions are built-in modules that provide additional functionality to a database. The extensions that are available to a Data Management for VMware Tanzu database depend on the PostgreSQL version provisioned for the database. Listing the Available and Registered Extensions describes the psql and SQL commands that you can run to identify the installed and available extensions for your PostgreSQL database.

Note: When you create a PostgreSQL database, Data Management for VMware Tanzu 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. Data Management for VMware Tanzu provides an alternate method of managing PostgreSQL extensions via user-defined functions (UDFs) because it does not grant SUPERUSER permissions to a database owner by default. These functions allow the database owner to create, drop, and update an extension in a database.

Data Management for VMware Tanzu creates the extension management UDFs in the default PostgreSQL template1 database, which makes them available to any database created from this database template. (The default behaviour of PostgreSQL is to use template1 as the database template when you create a new database.)

By default, the Username that you configure when you create a database is the owner of the database specified in Database Name, and has access to the extension management functions. This user also has the permissions required to grant access to the extension management functions to other PostgreSQL users as described in Granting Access to the Extension Management UDFs.

About the Extension Management UDFs

Data Management for VMware Tanzu provides the following UDFs to manage database extensions:

Function Signature(s)	Description
dms_create_extension( name text ) dms_create_extension( name text, schema text, version text,      cascade bool  DEFAULT FALSE)	Creates (registers) the named extension in the database.
dms_update_extension( name text, version text )	Updates the version of a previously-created extension in the database. (The extension must support the upgrade operation.)
dms_drop_extension( name text, cascade bool DEFAULT FALSE )	Drops the named extension from the database.

These UDFs are available to any user with access, and can be invoked using SQL from any PostgreSQL client.

Granting Access to the Extension Management UDFs

The database owner can grant other PostgreSQL users access to the extension management UDFs with the following SQL commands:

GRANT EXECUTE ON FUNCTION dms_create_extension(text) TO <user>;
GRANT EXECUTE ON FUNCTION dms_create_extension(text, text, text, bool) TO <user>;
GRANT EXECUTE ON FUNCTION dms_drop_extension(text, bool) TO <user>;
GRANT EXECUTE ON FUNCTION dms_update_extension(text, text) TO <user>;

Replace <user> with the PostgreSQL user name to which you want to grant access.

Listing the Available and Registered Extensions

All extensions installed with PostgreSQL are available to be created (i.e. registered) in any database. You may find it useful to view a list of the available extensions. You may also be interested in a list of the extensions that are already registered in a specific database.

• You can query the PostgreSQL pg_available_extensions catalog table directly to view the name, default version, and installed version of all available extensions:

  db=> SELECT * from pg_available_extensions;
  

• The \dx meta-command displays a list of the extensions created in the current database. You can run the command from any psql client:

  db=> \dx
  

Example Usage

The extension management UDFs allow users with access to create, update, or drop an extension in a database by invoking SQL commands from any PostgreSQL client.

For example, to register the latest version of the hstore extension in the current database (named db), run the following command:

db=> SELECT dms_create_extension( 'hstore' );

To register version 1.6 of the hstore extension in the schema named extschema in the current database:

db=> SELECT dms_create_extension( 'hstore', 'extschema', '1.6' );

To drop this extension, and any dependent objects, from the current database:

db=> SELECT dms_drop_extension( 'hstore', true );

Importing and Exporting Data

The $HOME directory of the db-admin user on the database contains a subdirectory named transfer/. This directory is a symbolic link to the database data disk. You can copy large data files for import operations to the transfer/ directory, or use it as a write target for data export operations.

Note: Any files that reside in the transfer/ directory use storage on the data disk, limiting the amount of disk space available to the database. Ensure that you promptly remove from this directory any files that you no longer require. You may also choose to extend the size of the data disk which is use by the database.

Note: Data Management for VMware Tanzu removes the contents of the transfer/ directory when a database is recovered.

Importing Data

Note: Because the COPY command is not available to the default user in PostgreSQL version 10.x, the db-admin user can import data using only the pg_restore command.

You can copy data files to the database to locally load data into the database, potentially avoiding network latency or connection issues that could arise with a remote operation.

To import data locally, the data file(s) must reside on the database. You can use ssh or other similar command to copy the data file(s) to the transfer/ directory on the database.

For example, the following command copies a local file named datafile.csv to the $HOME/transfer directory for the db-admin OS user on the database:

$ scp datafile.csv db-admin@<db-vm>:transfer

When you reference a file that you have copied to the database in an import operation, you must specify the absolute path to the file.

Examples

Load a data file on the database file system into a PostgreSQL table:

db-admin@<db-vm>$ psql -c "COPY <table-name> (<column-name>[, ...]) FROM '/home/db-admin/transfer/<data-file-name>'"

Use cat and the psql \copy command to stream a file and load it into a PostgreSQL table:

db-admin@<db-vm>$ cat transfer/<data-file-name> | psql -c "\copy <table-name> (<column-name>[, ...]) FROM stdin"

Use the pg_restore command to load a dump file into a database:

db-admin@<db-vm>$ pg_restore -d <database-name> transfer/<dump-file-name>

Exporting Data

Note: Because the COPY command is not available to the default user in PostgreSQL version 10.x, the db-admin user can export data using only the pg_dump command.

You may choose to export data from a database to examine the data or to load it into another database. Using PostgreSQL tools, you can export data from one or more PostgreSQL tables, or back up an entire database to a file.

When you export data, the exported data file will be written to the database. You can use ssh or other similar command to copy the data file(s) from the database to your local host:

$ scp db-admin@<db-vm>:transfer/<export-file-name> ./localdir/

When you reference a file on the database in a PostgreSQL export operation, you must specify the absolute path to the file.

Examples

Export table data to the database file system:

db-admin@<db-vm>$ psql -c "COPY <table-name> TO '/home/db-admin/transfer/<export-file-name>'"

Use the pg_dump command to create a logical backup of a database and write the file to the database file system:

db-admin@<db-vm>$ pg_dump -F -t <database-name> > transfer/<dump-file-name>