Re: Detecting File Damage & Inconsistencies

On Wed, 14 Jul 2021 at 05:01, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:

> The patch is simple but its use doesn't seem to be very clear. You
> have mentioned its use for future PITR patches and Craig mentioned
> some use cases in logical decoding and it appears to me that to
> support the use cases mentioned by Craig, it is important to LOG this
> earlier than at commit time. As there are no details about how it will
> be used for PITR patches and whether such patch ideas are accepted, it
> makes it harder to judge the value of this patch.

> I think if we would have patches (even at WIP/POC stage) for the ideas
> you and Craig have in mind, it would have been much easier to see the
> value of this patch.

Fair question. This is one of a series of 4 independent patches I have
planned to provide enhanced information and/or recovery tools. (The
second one is already in this CF). This is an area I know lots about
and nobody else is working on, so I thought I would contribute. I've
not discussed this off-list with anyone else. So this is PITR as
recovery in a broad sense, not just that specific feature.

For this patch, the idea is to be able to go in either direction:
Data damage <--> User info

So if you know a user was an intruder, you can detect the damage they caused.
Or, if you detect damage, you can work out who caused it, work out if
they were an intruder and if so, detect what else they did.

The most important thing is to have the info available in WAL, nothing
is possible until that is available.
We already added an option to add this same info to log_line_prefix,
yet nobody said it wasn't useful there, or needed other uses to allow
the feature.
The two sources of info are designed to be able to be used in combination.

My experience of recovery scenarios is that you often have to build
custom search tools to make it work. It's hard to say whether you'll
want to track the user, the specific session, or even specific
transactions.

But I do understand the overall request, so I propose adding
* pg_waldump output for wal_sessioninfo data, if it exists
* pg_waldump --user=USERNAME as a filter on username
to demonstrate the use of this

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