Re: temporal support patch - Mailing list pgsql-hackers

On Mon, 2012-08-20 at 17:04 -0400, Robert Haas wrote:
> On Sun, Aug 19, 2012 at 6:28 PM, Jeff Davis <pgsql@j-davis.com> wrote:
> >> The other issue is how to handle multiple changes of the same record
> >> within the transaction. Should they be stored or not?
> >
> > In a typical audit log, I don't see any reason to. The internals of a
> > transaction should be implementation details; invisible to the outside,
> > right?
> 
> I'm not convinced.

As I understand it, we are talking about recording data changes in one
table to another table. Auditing of reads or the logging of raw
statements seem like very different kinds of projects to me; but tell me
if you think differently.

So if we are recording data changes, I don't see much point in recording
uncommitted changes. Perhaps my imagination is failing, and someone else
can fill me in on a use case.

I'm also struggling with the semantics: if we record uncommitted
changes, do we record them even if the transaction aborts? If so, what
guarantees do we offer about the change actually being recorded?

> >> I'm not sure that the database user is the proper thing to be stored in
> >> the history table. Many applications usually connect to a database using
> >> some virtual user and have their own users/roles tables to handle with
> >> privileges. There should be some way to substitute the stored user in
> >> the history table with the application's one. It's also helpful to store
> >> transaction id that inserted/updated/deleted the record.
> >
> > If the system is recording it for audit purposes, then it better be sure
> > that it's true. You can't allow the application to pick and choose what
> > gets stored there.
> 
> That position would render this feature useless for every application
> for which I would otherwise have used it.

We could offer a GUC like "audit_context" or "audit_app_context" that
takes a text string, and the audit log would record the value stored in
that GUC along with the data changes in question.

The main thing I object to is an implication that the system is vouching
for some particular fact that is supplied by a userset GUC. Remember,
there are guaranteed to be application-level problems that allow these
GUCs to get set improperly for all kinds of reasons. We don't want bug
reports along the lines of "security breach! PG allows application_name
to be spoofed in the audit log!".

Also, I'd prefer not use existing GUCs, because there may be all kinds
of other reasons that people set existing GUCs, and we want them to be
able to handle the audit_context one more carefully and have a clear
warning in the documentation.

>  I think it's just nonsense
> to talk about what we can or can't let the user do.  The user is in
> charge, and our job is to allow him to do what he wants to do more
> easily, not to dictate what he must do.

Remember that the users who depend on the veracity of the audit log are
users, too. Let's try to serve both classes of user if we can.

Regards,Jeff Davis