Обсуждение: Database Design: Maintain Audit Trail of Changes

   I have the need to develop an application that will use postgres as the
back end, and most of the design has been worked out, but I've one issue
left to resolve and want help in this. If this is not the appropriate forum
for this type of question, please point me in the right direction.

   For several reasons (including operational and legal) once data are
entered in a table they cannot be changed or deleted without an audit trail
of the change, when it occurred, who made the change, and the reason for it.
Tables might contain laboratory or instrument measurement values or the
names of regulatory staff.

   My current thoughts are that there needs to be a separate table, perhaps
called 'changes', with attribute columns for the source table, identifying
value(s) for the original row, new value, date of change, person making the
change, and the reason for the change. The original table should have an
attribute flag to indicated that a row has been changed.

   The middleware of the application needs to check this table when data are
to be viewed in the UI and present only the current row contents. A separate
view would display a history of changes for that row.

   All thoughts, suggestions, and recommendations based on your expertise and
experience will be most welcome.

TIA,

Rich

On 01/03/2013 07:38 AM, Rich Shepard wrote:

>
>    The middleware of the application needs to check this table when data
> are
> to be viewed in the UI and present only the current row contents. A
> separate
> view would display a history of changes for that row.
>
>    All thoughts, suggestions, and recommendations based on your
> expertise and
> experience will be most welcome.

As a matter of course I include fields to record the timestamp and user
for insert of records and last update of record on my tables.

For a relatively simple solution see this blog post I put up this summer:

http://aklaver.org/wordpress/2012/06/23/postgres-and-hstore/

I have since expanded that to include updates by using TG_OP to
determine the operation being done on the table.

There is also pg_audit
:
https://github.com/jcasanov/pg_audit

>
> TIA,
>
> Rich
>
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

On Thu, 3 Jan 2013, Fabrízio de Royes Mello wrote:

> And keep in mind that kind of table tend to grow quickly, so you must use
> some strategy to purge old historical data or make your audit table
> partitioned...

Fabrizio,

   There should not be many changes in these tables. And historical data
cannot be purged or the purpose of maintaining a history is lost. The
history is valuable for tracking changes over time in regulatory agency
staff and to prevent data manipulation such as was done several years ago by
the president of Southwestern Resources (a gold mining company) to pump up
the company's stock price by changing assay results.

Rich

On Thu, 3 Jan 2013, Adrian Klaver wrote:

> As a matter of course I include fields to record the timestamp and user for
> insert of records and last update of record on my tables.

Adrian,

   This is a useful addition to the application.

> For a relatively simple solution see this blog post I put up this summer:
> http://aklaver.org/wordpress/2012/06/23/postgres-and-hstore/
> I have since expanded that to include updates by using TG_OP to determine the
> operation being done on the table.
>
> There is also pg_audit
> :
> https://github.com/jcasanov/pg_audit

   Once again you came through with valuable advice and guidance.

Many thanks!

Rich

Hi Rich,

if it's a strict legal requirement you may want to enforce it with a
trigger system, so that each time a record is inserted/updated/deleted
you create an exact copy of it in a historical table, that has the
original record plus data about who performed the operation, when,
from which IP, maybe a comment field, etc. So your actual table
remains limited in size and it's performing well, while the size
problem is local to the audit logs.

You also want to use triggers to disable updates and deletes on this
historical table, for a matter of additional security (you might end
up needing a procedure to trim it, however, if it grows out of
affordable bounds).

Cheers
Bèrto

On 3 January 2013 16:52, Rich Shepard <rshepard@appl-ecosys.com> wrote:
> On Thu, 3 Jan 2013, Adrian Klaver wrote:
>
>> As a matter of course I include fields to record the timestamp and user
>> for insert of records and last update of record on my tables.
>
>
> Adrian,
>
>   This is a useful addition to the application.
>
>
>> For a relatively simple solution see this blog post I put up this summer:
>> http://aklaver.org/wordpress/2012/06/23/postgres-and-hstore/
>> I have since expanded that to include updates by using TG_OP to determine
>> the operation being done on the table.
>>
>> There is also pg_audit
>> :
>> https://github.com/jcasanov/pg_audit
>
>
>   Once again you came through with valuable advice and guidance.
>
> Many thanks!
>
> Rich
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.

On Thu, 3 Jan 2013, Bèrto ëd Sèra wrote:

> if it's a strict legal requirement you may want to enforce it with a
> trigger system, so that each time a record is inserted/updated/deleted
> you create an exact copy of it in a historical table, that has the
> original record plus data about who performed the operation, when,
> from which IP, maybe a comment field, etc. So your actual table
> remains limited in size and it's performing well, while the size
> problem is local to the audit logs.

Bèrto,

   That's in line with Adrian's suggestion and certainly worth doing. It's
not a required legal requirement but provides the company (and potential
investors) with assurance that data have not been manipulated.

> You also want to use triggers to disable updates and deletes on this
> historical table, for a matter of additional security (you might end up
> needing a procedure to trim it, however, if it grows out of affordable
> bounds).

   Yes, the history table will be read-only to all users; writing done by
triggers only.

Much appreciated,

Rich

Hi again,

> I understand it and for this reason I said to "use some strategy to purge
> old historical data *OR* make your audit tables partitioned"...

yes, prepare to scale up in any case, even if it seems to be a remote
chance ATM. If the "untouched" nature of this data is so critical, you
have no chances to tamper with it in the future, or it will lose its
value. On the contrary, being able to scale up to a very large amount
of historical data can be sold as a plus to the same audience/market,
as you clearly are planning to "think big".

If it cannot be partitioned because of budget concerns, a low cost
alternative is to print it out and have it authenticated by a notary
(since your historical records bear a prog number you clearly cannot
hide "sections" in the process). Pretty much what you do with
book-keeping.

Cheers
Bèrto

--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.

On 4/01/2013 12:09 AM, Adrian Klaver wrote:
> On 01/03/2013 07:38 AM, Rich Shepard wrote:
>
>>
>>    The middleware of the application needs to check this table when data
>> are
>> to be viewed in the UI and present only the current row contents. A
>> separate
>> view would display a history of changes for that row.
>>
>>    All thoughts, suggestions, and recommendations based on your
>> expertise and
>> experience will be most welcome.
>
> As a matter of course I include fields to record the timestamp and
> user for insert of records and last update of record on my tables.
>
> For a relatively simple solution see this blog post I put up this summer:
>
> http://aklaver.org/wordpress/2012/06/23/postgres-and-hstore/
>
> I have since expanded that to include updates by using TG_OP to
> determine the operation being done on the table.
>
> There is also pg_audit
> :
> https://github.com/jcasanov/pg_audit

Additionally, this is an audit trigger I was using internally and
generalized:

http://wiki.postgresql.org/wiki/Audit_trigger_91plus

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

>    For several reasons (including operational and legal) once data are
> entered in a table they cannot be changed or deleted without an audit
> trail of the change, when it occurred, who made the change, and the
> reason for it.

Besides the need for storing additional information that the user who
modifies the data may be required to enter manually, couldn't the
function of simply tracking what was inserted, updated and deleted be
implemented by an "eternal" transaction log that never gets purged, but
regularly archived in partitions, PGP-signed (with a qualified key) if
required to prove authenticity?

After all, PostgreSQL maintains this automatically anyway, so why not
use it?

TIA,

Sincerely,

Wolfgang

On 12 April 2014 07:02, Moshe Jacobson <moshe@neadwerx.com> wrote:
>
> I know this is a terribly old thread, but if you are still looking for software to provide an audit trail of changes
inthe database, please see Cyan Audit at http://pgxn.org/dist/cyanaudit. I think it will do just what you're looking
for.
> (Full disclosure: I am the author of this software)

Nice! I really like the idea of "undo".

Do you plan to support primary keys other than single column integers?

Cheers,

Tony