데이터베이스 감사는 조직의 규정 준수 요구 사항의 중요한 구성 요소입니다. 대상 활동을 모니터링하여 보안 기준을 달성할 수 있습니다. Azure Database for PostgreSQL 유연한 서버에서 PostgreSQL용 Azure Database의 감사 로깅에 설명된 대로 pgaudit PG 확장을 사용하여 감사를 설정할 수 있습니다.
한 가지 과제는 Microsoft Entra ID 그룹을 사용하고 개별 그룹 구성원의 작업을 감사하려는 경우 Microsoft Entra ID 인증과 함께 감사 기능을 사용하는 것입니다. 이 문제는 그룹 구성원이 개인 액세스 토큰을 사용하여 로그인하지만 그룹 이름을 사용자 이름으로 사용하기 때문에 발생합니다.
KQL(Kusto Query Language)은 Azure 서비스 로그를 쿼리할 수 있는 강력한 파이프라인 기반 읽기 전용 쿼리 언어입니다. KQL은 Azure 로그 쿼리를 지원하여 대량의 데이터를 신속하게 분석합니다. 이 문서에서는 KQL을 사용하여 Azure Postgres 로그를 쿼리하고 감사 로그에서 Microsoft Entra ID 사용자 정보를 추출합니다.
필수 조건
- 감사 로깅 사용 - Azure Database for PostgreSQL에서 감사 로깅
- Azure Postgres 로그를 Azure Log Analytics로 보내도록 활성화 - 로그 구성 및 액세스
-
log_line_prefix매개 변수 조정: 매개 변수 창에서log_line_prefix을(를) 설정하여 이스케이프user=%u,db=%d,session=%c,sess_time=%s가 동일한 시퀀스에 포함되도록 하면 원하는 결과를 얻을 수 있습니다.- 전에:
log_line_prefix=%t-%c- - 후:
log_line_prefix=%t-%c-user=%u,db=%d,session=%c,sess_time=%s
- 전에:
Kusto 쿼리
다음 Kusto 쿼리는 AzureDiagnostics을 두 번 쿼리합니다.
첫 번째 하위 쿼리는 문자열 Microsoft Entra ID connection authorized 이 포함된 모든 줄을 찾아 이러한 로그 줄과 함께 PrincipalName추출합니다SessionId.
두 번째 하위 쿼리는 모든 감사 로그를 찾습니다.
마지막으로, 이 두 하위 쿼리는 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
결과 예
결과 테이블은 다음과 같습니다.
| 타임제너레이티드 | 대표 이름 | 역할 이름 | 운영 유형 | 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; |
사용자가 그룹 역할로 로그인하는 경우 예제의 PrincipalName 첫 번째 행과 같이 열과 RoleName 열에 다른 값이 표시됩니다.
이 값은 PrincipalName 로그인한 사용자를 식별합니다. 이 값은 RoleName 사용자가 로그인한 후 액세스하는 PostgreSQL의 역할을 식별합니다.
PrincipalName 은 사용자 계정 또는 서비스 주체가 로그인하는지 여부에 따라 UPN(사용자 계정 이름) 또는 AppId 입니다.