Automate PostgreSQL audit log extraction and analysis with Amazon S3

This article demonstrates how to automate PostgreSQL audit log extraction from CloudWatch Logs, convert them to structured CSV format, and store them in Amazon S3 using an event-driven Lambda pipeline.

• Deploy an automated pipeline using CloudFormation that extracts pgAudit logs from CloudWatch Logs in near real time
• Convert unstructured audit logs to structured CSV format with fields like timestamp, user, database, action, object, and query
• Store logs in S3 with date-partitioned organization and lifecycle policies for cost optimization
• Enable optional ad-hoc SQL queries on audit logs using Amazon Athena without additional infrastructure
• Includes security considerations for handling sensitive data like PII in query parameters stored in S3
• Solution designed for single database cluster; multiple databases require separate CloudFormation stacks

The solution eliminates manual audit log review bottlenecks for RDS PostgreSQL and Aurora PostgreSQL users by automating extraction, structuring, and long-term retention of audit data.