VMware Smart Assurance NCM: How to enable debug in PostgreSQL DB without restarting the service?

Products

VMware

Issue/Introduction

Symptoms:
This article aims to provide a procedure to enable debug in PostgreSQL without restarting the controldb service.

Environment

VMware Smart Assurance - NCM

Resolution

In NCM 9.x and 10.1 versions:

Login to the Combination Server / Database Server and follow below instructions:

Edit $VOYENCE_HOME/db/controldb/data/postgresql.conf
Uncomment log_statement and change the line from

#log_statement = 'none'

To:

log_statement = 'all'

Login to the Database server with following command: su - pgdba -c 'psql voyencedb voyence'. Enter database password when prompted.
Execute below query after logging into the postgresql DB:
SELECT pg_reload_conf();

Debug entries will be written to $VOYENCE_HOME/db/controldb/logs/server.postmaster file.

In NCM 10.1.1 and above versions:

Login to the Combination Server / Database Server and follow below instructions:

Edit $VOYENCE_HOME/db/controldb/data/postgresql.conf
Change the value of log_statement from

log_statement = 'ddl'

To:

log_statement = 'all'

Login to the Database server with following command: su - pgdba -c 'psql voyencedb voyence'
Enter database password when prompted.
Execute below query after logging into the postgresql DB:
SELECT pg_reload_conf();

Debug entries will be written to /var/log/messages file. To address the postgreSQL STIG issue V-72909, DB debug logging file destination has been changed. Refer pg.41 of https://docs.vmware.com/en/VMware-Smart-Assurance/10.1.1/ncm-security-configuration-guide-10.1.1.pdf for details.

Example of successful query execution:

voyencedb=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
voyencedb=#

Example of debug messages in the log file after setting all value for log_statement:

2016-10-05 13:53:04.656 GMT-10 - SessionId:57e23baf.3a72 - TransId:0 - LOG: execute S_6: select encode(cm_config_unit_revision.revision_id,'hex'), encode(cm_config_unit_revision.device_id,'hex'), cm_config_unit_revision.revision_number,cm_config_unit_revision.name,package_identifier from cm_device,cm_config_unit_revision left join cm_config_diff on (cm_config_diff.revision_id = cm_config_unit_revision.revision_id),cm_config_file where cm_config_unit_revision.revision_id = cm_config_file.revision_id AND cm_config_diff.revision_id is null AND cm_config_unit_revision.revision_number > 1 AND cm_config_unit_revision.device_id = cm_device.device_id LIMIT $1
2016-10-05 13:53:04.656 GMT-10 - SessionId:57e23baf.3a72 - TransId:0 - DETAIL: parameters: $1 = '500'
2016-10-05 13:53:04.657 GMT-10 - SessionId:57e23baf.3a72 - TransId:0 - LOG: execute S_2: ROLLBACK
2016-10-05 13:53:04.657 GMT-10 - SessionId:57e218e1.16af - TransId:0 - LOG: execute S_1: BEGIN
2016-10-05 13:53:04.657 GMT-10 - SessionId:57e218e1.16af - TransId:0 - LOG: execute S_5: SELECT encode(cm_device_audit_history.history_id,'hex') from cm_device_audit_history join (select config_state_id, max(compliance_audit_time) as enforcement_time from cm_device_audit_history group by config_state_id)A on (cm_device_audit_history.compliance_audit_time = A.enforcement_time AND cm_device_audit_history.config_state_id = A.config_state_id) left join cm_rpt_enforcement_trail on (cm_device_audit_history.history_id = cm_rpt_enforcement_trail.history_id) where cm_rpt_enforcement_trail.history_id is null LIMIT $1
2016-10-05 13:53:04.657 GMT-10 - SessionId:57e218e1.16af - TransId:0 - DETAIL: parameters: $1 = '500'

Additional Information

Refer postgresql documentation at: https://www.postgresql.org/docs/11/runtime-config-logging.html
Abstract from the reference documentation link above:

Refer postgresql documentation at: https://www.postgresql.org/docs/11/functions-admin.html
Abstract from the reference documentation link above:

Impact/Risks:
Enable the postgresql debug when needed to debug an issue with database and disable it after capturing the required logs. Leaving the DB debug on would increase the log file size quickly.