5

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 Nov 26 '17 at 13:26
5

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.