Re: SET SESSION AUTHORIZATION superuser limitation.

On Mon, Dec 21, 2015 at 9:27 PM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> Right now the logs just have to be treated as security critical. Which
> sucks, but is not easily solved.
>
> Nothing is going to stop:
>
> ALTER USER fred PASSSSWORD 'sekrit';
>
> from logging the password in a syntax error. But it'd be nice to let utility
> commands define a log hook that lets them emit a sanitized version of
> themselves based on their parse tree representation to the logs.

+1. Although, figuring out how to construct that sanitized version is
not altogether trivial. Maybe instead of sanitizing, we should just
have a way that the log messages says "<query text redacted, user
password change>" or something like that.

> Except that users will want to be able to mask log output too. I see lots of
> questions about how to stop pgcrypto sql function calls from exposing key
> materials in the logs. Right now the answer is "you can't". With logging
> based on the raw statement text before parsing I don't see any way to change
> that. I advise people to do their symmetric crypto and their secret key
> operations in the application instead, which has the advantage of also
> better isolating the key material from its persistent storage in the
> database.

This is a much harder problem. I don't think we can realistically
call user-defined code and ask it whether it would like to be logged.
In the first place, malicious people might say "no" for reasons of
which we don't approve. In the second place, we haven't even finished
parsing at this point, so we haven't done anything like permissions
checks or locking yet. We therefore can't just go start calling
user-defined functions.

> We have to be able to emit syntax errors and other things that use the raw
> SQL text. We also don't have any functionality to turn a parsetree back into
> SQL text with parts of it masked out, and it'd be impractical to do that
> just for logging anyway.

Agreed.

> I can see it being useful to be able to set a session level flag that limits
> logging to command tags, not command text. Let the superuser GRANT the right
> to set it to other users. Use a GUC to toggle it, preferably via SET LOCAL.
> It has to be session level not statement level because we've got no way to
> set generic options per-statement, and plus that'd risk leaking the
> statement on a parse error. We'd probably replace the statement text with a
> string like 'PARSE_ERROR' until the command tag was known, then replace it
> with the command tag. This would reduce the audit utility of the logs a
> little, but if it's superuser-only unless granted you're already stuffed if
> someone who's not meant to gets hold of it.

Hmm, not sure how useful this is, really...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company