Обсуждение: Database audit / Associating a statement with connection

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

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

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

>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