Configuring and Using VMware Postgres

Backing up with pgBackRest

pgBackrest, an open source backup and restore solution for PostgreSQL, is included with VMware Postgres. You can configure PostgreSQL and pgBackRest to set up backup and restore for your Postgres databases.

Procedure

1. Install pgBackRest Perl prerequisites.

   $ sudo yum install perl perl-Time-HiRes perl-Digest-SHA perl-JSON-PP
   

2. Create pgBackRest configuration directories and files.

   $ sudo mkdir -p /etc/pgbackrest
   $ sudo mkdir -p /etc/pgbackrest/conf.d
   $ sudo touch /etc/pgbackrest/pgbackrest.conf
   $ sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
   $ sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
   

3. Check that pgBackRest is properly installed.

   $ sudo -u postgres pgbackrest
   

   You will see errors if any dependencies are missing.

See the pgBackRest User Guide for help configuring Postgres and pgBackRest, and for backing up and restoring your Postgres databases.

Using pgAudit for logging

VMware Postgres includes the PostgreSQL extension pgAudit, that allows users to monitor specific operations and objects. pgAudit enhances the native Postgres logging abilities with improved log formatting and filtering operations, that assists with regulation compliance.

Installing pgAudit

1. Use the following table as a reference of PostgreSQL to pgAudit version mapping:

   PostgreSQL version	pgAudit version
   PostgreSQL 14	1.6.X
   PostgreSQL 13	1.5.X
   PostgreSQL 12	1.4.X
   PostgreSQL 11	1.3.X
   PostgreSQL 10	1.2.X

   Install pgAudit using the appropriate package name for your VMware Postgres. For example:

   sudo yum install vmware-postgres14-pgaudit-extension-1.6.1-1.el7.src.rpm
   

2. Add pgAudit to the shared_preload_libraries in the postgresql.conf configuration file. For example:

   shared_preload_libraries = 'pgaudit'
   

3. Restart the database to apply the change into Postgres.

4. Enable the pgAudit extension:

   CREATE EXTENSION pgaudit;
   

   CREATE EXTENSION
   

5. Verify the installation using a command similar to:

   postgres=# select count(*) from pg_extension where extname = 'pgaudit';
   

   count
   -------
   1
   (1 row)
   

Session Audit logging

Session logging will log operations performend by a specific user. Use the parameter pgaudit.log to start auditing, depending on your requirements.

To view the default pgAudit parameters, log into your database, and use the system view pg_settings in a command similar to:

postgres=# SELECT name,setting FROM pg_settings WHERE name LIKE 'pgaudit%';

which returns an output similar to:

           name            | setting
---------------------------+---------
pgaudit.log                | none
pgaudit.log_catalog        | on
pgaudit.log_client         | off
pgaudit.log_level          | log
pgaudit.log_parameter      | off
pgaudit.log_relation       | off
pgaudit.log_rows           | off
pgaudit.log_statement      | on
pgaudit.log_statement_once | off
pgaudit.role               |
(10 rows) 

Use the configuration parameter pgaudit.log, and a comma-separated list, to specify the classes of statements that will be logged in session mode. The possible values are: READ, WRITE, FUNCTION, ROLE, DDL, MISC, MISC_SET, ALL and NONE. The default is none.
For details on each of the options, see pgaudit.log in the official pgAudit documentation.

Set pgaudit.log in postgresql.conf to apply globally, use ALTER DATABASE <your-database> SET pgaudit.log = '<your-values>' to monitor per-database, and use ALTER ROLE <your-database> SET pgaudit.log = '<your-values>' to monitor per-user.

Inspect the Postgres log files to confirm logging works as expected.

Example:

To audit all the reads, and writes, run:

postgres=# set pgaudit.log = 'read, write';

To create a separate log entry for each relation reference in a statement, use:

postgres=# set pgaudit.log_relation=on

Object Audit Logging

Object logging will audit all actions performed on a specific object, using the parameter pgaudit.role. Object mode supports SELECT, INSERT, UPDATE, and DELETE statements only.

Use object logging when you wish restrict logging to a subset of objects, and avoid monitoring all the session level information. In order to leverage specific object auditing you need to create an auditor role and grant that role on the objects you wish to audit.

Example:

To log every social security number read by anyone with the role auditor_role assigned, while avoiding logging everything with a session-level audit log, use:

CREATE ROLE auditor_role;
GRANT SELECT (social_security_number) ON payroll.employees TO auditor_role;
/* Assign auditor_role to applicable users */
SET pgaudit.role = 'auditor_role';
SET pgadut.log = 'read';

For further details and examples, see Object Audit Logging in the pgAudit documentation.

Configuring psqlODBC

You configure psqlODBC by placing settings into a configuration file, odbc.ini, and then using an ODBC driver manager such as UnixODBC or iODBC to load the configuration file. See 7.3. Configuration Files in the PostgreSQL documentation for information about the ODBC driver configuration settings.

See also psqlODBC - PostgreSQL ODBC driver for additional HOWTOs and release-specific information associated with the driver software.

Configuring pgjdbc

In order to use the pgjdbc driver from a Java application, you must include the driver software in your CLASSPATH as described in Setting up the Class Path. See also the full documentation at The PostgreSQL JDBC Interface for information about initializing and using the JDBC driver in your Java applications.