Configure DB Audit Trailing for MS Azure PostgreSQL
Along with other functionalities, DataSunrise features a dedicated auditing tool, the DB Audit Trailing, based on usage of database native auditing tools and mechanisms. In this article we explain how to configure DataSunrise and your PostgreSQL database hosted on MS Azure to do DB Audit Trailing.
Note that this guide describes all the actions required for DB Audit Trailing to work from the very beginning. Let’s assume that you don’t have neither a database nor the corresponding Azure resources. To follow the steps of this guide you need only DataSunrise installed on your machine and access to Azure portal.
Log into Azure Portal
Log into Azure portal. If you’re not acquainted with Azure’s interface, you might find it useful to use the search field located at the top of the screen (“Search resources, services, and docs”).
Registration of an Azure Application
DataSunrise will use this application to download your PostgreSQL’s log files out of the Blob container associated with your Storage account. Follow the steps below:
1. Navigate to App registrations -> New registration and register your application. Note that Redirect URI (optional) is not required.
2. Grant your App the permissions to access Blob containers.
At the API permissions page, click Add a permission
In the Microsoft APIs tab, select Azure Storage then select Delegated permissions and user_impersonation:
Click Add permissions to apply the changes
3. Create a Secret for your App so Azure could identify your App.
- Navigate to Certificates and secrets
- At Client secrets, click New client secret to create a new secret. SAVE THE SECRET VALUE SOMEWHERE: you will need it later.
Creation of a Resource Group
A Resource group is needed to contain entities (resources) associated with your database environment.
Navigate to Resource groups and create a Resource group.
Creation of a Storage Account
Storage account will contain your PostgreSQL’s log files. DataSunrise will get these logs from your Storage account.
1. Navigate to Storage accounts and create an Account
2. Select your Resource group in the Storage account’s settings and name the Storage account. Leave all other settings by default
3. Navigate to Access Control (IAM) and click Add -> Add role assignment
Select Reader: Next. Click +Select members and select your registered App. Review and assign. This is needed for your App to be able to access log files contained in the Blob containers of your Storage account:
4. Click Add role assignment again and select Storage Blob Data Reader.
5. Click +Select Members and select your registered App. Review and assign
Creation of a PostgreSQL Database Server
Create a PostgreSQL database server if you don’t have one. Otherwise, edit your database’s settings according to the steps below.
1. Navigate to Azure Database for PostgreSQL servers and create a new server: navigate to Create -> Flexible server -> Create
2. Provide all the required server details
3. At the Networking tab, check Public access… and add required firewall rules. Complete the deployment.
4. Navigate to your PostgreSQL database -> Settings -> Server Parameters and set the following parameters according to the table below:
| Setting | Required value |
|---|---|
| log_checkpoints | OFF |
| log_destination | CSVLOG |
| pgaudit.log | ALL |
| shared_preload_libraries | PG_STAT_STATEMENTS, PGAUDIT |
| log_line_prefix | %t-%c-u”%u”u- |
Save the settings
5. Configure your database to store logs in an associated Storage account.
Navigate to Monitoring -> Diagnostic settings -> Add diagnostic setting
Check both PostgreSQL Server Log and PostgreSQL Sessions Data. Then, check Archive to a storage account and select your Storage Account
Creation of a PostgreSQL Instance in DataSunrise
Open DataSunrise’s Web Console and navigate to Configuration -> Databases to create a PostgreSQL Database Instance (refer to subs. “Creating a Target Database Profile” of the DataSunrise’s User Guide for details):
You will need to provide the following information:
1. Select Trailing the DB Audit Logs in the Instance’s settings. Fill out all the required fields.
2. Copy ClientID and TenantID from your Azure App to make DataSunrise able to use the App for downloading your PostgreSQL’s log files
3. Input Client Secret saved before (refer to step 3 of Registration of an Azure Application).
4. Input Blob Container Name. You can find your Blob container name in Azure’s Storage Accounts -> your account -> Containers of your Azure settings:
Audit Results
Create some Audit Rules for your PostgreSQL database (refer to subs. “Creating a Data Audit Rule” of the User Guide) and execute some queries on your PostgreSQL database and navigate to Audit -> Transactional trails for auditing results:
