Re: Exclude logging certain connections?

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