Configuring pgAudit for Effective Database Auditing

In today’s highly regulated and security-conscious world, database auditing has become a critical requirement for monitoring database activity, ensuring compliance, and preventing malicious activity. PostgreSQL, one of the most powerful open-source relational databases, provides a robust auditing mechanism through the pgAudit extension.

In our previous blog, we explored the importance of this extension and its installation. In this blog, we’ll explore some of the configuring parameters of the pgAudit extension effectively to achieve secure and comprehensive auditing in your PostgreSQL environment.

Understanding some of the parameters

pgaudit.log

The pgaudit.log setting in postgresql.conf specifies what kinds of SQL statements should be logged by pgAudit. It’s a flexible configuration option that lets you filter what gets logged, making it easier to manage log volumes and ensure you’re capturing the right activities for your audit trail.

The general format for configuring pgaudit.log is:

pgaudit.log = 'option1, option2, ...'
#If you wanna use the alter command
ALTER SYSTEM SET pgaudit.log TO 'option1,option2,...';

Where each option specifies a category of PostgreSQL operations to be logged. Multiple classes can be provided using a comma-separated list and classes can be subtracted by prefacing the class with a - sign . The default is none. To check the option available , please check the previous blog

For example,

# Enabling one option
postgres=# ALTER SYSTEM SET pgaudit.log TO 'WRITE';
ALTER SYSTEM
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)
postgres=# SHOW pgaudit.log;
 pgaudit.log
-------------
 WRITE
(1 row)

# Enabling multiple options
postgres=# ALTER SYSTEM SET pgaudit.log TO 'WRITE,READ';
ALTER SYSTEM
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)
postgres=# show pgaudit.log;
 pgaudit.log
-------------
 WRITE,READ
(1 row)

# Excluding an option
postgres=# ALTER SYSTEM SET pgaudit.log TO 'ALL,-MISC';
ALTER SYSTEM
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)
postgres=# SHOW pgaudit.log;
 pgaudit.log
-------------
 ALL,-MISC
(1 row)

pgaudit.log_catalog

The pgaudit.log_catalog parameter in pgAudit controls whether or not catalog tables (i.e., system tables) are logged in audit logs. The default is on

There are both pro’s and con’s enabling this parameter. Here are some

• Enabling this parameter
  • Pro’s
    • Detailed Audit trail of System Activity.
    • Compliance and Security.
    • Prevents unauthorized access/usage
  • Con’s
    • Increase in log volumes
    • Performance overhead

Enabling or disabling this option is purely based on the Organization requirements. If you want to disable this option , here is the command

postgres=# ALTER SYSTEM SET pgaudit.log_catalog TO 'off';
ALTER SYSTEM
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)
postgres=# SHOW pgaudit.log_catalog;
 pgaudit.log_catalog
---------------------
 off
(1 row)

pgaudit.log_client & pgaudit.log_level

pgaudit.log_client is used to audit the client connection such as psql. Default is off

The pgaudit.log_level parameter determines the level of logging detail for pgAudit operations. It controls the verbosity of the audit logs. Essentially, it allows you to adjust how much information is recorded in the PostgreSQL logs when using pgAudit. pgaudit.log_level is only enabled when pgaudit.log_client is on; otherwise the default will be used i.e., log .

Severity	Usage	syslog	eventlog
DEBUG1 .. DEBUG5	Provides successively-more-detailed information for use by developers.	DEBUG	INFORMATION
INFO	Provides information implicitly requested by the user, e.g., output from VACUUM VERBOSE.	INFO	INFORMATION
NOTICE	Provides information that might be helpful to users, e.g., notice of truncation of long identifiers.	NOTICE	INFORMATION
WARNING	Provides warnings of likely problems, e.g., COMMIT outside a transaction block.	NOTICE	WARNING
LOG	Reports information of interest to administrators, e.g., checkpoint activity.	INFO	INFORMATION

There are a lot more parameters in pgAudit to look at, but focusing on key settings like pgaudit.log, pgaudit.log_catalog, pgaudit.log_client, and pgaudit.log_level will help you establish a solid auditing foundation. As your auditing needs evolve, you can explore additional parameters to fine-tune your logging configuration. Always remember to balance detailed auditing with performance considerations, especially in production environments. If required, feel free to take our help in optimizing and configuring your pgAudit setup to best suit your needs.

Conclusion

In conclusion, the pgAudit extension provides a powerful and flexible mechanism for auditing PostgreSQL database activity. By configuring key parameters such as pgaudit.log, pgaudit.log_catalog, pgaudit.log_client, and pgaudit.log_level, you can tailor your auditing to meet both security and compliance needs.

Choosing the right logging options, whether you’re tracking write/read operations, system table access, or client connections, is crucial to ensuring an effective and manageable audit trail. While enabling comprehensive auditing may lead to increased log volumes and some performance overhead, it significantly enhances your ability to monitor, detect, and prevent unauthorized activity in your PostgreSQL environment.

By carefully balancing auditing granularity and performance, you can establish a robust auditing strategy that aligns with your organization’s security policies and regulatory requirements, providing better visibility and control over your database operations.