pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDatabase audit / Associating a statement with connection
4+ messages / 2 participants
[nested] [flat]
* Database audit / Associating a statement with connection
@ 2005-02-25 18:24 Ben Kim <bkim@coe.tamu.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Ben Kim @ 2005-02-25 18:24 UTC (permalink / raw)
To: pgsql-admin
I have some questions and would like help.
(1) Audit practice
Is there a good source of information about audit for postgresql
databases?
Specifically I'd like some advice on these:
(2) Associating a statement with a database connection
I'm logging statements and connections, but it seems the statement is not
associated with the connection in the log.
- log_connections = true
- log_statement = true
connection received: host=128.91.23.43 port=35644
query: SELECT ID, ....
Is there a way to associate them so I know which statement came from which
connection?
(3) Associating a web connection with a database connection
Also, if I have a web frontend for the database, what would be the best
practice to associate a statement with a particular web client IP?
For example, I have in the postgresql log
connection received: host=128.91.23.43 port=35644
where 128.91.23.43 is my web server.
The web server log records all client connections and accessed pages, but
I don't have a good method of associating the web client connections with
the database client connections.
I'd appreciate any pointers.
Regards,
Ben Kim
Database Developer/Systems Administrator
434E Harrington Tower / College of Education
Texas A&M University
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Database audit / Associating a statement with connection
@ 2005-02-25 19:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ben Kim <bkim@coe.tamu.edu>
0 siblings, 2 replies; 4+ messages in thread
From: Tom Lane @ 2005-02-25 19:11 UTC (permalink / raw)
To: Ben Kim <bkim@coe.tamu.edu>; +Cc: pgsql-admin
Ben Kim <bkim@coe.tamu.edu> writes:
> Is there a way to associate them so I know which statement came from which
> connection?
Log the PID originating each message.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Database audit / Associating a statement with connection
@ 2005-02-27 03:26 Ben Kim <bkim@coe.tamu.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Ben Kim @ 2005-02-27 03:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
> > Is there a way to associate them so I know which statement came from which
> > connection?
>
> Log the PID originating each message.
Thanks.
Ben Kim
Database Developer/Systems Administrator
434E Harrington Tower / College of Education
Texas A&M University
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Database audit / Associating a statement with connection
@ 2005-04-16 04:07 Ben Kim <bkim@coe.tamu.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Ben Kim @ 2005-04-16 04:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
>Ben Kim <bkim@coe.tamu.edu> writes:
>> Is there a way to associate them so I know which statement came from which
>> connection?
> Log the PID originating each message.
> regards, tom lane
This is a follow up in addition to the log pid. It's a vanilla solution,
but we tried comments like this in the sql statements.
in perl:
$sql=$sql." -- IP=$ENV{REMOTE_ADDR},ID=$id,PAGE=$ENV{SCRIPT_FILENAME}";
or in jsp:
sql = "select ... from customers -- " + "url: " + request.getRequestURI()
+ " ip: " + request.getRemoteAddr();
This worked, and added the remote ip address and the page it came from
into the postgresql log nicely without needing a full setup. Till now we
didn't see a problem with it. Hoped someone might find this useful.
Regards,
Ben Kim
Database Developer/Systems Administrator
434E Harrington Tower / College of Education
Texas A&M University
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-04-16 04:07 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-25 18:24 Database audit / Associating a statement with connection Ben Kim <bkim@coe.tamu.edu>
2005-02-25 19:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-27 03:26 ` Ben Kim <bkim@coe.tamu.edu>
2005-04-16 04:07 ` Ben Kim <bkim@coe.tamu.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox