AWS Marketplace

Database auditing with DataSunrise Security in AWS Marketplace

Managing multiple database types in your production environments can be difficult, and creating audit reports can be time-consuming. The job would be easier if there was a tool that could manage all database engines and create reports for you.

DataSunrise offers the ability to automate data auditing according to your policies and schedules.

Customers often choose DataSunrise to protect data against external and internal threats, as it offers dynamic and static data masking, database firewalls, sensitive data discovery and database activity monitoring. It gives unified control and a single-user experience when protecting different platforms that run on AWS. DataSunrise is compatible with Amazon Redshift, Amazon Aurora, all Amazon Relational Database Service (Amazon RDS) database engines, Amazon DynamoDB, Amazon Athena and Amazon Simple Storage Service (Amazon S3) among others.

In this post, Juston and I will show you how to make an automated data access report using the DataSunrise application for Aurora PostgreSQL-Compatible Edition.

Solution overview

Security and compliance best practices should compel you to store information about data access. Many database systems enable auditing to be configured natively. Depending on the engine, you can do this in different ways. For example, Amazon Aurora writes logs in comma-separated variable (CSV) format, Amazon RDS for Oracle uses XML format, and Amazon Redshift keeps its data compressed in Amazon S3 buckets. Each data type requires different ways of processing and analysis, and DataSunrise can audit every one of them.

The following diagram outlines the native auditing process with DataSunrise: the users’ activity gets logged in the target database, DataSunrise reads the logged data, and then stores it in the audit storage according to defined rules and filters. Administrators can then access the filtered data and create a periodic report on the necessary information.

datasunrise architecture diagram

Prerequisites

  1. You must have the DataSunrise service For this, you can either use a single Amazon Elastic Compute Cloud (Amazon EC2) instance, AWS CloudFormation with several Amazon EC2 instances, or Amazon Elastic Container Service (Amazon ECS) tasks. You can get additional information on the official DataSunrise product page, and start your two-week trial license at the DataSunrise website.
  2. You must configure your Amazon Aurora PostgreSQL-Compatible instance for native auditing. On the Amazon RDS console, from the left sidebar, select Parameter groups and then choose Create parameter group. Enter a name and select the same version of the Aurora PostgreSQL-Compatible parameter group family as your instance. Modify the following parameters in the group:

log_connections = 1

log_destination = csvlog

log_disconnections = 1

pgaudit.log = all

pgaudit.role = rds_pgaudit

shared_preload_libraries = pg_stat_statements, pgaudit

Then, attach the group to your Aurora PostgreSQL-Compatible Instance and reboot the instance for changes to take effect.

  1. You must also create a role and an extension for your instance by logging into your database and executing the following queries:

CREATE ROLE rds_pgaudit;
CREATE EXTENSION pgaudit;

  1. Make sure that your network access control list (ACL) and security groups are configured to allow the DataSunrise instance deployed in your AWS environment to connect to the Amazon Aurora cluster. Refer to the documentation about configuring network ACL and Security Groups for this step.

Solution walkthrough: Configuring Amazon Aurora PostgreSQL-Compatible in DataSunrise

To be able to audit and filter native logs of the database with DataSunrise, you must register the Amazon Aurora PostgreSQL-Compatible instance in DataSunrise and configure the audit rule. To do that, follow these steps:

1. Adding the instance

To be able to choose what information you want to gather and monitor, add the Amazon Aurora PostgreSQL-Compatible instance to your DataSunrise console. To do that, follow these steps:

  • Access your DataSunrise web console on port 11000 of your DataSunrise server or load balancer using HTTPS protocol (e. g. https://<server_ip>:11000) and sign in.
  • On the left sidebar, select Configuration, and then Databases. Select + Add Database.
  • On this page, register a new database and complete the fields according to your database’s connection setup. The required fields in this step are hostname, login, and password. DataSunrise uses this information for access to metadata about schemas, tables, and other information that need for processing and handling audit data.
  • In the Capture Mode menu, choose Trailing DB Audit Logs mode and fill out the required fields. In this section, you must configure specific information that DataSunrise needs to access the database’s native audit logs. Aurora Postgres logs are available via AWS APIs, so you must specify Database Identifier (Aurora Cluster ID), Authentication Method, as well as Access and Secret Keys.
  • Once you select Test and see a message that the connection test is successful, you can proceed to the creation of the Audit Report.

2. Audit rule creation

When you add a database instance, DataSunrise starts reading database logs. You must then configure rules and choose which exact events you want to store in DataSunrise’s internal audit storage. You can choose to audit certain query types, sensitive data access, or a specific user’s activity. To do this, follow these steps in your DataSunrise web console:

  • From the left sidebar, select Audit and then Rules, then select + Add Rule. You can customize the rule according to your needs and policies. In this case, you will create a rule that will audit information about all Select and Data Manipulation Language (DML) events.
  • In the Instance drop-down menu, choose your instance and enable Log Event in Storage. Choose Filter Statements and then Query Types. Choose Select Query Type and in the modal window, select the Select, Insert, Update and Delete
  • Add any other parameters that are required by your policies. After finishing the configuration, Save the rule.
  • To check if the rule has been configured, connect to your Amazon Aurora PostgreSQL-Compatible and perform several access queries. To confirm that the connections have been logged, navigate to the left sidebar, select Audit, and then Transactional Trails. It should show the ID, Rule, Query, Rows, Error, Query Type, and Starting Time for all audited actions. The following screenshot shows the DataSunrise Audit page with a one-day date filter applied. Columns show ID, Query, Rows, Error, Query Type, and Starting Time. The page shows eight IDs for the Audit All Access rule, all with no errors.

confirm connections logged DataSunrise screenshot

3. Report task creation

Once you have created all the necessary rules for auditing, you can create an audit report. There are many types of reports available to configure, but for this post, select the Audit report type. To do that, perform the following steps:

  • In the Sunrise left sidebar, navigate to Reporting and select Report Gen, and then select New Task.
  • Choose the report file type (CSV or PDF), its view, and its frequency. Select the audit rule you created in step 2.
  • Configure the rest of the parameters according to your requirements.
  • After the set time or manual execution of the task, view and download the generated report inside of the task in the Reports section.

The resulting report will contain the grouped data in the format you have configured, and you can send it to the required recipients.

Conclusion

In this post, Juston and I showed you how to set up native auditing for Aurora PostgreSQL-Compatible. Using DataSunrise, Juston and I showed how to make an automated report on data access for easier database auditing.

Cleanup

While there are no additional charges for auditing in Amazon Aurora PostgreSQL-Compatible instance, it may still create unnecessary load on your database. Once you are done with the testing, switch the parameter group of your RDS back to default, reboot it and stop the DataSunrise instance. When using other types of databases, keep in mind how they store audit data; using S3 buckets may result in additional charges which need to be accounted for.

Next steps

To find out how DataSunrise can help you audit and protect your databases and data hosted on AWS and to continue to explore the DataSunrise Solution, visit the DataSunrise page in AWS Marketplace.

About the authors

Radik_ChumarenRadik Chumaren is an engineering leader at DataSunrise. Radik is specializing in heterogeneous database environments with focus on building database security software in the cloud. He enjoys reading and playing soccer.

.

.

Juston Salcido is a Technical Business Development Manager focusing on helping independent software vendors (ISVs) understand how to list their solutions in AWS Marketplace. In his role, he provides ISVs with the best practices to use their current solution, pricing, and sales motions to build a successful listing in AWS Marketplace. Juston has over 10 years of experience working in the technology industry and 6 years of experience working at Amazon, and over 4 years have been spent working in AWS Marketplace. Juston is located in Bozeman, MT, and enjoys golfing, playing softball, and traveling to compete in amateur billiards competitions.