Обсуждение: [ADMIN] Passwords in clear text in server log

Don Seiler <don@seiler.us> writes:
> When I run a CREATE USER or ALTER USER statement and set a password for a
> user, that statement gets printed to the server log, along with the
> password, IN CLEAR TEXT.

This is why psql has provisions for encrypting a new password on the
client side --- see \password.

More generally, almost any SQL command might contain data that somebody
thinks is sensitive for some purpose or other.  If you're going to log
commands, it behooves you to make sure the log is not widely readable.
        regards, tom lane


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Wed, Oct 11, 2017 at 9:22 AM, Don Seiler <don@seiler.us> wrote:
> On Wed, Oct 11, 2017 at 9:48 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> Don Seiler <don@seiler.us> writes:
>> > When I run a CREATE USER or ALTER USER statement and set a password for
>> > a
>> > user, that statement gets printed to the server log, along with the
>> > password, IN CLEAR TEXT.
>>
>> This is why psql has provisions for encrypting a new password on the
>> client side --- see \password.
>
>
>  That's nice to have that option, but why even make it an option? If this is
> a dead horse that was finished being beaten years ago, my apologies. I'm
> curious what other non-psql clients do when allowing a user to change their
> password, I've only ever tried it with psql on the local DB host.
>
>> More generally, almost any SQL command might contain data that somebody
>> thinks is sensitive for some purpose or other.  If you're going to log
>> commands, it behooves you to make sure the log is not widely readable.
>
>
> I strongly disagree. Sure, I might have HIPAA or financial data but we're
> talking about database user security here. Why would we *ever* want that
> logged to server logs? Regardless of if it was initially transmitted over
> the wire in plain text or whatever else the client/user can control, there
> should never be a reason to log that value in clear text (IMHO). It seems
> like it would only ever be a liability. Log the CREATE/ALTER user command
> (according to the log_statement value) but mask the password.

FYI our standard hack here is to run

set log_statement='none';
alter user ...

I do agree it would be nice to have postgres stamp out the password
field with *** when logging though


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Don Seiler <don@seiler.us> writes:
> On Wed, Oct 11, 2017 at 9:48 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> This is why psql has provisions for encrypting a new password on the
>> client side --- see \password.

>  That's nice to have that option, but why even make it an option? If this
> is a dead horse that was finished being beaten years ago, my apologies.

Yes, people have complained about this before, but they're asking for
an impossibility, which is for necessarily-pretty-dumb logging code
to decide which parts of SQL commands somebody might think are sensitive.

I don't intend to spend much time arguing about this, because you can find
previous discussions in the PG archives if you're so inclined.  But I do
remember one simple counterexample: if you fat-finger the command syntax,
say
ALTER YSER joe PASSWORD 'notsosecret'

would you still expect the logging code to figure out that it should
suppress the password?
        regards, tom lane


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Scott, Don, all,

* Scott Marlowe (scott.marlowe@gmail.com) wrote:
> FYI our standard hack here is to run
>
> set log_statement='none';
> alter user ...

That's pretty terrible, frankly.

> I do agree it would be nice to have postgres stamp out the password
> field with *** when logging though

The right approach is to use SCRAM and the exported libpq functions for
generating a proper verifier that is then passed to ALTER USER, just
like \password does in psql.

Of course, SCRAM is only in v10.  The old md5 method has other issues
beyond this.  Better than all of the above is to use either Kerberos or
client-side certificates.

Thanks!

Stephen

Don,

* Don Seiler (don@seiler.us) wrote:
> Long-term I'm hoping to get our PG databases talking to our LDAP, there's a
> few internal issues and priorities that have that on the back burner for
> now.

As I understand it, you're in an Active Directory environment, where
what you really want to be using for authentication is Kerberos / GSSAPI,
not LDAP.  With LDAP, the password is still sent to the PG server in
cleartext during the authentication and that's entirely unnecessary in
an Active Directory environment where you have a Kerberos realm already
in place.

Thanks!

Stephen

Don Seiler wrote:

> If you're going to log statements that fail to parse, then yes it will make
> it harder to close these loopholes. That's also new to me, coming from a
> different RDBMS world. It logs neither bad (failed to parse) SQL nor user
> passwords.

Actually, I do wonder why we log statements that fail to parse.  Surely
the client ought to know that it failed, but what is the value of
additionally storing the query in the server log?

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Alvaro Herrera  wrote:
> Actually, I do wonder why we log statements that fail to parse.  Surely
> the client ought to know that it failed, but what is the value of
> additionally storing the query in the server log?
>

To debug clients who claim it is working on their end, but data does not change.


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Ervin Weber <webervin@gmail.com> writes:
> Alvaro Herrera  wrote:
>> Actually, I do wonder why we log statements that fail to parse.  Surely
>> the client ought to know that it failed, but what is the value of
>> additionally storing the query in the server log?

> To debug clients who claim it is working on their end, but data does not change.

We have heard many times from people who don't have enough insight, or
enough debug support client-side, to know exactly what queries their
apps are issuing.  Disabling query logging would be a horrible setback
for debuggability of such apps.  How many times have you said "consult
the postmaster log to find out what's going on"?
        regards, tom lane


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Don,

* Don Seiler (don@seiler.us) wrote:
> On Wed, Oct 11, 2017 at 3:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> > We have heard many times from people who don't have enough insight, or
> > enough debug support client-side, to know exactly what queries their
> > apps are issuing.  Disabling query logging would be a horrible setback
> > for debuggability of such apps.  How many times have you said "consult
> > the postmaster log to find out what's going on"?
>
> Not logging statements that fail to parse isn't the same as disabling query
> logging. If a statement fails to parse it can't really be considered a
> query in my opinion. When it fails to parse it *should* send a
> loud-and-clear error to whatever client-side application sent it. Granted a
> lazy developer could code their app to swallow all errors, but then I'd say
> they deserve the headache.

While I enjoy the general sentiment, it's really just overly constrained
when it comes to the development environments out there today.  Quite
often, developers aren't actually hand-crafting SQL queries but instead
letting some framework or what-have-you generate them and the error
being thrown on a parse failure could be difficult to distinguish from a
server closed connection or similar failure at the higher levels.  Yes,
ideally, that would still end up getting into a log file somewhere, but
now you're talking about the app-side log files which are often spread
across hundreds of app servers, or, at best, collected into some massive
logging system that it isn't easy to look through.

All that said, I'd be open to allowing users to decide if they wish to
log parse errors or not and perhaps we can put some caveats around that
to let people know how logging of parse errors could end up putting
things into the logs that they may not wish were there.  Further, we
could then consider doing something more interesting when it comes to
logging of ALTER ROLE statements when passwords are included, perhaps,
since the above considered switch would eliminate the concern about
syntax errors.

I'm not sure how ugly that would end up getting though, so no promises.

Thanks!

Stephen