Обсуждение: Exclude logging certain connections?
Hi, is there a way to exclude certain connections, for example recurring monitoring connections, from the postgres.log? I found this from in old post on stackexchange.com: postgres=# create role mon login; CREATE ROLE postgres=# alter role mon set log_connections=off; ERROR: parameter "log_connections" cannot be set after connection start (source: https://dba.stackexchange.com/questions/118018/is-it-possible-to-exclude-specific-users-in-log-activity-of-postgresql) There is no connection at the time. So why do I get the error? And what can I do about it? Cheers, Paul
On 3/15/20 3:09 AM, Paul Förster wrote: > Hi, > > is there a way to exclude certain connections, for example recurring monitoring connections, from the postgres.log? > > I found this from in old post on stackexchange.com: > > postgres=# create role mon login; > CREATE ROLE > postgres=# alter role mon set log_connections=off; > ERROR: parameter "log_connections" cannot be set after connection start > > (source: https://dba.stackexchange.com/questions/118018/is-it-possible-to-exclude-specific-users-in-log-activity-of-postgresql) > > There is no connection at the time. So why do I get the error? And what can I do about it? Nothing from what I see: From here: https://www.postgresql.org/docs/12/config-setting.html#id-1.6.6.4.5 env PGOPTIONS="-c log_connections=off" psql -d test -U aklaver psql: error: could not connect to server: FATAL: permission denied to set parameter "log_connections" First problem is you need to be superuser. Alright so be superuser: date Sun Mar 15 09:24:20 PDT 2020 aklaver@maura:~> env PGOPTIONS="-c log_connections=off" psql -d test -U postgres psql (12.1) Type "help" for help. test=# \x Expanded display is on. test=# select * from pg_settings where name = 'log_connections'; -[ RECORD 1 ]---+------------------------------------ name | log_connections setting | off unit | category | Reporting and Logging / What to Log short_desc | Logs each successful connection. extra_desc | context | superuser-backend vartype | bool source | client min_val | max_val | enumvals | boot_val | off reset_val | off sourcefile | sourceline | pending_restart | f The connection is still recorded: [unknown]-[unknown]-2020-03-15 09:24:23.460 PDT-0LOG: connection received: host=[local] [unknown]-postgres-2020-03-15 09:24:23.460 PDT-0LOG: connection authorized: user=postgres database=test application_name=psql To me it looks like log_connections is all or none. > > Cheers, > Paul > -- Adrian Klaver adrian.klaver@aklaver.com
Hi Adrian, > Nothing from what I see: > > From here: > > https://www.postgresql.org/docs/12/config-setting.html#id-1.6.6.4.5 > > env PGOPTIONS="-c log_connections=off" psql -d test -U aklaver > > psql: error: could not connect to server: FATAL: permission denied to set parameter "log_connections" > > First problem is you need to be superuser. Alright so be superuser: > > date > Sun Mar 15 09:24:20 PDT 2020 > > aklaver@maura:~> env PGOPTIONS="-c log_connections=off" psql -d test -U postgres > > psql (12.1) > Type "help" for help. > > test=# \x > Expanded display is on. > test=# select * from pg_settings where name = 'log_connections'; > -[ RECORD 1 ]---+------------------------------------ > name | log_connections > setting | off > unit | > category | Reporting and Logging / What to Log > short_desc | Logs each successful connection. > extra_desc | > context | superuser-backend > vartype | bool > source | client > min_val | > max_val | > enumvals | > boot_val | off > reset_val | off > sourcefile | > sourceline | > pending_restart | f > > > The connection is still recorded: > > [unknown]-[unknown]-2020-03-15 09:24:23.460 PDT-0LOG: connection received: host=[local] > [unknown]-postgres-2020-03-15 09:24:23.460 PDT-0LOG: connection authorized: user=postgres database=test application_name=psql > > > To me it looks like log_connections is all or none. that's what I suspected. This is also what I found out so far. This is ugly because unwanted monitoring connections likethose of pgwatch2 keep spamming the logfiles this way. It would be great if there was an option to specify a list ofusers whose connections would not be logged, even with logging connect/disconnect enabled. Something like this: log_exclude_user_connect = 'pgwatch2,myself,...' in postgresql.conf Thanks very much. Cheers, Paul