Thread: Emit postgres log messages that have security or PII with special flags/error code/elevel

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] 
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.
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';