10

I need to monitor the activity of users in our databases. I have set the following parameters in postgresql.conf:

log_min_duration_statement = 0
log_connections = on
log_disconnections = on
log_line_prefix = '%t %a %d %h %u |'

However, I realize that most of the log files are filled with statements executed by the postgres user, which is used by scripts I wrote for maintenance tasks: recalculate materialized views, pg_dump, pg_restore, extract views as tabulated files, etc. The result is daily log files having over 12 Mb in size.

Is there a way to exclude specific users' activity from the log?

  • 3
    IIRC ALTER USER ... SET log_connections = off etc. – Craig Ringer Oct 14 '15 at 15:51
  • Brilliant, I'll do that. – Sébastien Clément Oct 14 '15 at 17:38
  • 1
    @CraigRinger logged in as dezso (a superuser), I always get ERROR: parameter "log_connections" cannot be set after connection start when trying to ALTER ROLE bob SET log_connections = off – dezso Oct 15 '15 at 13:48
  • @dezso Drat. Part of why I qualified with IIRC ... wasn't sure. – Craig Ringer Oct 15 '15 at 13:57
  • 1
    @CraigRinger I've tried this yesterday, thinking it was possible - then upvoted the question :D – dezso Oct 15 '15 at 14:22
5

Using the ALTER ROLE ... SET parameter; command, it was possible to tailor user-specific log parameters. Note that the parameter only takes effect after logout.

Setting log_min_duration_statement=-1 (1st login):

psql console

P:\>psql -U postgres -h 132.156.208.45 -d my_db

my_db=# SHOW log_min_duration_statement;
 log_min_duration_statement
----------------------------
 0
(1 ligne)

my_db=# SELECT COUNT(*) FROM organisms;
 count
-------
   153
(1 ligne)

my_db=# ALTER ROLE postgres SET log_min_duration_statement=-1;
ALTER ROLE

my_db=# SHOW log_min_duration_statement;
 log_min_duration_statement
----------------------------
 0
(1 ligne)

my_db=# SELECT COUNT(*) FROM mv_rings;
 count
--------
 115270
(1 ligne)

my_db=# \q

Result on the log:

Note that within that session, all statements are visible in the log even after setting log_min_duration_statement=-1.

2015-10-15 14:14:01 EDT [unknown] my_db 123.456.789.012 postgres |LOG:  connection authorized: user=postgres database=my_db
2015-10-15 14:14:08 EDT psql my_db 123.456.789.012 postgres |LOG:  duration: 0.000 ms  statement: SHOW log_min_duration_statement;
2015-10-15 14:15:26 EDT psql my_db 123.456.789.012 postgres |LOG:  duration: 32.000 ms  statement: SELECT COUNT(*) FROM organisms;
2015-10-15 14:15:45 EDT psql my_db 123.456.789.012 postgres |LOG:  duration: 31.000 ms  statement: ALTER ROLE postgres SET log_min_duration_statement=-1;
2015-10-15 14:16:08 EDT psql my_db 123.456.789.012 postgres |LOG:  duration: 0.000 ms  statement: SHOW log_min_duration_statement;
2015-10-15 14:17:10 EDT psql my_db 123.456.789.012 postgres |LOG:  duration: 2059.000 ms  statement: SELECT COUNT(*) FROM mv_rings;
2015-10-15 14:17:29 EDT psql my_db 123.456.789.012 postgres |LOG:  disconnection: session time: 0:03:27.450 user=postgres database=my_db host=123.456.789.012 port=65269

Seeing the effect of log_min_duration_statement (2nd login):

psql console

P:\>psql -U postgres -h 132.156.208.45 -d my_db

my_db=# SHOW log_min_duration_statement;
 log_min_duration_statement
----------------------------
 -1
(1 ligne)

my_db=# SELECT COUNT(*) FROM germplasms;
 count
--------
 475290
(1 ligne)

my_db=# \q

Result on the log:

As expected none of the statements are logged.

2015-10-15 14:17:44 EDT [unknown] my_db 123.456.789.012 postgres |LOG:  connection authorized: user=postgres database=my_db
2015-10-15 14:20:27 EDT psql my_db 123.456.789.012 postgres |LOG:  disconnection: session time: 0:02:43.333 user=postgres database=my_db host=123.456.789.012 port=49372

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.