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(at)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(dot)klaver(at)aklaver(dot)com