3

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.

2
  • It appears that your function raises an error and that's why it's logged; you should probably investigate why it raises an error.
    – mustaccio
    Feb 27, 2021 at 14:06
  • @mustacio. It looks like the RAISE LOG statement in my function is causing this additional information to be printed; there is no query level error. Feb 27, 2021 at 16:05

3 Answers 3

0

The presence of CONTEXT depends on log_error_verbosity.

Another setting you might want to tune down is log_min_error_statement.

From the documentation:

log_min_error_statement (enum)

Controls which SQL statements that cause an error condition are recorded in the server log. The current SQL statement is included in the log entry for any message of the specified severity or higher. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC. The default is ERROR, which means statements causing errors, log messages, fatal errors, or panics will be logged. To effectively turn off logging of failing statements, set this parameter to PANIC. Only superusers can change this setting.

log_error_verbosity (enum)

Controls the amount of detail written in the server log for each message that is logged. Valid values are TERSE, DEFAULT, and VERBOSE, each adding more fields to displayed messages. TERSE excludes the logging of DETAIL, HINT, QUERY, and CONTEXT error information. VERBOSE output includes the SQLSTATE error code (see also Appendix A) and the source code file name, function name, and line number that generated the error. Only superusers can change this setting.

1
  • Thanks @Daniel. It looks like the RAISE LOG statement in my function is causing this additional information to be printed. Is there a way to suppress this additional information for that specific function so that only my log message is printed in the log file without the additional CONTEXT and STATEMENT? Feb 27, 2021 at 16:12
2

The message level LOG is for information that should be written to the log file. So if you don't want the information to get logged, use RAISE NOTICE, which gets sent to the client by default.

0

I agree with author that there should be verbosity option per log level and by default should be enabled (like CONTEXT) above WARNING. Usually when I log a notice I'm interested only with my message. If I want a context or a statement there should be an option / command to generate such. If there is option to disable them globally then even when context is needed like errors it will be disabled.

Programmer should choose what kind of action is expected - log level and with or without stack trace (CONTEXT or STATEMENT) per each log or globally per each log level.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.