VMware Smart Assurance NCM: ERROR - canceling statement due to statement timeout

Products

• VMware

Issue/Introduction

Symptoms:
ERROR "canceling statement due to statement timeout" is thrown in NCM 10.1.x deployment during database maintenance operations such as database-utility.pl execution, VACUUM FULL, REINDEX TABLE, ANALYZE operations execution.

Environment

VMware Smart Assurance - NCM

Cause

In NCM 10.1.1 version release, the parameter 'statement_timeout' was set as 10s in postgres.conf file to invalidate session upon user logout or other session termination, as part of PostgreSQL STIG hardening reported issues & subsequent fixes (V-73037). It is documented in NCM 10.1.1 Security Configuration Guide.

Resolution

Users are recommended to disable the "statement_timeout" parameter in $VOYENCE_HOME/db/controldb/data/postgresql.conf file in NCM 10.1.x database server or combination server prior to performing database maintenance activity. Steps to disable "statement_timeout" parameter is detailed as follows-

1. Open $VOYENCE_HOME/db/controldb/data/postgresql.conf file and change existing entry of "statement_timeout = 10000" to "statement_timeout = 0", save and exit the file. 
2. Execute command "service controldb restart" for the changes to be taken into effect or refer KB article https://kb.vmware.com/s/article/491405?lang=en_US for instructions on reloading postgresql.conf file without database restart.

Additional Information

Impact/Risks:
The error is automatically thrown interrupting database query execution during maintenance activity when large number of datasets are extracted from postgres DB, exceeding the 10s duration and resulting query execution timeout.