Amazon Aurora PostgreSQL Data Activity History

Ensuring security and compliance is essential for businesses managing sensitive information. Amazon Aurora PostgreSQL provides robust tools for auditing and monitoring database activity, enabling organizations to meet regulatory standards. This article explores the native Data Activity History features of Amazon Aurora PostgreSQL, with SQL examples for implementation, and discusses how DataSunrise enhances database auditing.

Native Amazon Aurora PostgreSQL Data Activity History

Overview of Data Activity History

Amazon Aurora PostgreSQL’s native Data Activity History allows businesses to track database operations in detail. This capability is vital for:

• Monitoring database access and modifications.
• Detecting unauthorized or suspicious activities.
• Maintaining compliance with regulations like GDPR,SOX,PCI DSSandHIPAA.

Aurora PostgreSQL supports two primary mechanisms for activity logging:

1. Database Activity Streams (DAS)
2. pgAudit Extension

Database Activity Streams (DAS)

Database Activity Streams provide near real-time logging of database activity in a unified JSON format. These logs can be integrated with monitoring tools for further analysis and reporting.

Key Features of DAS:

1. Real-Time Monitoring: Captures a live stream of database operations.
2. Seamless Integration: Works with AWS services and third-party tools.
3. Encryption: Secures logs with AWS Key Management Service (KMS).
4. Operational Modes:
• Asynchronous: Prioritizes performance; may skip logs during high workloads.
• Synchronous: Guarantees complete logs at the cost of performance.

Configuring DAS:

1. Prerequisites:
   
   
• Ensure IAM Database Authentication is enabled.
• Verify that the cluster uses Aurora PostgreSQL version 11 or later.


2. Enabling DAS via AWS CLI:

 aws rds modify-db-cluster \
--db-cluster-identifier <cluster-id> \
--enable-activity-stream \
--kms-key-id <kms-key-id> \
--mode <sync|async>

3. Viewing Activity Logs: Logs are stored in Amazon CloudWatch or can be exported to Amazon S3 for further processing.

pgAudit Extension

The pgAudit extension offers detailed session- and object-level activity logging directly within PostgreSQL. It complements DAS by providing granular control over which operations to log.

Configuring pgAudit:

1. Enable the Extension:

CREATE EXTENSION pgaudit;

2. Set Logging Parameters:

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

3. Example Log Query:

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

Use Cases for pgAudit:

• Auditing specific tables or users.
• Tracking DDL and DML commands for compliance.

Implementing Native Data Activity History with SQL

Connecting to the Database

Use Python’s psycopg2 library to connect to Aurora PostgreSQL:

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 Activity Logging

Enable Session-Level Logging:

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

Enable User-Level Logging:

ALTER ROLE your_role SET pgaudit.log = 'DDL, FUNCTION';

Viewing Activity History

Run the following SQL query to retrieve audit logs:

SELECT log_time, user_name, command_tag, command_text
FROM pg_catalog.pg_logs
WHERE command_tag IN ('INSERT', 'UPDATE', 'DELETE');

DataSunrise for Amazon Aurora PostgreSQL Data Activity History

Overview of DataSunrise

DataSunrise enhances database security by offering:

• Audit: Detailed activity logging.

• Security: Real-time threat detection.

• Masking: Protect sensitive data from unauthorized access.

• Data Discovery: Identify sensitive information within databases.

Setting Up DataSunrise for Aurora PostgreSQL

Configuring a DataSunrise Instance

1. Access the Web Interface:
• Navigate to the DataSunrise dashboard.
• Log in with administrative credentials.


2. Add an Aurora PostgreSQL Database:
• Enter connection details (host, port, database name, credentials).
• Configure SSL/TLS settings for secure connections.


3. Define Auditing Rules:
• Set filters for specific query types, users, or tables.

Viewing Audited Logs

Logs are accessible through the DataSunrise dashboard. Use filters to:

• Focus on specific actions (e.g., SELECT, UPDATE).
• Track user sessions and roles.
• Identify potential SQL injection attempts.

Benefits of DataSunrise

1. Centralized Control: Manage all audit policies in one place.
2. Customizable Filters: Tailor rules to organizational needs.
3. Enhanced Security: Detect threats in real time.
4. Compliance Support: Generate reports for regulatory requirements.

Conclusion

Amazon Aurora PostgreSQL offers powerful native tools like DAS and pgAudit for comprehensive Data Activity History. These tools ensure compliance, enhance security, and provide valuable insights into database operations. For organizations seeking advanced features, DataSunrise complements Aurora PostgreSQL with robust auditing, security, and data discovery capabilities. Explore DataSunrise’s offerings to elevate your database security strategy. Visit our website for an online demonstration and learn how to achieve centralized control over your database audit rules.