PostgreSQL Security Features: Episode 4 - Auditing

Database​ auditing​ allows database administrators​, auditors, and operators to track and analyze database activities in support of complex auditing requirements. These audited activities include database access and usage along with data creation, change, or deletion.

It’s recommended that you audit the following items:
- User connections
- DDL changes
- Data changes
- Data views

Highly detailed levels of scrutiny can result in a lot of log messages—so log only at the level you need. Obviously you need to review your audit logs frequently for anomalous behavior, and establish a chain of custody for your logs.

Now beware… In open source PostgreSQL​, a high logging level, combined with storage of passwords in the database, can result in passwords being displayed in the logs. EDB​ Postgres Advanced Server has advanced logging capabilities that allow you to redact passwords in the audit log.

With Postgres, you can adjust logging levels on a per-user and per-database basis. In EDB Postgres Advanced Server, you can also control logging at the object type level, such as tables, views, triggers, function, etc.