Audit Logging and Azure Active Directory 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 Azure AD groups and want to audit the actions of AD 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 Azure Active Directory Authentication, which will cover: 

         A- Active Directory Users without (not part of) Azure AD group. 

         B- Active Directory Users that are part of Azure AD group. 

         C- Mix of A and B; you have mix of Active Directory Users that are part of AD groups   and others are not. 

     

     3. 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 

Before:  log_line_prefix = "%t-%c-" 

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

 

 

let opindex = 3;
let startIndex = toscalar(range thirdIndex from opindex to opindex step 1 | project thirdIndex);
AzureDiagnostics   
|where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"   
|where TimeGenerated >= ago(8d)   
|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 SqlQueryP1= tostring(split(tostring(split(Message, ",,,")[-1]),",<")[-2])
| extend SqlQueryP2= replace_string(tostring(split(SqlQueryP1, ",\"")[-1]),'"', '')
| extend SqlQueryP3= tostring(split(Message, ",,,")[1])
| extend SqlQuery = trim('"',case( operationtype == "EXECUTE", SqlQueryP2, SqlQueryP1 == "", SqlQueryP3, SqlQueryP1 )) 
|project TimeGenerated,UserName,SqlQuery,operationtype 

 

 

 

Audits in Azure database for PostgreSQL flexible server with Azure Active Directory

 

A. Active Directory Users without (not part of) Azure AD group

Prerequisites: 

1. Enable Azure Database for PostgreSQL flexible server Azure AD authentication - Use Azure AD for authentication with Azure Database for PostgreSQL - Flexible Server 
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 

Before:  log_line_prefix = "%t-%c-" 

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

 

 

let opindex = 3;
let startIndex = toscalar(range thirdIndex from opindex to opindex step 1 | project thirdIndex);
AzureDiagnostics  
| where ResourceProvider =="MICROSOFT.DBFORPOSTGRESQL"  
| where TimeGenerated >= ago(8d)
| where  Message contains "Azure Active Directory connection"  
|extend SessionId = tostring(split(tostring(split(Message, "session=")[-1]),",sess_time")[-2])  
|extend AADUSER = tostring(split(tostring(split(Message, "UPN=")[-1]),"oid=")[-2])    
| project TimeGenerated, SessionId,AADUSER
| join kind= innerunique   
(AzureDiagnostics  
| where ResourceProvider =="MICROSOFT.DBFORPOSTGRESQL"  
| where TimeGenerated >= ago(8d)
| 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 SqlQueryP1= tostring(split(tostring(split(Message, ",,,")[-1]),",<")[-2])
| extend SqlQueryP2= replace_string(tostring(split(SqlQueryP1, ",\"")[-1]),'"', '')
| extend SqlQueryP3= tostring(split(Message, ",,,")[1])
| extend SqlQuery = trim('"',case( operationtype == "EXECUTE", SqlQueryP2, SqlQueryP1 == "", SqlQueryP3, SqlQueryP1 ))
) on $left.SessionId == $right.SessionId  
| project TimeGenerated,AADUSER,SqlQuery,operationtype

 

 

 

 

B. Active Directory Users that are part of Azure AD group

 

Prerequisites: 

1. Enable Azure Database for PostgreSQL flexible server Azure AD authentication - Use Azure AD for authentication with Azure Database for PostgreSQL - Flexible Server 
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 

Before:  log_line_prefix = "%t-%c-" 

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

 

 

let opindex = 3;
let startIndex = toscalar(range thirdIndex from opindex to opindex step 1 | project thirdIndex);
AzureDiagnostics  
| where ResourceProvider =="MICROSOFT.DBFORPOSTGRESQL"  
| where TimeGenerated >= ago(8d)
| where  Message contains "Azure Active Directory connection"  
|extend SessionId = tostring(split(tostring(split(Message, "session=")[-1]),",sess_time")[-2])  
|extend AADUSER = tostring(split(tostring(split(Message, "UPN=")[-1]),"oid=")[-2])  
|extend AADGROUP = tostring(split(tostring(split(Message, "role=")[-1]),"UPN=")[-2])  
| project TimeGenerated, SessionId,AADUSER,AADGROUP
| join kind= innerunique   
(AzureDiagnostics  
| where ResourceProvider =="MICROSOFT.DBFORPOSTGRESQL"  
| where TimeGenerated >= ago(8d)
| 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 SqlQueryP1= tostring(split(tostring(split(Message, ",,,")[-1]),",<")[-2])
| extend SqlQueryP2= replace_string(tostring(split(SqlQueryP1, ",\"")[-1]),'"', '')
| extend SqlQueryP3= tostring(split(Message, ",,,")[1])
| extend SqlQuery = trim('"',case( operationtype == "EXECUTE", SqlQueryP2, SqlQueryP1 == "", SqlQueryP3, SqlQueryP1 ))
) on $left.SessionId == $right.SessionId  
| project TimeGenerated,AADUSER,AADGROUP,SqlQuery,operationtype

 

 

 

C. Mix of A and B; you have mix of Active Directory Users that are part of AD groups and others are not

 

Prerequisites: 

1. Enable Azure Database for PostgreSQL flexible server Azure AD authentication - Use Azure AD for authentication with Azure Database for PostgreSQL - Flexible Server 
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 

Before:  log_line_prefix = "%t-%c-" 

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

 

 

 

let opindex = 3;
let startIndex = toscalar(range thirdIndex from opindex to opindex step 1 | project thirdIndex);
AzureDiagnostics  
| where ResourceProvider =="MICROSOFT.DBFORPOSTGRESQL"  
| where TimeGenerated >= ago(8d)
| where  Message contains "Azure Active Directory connection"  
|extend SessionId = tostring(split(tostring(split(Message, "session=")[-1]),",sess_time")[-2])  
|extend AADUSER = tostring(split(tostring(split(Message, "UPN=")[-1]),"oid=")[-2])  
|extend AADGROUP = tostring(split(tostring(split(Message, "role=")[-1]),"UPN=")[-2])  
| project TimeGenerated, SessionId,AADUSER,AADGROUP
| join kind= innerunique   
(AzureDiagnostics  
| where ResourceProvider =="MICROSOFT.DBFORPOSTGRESQL"  
| where TimeGenerated >= ago(8d)
| 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 SqlQueryP1= tostring(split(tostring(split(Message, ",,,")[-1]),",<")[-2])
| extend SqlQueryP2= replace_string(tostring(split(SqlQueryP1, ",\"")[-1]),'"', '')
| extend SqlQueryP3= tostring(split(Message, ",,,")[1])
| extend SqlQuery = trim('"',case( operationtype == "EXECUTE", SqlQueryP2, SqlQueryP1 == "", SqlQueryP3, SqlQueryP1 ))
) on $left.SessionId == $right.SessionId  
| project TimeGenerated,AADUSER,AADGROUP,SqlQuery,operationtype

 

 

 

 

Conclusion

 

Azure Database for Postgres Flexible Server has unique ability to send logs to Azure monitor Log Analytics. This integration makes the analysis of logs very easy. In this post above, we have explained the simple steps to analyze PgAudit logs in Log Analytics to find out database object operations that are running in your instance. You can further segregate these activities by type of logins including AD (Active Directory) and standard postgres logins.