Re: New feature request for adding session information to PostgreSQL transaction log

Hi Scott,
As per the PostgreSQL bug reporting page (https://www.postgresql.org/docs/current/bug-reporting.html) , we have to send email to pgsql-hackers(at)lists(dot)postgresql(dot)org<mailto:pgsql-hackers(at)lists(dot)postgresql(dot)org> for creating enhancement request.

Hence I have sent email to pgsql-hackers(at)lists(dot)postgresql(dot)org<mailto:pgsql-hackers(at)lists(dot)postgresql(dot)org>, for the enhancement request of adding session information to PostgreSQL transaction log.

Warm regards,
Sumanth Vishwaraj
________________________________
From: Scott Corbin <scott(dot)corbin(at)oracle(dot)com>
Sent: Wednesday, January 15, 2025 8:44 PM
To: Sumanth Vishwaraj <sumanth(dot)vishwaraj(at)oracle(dot)com>; pgsql-hackers(at)lists(dot)postgresql(dot)org <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: Mahesh Rao <mahesh(dot)p(dot)rao(at)oracle(dot)com>; Nazia Zaidi <najiah(dot)abide(at)oracle(dot)com>; Jitesh Tiwari <jitesh(dot)tiwari(at)oracle(dot)com>; Nick Wagner <nick(dot)wagner(at)oracle(dot)com>; Mack Bell <mack(dot)bell(at)oracle(dot)com>; Avinash Dubey <avinash(dot)x(dot)dubey(at)oracle(dot)com>
Subject: RE: New feature request for adding session information to PostgreSQL transaction log

Sumanth,

We generally add whatever contextual information is available from the database transaction logs when we implement capture for a given database. If what you need is not currently supported, it is most likely because the database itself does not support it. If you would like to support a particular database in audit vault then you should start by doing research for that particular database to see if it records the information that you would like to capture. Most databases document what can we captured, and often provide utilities to evaluate the contents of change records in the transaction log. If the information is not available then you should start with an enhancement request for the database itself. Once you have confirmed what information is available for a given database then please submit an enhancement request for GoldenGate to capture that specific information if it is not already supported.

Thanks,

Scott

From: Sumanth Vishwaraj <sumanth(dot)vishwaraj(at)oracle(dot)com>
Sent: Wednesday, January 15, 2025 2:54 AM
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Cc: Mahesh Rao <mahesh(dot)p(dot)rao(at)oracle(dot)com>; Nazia Zaidi <najiah(dot)abide(at)oracle(dot)com>; Jitesh Tiwari <jitesh(dot)tiwari(at)oracle(dot)com>; Nick Wagner <nick(dot)wagner(at)oracle(dot)com>; Scott Corbin <scott(dot)corbin(at)oracle(dot)com>; Mack Bell <mack(dot)bell(at)oracle(dot)com>; Avinash Dubey <avinash(dot)x(dot)dubey(at)oracle(dot)com>
Subject: New feature request for adding session information to PostgreSQL transaction log

Hi PostgreSQL team,

Oracle Audit Vault and Database Firewall (AVDF) audits/monitors database activities. This product helps enterprises to manage the security posture of Oracle , PostgreSQL and other databases.

Oracle AVDF helps customers in India comply with the Ministry of Corporate Affairs (MCA) Guidelines (https://www.mca.gov.in/Ministry/pdf/AuditAuditorsAmendmentRules_24032021.pdf)

As per the MCA guidelines it is mandatory to capture details of what data was changed, when it was changed and who made the change.

PostgreSQL generates and stores (change data capture) information in transaction log, which is in turn read by Oracle GoldenGate and stored in XML files. These XML files are processed by AVDF and stored in AVDF database.

From an auditing perspective, three details are mandatory.

* What was the change?

* When did the change happen?

* Who made the change?

The PostgreSQL transaction log currently has information about what was the change, and when the change happened. But it does not have any information about who made the change.

We would like PostgreSQL to store the details of who made the change (user/session) information in the transaction log.

Below are the user/session information from an auditing perspective.

Mandatory critical session information

* DB User Name

* OS User Name

* Client Host Name

* Client/AppUser ID

Other important session information

* Program Name

* OS Terminal Name

* Process ID

* Proxy Session ID

Since these details are mandated by MCA regulations, we would humbly request your expertise in prioritizing this enhancement.

Warm regards,

Sumanth Vishwaraj