Infrastructure at your Service

This is another post dedicated to EnterpriseDB Postgres. Sometimes you may want to get specific messages not getting logged to the server’s logfile or audit records. That might be specific error codes or, even more important, passwords you specify when you create users. EDB comes with a solution for that by providing an extension which is called EDB Filter Log. Lets see how you can install, and even more important, how to use that extension.

The first thing I usually do when I want to check what extensions are available is looking at . I was quite surprised that this extension is not listed there:

edb=# select * from pg_available_extensions where name like '%filter%';
 name | default_version | installed_version | comment 
------+-----------------+-------------------+---------
(0 rows)

Anyway you can load it by adjusting the shared_preload_libraries parameter:

edb=# show shared_preload_libraries;
             shared_preload_libraries              
---------------------------------------------------
 $libdir/dbms_pipe,$libdir/edb_gen,$libdir/dbms_aq
(1 row)
edb=# alter system set shared_preload_libraries='$libdir/dbms_pipe,$libdir/edb_gen,$libdir/dbms_aq,$libdir/edb_filter_log';
ALTER SYSTEM
edb=# \q
[email protected]:/var/lib/edb/ [pg1] pg_ctl -D $PGDATA restart -m fast
[email protected]:/var/lib/edb/ [pg1] psql edb
psql.bin (11.2.9)
Type "help" for help.


edb=# show shared_preload_libraries ;
                         shared_preload_libraries                         
--------------------------------------------------------------------------
 $libdir/dbms_pipe,$libdir/edb_gen,$libdir/dbms_aq,$libdir/edb_filter_log
(1 row)

But even then the extension does not show up in pg_available_extensions:

edb=# select * from pg_available_extensions where name like '%filter%';
 name | default_version | installed_version | comment 
------+-----------------+-------------------+---------
(0 rows)

Lets assume you do not want violations on unique constraints to get logged in the server’s logfile. Usually you get this in the log file once a constraint is violated:

edb=# create table t1 ( a int );
CREATE TABLE
edb=# create unique index i1 on t1(a);
CREATE INDEX
edb=# insert into t1 values(1);
INSERT 0 1
edb=# insert into t1 values(1);
ERROR:  duplicate key value violates unique constraint "i1"
DETAIL:  Key (a)=(1) already exists.
edb=# select pg_current_logfile();
      pg_current_logfile       
-------------------------------
 log/edb-2019-03-24_162021.log
(1 row)
edb=# \! tail -20 $PGDATA/log/edb-2019-03-24_162021.log
...
2019-03-24 16:35:32 CET ERROR:  duplicate key value violates unique constraint "i1"
2019-03-24 16:35:32 CET DETAIL:  Key (a)=(1) already exists.
...

Using the extension you can do it like this (23505 is the SQLSTATE for unique constraint violations):

edb=# show edb_filter_log.errcode;
 edb_filter_log.errcode 
------------------------
 
(1 row)

edb=# alter system set edb_filter_log.errcode='23505';
ALTER SYSTEM
edb=# select context from pg_settings where name = 'edb_filter_log.errcode';
 context 
---------
 user
(1 row)
edb=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

edb=# show edb_filter_log.errcode;
 edb_filter_log.errcode 
------------------------
 23505
(1 row)

edb=# insert into t1 values(1);
ERROR:  duplicate key value violates unique constraint "i1"
DETAIL:  Key (a)=(1) already exists.
edb=# \! tail -20 $PGDATA/log/edb-2019-03-24_162021.log
...
2019-03-24 16:39:05 CET LOG:  received SIGHUP, reloading configuration files
2019-03-24 16:39:05 CET LOG:  parameter "edb_filter_log.errcode" changed to "23505"
edb=# 

This specific error is not any more reported in the logfile. Of course can use multiple codes for edb_filter_log.errcode by separating them with a comma. The complete list of codes is documented here.

This is for suppressing messages in the log file. What about passwords? Imagine you are logging all statements:

edb=# alter system set log_statement='all';
ALTER SYSTEM
edb=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

edb=# show log_statement;
 log_statement 
---------------
 all
(1 row)

In this configuration this will be captured as well and you will find the password in the logfile:

edb=# create user u1 with login password 'password';
CREATE ROLE
edb=# select pg_current_logfile();
      pg_current_logfile       
-------------------------------
 log/edb-2019-03-24_162021.log
edb=# \! tail -20 $PGDATA/log/edb-2019-03-24_162021.log | grep password

2019-03-24 16:46:59 CET LOG:  statement: create user u1 with login password 'password';

This is what you usually do not want to see there and exactly this is what “edb_filter_log.redact_password_commands” is for:

edb=# alter system set edb_filter_log.redact_password_commands = true;
ALTER SYSTEM
edb=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

edb=# show edb_filter_log.redact_password_commands;
 edb_filter_log.redact_password_commands 
-----------------------------------------
 on
(1 row)

When this is set to on the plain text password will not be anymore written to the log file when you create or alter users:

edb=# \! tail -20 $PGDATA/log/edb-2019-03-24_162021.log | grep secret
2019-03-24 16:51:19 CET STATEMENT:  create user u2 login with password 'secret';
2019-03-24 16:51:28 CET LOG:  statement: create user u2 with login password 'secret';

… and it is still there. A restart is required for that becoming active?:

[email protected]:/var/lib/edb/as11/data/ [pg1] pg_ctl -D $PGDATA restart -m fast
[email protected]:/var/lib/edb/as11/data/ [pg1] psql -X edb
psql.bin (11.2.9)
Type "help" for help.

edb=# create user u3 with login password 'topsecret';
CREATE ROLE
edb=# select pg_current_logfile();
      pg_current_logfile       
-------------------------------
 log/edb-2019-03-24_165229.log
(1 row)

edb=# \! tail -20 $PGDATA/log/edb-2019-03-24_165229.log | grep topsecret
2019-03-24 16:54:22 CET LOG:  statement: create user u3 with login password 'topsecret';

And we do still see it in the log file, why that? The issue is with the syntax. Consider this:

edb=# create user u4 with login password 'text';
CREATE ROLE
edb=# create user u5 login password 'text2';
CREATE ROLE
edb=# create user u6 password 'text3';
CREATE ROLE
edb=# 

Only the last command will replace the password in the log file:

2019-03-24 17:03:31 CET LOG:  statement: create user u4 with login password 'text';
2019-03-24 17:03:45 CET LOG:  statement: create user u5 login password 'text2';
2019-03-24 17:04:12 CET LOG:  statement: create user u6 password 'x';

You have to follow exactly this syntax:

{CREATE|ALTER} {USER|ROLE|GROUP} identifier { [WITH] [ENCRYPTED]
PASSWORD 'nonempty_string_literal' | IDENTIFIED BY {
'nonempty_string_literal' | bareword } } [ REPLACE {
'nonempty_string_literal' | bareword } ]

…otherwise it will not work.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure