Обсуждение: [ADMIN] Passwords in clear text in server log
www.seiler.us
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
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.
www.seiler.us
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
FYI our standard hack here is to run
set log_statement='none';
alter user ...
I've seen that suggested in some forums as well. Then you aren't logging the fact that the password was changed at all. I think you'd still want to know of the fact that it occurred, but my suggestion is that we shouldn't be logging the value.
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
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?
www.seiler.us
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
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.
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
enough debug support client-side, to know exactly what queries theirWe have heard many times from people who don't have enough insight, or
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"?
www.seiler.us
"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"?"
I completely agree. There are many cases, not just edge cases, where this has been vital to isolate and resolve issues.
Sent: Wednesday, October 11, 2017 4:01:10 PM
To: Ervin Weber
Cc: Alvaro Herrera; Don Seiler; pgsql-admin
Subject: Re: [ADMIN] Passwords in clear text in server log
> 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
Hello,
Why was my message flagged via fraud detection? What do I need to do to prevent that so I can reply?
We have several email aliases at my work location: awilliams@teamdrg.com , awilliams@dresources.com and awilliams@dresourcesgroup.com – I believe my outlook client was reconfigured recently to use @teamdrg.com, but I have posted here before, but I think that was using @dresources.com
Thanks,
Alex
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Williams, Alex
Sent: Wednesday, October 11, 2017 4:18 PM
To: Tom Lane <tgl@sss.pgh.pa.us>; Ervin Weber <webervin@gmail.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; Don Seiler <don@seiler.us>; pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: [ADMIN] Passwords in clear text in server log
This sender failed our fraud detection checks and may not be who they appear to be. Learn about spoofing |
"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"?"
I completely agree. There are many cases, not just edge cases, where this has been vital to isolate and resolve issues.
From: pgsql-admin-owner@postgresql.org <pgsql-admin-owner@postgresql.org> on behalf of Tom Lane <tgl@sss.pgh.pa.us>
Sent: Wednesday, October 11, 2017 4:01:10 PM
To: Ervin Weber
Cc: Alvaro Herrera; Don Seiler; pgsql-admin
Subject: Re: [ADMIN] Passwords in clear text in server log
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