Re: [GENERAL] Database Design: Maintain Audit Trail of Changes

[Moshe Jacobson]

On Sat, Apr 12, 2014 at 2:57 AM, Tony Theodore  wrote:

>
> I know this is a terribly old thread, but if you are still looking for
>> software to provide an audit trail of changes in the database, please see
>> Cyan Audit at http://pgxn.org/dist/cyanaudit. I think it will do just
>> what you're looking for.
>>
>

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

I may modify it to support bigints, but I don't have any plans to support
multi-column primary keys. It would require too much overhaul of the code.

Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc. 
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339

"Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] Database Design: Maintain Audit Trail of Changes

[Tony Theodore]

On 12 April 2014 07:02, Moshe Jacobson  wrote:
>
> I know this is a terribly old thread, but if you are still looking for 
> software to provide an audit trail of changes in the 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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database Design: Maintain Audit Trail of Changes

[Moshe Jacobson]

I know this is a terribly old thread, but if you are still looking for
software to provide an audit trail of changes in the 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)


Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc. 
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339

"Quality is not an act, it is a habit." -- Aristotle


On Thu, Jan 3, 2013 at 10:38 AM, Rich Shepard wrote:

>   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
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Database Design: Maintain Audit Trail of Changes

[Wolfgang Keller]

>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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database Design: Maintain Audit Trail of Changes

[Stevo Slavić]

In Java world, for this purpose I tend to use JPA/Hibernate with Envers
http://www.jboss.org/envers - db vendor agnostic solution.

Kind regards,
Stevo Slavic.


On Tue, Jan 8, 2013 at 6:32 AM, Craig Ringer  wrote:

> 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
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Database Design: Maintain Audit Trail of Changes

[Craig Ringer]

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



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database Design: Maintain Audit Trail of Changes

[Bèrto ëd Sèra]

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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database Design: Maintain Audit Trail of Changes

[Fabrízio de Royes Mello]

On Thu, Jan 3, 2013 at 2:50 PM, Rich Shepard 
wrote:
>
>   There should not be many changes in these tables.

Ok.

>
> 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.

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

regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello

Re: [GENERAL] Database Design: Maintain Audit Trail of Changes

[Rich Shepard]

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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database Design: Maintain Audit Trail of Changes

[Bèrto ëd Sèra]

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  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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database Design: Maintain Audit Trail of Changes

[Rich Shepard]

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

Re: [GENERAL] Database Design: Maintain Audit Trail of Changes

[Rich Shepard]

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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database Design: Maintain Audit Trail of Changes

[Fabrízio de Royes Mello]

On Thu, Jan 3, 2013 at 2:09 PM, 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
>

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...

I implemented the same think in our ERP a long time ago using partitioned
approach, because its easy to purge old historical data.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello

Re: [GENERAL] Database Design: Maintain Audit Trail of Changes

[Adrian Klaver]

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.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general