Do we need to set the pg_audit at the database level?

You have flexibility in how you configure pgAudit for your Amazon RDS PostgreSQL or Aurora PostgreSQL database instances. You don't necessarily need to set pgAudit parameters at the database level; you can set them in the parameter group, which applies to the entire instance.

When you set up pgAudit, you typically configure it at the instance level using a custom parameter group. This allows you to apply the audit settings consistently across all databases within that instance. Key steps include:

1. Creating the 'rds_pgaudit' role in your database.
2. Modifying your custom parameter group to include pgAudit in shared_preload_libraries.
3. Setting the pgaudit.role parameter to 'rds_pgaudit' in your custom parameter group.
4. Configuring the pgaudit.log parameter in your custom parameter group to specify what you want to audit.

These settings in the parameter group will apply to all databases in the instance by default.

However, if you need more granular control, you can indeed set pgAudit parameters at the database level. This allows you to have different audit settings for different databases within the same instance. For example, you could set the pgaudit.log parameter to 'none' at the parameter group level, and then configure specific audit settings for individual databases as needed.

It's important to note that session-level parameter settings take precedence over the settings in the custom DB parameter group. This means that if you allow users to modify pgAudit settings within their sessions, they could potentially bypass your instance-wide audit configuration.

In summary, while you can set pgAudit parameters at the database level for more fine-grained control, setting them in the parameter group is a common and efficient way to apply audit settings across your entire RDS PostgreSQL or Aurora PostgreSQL instance.
Sources
Use the pgaudit Extension to Audit an Amazon RDS DB Instance That Is Running PostgreSQL | AWS re:Post
Auditing database objects - Amazon Relational Database Service
Excluding users or databases from audit logging - Amazon Relational Database Service