Thread: Emit postgres log messages that have security or PII with special flags/error code/elevel
Emit postgres log messages that have security or PII with special flags/error code/elevel
From
Bharath Rupireddy
Date:
Hi,
Today, postgres doesn't distinguish the log messages that it emits to server logs via ereport/elog mechanism, based on security information or PII (Personally Identifiable Information) or other sensitive information [1]. In production environments, these log messages would be captured and stored (perhaps in a different intermediate database specially designed for text and log analytics) for debug, analytical, reporting or on-demand-delivery to the customers via portal/tools. In this context, the customers will expect to treat the sensitive information differently (perhaps encode/mask before storing) for security and compliance purposes. Also, it's not safe to show all the log messages as-is for internal debugging purposes as the sensitive information can be misused intentionally or unintentionally.
Today, one can implement an emit_log_hook which can look for sensitive log messages based on the errmsg i.e. "text" and treat them differently. But the errmsg based approach has its own disadvantages - errmsg can get tweaked, there can be too many sensitive type log messages, not everyone can rightly distinguish what a sensitive log message is and what is not, the hook implementation and maintainability is a huge problem in the long run.
Here's an idea - what if postgres can emit log messages that have sensitive information with special error codes or flags? The emit_log_hook implementers will then just need to look for those special error codes or flags to treat them differently.
Thoughts?
[1]
Today, one can implement an emit_log_hook which can look for sensitive log messages based on the errmsg i.e. "text" and treat them differently. But the errmsg based approach has its own disadvantages - errmsg can get tweaked, there can be too many sensitive type log messages, not everyone can rightly distinguish what a sensitive log message is and what is not, the hook implementation and maintainability is a huge problem in the long run.
Here's an idea - what if postgres can emit log messages that have sensitive information with special error codes or flags? The emit_log_hook implementers will then just need to look for those special error codes or flags to treat them differently.
Thoughts?
[1]
errmsg("role \"%s\" cannot be dropped because some objects depend on it"
errmsg("role \"%s\" already exists"
errmsg("must have admin option on role \"%s\""
errmsg("role \"%s\" is a member of role \"%s\""
errmsg("must have admin option on role \"%s\""
errmsg("pg_hba.conf rejects replication connection for host \"%s\", user \"%s\", %s"
errmsg("duplicate key value violates unique constraint \"%s\""
log_connections and log_disconnections messages
.....
.....
.....
Regards,
Bharath Rupireddy.Re: Emit postgres log messages that have security or PII with special flags/error code/elevel
From
Julien Rouhaud
Date:
Hi, On Mon, Jun 27, 2022 at 06:41:21PM +0530, Bharath Rupireddy wrote: > > Here's an idea - what if postgres can emit log messages that have sensitive > information with special error codes or flags? The emit_log_hook > implementers will then just need to look for those special error codes or > flags to treat them differently. This has been discussed multiple times in the past, and always rejected. The main reason for that is that it's impossible to accurately determine whether a message contains sensitive information or not, and if it were there wouldn't be a single definition that would fit everyone. As a simple example, how would you handle the log emitted by this query? ALTERR OLE myuser WITH PASSWORD 'my super secret password';