Re: Detecting File Damage & Inconsistencies

On Fri, 13 Nov 2020 at 11:24, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On Fri, 13 Nov 2020 at 00:50, tsunakawa(dot)takay(at)fujitsu(dot)com
> <tsunakawa(dot)takay(at)fujitsu(dot)com> wrote:
> >
> > From: Simon Riggs <simon(at)2ndquadrant(dot)com>
> > > If a rogue user/process is suspected, this would allow you to identify
> > > more easily the changes made by specific sessions/users.
> >
> > Isn't that kind of auditing a job of pgAudit or log_statement = mod? Or, does "more easily" mean that you find pgAudit complex to use and/or log_statement's overhead is big?
>
> Well, I designed pgaudit, so yes, I think pgaudit is useful.
>
> However, pgaudit works at the statement level, not the data level. So
> using pgaudit to locate data rows that have changed is fairly hard.
>
> What I'm proposing is an option to add 16 bytes onto each COMMIT
> record, which is considerably less than turning on full auditing in
> pgaudit. This option would allow identifying data at the row level, so
> you could for example find all rows changed by specific sessions.
> Also, because it is stored in WAL it will show updates that might no
> longer exist in the database because the changed row versions might
> have been vacuumed away. So pgaudit will tell you that happened, but
> having extra info in WAL is important also.
>
> So thank you for the question because it has allowed me to explain why
> it is useful and important.

Patch attached to implement "wal_sessioninfo" option.

--
Simon Riggs http://www.EnterpriseDB.com/