postgresqlddlauditazure-postgresqlpgaudit

Postgres Audit - How to get information on who is performing the DDL operation?

I'm trying to enable pgaudit in an Azure Postgres Single Server. I have followed the instructions provided by Microsoft and also the documentation of Postgres.

These are my settings ( the 2 Selected in the picture below are DDL and ROLE )

enter image description here

Log parameters

enter image description here

Then I have configured a destination for the files using a storage account. So far so good. The I ran this test case

postgres=> create table test_audit ( c1 integer ) ;
CREATE TABLE
postgres=> \dt test_audit
            List of relations
 Schema |    Name    | Type  |   Owner
--------+------------+-------+------------
 public | test_audit | table | posadmn001
(1 row)
postgres=> insert into test_audit values ( 1 ) ;
INSERT 0 1
postgres=> insert into test_audit values ( 2 ) ;
INSERT 0 1
postgres=> alter table test_audit add c2 integer ;
ALTER TABLE
postgres=> drop table test_audit ;
DROP TABLE

So I should expect only entries for DDL operations, not for the DML, and that is working fine.

Create Table

"message": "*AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.test_audit,create table test_audit ( c1 integer ) ;,<none>","detail": "","errorLevel": "LOG","domain": "postgres-11","schemaName": "","tableName": "","columnName": "","datatypeName"

Alter Table

"message": "AUDIT: SESSION,2,1,DDL,ALTER TABLE,TABLE,public.test_audit,alter table test_audit add c2 integer ;,<none>","detail": "","errorLevel": "LOG","domain": "postgres-11","schemaName": "","tableName": "","columnName": "","datatypeName"

Drop table

"message": "AUDIT: SESSION,1,1,DDL,DROP TABLE,TABLE,public.test_audit,drop table test_audit ;,<none>","detail": "","errorLevel": "LOG","domain": "postgres-11","schemaName": "","tableName": "","columnName": "","datatypeName"

However, the audit trails don't show who have performed such operations. I mean, if I review the logs line by line, obviously there is an entry for the connection itself by the admin user, but not related to the AUDIT itself. Is there any way to collect the user within the audit trail together with the DDL operation? So that when I am looking for messages with the word AUDIT, I can get the command and the user who did it.

Thank you for your help

Solution

  • You can get the user name in the log line prefix by adding %u to log_line_prefix. A useful setting might be

    log_line_prefix = '%m [%p] %q%u@%d (%h) '