Amazon Aurora PostgreSQL Data Audit Trail

The rapid growth of data has heightened the importance of compliance and protecting sensitive information. Amazon Aurora PostgreSQL addresses these challenges by offering advanced Data Audit Trail solutions, enabling organizations to monitor and safeguard database activity while adhering to regulatory standards. In this article, we delve into Aurora PostgreSQL’s native audit trail capabilities and illustrate how to use query language features for comprehensive auditing. We’ll also examine how DataSunrise expands these capabilities with additional tools and features for enhanced data security.

Native Amazon Aurora PostgreSQL Data Audit Trail

Overview of Database Auditing

Database auditing helps organizations ensure compliance with regulations like GDPR,SOX,PCI DSS and HIPAA. Key auditing goals include:

• Monitoring and logging database activity.
• Storing audit logs securely.
• Detecting suspicious behavior.
• Generating alerts and reports.

Amazon Aurora PostgreSQL supports two primary auditing mechanisms: Database Activity Streams and the pgAudit extension.

Database Activity Streams

Database Activity Streams capture near real-time database activity and provide detailed logs in JSON format. These logs can integrate with monitoring tools for further analysis. Key features include:

1. Modes of Operation:
• Asynchronous: Prioritizes database performance but may skip some log records.
• Synchronous: Ensures complete logging but may impact performance.


2. Integration: Works seamlessly with AWS and third-party tools.
3. Encryption: Secures logs using AWS Key Management Service (KMS).

Example Setup

To enable Database Activity Streams:

1. Open the Amazon RDS Console.

2. Select your Aurora PostgreSQL cluster.
3. Navigate to the Logs and events section.
4. Enable Database Activity Streams.

pgAudit Extension

The pgAudit extension enhances PostgreSQL’s native logging by capturing detailed session and object-level activities. It logs statements like SELECT, INSERT, and DDL commands, ensuring comprehensive auditing.

Configuring pgAudit

1. Enable the pgAudit extension in your Aurora PostgreSQL instance.
2. Configure parameters in the DB parameter group:

ALTER SYSTEM SET pgaudit.log = 'ALL';
SELECT pg_reload_conf();

3. Restart the instance to apply changes.

Viewing Logs

Audit logs are stored in PostgreSQL’s standard logging facility. Use the following query to retrieve logs:

SELECT * FROM pg_catalog.pg_logs WHERE log_line LIKE '%AUDIT%';

Implementing Native Audit Trail with SQL

Connecting to Aurora PostgreSQL

Use the following Python script to connect to your database:

import psycopg2
conn = psycopg2.connect(
dbname="your_db_name",
user="your_username",
password="your_password",
host="your_host",
port="your_port"
)
cursor = conn.cursor()

Setting Up an Audit Trail

Enable audit logging for specific tables or users:

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

Viewing Audit Results

Run a query to fetch audit logs from the database:

SELECT log_time, user_name, query_text FROM pg_catalog.pg_logs WHERE log_severity = 'AUDIT';

DataSunrise’s Amazon Aurora PostgreSQL Data Audit Trail

Overview of DataSunrise

DataSunrise offers advanced database security, including:

• Auditing.
• Masking sensitive data.
• Real-time threat detection.

Setting Up DataSunrise for Aurora PostgreSQL

Creating a DataSunrise Instance

1. Open the DataSunrise web interface.
2. Configure your Aurora PostgreSQL instance:
• Enter database credentials.
• Specify the target database.


3. Set up auditing policies.

Viewing Audited Logs

Logs are accessible in the DataSunrise dashboard. Use filters to refine results by:

1. Object Group: Filter logs by database objects.

2. Query Group: Focus on specific query types.

3. Query Type: Isolate SQL injection attempts.

4. SQL Injection: filter log by kind of sql injection.

5. Session Events: Monitor user sessions and login/logout activities.

Benefits of Using DataSunrise

1. Centralized Control: Manage all audit rules from a single interface.
2. Customizable Filters: Tailor auditing policies to your organization’s needs.
3. Enhanced Security: Detect and block SQL injection attempts.
4. Compliance Readiness: Generate reports to meet regulatory requirements.

Conclusion

Amazon Aurora PostgreSQL provides powerful native auditing tools through Database Activity Streams and pgAudit. These features ensure compliance and data security. For advanced requirements, DataSunrise enhances auditing with robust filtering, centralized control, and real-time monitoring. Explore DataSunrise’s security suite to optimize your database’s audit trail capabilities. Visit our website for an online demonstration and discover how our tools can transform your database security strategy.