Обсуждение: New feature request for adding session information to PostgreSQL transaction log

• What was the change?
• When did the change happen?
• Who made the change?

• DB User Name
• OS User Name
• Client Host Name
• Client/AppUser ID

• Program Name
• OS Terminal Name
• Process ID
• Proxy Session ID

Sumanth Vishwaraj <sumanth.vishwaraj@oracle.com> writes:
> We would like PostgreSQL to store the details of who made the change (user/session) information in the transaction
log.

You can build that yourself, typically by adding a trigger that stores
the value of "current_user" into inserted/updated rows.  (If you want
to also track deletions, a separate audit log table would work
better.)  The event-trigger feature might also be useful.

It's unlikely that we'd consider implementing such functionality at a
lower level, because these sorts of requirements always come with an
array of application-specific details about what is to be logged.
It would be impractical to satisfy all such cases in one implementation.
There would also be concerns about imposing overhead on users who
have no need for such a feature.

            regards, tom lane

On Wed, 15 Jan 2025 08:54:06 +0000
Sumanth Vishwaraj <sumanth.vishwaraj@oracle.com> wrote:

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

Hi Sumanth -

I think your question would be better suited to the general (users)
list, since it's more of a "user" question. This "hackers" email list
is used by developers working on Postgres internals.

I think you might misunderstand Oracle's auditing features. IIUC,
neither the traditional SYS.AUD$ table nor the new unified audit trail
in Oracle are populated from redo, but both are populated by directly
intercepting events.

A common solution following a similar model in the Postgres space is
pgaudit. I would suggest to start out by reading the pgaudit
documentation here:

https://github.com/pgaudit/pgaudit/blob/main/README.md

Pgaudit is an "extension" that's installed separately and added on to
Postgres, with its own distinct group of maintainers. I'm not sure if it
has a dedicated forum for questions and discussion, but I'm sure you
could ask questions on the community Postgres slack, IRC, telegram, and
other popular online Postgres community forums.

I don't know if it would be considered out of place to ask questions
about pgaudit on the pgsql-general list (because it's an extension and
doesn't come from postgresql.org) but I am sure there are a lot of
pgaudit users here, so questions might be ok over on the general (users)
mailing list.

There are a lot of people (including my company) using Postgres in
regulated industries around the world and it has robust capabilites to
meet regulations. Oracle is a great database too. Good luck with your
project!

-Jeremy

On 01/19/25 12:02, Tom Lane wrote:
> You can build that yourself, typically by adding a trigger that stores
> the value of "current_user" into inserted/updated rows.  (If you want
> to also track deletions, a separate audit log table would work
> better.)  The event-trigger feature might also be useful.

I wonder how close one could get to the customer request (better
forensics without having to build extra columns and triggers at the
SQL level) with an extension and existing hooks.

I haven't used it, but isn't there now a facility for inserting
additional custom records into the WAL? With ClientAuthentication_hook,
could an extension add a record there for the creation of a new session,
with timestamp and authenticated role oid?

Could an XactCallback be used to add a custom record at commit time
identifying the responsible session? There would then be enough breadcrumbs
to follow forensically from the commit to the session to the credentials.

An added custom record at commit time likely costs more in space than
extending the existing commit record with a session id, but seems like
something an extension could do without changes in core.

Forensic information would then be present with no need for a customer
to build extra table columns and triggers. It would be recorded
transparently, and would need some custom WAL-reading forensic tool
to piece it back together if ever needed. But just such a facility would
have made me quite happy back when I was experimenting[1] with using
pg_commit_ts forensically.

Regards,
-Chap


[1]
https://www.postgresql.org/message-id/8527e4bf-a3c0-f056-978b-ff4096951e3d%40anastigmatix.net

Chapman Flack <jcflack@acm.org> writes:
> I wonder how close one could get to the customer request (better
> forensics without having to build extra columns and triggers at the
> SQL level) with an extension and existing hooks.

An extension would be a great solution if we can make it work.

> I haven't used it, but isn't there now a facility for inserting
> additional custom records into the WAL? With ClientAuthentication_hook,
> could an extension add a record there for the creation of a new session,
> with timestamp and authenticated role oid?

I believe this is possible.

> Could an XactCallback be used to add a custom record at commit time
> identifying the responsible session? There would then be enough breadcrumbs
> to follow forensically from the commit to the session to the credentials.

I'm less sure about whether all the hooks exist today to do this, but
it seems like something we could allow if it's not there already.
However, I wonder whether this is sufficient.  As you've sketched it,
it would permit tracing actions to sessions' login roles, but
individual actions might be taken under other roles.  Does that need
to be captured?  If so at what granularity?  The maximalist approach
would probably be that every WAL record has to be labeled with
current_role, which would be a lot of overhead in the best case,
and still more if it has to be done via a separate WAL record.

> An added custom record at commit time likely costs more in space than
> extending the existing commit record with a session id, but seems like
> something an extension could do without changes in core.

I think this'd be an acceptable tradeoff if it only has to happen once
per commit.  Not sure if that answers the request though.

            regards, tom lane

Hi,

On 2025-01-21 13:28:43 -0500, Chapman Flack wrote:
> On 01/19/25 12:02, Tom Lane wrote:
> > You can build that yourself, typically by adding a trigger that stores
> > the value of "current_user" into inserted/updated rows.  (If you want
> > to also track deletions, a separate audit log table would work
> > better.)  The event-trigger feature might also be useful.
> 
> I wonder how close one could get to the customer request (better
> forensics without having to build extra columns and triggers at the
> SQL level) with an extension and existing hooks.
> 
> I haven't used it, but isn't there now a facility for inserting
> additional custom records into the WAL? With ClientAuthentication_hook,
> could an extension add a record there for the creation of a new session,
> with timestamp and authenticated role oid?

I'd probably not create custom records, I'd just use pg_logical_emit_message()
with an appropriate prefix. That way you can emit both transactional and
non-transactional records etc


> Could an XactCallback be used to add a custom record at commit time
> identifying the responsible session? There would then be enough breadcrumbs
> to follow forensically from the commit to the session to the credentials.

Yes.


> An added custom record at commit time likely costs more in space than
> extending the existing commit record with a session id, but seems like
> something an extension could do without changes in core.

The added space overhead should be small enough to not really matter in most
scenarios. Unless you do a lot of tiny tiny transaction it's not going to be a
lot compared of the size of WAL for actual DML.

The one issue I see is that it's not quite trivial to emit a WAL record with
extra information for a transaction iff the transaction actually performed
DML.

Greetings,

Andres Freund