How to apply and configure pgaudit in Greenplum Database

Products

VMware Tanzu Greenplum VMware Tanzu Greenplum / Gemfire VMware Tanzu Data Suite VMware Tanzu Data Suite

Issue/Introduction

The extension "pgaudit" was added to Greenplum Database 6.25.0 release.

Here are step to try applying pgaudit in Greenplum Database

1) Create pgaudit extension

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

2) Specify the required library in the shared_preload_libraries GUC and restart Greenplum Database.

# Display the current setting
[gpadmin@mdw ~]$ gpconfig -s shared_preload_libraries
Values on all segments are consistent
GUC : shared_preload_libraries
Master value: metrics_collector
Segment value: metrics_collector

# Add "pgaudit" to the list displayed in previous command
[gpadmin@mdw ~]$ gpconfig -c shared_preload_libraries -v 'metrics_collector,pgaudit'

# Restart the database to load the library
[gpadmin@mdw ~]$ gpstop -arf

# Display the new setting of the GUC
[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                |

Relevant Links

Broadcom documentation for pgaudit
pgaudit github documentation

Resolution

List the 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]

To change one of above settings, example pgaudit.log:

[gpadmin@mdw ~]$ gpconfig -c pgaudit.log -v all
[gpadmin@mdw ~]$ gpstop -ar # This will restart the DB

Run queries to verify pgaudit logs auditing information:

[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 github documentation