Which is better for auditing purpose pg_audit or log_statement?

For auditing purposes in PostgreSQL RDS, pgAudit (PostgreSQL Audit extension) is generally considered better than using log_statement alone. Here's why:

1. Detailed logging: pgAudit provides more comprehensive and detailed logs compared to log_statement. It captures specific information about database activities, including changes made to databases and tables, the user who made the change, and other important details.

2. Regulatory compliance: pgAudit is specifically designed to meet regulatory requirements for financial institutions, government agencies, and industries that need to maintain detailed audit logs. It offers the level of detail typically needed by auditors.

3. Flexibility: pgAudit allows you to configure auditing with more specificity. You can audit all databases and users, or selectively audit certain objects while excluding others.

4. Security: pgAudit automatically redacts sensitive data like cleartext passwords from the logs, enhancing security.

5. Extended functionality: pgAudit builds upon and extends the native PostgreSQL logging infrastructure, providing more robust auditing capabilities.

While log_statement can be useful for basic query logging, it has limitations:

1. Less detail: log_statement primarily logs SQL statements and doesn't provide the same level of contextual information as pgAudit.

2. Security concerns: When using log_statement, you need to take additional steps to prevent exposure of sensitive information like passwords in log files.

3. Limited configurability: log_statement offers fewer options for fine-tuning what gets logged compared to pgAudit.

In summary, while both tools have their uses, pgAudit is the preferred choice for comprehensive auditing in PostgreSQL RDS, especially when detailed logging and regulatory compliance are important considerations.
Sources
Using pgAudit to log database activity - Amazon Relational Database Service
Using pgAudit to log database activity - Amazon Aurora
Turning on query logging for your RDS for PostgreSQL DB instance - Amazon Relational Database Service

Based on the question you asked about if you need to set the pg_audit at the database level?

Yes, when using pgAudit (PostgreSQL Audit extension) for auditing in a PostgreSQL RDS instance, you typically need to configure it at the database level because It allows fine-grained control over what types of actions are logged and provides detailed auditing for regulatory compliance or troubleshooting