Re: temporal support patch - Mailing list pgsql-hackers

> -----Original Message-----
> From: Robert Haas [mailto:robertmhaas@gmail.com]
> Sent: Saturday, August 25, 2012 12:46 PM
> To: David Johnston
> Cc: Jeff Davis; Vlad Arkhipov; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] temporal support patch
> 
> On Mon, Aug 20, 2012 at 7:17 PM, David Johnston <polobo@yahoo.com>
> wrote:
> > Ideally the decision of whether to do so could be a client decision.
> > Not storing intra-transaction changes is easier than storing all
changes.
> 
> Not really.  If you don't care about suppressing intra-transaction
changes, you
> can essentially just have a trigger that fires on every update and adds
> information to the side table.  If you do care about suppressing them, you
> have to do something more complicated.  Or so it seems to me.
> 

My internals knowledge is basically zero but it would seem that If you
simply wanted the end-of-transaction result you could just record nothing
during the transaction and then copy whatever values are present at commit
to whatever logging mechanism you need.  If you are recording
intra-transaction values you could do so to a temporary storage area and
then, at commit, decide whether the recent value for a given
relation/attribute is going to be retained in the final log or whether you
end up persisting all of the intermediate values as well.


> > "You cannot allow the application to choose what is stored to identify
> > itself (client)" - i.e., its credentials identify who it is and those
> > are stored without consulting the application
> 
> I don't think we can violate the general principle that the database
super-
> user or table owner can do whatever they want.  If one of those folks
wants
> to falsify their history, are we really going to tell them "no"?  To me
that has
> "I'm sorry, Dave, I can't do that" written all over it, and I think we'll
get about
> the same reaction that Hal did.
> Now, if user A is inserting into user B's table, and is not the
super-user, then,
> of course, we can and should ensure that no falsification is possible.
> 

With respect to the physical log file there is no way for the super-user to
currently falsify (at time of statement execution) the user/role that they
are using.  Even a "SET ROLE" doesn't change the session user (I forget the
exact mechanics but I pretty sure on the general point).  I do not see how
this is that much different.

I agree that it is pointless to even try to maintain true in-database
auditing in the presence of god-like super-users so most of what I envision
relates to limited permissioned users that are forced to rely upon the
standard mechanisms provided by the database.  As a matter of principle
those wanting a secure and auditable environment should not be using
ownership level roles.

Since these temporal/audit tables are intended to be maintained by the
system if you do not ask the users to identify themselves but instead take
the information directly from the environment, you never have to give a "I'm
sorry Dave" response because Dave is never given the chance to submit a
proposed value.

David J.