I've defined some PL/pgSQL functions which get invoked from the client (e.g. psql, but could be JDBC/Hibernate as well), which is connected using a non super user role. e.g. SELECT chk_pwd('(some.user,example.com)', 'P@ssW0rd');
I've also set log_statement = 'none'
in the postgresql.conf of my server. That stops logging of all SQL statements. However, the signatures and invocations of the PL/pgSQL functions are still being logged, leaking sensitive data into the log files.
How do I prevent this?
On further investigation, it looks like the RAISE LOG statement in my function is causing this additional information to be printed. So, although it is just a log message, it seems to be getting treated as an error. Please correct me if I'm mistaken, but from reading the manual, there does not seem to be a way to suppress these additional messages while only printing the message from the RAISE LOG statement alone. An option like log_log_verbosity would have been useful.
2021-02-27 17:42:58.483 IST [13008] myrole@test_db CONTEXT: PL/pgSQL function chk_pwd(email_t,character varying) line 10 at RAISE
2021-02-27 17:42:58.483 IST [13008] myrole@test_db STATEMENT: SELECT chk_pwd('(some.user,example.com)', 'P@ssW0rd');
Finally, as per postgresql.conf, the definition of log_min_error_statement considers log at a higher error level than error, which is quite unintutive.I ended up resolving the situation by using RAISE NOTICE instead of RAISE LOG. However, the verbosity of NOTICE is controlled by log_error_verbosity. So, it continues to show the CONTEXT, though thankfully not the STATEMENT, thus preventing the leakage of sensitive values from the function parameters.
Strictly speaking, I would not expect log_error_verbosity to be applicable to NOTICE, since it is not an error. Verbosity for NOTICE should be controlled by log_notice_verbosity (or log_non_error_verbosity), but there is no such setting available.
Best Answer
The presence of
CONTEXT
depends onlog_error_verbosity
.Another setting you might want to tune down is
log_min_error_statement
.From the documentation:
log_min_error_statement (enum)
log_error_verbosity (enum)