Edit

Share via

Audit logging in Azure Database for PostgreSQL for Microsoft Entra ID principals

Database audits are an important component of your organization's compliance requirements. By monitoring targeted activities, you can achieve your security baseline. In Azure Database for PostgreSQL flexible server, you can set up audits by using the pgaudit PG extension, as described in Audit logging in Azure Database for PostgreSQL.

One challenge is using the auditing feature alongside Microsoft Entra ID authentication when you're using Microsoft Entra ID groups and want to audit the actions of individual group members. This challenge exists because group members sign in by using their personal access tokens but use the group name as the username.

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, use KQL to query Azure Postgres Logs and extract Microsoft Entra ID user information from audit logs.

Prerequisites

  1. Enable audit logging - Audit logging in Azure Database for PostgreSQL
  2. Enable Azure Postgres logs to be sent to Azure log analytics - Configure Log Analytics
  3. Adjust the 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 to get the desired results.
    • Before: log_line_prefix = %t-%c-
    • After: log_line_prefix = %t-%c-user=%u,db=%d,session=%c,sess_time=%s

Kusto query

The following Kusto query queries the AzureDiagnostics two times.
The first subquery finds all lines that contain the string Microsoft Entra ID connection authorized and extracts the PrincipalName from these log lines, alongside the SessionId.
The second subquery finds all audit logs.
Finally, these two subqueries are joined on the SessionId.

let lookbackTime = ago(3d);
let opindex = 3;
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 authorized'
| extend SessionId = tostring(split(tostring(split(Message, 'session=')[-1]), ',sess_time')[-2])
| extend UPN = iff(Message contains 'UPN',tostring(split(tostring(split(Message, 'UPN=')[-1]), 'oid=')[-2]), '')
| extend appId = iff(Message contains 'appid', tostring(split(tostring(split(Message, 'appid=')[-1]), 'oid=')[-2]), '')
| extend PrincipalName = strcat(UPN, appId)
| project SessionId, PrincipalName
| join kind=leftouter
    (
    AzureDiagnostics
    | where ResourceProvider == 'MICROSOFT.DBFORPOSTGRESQL'
    | where TimeGenerated >= lookbackTime
    | where Message contains 'AUDIT: SESSION'
    | extend RoleName = tostring(split(tostring(split(Message, 'user=')[-1]), ',db')[-2])
    | where RoleName !in ('azuresu', '[unknown]', 'postgres', '')
    | extend SessionId = tostring(split(tostring(split(Message, 'session=')[-1]), ',sess_time')[-2])
    | extend SubMessage = tostring(split(Message, 'SESSION,')[-1])
    | extend splitArray = split(SubMessage, ',')
    | extend SqlQueryP1 = tostring(split(tostring(split(Message, ',,,')[-1]), ',<')[-2])
    | extend SqlQueryP2 = replace_string(tostring(split(SqlQueryP1, ',\"')[-1]), '"', '')
    | extend SqlQueryP3 = tostring(split(Message, ',,,')[1])
    | extend OperationType = tostring(splitArray[startIndex])
    | extend SqlQuery = trim('"', case(OperationType == 'EXECUTE', SqlQueryP2, SqlQueryP1 == '', SqlQueryP3, SqlQueryP1))
    )
    on $left.SessionId == $right.SessionId
| project TimeGenerated, PrincipalName, RoleName, OperationType, SqlQuery

Example results

The resulting table looks like this:

TimeGenerated PrincipalName RoleName OperationType SqlQuery
2025-12-12T16:25:05.104Z user@example.com ExampleGroupName SELECT select * from pg_seclabels;
2025-12-12T16:25:04.000Z user@example.com user@example.com SELECT select * from pg_seclabels;

If the user signs in as a group role, the PrincipalName and RoleName columns show different values, like in the first row of the example.
The PrincipalName value identifies the user who signed in. The RoleName value identifies the role in PostgreSQL that the user accesses after signing in.

PrincipalName is either the User Principal Name (UPN) or AppId depending on whether the user principal or service principal signs in.

Related content

  • Last updated on