Hi all,

I've been thinking implementing auditing for Postgres 7.3 and wanted to
see if anyone had any thoughts about it.

Auditing would allow a user to log queries executed upon different
'schema' objects - I use the loose sense of the word here. The user would
be able to define the type of query - insert, delete, etc - as well as
choose to log only those queries which were successful or otherwise.

The superuser would be able to audit unprivileged users. Unprivileged
users would only be able to produce an audit trail upon objects which
he/she owns or has been granted audit privileges to.

The audit trail would be written either to a new internal system table,
pg_audit, or optionally a file on the file system. I imagine that an
external program would also be needed to read/dump the audit trail.

So what would an audit trail consist of?

timestamp
query type
query
query result (successful|unsuccessful)
audit object oid

I haven't really thought about this too hard just yet but thought I'd see
if people considered this to be a useful addition to Postgres or not, or
if I was going about this the wrong way.

Gavin


Wed, 23 Jan 2002 21:18:57 +1100 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Auditing and Postgres 7.3
Hi Gavin,

I can see the usefulness of this concept from a "Data Security" point of
view.

At one place I worked, it was known one of the marketing people had a
reputation of gathering customer details before leaving a job, just so
he had something to bargain a pay increase with for his next job.  Don't
know why people hire a guy like that (I wouldn't), but these people
exist.

It should definitely be optional, and if not turned on for an object I
don't think it should have an associated noticable performance penalty.

My thought is useful, but not sure how urgent when compared to other
improvements.

:)

+ Justin


Gavin Sherry wrote:
> 
> Hi all,
> 
> I've been thinking implementing auditing for Postgres 7.3 and wanted to
> see if anyone had any thoughts about it.
> 
> Auditing would allow a user to log queries executed upon different
> 'schema' objects - I use the loose sense of the word here. The user would
> be able to define the type of query - insert, delete, etc - as well as
> choose to log only those queries which were successful or otherwise.
> 
> The superuser would be able to audit unprivileged users. Unprivileged
> users would only be able to produce an audit trail upon objects which
> he/she owns or has been granted audit privileges to.
> 
> The audit trail would be written either to a new internal system table,
> pg_audit, or optionally a file on the file system. I imagine that an
> external program would also be needed to read/dump the audit trail.
> 
> So what would an audit trail consist of?
> 
> timestamp
> query type
> query
> query result (successful|unsuccessful)
> audit object oid
> 
> I haven't really thought about this too hard just yet but thought I'd see
> if people considered this to be a useful addition to Postgres or not, or
> if I was going about this the wrong way.
> 
> Gavin
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi

Wed, 23 Jan 2002 21:53:04 +1100
Justin Clift <justin@postgresql.org>
Re: Auditing and Postgres 7.3
On Wed, 23 Jan 2002, Justin Clift wrote:

> Hi Gavin,
> 
> I can see the usefulness of this concept from a "Data Security" point of
> view.
> 
> At one place I worked, it was known one of the marketing people had a
> reputation of gathering customer details before leaving a job, just so
> he had something to bargain a pay increase with for his next job.  Don't
> know why people hire a guy like that (I wouldn't), but these people
> exist.

This is an administration/management issue. Whilst auditing of a database
would provide you with a large amount of data about the actions of, say,
the marketing department, it is not a solution to the problem. The real
solution is solved at the system privileges/application level.

An auditing trail is much more useful for application debugging, load
assessment, planning etc.

> 
> It should definitely be optional, and if not turned on for an object I
> don't think it should have an associated noticable performance penalty.

A user would turn auditing on. Eg

AUDIT INSERT;

or,

AUDIT ALTER ON TABLE users WHENEVER NOT SUCCESSFUL;

This pretty much replicates the oracle syntax. How much it would different
from this kind of syntax I don't know.

Auditing would result in performance issues -- but new features generally
do. The thing with auditing is that it would generally not be used in
production systems which relied on high performance. There is still the
performance cost of hitting the cache to see if the particular query is to
be audited. I would look at this cost when I started to implement it.

Thanks,

Gavin


Wed, 23 Jan 2002 23:45:41 +1100 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: Auditing and Postgres 7.3

the lack of a true full audit trail capabiity in postgres is perhaps 
it's biggest fundamental weakness as a "commercial" use system

it has "commercial" use viability at this moment like the XT had 
"commercial" use viabilty in the early 80's

ie it's demand driven in a market where many (un aware or unconcrned) 
people/businesses are prepared to pay for something that's really not 
the real thing (hope i havn't broken and stupid copyright laws there)

in fact. if i was to want to design a database system for "commercial" 
use the very first thing i would start with would be the audit system

objects oriented? no, after audit

referenential integrety?, no, after audit

really - even just on a practicality basis the audit is essential

there needs to be a front end to the database - a completely new layer - 
that layer feeds the database and no other and that layer is itself the 
audit trail

it should be possible to run an audit trail backwards against a database 
and undo everything back to an earlier state (assuming that this is done 
in standalone mode)

the audit then IS the database - or rather it IS the data - all of it - 
and ideally it wold be in a form that is almost human readable

just MHO

m




Justin Clift wrote:

>Hi Gavin,
>
>I can see the usefulness of this concept from a "Data Security" point of
>view.
>
>At one place I worked, it was known one of the marketing people had a
>reputation of gathering customer details before leaving a job, just so
>he had something to bargain a pay increase with for his next job.  Don't
>know why people hire a guy like that (I wouldn't), but these people
>exist.
>
>It should definitely be optional, and if not turned on for an object I
>don't think it should have an associated noticable performance penalty.
>
>My thought is useful, but not sure how urgent when compared to other
>improvements.
>
>:)
>
>+ Justin
>
>
>Gavin Sherry wrote:
>
>>Hi all,
>>
>>I've been thinking implementing auditing for Postgres 7.3 and wanted to
>>see if anyone had any thoughts about it.
>>
>>Auditing would allow a user to log queries executed upon different
>>'schema' objects - I use the loose sense of the word here. The user would
>>be able to define the type of query - insert, delete, etc - as well as
>>choose to log only those queries which were successful or otherwise.
>>
>>The superuser would be able to audit unprivileged users. Unprivileged
>>users would only be able to produce an audit trail upon objects which
>>he/she owns or has been granted audit privileges to.
>>
>>The audit trail would be written either to a new internal system table,
>>pg_audit, or optionally a file on the file system. I imagine that an
>>external program would also be needed to read/dump the audit trail.
>>
>>So what would an audit trail consist of?
>>
>>timestamp
>>query type
>>query
>>query result (successful|unsuccessful)
>>audit object oid
>>
>>I haven't really thought about this too hard just yet but thought I'd see
>>if people considered this to be a useful addition to Postgres or not, or
>>if I was going about this the wrong way.
>>
>>Gavin
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 4: Don't 'kill -9' the postmaster
>>
>




Wed, 23 Jan 2002 23:56:47 +1100
Murray Prior Hobbs <murray@efone.com>
Re: Auditing and Postgres 7.3
Murray,

On Wed, 23 Jan 2002, Murray Prior Hobbs wrote:

> in fact. if i was to want to design a database system for "commercial" 
> use the very first thing i would start with would be the audit system
> 
> objects oriented? no, after audit
> 
> referenential integrety?, no, after audit
> 
> really - even just on a practicality basis the audit is essential
> 
> there needs to be a front end to the database - a completely new layer - 
> that layer feeds the database and no other and that layer is itself the 
> audit trail
> 
> it should be possible to run an audit trail backwards against a database 
> and undo everything back to an earlier state (assuming that this is done 
> in standalone mode)

You seem to be confusing my defition of audit (track user queries, not
the effect they have on the database) with 'transaction logging' or
'journalling'. The latter is the job of the write-ahead log already
implemented in Postgres.

Gavin



Thu, 24 Jan 2002 00:01:21 +1100 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: Auditing and Postgres 7.3
On Wednesday 23 January 2002 07:56 am, Murray Prior Hobbs wrote:
> the lack of a true full audit trail capabiity in postgres is perhaps
> it's biggest fundamental weakness as a "commercial" use system

> in fact. if i was to want to design a database system for "commercial"
> use the very first thing i would start with would be the audit system

Understand that Postgres was *never* designed to be a 'commercial' use system 
from the ground up. It was originally designed as a research platform.  We 
are hammering at it becoming commercial-grade, though.

Gavin's idea sounds OK to me -- although you can get that information now 
from the query logs if you set the logging up.  It's not per-table --- and I 
like the per-table idea.  This audit trail would make a handy debugging log 
as well.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Wed, 23 Jan 2002 08:09:41 -0500
Lamar Owen <lamar.owen@wgcr.org>
Re: Auditing and Postgres 7.3
I think that having an audit trail would be a very good feature for
PostgreSQL.  And I especially like the idea of superuser being able to
audit unprivileged users (Turn it on for users one things are abusing
the system, etc) rather than just on specific tables.

On Wed, 2002-01-23 at 03:18, Gavin Sherry wrote:
> Hi all,
> 
> I've been thinking implementing auditing for Postgres 7.3 and wanted to
> see if anyone had any thoughts about it.
> 
> Auditing would allow a user to log queries executed upon different
> 'schema' objects - I use the loose sense of the word here. The user would
> be able to define the type of query - insert, delete, etc - as well as
> choose to log only those queries which were successful or otherwise.
> 
> The superuser would be able to audit unprivileged users. Unprivileged
> users would only be able to produce an audit trail upon objects which
> he/she owns or has been granted audit privileges to.
> 
> The audit trail would be written either to a new internal system table,
> pg_audit, or optionally a file on the file system. I imagine that an
> external program would also be needed to read/dump the audit trail.
> 
> So what would an audit trail consist of?
> 
> timestamp
> query type
> query
> query result (successful|unsuccessful)
> audit object oid
> 
> I haven't really thought about this too hard just yet but thought I'd see
> if people considered this to be a useful addition to Postgres or not, or
> if I was going about this the wrong way.
> 
> Gavin
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
-- 

Virtually, 
Ned Wolpert <ned.wolpert@knowledgenet.com>

D08C2F45:  28E7 56CB 58AC C622 5A51  3C42 8B2B 2739 D08C 2F45
23 Jan 2002 08:26:47 -0700
Ned Wolpert <ned.wolpert@knowledgenet.com>
Re: Auditing and Postgres 7.3
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> I've been thinking implementing auditing for Postgres 7.3 and wanted to
> see if anyone had any thoughts about it.
< snip >
> So what would an audit trail consist of?
>
> timestamp
> query type
> query
> query result (successful|unsuccessful)
> audit object oid

We have implemented an audit trail at the application level several times for 
several different projects.  While what you have described in this thread is 
more aimed at debugging, what we have used it for is to see who changed what 
value to what when.  We can show the complete history of all the values for 
parts of our schema, and who changed them when.  Very useful for a number of 
situation.  This I would be a nice feature to have the database manage.  
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE8Tty+8BXvT14W9HARAoFVAJ9fMHF9q0QhcRQSdmmGbTZX3P1vyACgkquy
WU0WElwZoF6LOP9yNgIvf1c=
=RazC
-----END PGP SIGNATURE-----

Wed, 23 Jan 2002 09:54:35 -0600
"Matthew T. O'Connor" <matthew@zeut.net>
Re: Auditing and Postgres 7.3