Amazon Aurora PostgreSQL Audit Trail

The rapid growth of data and increasing regulatory demands have made database auditing a critical aspect of ensuring compliance and securing sensitive data. Amazon Aurora PostgreSQL-Compatible Edition offers advanced tools for database auditing to address these needs effectively. This article delves into the native Amazon Aurora PostgreSQL audit trail features and highlights the added advantages of using third-party solutions like DataSunrise for enhanced auditing and security.

Native Amazon Aurora PostgreSQL Audit Trail

Amazon Aurora PostgreSQL offers two main options for auditing: Database Activity Streams and the pgAudit extension. These methods provide detailed insights into database activity, enabling organizations to monitor, record, and analyze sensitive data usage.

Database Activity Streams

The Database Activity Streams (DAS) feature in Aurora allows you to capture a near-real-time stream of database activities. This stream integrates with AWS-native or third-party monitoring tools, providing robust auditing capabilities.

Key Features:

• Detailed Audit Data: Captures a complete set of database activities.
• Real-Time Monitoring: Provides near-real-time activity data for compliance and security.
• Separation of Duties: Ensures security personnel and DBAs have distinct responsibilities.
• Secure Storage: Audit logs are encrypted using AWS Key Management Service (KMS).
• Flexible Modes: Supports both synchronous (ensures durability) and asynchronous (prioritizes performance) modes.

Example Setup:

1. Enable Database Activity Streams in the AWS Management Console:

• Navigate to your Aurora cluster.
• Under Logs and Events, enable Database Activity Streams.


2. Configure Storage: Store the audit stream in Amazon S3 or use a monitoring tool.
   
   
3. Query Logs:
   
   
• Use AWS CloudWatch or a custom query parser to analyze the JSON-formatted logs.

Considerations:

• Performance Impact: Synchronous mode may slightly reduce database performance.
• Cost: Audit logs stored in Amazon S3 incur storage charges. Consider optimizing storage with formats like Apache Parquet.

pgAudit Extension

The pgAudit extension provides detailed session and object audit logging within PostgreSQL. This extension focuses on recording specific user activities and SQL statements, offering granular control over audit logging.

Key Features:

• Logs detailed user activities, including SELECT, INSERT, UPDATE, DELETE, and DDL commands.
• Supports instance-level, user-level, and database-level configurations.
• Compatible with standard PostgreSQL logging facilities.

Example Setup:

Install pgAudit:



 CREATE EXTENSION pgaudit;

1. Configure Parameters: Update the parameter group associated with your Aurora cluster to include the following:
   

   shared_preload_libraries = 'pgaudit'
   pgaudit.log = 'READ, WRITE, DDL'

2.  Restart the database to apply changes.
   
   

Enable Auditing at Database Level:



ALTER DATABASE mydb SET pgaudit.log = 'ALL';

3. View Logs: Access the PostgreSQL logs via the AWS RDS console or CloudWatch Logs to review audited activities.
   
   

Example Query:

SELECT * FROM pg_stat_activity WHERE state = 'active';

This query identifies active database sessions, providing insights into current activity.

Native Audit Results

Once auditing is configured, you can retrieve logs from:

• CloudWatch Logs: Use the AWS Console or CLI tools.
• PostgreSQL Logs: Download and parse logs for detailed analysis.

Audit records include details like session ID, statement executed, object type, and user details, helping ensure compliance with regulations like GDPR,SOX,PCI DSSand HIPAA.

DataSunrise for Enhanced Amazon Aurora PostgreSQL Audit Trail

DataSunrise offers an advanced security suite that complements native Aurora PostgreSQL auditing capabilities. With centralized control over Audit Trail rules and robust compliance tools, DataSunrise simplifies complex auditing requirements.

Benefits of DataSunrise:

• Centralized Management: Uniformly manage audit rules across multiple databases.
• Enhanced Security: Detect and block unauthorized access in real time.
• Compliance Reporting: Generate detailed reports for audits and regulatory requirements.

Configuring DataSunrise Audit Trail

1. Create a DataSunrise Instance:
   
   
• Install and configure DataSunrise on a dedicated server.
• Connect the tool to your Amazon Aurora PostgreSQL database.

2. Enable Auditing:
   
   
• Use the DataSunrise audit page to define audit rules based on user roles, SQL commands, or object types.

3. View Audit Logs:
   
   
• Access audit logs directly from the DataSunrise interface.
• Export logs for compliance reporting or further analysis.

Example Use Case:

DataSunrise can detect unauthorized SELECT statements on sensitive tables like employees or finance and trigger real-time alerts to security teams.

Conclusion

Auditing Amazon Aurora PostgreSQL databases is essential for ensuring compliance and protecting sensitive data. The native auditing tools—Database Activity Streams and pgAudit—offer robust solutions tailored to different use cases. For organizations seeking enhanced security and centralized control, tools like DataSunrise provide powerful capabilities that go beyond native features.

Explore DataSunrise’s robust tools for database security and auditing by visiting our website for an online demonstration.