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.

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"

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])

| extend op = tostring(split(tostring(split(Message, ",,,")[-2]),",")[-1])

| extend operationtype = case(op =="","EXECUTE,FUNCTION",tostring(split(tostring(split(Message, ",,,")[-2]),",")[-1]))

| where UserName !in ('azuresu','[unknown]','postgres','')

| extend SqlQuery=case(operationtype=="EXECUTE,FUNCTION", trim(@"""",tostring(split(tostring(split(Message, "EXECUTE,FUNCTION,")[-1]),",<")[-2])), trim(@"""",tostring(split(tostring(split(Message, ",,,")[-1]),",<")[-2])))

| 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"

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 op = tostring(split(tostring(split(Message, ",,,")[-2]),",")[-1])

| extend operationtype = case(op =="","EXECUTE,FUNCTION",tostring(split(tostring(split(Message, ",,,")[-2]),",")[-1]))

| extend SqlQuery=case(operationtype=="EXECUTE,FUNCTION", trim(@"""",tostring(split(tostring(split(Message, "EXECUTE,FUNCTION,")[-1]),",<")[-2])), trim(@"""",tostring(split(tostring(split(Message, ",,,")[-1]),",<")[-2])))

| project UserName,SqlQuery,SessionId,operationtype

) 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"

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 op = tostring(split(tostring(split(Message, ",,,")[-2]),",")[-1])

| extend operationtype = case(op =="","EXECUTE,FUNCTION",tostring(split(tostring(split(Message, ",,,")[-2]),",")[-1]))

| extend SqlQuery=case(operationtype=="EXECUTE,FUNCTION", trim(@"""",tostring(split(tostring(split(Message, "EXECUTE,FUNCTION,")[-1]),",<")[-2])), trim(@"""",tostring(split(tostring(split(Message, ",,,")[-1]),",<")[-2])))

| project UserName,SqlQuery,SessionId,operationtype

) 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"

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 op = tostring(split(tostring(split(Message, ",,,")[-2]),",")[-1])

| extend operationtype = case(op =="","EXECUTE,FUNCTION",tostring(split(tostring(split(Message, ",,,")[-2]),",")[-1]))

| extend SqlQuery=case(operationtype=="EXECUTE,FUNCTION", trim(@"""",tostring(split(tostring(split(Message, "EXECUTE,FUNCTION,")[-1]),",<")[-2])), trim(@"""",tostring(split(tostring(split(Message, ",,,")[-1]),",<")[-2])))

| project UserName,SqlQuery,SessionId,operationtype

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

Continue reading...