Aurora PostgreSQL Audit Log

Introduction

In today's data-driven landscape, properly configured audit log for Amazon Aurora PostgreSQL is essential for security, compliance, and operational oversight. According to recent cybersecurity statistics, database intrusions and unauthorized access remain significant threats across industries, making robust audit logging a critical component of your database security strategy.

This guide explores the various methods to implement and manage Amazon Aurora PostgreSQL audit logs, covering native PostgreSQL logging, pgAudit extension, Database Activity Streams, and DataSunrise – our solution for security and compliance.

Native Aurora PostgreSQL Audit Log

Overview

Aurora PostgreSQL includes built-in logging capabilities inherited from the core PostgreSQL engine. These logs capture various database events including connections, disconnections, errors, slow queries, and more.

Configuration Parameters

Key parameters for configuring Aurora PostgreSQL logs include:

-- Enable connection logging
log_connections = on

-- Enable disconnection logging
log_disconnections = on

-- Log all statements
log_statement = 'all'  -- Options: none, ddl, mod, all

-- Log duration of statements
log_duration = on

-- Log statements that take longer than specified milliseconds
log_min_duration_statement = 1000  -- Log statements running longer than 1 second

You can configure these parameters in the AWS RDS Parameter Group associated with your Aurora PostgreSQL cluster.

Accessing Native Logs

Aurora PostgreSQL logs are accessible through:

1. AWS Management Console:

   • Navigate to Amazon RDS > Databases > Your Aurora Cluster
   • Select the primary instance > Logs & events tab

2. AWS CLI:

   aws rds download-db-log-file-portion --db-instance-identifier your-instance-id --log-file-name postgresql.log
   

3. CloudWatch Logs (if configured):

   • Navigate to CloudWatch > Log groups > /aws/rds/instance/your-instance-id/postgresql

pgAudit Extension

Overview

The PostgreSQL Audit Extension (pgAudit) provides more detailed audit logging than native PostgreSQL logging. It allows for granular control over which database activities are logged, making it ideal for compliance requirements.

As detailed in the AWS Database Blog, pgAudit offers significantly enhanced logging capabilities.

Implementation Steps

1. Enable pgAudit in Parameter Group:

   shared_preload_libraries = 'pgaudit'
   

2. Create the Extension:

   CREATE EXTENSION pgaudit;
   

3. Configure Audit Logging:

   You can configure pgAudit at different levels:

   • Instance Level:

     pgaudit.log = 'ALL'  -- In parameter group
     

   • Database Level:

     ALTER DATABASE your_database SET pgaudit.log = 'DDL,ROLE,WRITE';
     

   • User Level:

     ALTER ROLE your_role SET pgaudit.log = 'READ,WRITE';
     

4. Session vs. Object Logging:

   • Session logging audits all statements executed by a user
   • Object logging audits operations on specific objects

   -- Enable object logging
   pgaudit.role = 'auditor'  -- In parameter group
   
   -- Grant privileges to audit role
   GRANT SELECT ON my_table TO auditor;
   

Audit Classes

pgAudit logs can be configured to include various classes of operations:

Class	Description	Example Commands
READ	Read operations	SELECT, COPY FROM
WRITE	Write operations	INSERT, UPDATE, DELETE, TRUNCATE
FUNCTION	Function calls	SELECT my_function()
ROLE	Role and privilege operations	GRANT, REVOKE, CREATE ROLE
DDL	Data definition language	CREATE, ALTER, DROP
MISC	Miscellaneous commands	VACUUM, ANALYZE
ALL	All of the above	All commands

AWS Database Activity Streams

Overview

AWS Database Activity Streams provide a near real-time stream of database activity that can be integrated with security monitoring and auditing tools.

Unlike log files, activity streams:

• Operate independently of the database engine
• Cannot be disabled by database users
• Are encrypted using AWS KMS
• Can be processed in real-time

Implementation Steps

1. Prerequisites:

   • Configure network prerequisites
   • Set up AWS KMS key

2. Enable Activity Streams:

   • Via Console: RDS > Databases > Your Aurora Cluster > Actions > Start database activity stream
   • Select encryption settings and mode (async/sync)

3. Configure Stream Processing:

   • Set up Amazon Kinesis Data Stream consumer
   • Process with AWS Lambda, Amazon Data Firehose, etc.
   • Store in Amazon S3 or other destinations

4. Monitor and Alert:

   • Create CloudWatch alarms
   • Set up automated notifications via SNS

Implementation Example

For a detailed implementation example, refer to AWS's step-by-step guide which covers:

• Creating AWS Lambda functions to process streams
• Setting up alerts and notifications
• Integrating with security information and event management (SIEM) systems

DataSunrise: Enhanced Audit Solution for Aurora PostgreSQL

For organizations requiring advanced audit capabilities, DataSunrise offers enhanced auditing features for Aurora PostgreSQL.

Key Features

• Centralized audit management
• Real-time monitoring and alerts
• Compliance reporting for regulations like GDPR, HIPAA, PCI DSS
• User behavior analysis
• Advanced filtering and rule-based auditing

Implementation Steps

1. Connect to Aurora PostgreSQL

1. Configure Audit Rules

1. View Audit Logs

For detailed setup instructions, visit DataSunrise's Audit Guide.

Comparison of Audit Solutions

Feature	Native Logging	pgAudit	Database Activity Streams	DataSunrise
Setup Complexity	Low	Low	Medium	Medium
Detail Level	Basic	High	High	Very High
Performance Impact	Low-Medium	Low-Medium	Low (Async)	Low
Real-time Monitoring	No	No	Yes	Yes
Separation of Duties	No	No	Yes	Yes
Compliance Reporting	Manual	Manual	Manual	Automated
Cost	Free	Free	AWS Service Costs	License

Best Practices for Aurora PostgreSQL Audit Log

1. Define Audit Goals – Identify compliance requirements for your data systems. Determine essential security events to monitor. Establish key operational metrics that need tracking.

2. Implement Defense in Depth – Deploy multiple audit mechanisms for comprehensive coverage. Create clear separation of duties between system roles. Protect audit logs from tampering using secure storage methods.

3. Optimize Performance – Monitor how logging impacts system speed. Adjust logging detail levels as needed. Implement rotation and archiving to manage log volumes.

4. Review and Alert – Regularly examine logs for unusual patterns. Set up automated alerts for suspicious activities. Create dashboards to visualize key security metrics.

5. Storage and Retention – Define how long audit data must be kept. Implement tamper-evident storage solutions. Balance compliance requirements with practical storage limitations.

Conclusion

Effective audit logging for Aurora PostgreSQL requires a well-structured approach that ensures security, compliance, and operational efficiency. By leveraging PostgreSQL’s built-in logging, the pgAudit extension, AWS Database Activity Streams, and advanced third-party solutions like DataSunrise, organizations can establish a robust audit framework.

Whether your priority is compliance, security monitoring, or gaining deeper operational insights, DataSunrise provides the visibility and control needed to safeguard your Aurora PostgreSQL environment. Request a demo today to see how DataSunrise can enhance your database auditing and security.