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

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

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:

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

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

-----Original Message-----
From: Murray Prior Hobbs [mailto:murray@efone.com]
Sent: 23 January 2002 12:57
To: Justin Clift
Cc: Gavin Sherry; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Auditing and Postgres 7.3

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

I may be way off (often am in fact :-) ), but isn't this basically the time
travel that I believe was removed in 6.3?

/Dave.

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

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