Thread: SET SESSION AUTHORIZATION superuser limitation.

Dmitry Igrishin <dmitigr@gmail.com> writes:
> There are feature which may be useful in conjunction with connection pools.
> It is the ability to change the session user without creating the new
> connection, like this:
> (pseudo REPL):
> notsuperuser > SELECT current_user, session_user;
> notsuperuser notsuperuser
> notsuperuser > SET SESSION AUTHORIZATION notsuperuser2 PASSWORD
> 'password_of_notsuperuser2';
> SET SESSION AUTHORIZATION
> notsuperuser2 > SELECT current_user, session_user;
> notsuperuser2 notsuperuser2
> notsuperuser2 > SET ROLE user3;
> notsuperuser2 > SELECT current_user, session_user;
> user3 notsuperuser2
> According to [1], SET SESSION AUTHORIZATION can only be
> used by superusers. Is it possible to extend it for use by not only
> superusers?

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.  It also assumes that the user *has* a password
that should be honored unconditionally, which is not the case in many
authentication setups.

Also, you have failed to explain why SET ROLE isn't an adequate substitute
for the cases that would plausibly be allowable to non-superusers.

Lastly, no connection pool that I would trust would use such a command
rather than maintaining separate connections for each userid.  There's
too much risk of security problems from leftover session state.
        regards, tom lane

PS: this has all been hashed out before.  See the archives.

On Sun, Dec 20, 2015 at 1:47 PM, Tom Lane <tgl@sss.pgh.pa.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.

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

Robert Haas <robertmhaas@gmail.com> writes:
> On Sun, Dec 20, 2015 at 1:47 PM, Tom Lane <tgl@sss.pgh.pa.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, as well as
psql's \password command ... but using that approach for actual
login to an account would be a security fail as well.
        regards, tom lane

On Mon, Dec 21, 2015 at 9:27 PM, Craig Ringer <craig@2ndquadrant.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