Thread: Auditing and Postgres 7.3
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
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: >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 >> >
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
> -----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.
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
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-----