How to log all SQL queries for a single connection or user or client host?

Question

Is there a way to log all SQL queries in Postgres 9.3.9 for a single connection or user or client host?

In other words, I want log_statement = all but only for a certain username or client hostname or client ip or connection id.

One option is to log SQL activity for all clients and search the logs. I'm hoping there's a better way.

To allow filtering logs you need to setup log_line_prefix. — vaughan
– vaughan, Commented Nov 26, 2017 at 13:26

jjanes · Accepted Answer · 2015-09-25 15:39:39Z

I don't know how to do it for a client host, but for a single database user, you can use this (as a superuser):

ALTER ROLE someone SET log_statement TO 'all';

Which will then turn on log_statement every time that that someone connects in the future.

For a single connection which is cooperating with you, you could create this function (as a superuser):

create or replace function set_log_statement() returns void as $$
 set log_statement='all' 
$$ language sql security definer ;

And then have the desired single connection invoke this function to turn on logging for itself.

1 Answer 1