Audit Logging and Microsoft Entra in Azure Database for PostgreSQL Flexible servers.

Database audits are one of the important components that need to be set up based on your organization’s compliance requirements, where you can monitor the targeted activities to achieve your security baseline. In Azure database for PostgreSQL flexible server, you can achieve that by using pgaudit PG extension as described in Audit logging in Azure Database for PostgreSQL - Flexible server. 

 

One of the challenges is utilizing auditing feature alongside PostgreSQL flexible server Azure Active Directory authentication when you are using Microsoft Entra  groups and want to audit the actions of Entra groups members. This is because when group members sign in, they use their personal access tokens but use the group name as the username. 

 

For this article we will cover the following scenarios: 

1. Audits in Azure database for PostgreSQL flexible server with PostgreSQL Authentication. 
2. Audits in Azure database for PostgreSQL flexible server with Microsoft Entra Authentication. 
3. Audit all users including PostgreSQL and MSFT Entra authentication.

We will offer Kusto queries for all of the above scenarios, which you can execute using your PostgreSQL flexible server resource, under Monitoring blade --> logs:

 

Audits in Azure database for PostgreSQL flexible server with PostgreSQL Authentication 

 

Kusto Query Language (KQL) is a powerful pipeline-driven, read-only query language that enables querying Azure Service Logs. KQL supports querying Azure logs to quickly analyze a high volume of data. For this article, we will use the KQL to query Azure Postgres Logs and find connections aggregated by their age. 

 

Prerequisites: 

1. Enable Audit logging - Audit logging in Azure Database for PostgreSQL - Flexible server 
2. Enable Azure Postgres logs to be sent to Azure log analytics - Configure Log Analytics 
3. Adjust “log_line_prefix” server parameter: 

From the Server Parameters blade - set the "log_line_prefix" to include the escapes "user=%u,db=%d,session=%c,sess_time=%s"  in the same sequence, in order to get the desired results 

 

Example 

log_line_prefix = "%t-%c-user=%u,db=%d,session=%c,sess_time=%s"

Use the below Kusto query:

let lookbackTime = ago(8d);
let opindex = 3;
let sqlindex = 6;
let startIndex = toscalar(range thirdIndex from opindex to opindex step 1 | project thirdIndex);
AzureDiagnostics   
|where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"   
|where TimeGenerated >= lookbackTime 
|where Message contains "AUDIT: SESSION"   
|extend UserName=tostring(split(tostring(split(Message,"user=")[-1]),",db=")[-2])   
|extend sessionid=tostring(split(tostring(split(Message,"session=")[-1]),",sess_time")[-2])   
| where UserName !in ('azuresu','[unknown]','postgres','')  
| extend SubMessage = tostring(split(Message, "SESSION,")[-1])
| extend splitArray = split(SubMessage, ',')
| extend operationtype = tostring(splitArray[startIndex])
| extend SqlQuery = trim('"', tostring(splitArray[sqlindex]))
|project TimeGenerated,UserName,SqlQuery,operationtype

 

 

Audits in Azure database for PostgreSQL flexible server with Microsoft Entra Authentication

 

The following steps will enable you to list Microsoft Entra users along with their associated Microsoft Entra groups, if applicable. This approach allows you to track users even when they log in using a Microsoft Entra ID group. Note that group members may sign in with their individual Entra ID credentials, but they can also authenticate using the group name as the username. In such cases, the login will be recorded accordingly with its actions.

Prerequisites: 

1. Enable Azure Database for PostgreSQL flexible server Microsoft Entra authentication - Use Microsoft Entra ID for authentication with Azure Database for PostgreSQL 
2. Enable Audit logging - Audit logging in Azure Database for PostgreSQL - Flexible server 
3. Enable Azure Postgres logs to be sent to Azure log analytics - Configure Log Analytics 
4. Adjust “log_line_prefix” server parameter: 

From the Server Parameters blade - set the "log_line_prefix" to include the escapes "user=%u,db=%d,session=%c,sess_time=%s"  in the same sequence, in order to get the desired results 

 

Example 

log_line_prefix = "%t-%c-user=%u,db=%d,session=%c,sess_time=%s"

 

 

 

Use the below Kusto query:

let lookbackTime = ago(8d);
let opindex = 3;
let sqlindex = 6;
let startIndex = toscalar(range thirdIndex from opindex to opindex step 1
    | project thirdIndex);
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where TimeGenerated >= lookbackTime
| where Message contains "Microsoft Entra ID connection"
| extend SessionId = tostring(split(tostring(split(Message, "session=")[-1]), ",sess_time")[-2])
| extend UPN = tostring(split(tostring(split(Message, "UPN=")[-1]), "oid=")[-2])
| extend role = tostring(split(tostring(split(Message, "role=")[-1]), "UPN=")[-2])
| project TimeGenerated, SessionId, UPN, role
| join kind=leftouter
    (
    AzureDiagnostics
    | where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
    | where TimeGenerated >= lookbackTime
    | where Message contains "AUDIT: SESSION"
    | extend UserName = tostring(split(tostring(split(Message, "user=")[-1]), ",db=")[-2])
    | extend SessionId = tostring(split(tostring(split(Message, "session=")[-1]), ",sess_time")[-2])
    | where UserName !in ('azuresu', '[unknown]', 'postgres', '')
    | extend SubMessage = tostring(split(Message, "SESSION,")[-1])
    | extend splitArray = split(SubMessage, ',')
    | extend operationType = tostring(splitArray[startIndex])
    | extend SqlQuery = trim('"', tostring(splitArray[sqlindex]))
    )
    on $left.SessionId == $right.SessionId
| project TimeGenerated, UPN, role, SqlQuery, operationType

 

 

 

 

Audits all users including PostgreSQL and MSFT Entra authentication

In this auditing mode, you can comprehensively track all database users, regardless of their authentication method. This includes users authenticated via standard PostgreSQL credentials as well as those using Microsoft Entra ID. By enabling this configuration, you ensure complete visibility into user activities, facilitating security monitoring, compliance, and auditing across both authentication types.

 

Prerequisites: 

1. Enable Azure Database for PostgreSQL flexible server Azure AD authentication - Use Microsoft Entra ID for authentication with Azure Database for PostgreSQL 
2. Enable Audit logging - Audit logging in Azure Database for PostgreSQL - Flexible server 
3. Enable Azure Postgres logs to be sent to Azure log analytics - Configure Log Analytics 
4. Adjust “log_line_prefix” server parameter: 

From the Server Parameters blade - set the "log_line_prefix" to include the escapes "user=%u,db=%d,session=%c,sess_time=%s"  in the same sequence, in order to get the desired results 

 

Example 

log_line_prefix = "%t-%c-user=%u,db=%d,session=%c,sess_time=%s"

 

Use the below Kusto query:

let lookbackTime = ago(8d);
let opindex = 3;
let sqlindex = 6;
let startIndex = toscalar(range thirdIndex from opindex to opindex step 1 | project thirdIndex);
AzureDiagnostics   
|where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"   
|where TimeGenerated >= lookbackTime 
|where Message contains "AUDIT: SESSION"   
|extend UserName=tostring(split(tostring(split(Message,"user=")[-1]),",db=")[-2])   
|extend sessionid=tostring(split(tostring(split(Message,"session=")[-1]),",sess_time")[-2])   
| where UserName !in ('azuresu','[unknown]','postgres','')  
| extend SubMessage = tostring(split(Message, "SESSION,")[-1])
| extend splitArray = split(SubMessage, ',')
| extend operationtype = tostring(splitArray[startIndex])
| extend SqlQuery = trim('"', tostring(splitArray[sqlindex]))
|project TimeGenerated,UserName,SqlQuery,operationtype

 

 

Conclusion

 

Azure Database for PostgreSQL Flexible Server offers a powerful integration that enables the seamless export of logs to Azure Monitor Log Analytics. This capability simplifies log analysis, providing a centralized platform for querying and visualizing data with tools like Kusto Query Language (KQL).

In the section above, we have outlined straightforward steps to analyze PgAudit logs within Log Analytics. These steps allow you to identify and examine database object operations occurring in your instance, such as DDL (Data Definition Language) commands, DML (Data Manipulation Language) activities, and more.

Furthermore, you can refine your analysis by segregating activities based on login types, including Microsoft Entra ID (formerly Azure Active Directory) authentications and standard PostgreSQL logins. This granular visibility enhances security monitoring, aids in compliance auditing, and supports efficient troubleshooting of user behaviors and access patterns. For optimal results, ensure that audit logging is enabled in your PostgreSQL server configuration, and consider setting up alerts or dashboards in Log Analytics to proactively monitor key metrics.