How to apply and configure pgaudit in Greenplum Database 6.25.x?

Products

VMware Tanzu Greenplum

Issue/Introduction

Recently there is a new extension, pgaudit has been added in Greenplum Database 6.25.x release. However there are no further action items which could see audit log as far as trying to setup and checking as per the following official document and pgaudit upstream page[2]. Because the location of audit-released log could not be found.

Here are step to try applying pgaudit in Greenplum Database 6.25.2.

1) Create pgaudit extension

[gpadmin@mdw ~]$ psql -d testdb
gpadmin=# create extension pgaudit;
gpadmin=# \dx
List of installed extensions
Name | Version | Schema | Description
-------------+----------+------------+----------------------------------------------------------------
pgaudit | 6.0 | public | provides auditing functionality

2) Enable the extension as a preloaded library and restart Greenplum Database.

[gpadmin@mdw ~]$ gpconfig -c shared_preload_libraries -v 'metrics_collector,pgaudit'

[gpadmin@mdw ~]$ gpstop -ar

[gpadmin@mdw ~]$ gpconfig -s shared_preload_libraries
Values on all segments are consistent
GUC : shared_preload_libraries
Master value: metrics_collector,pgaudit
Segment value: metrics_collector,pgaudit

3) Configure session audit logging

gpadmin=# ALTER DATABASE testdb set pgaudit.log='All';
gpadmin=# SELECT name,setting FROM pg_settings WHERE name LIKE 'pgaudit%';
name | setting
----------------------------+---------
pgaudit.log | all
pgaudit.log_catalog | on
pgaudit.log_client | off
pgaudit.log_level | log
pgaudit.log_parameter | off
pgaudit.log_relation | off
pgaudit.log_statement_once | off
pgaudit.role |

However there are no any log files as described at the above list of select query. How could it be seen in the certain files and where is it located?

[ Relevant Links ]
[1] https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/ref_guide-modules-pgaudit.html
[2] https://github.com/pgaudit/pgaudit/blob/master/README.md

Environment

Product Version: 6.25
OS: RHEL or CentOS 7..x

Resolution

Since pgaudit is postgresql extension it's not same way to apply for Greenplum Database. Because Greenplum Database is MPP architecture with multiple segment hosts for OLAP and it always should be changed and converted in GUCs of Greenplum.

So, firstly find the each GUCs for pgaudit extension with gpconfig command.

[gpadmin@mdw ~]$ gpconfig -l | grep pgaudit
[name: pgaudit.log] [unit: None] [context: superuser] [vartype: string] [min_val: None] [max_val: None]
[name: pgaudit.log_catalog] [unit: None] [context: superuser] [vartype: bool] [min_val: None] [max_val: None]
[name: pgaudit.log_client] [unit: None] [context: superuser] [vartype: bool] [min_val: None] [max_val: None]
[name: pgaudit.log_level] [unit: None] [context: superuser] [vartype: string] [min_val: None] [max_val: None]
[name: pgaudit.log_parameter] [unit: None] [context: superuser] [vartype: bool] [min_val: None] [max_val: None]
[name: pgaudit.log_relation] [unit: None] [context: superuser] [vartype: bool] [min_val: None] [max_val: None]
[name: pgaudit.log_statement_once] [unit: None] [context: superuser] [vartype: bool] [min_val: None] [max_val: None]
[name: pgaudit.role] [unit: None] [context: superuser] [vartype: string] [min_val: None] [max_val: None]

and then secondly try to apply one of above pgaudit.log options.

[gpadmin@mdw ~]$ gpconfig -c pgaudit.log -v all
[gpadmin@mdw ~]$ gpstop -ar

Lastly try to run queries and see what happens by pgaudit.

[gpadmin@mdw ~]$ psql
psql (9.4.26)
Type "help" for help.
 
gpadmin=# create table account
(
    id int,
    name text,
    password text,
    description text
);

You would see the following AUDIT actions in pg_log where is located in $MASTER_DATA_DIRECTORY.

2023-11-14 12:17:16.050679 KST,"gpadmin","testdb",p29182,th1742420096,"[local]",,2023-11-14 12:17:11 KST,761,con10,cmd1,seg-1,,dx4,x761,sx1,"LOG","00000","AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,""create table account
(
    id int,
    name text,
    password text,
    description text
);"",<not logged>",,,,,,"create table account
(
    id int,
    name text,
    password text,
    description text
);",0,,"pgaudit.c",839,

For more details about the settings of pgaudit, please refer the below link[1] and then let me know if you have any other questions.

[ References ]
https://github.com/pgaudit/pgaudit/blob/master/README.md#session-audit-logging