Re: SET SESSION AUTHORIZATION superuser limitation.

On 21 December 2015 at 22:57, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Sun, Dec 20, 2015 at 1:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> The syntax you propose exposes the user's password in cleartext in
> >> the command, where it is likely to get captured in logs for example.
> >> That's not going to do.
>
> > Of course, right now, the ALTER USER ... PASSWORD command has that
> > problem which is, uh, bad.
>
> Which is why we invented the ENCRYPTED PASSWORD syntax

... which doesn't actually help anything much at all.

It prevents exposure of the user's cleartext password, sure, but the hashed
("encrypted") password passed to ALTER USER ... ENCRYPTED PASSWORD is
sufficient to log in. It substitutes for the original password entirely.

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.

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.

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.

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.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services