Обсуждение: Changing Passwords as Encrypted not Clear-Text

On Mon, 2011-12-19 at 15:01 +0200, MURAT KOÇ wrote:
> Hi List,
>
> When I try to change my db password like below SQL statement from psql or
> pgAdmin tool, it outputs to server logs as like this:
>
>  *postgres=# alter user mkoc password 'dummy';
> ALTER ROLE
> postgres=# alter user mkoc with password 'dummy';
> ALTER ROLE
> *
>
> ### Server Logs ###
> 2011-12-19 14:35:31
> EET--postgres--postgres--[local]--psql--idle--00000LOG:  statement: alter
> user mkoc password 'dummy';
> 2011-12-19 14:35:41
> EET--postgres--postgres--[local]--psql--idle--00000LOG:  statement: alter
> user mkoc with password 'dummy';
>
> So, an OS user who can access to server log files can read DB users'
> clear-text passwords from these logs. In my opinion, this is a big security
> gap.
>
> I don't want to see these changing password logs in clear-text. These
> logs must be encrypted passwords instead of clear-text like below:
>
>  *Server Logs must be;
> *2011-12-19 14:35:31
> EET--postgres--postgres--[local]--psql--idle--00000LOG:  statement: alter
> user mkoc password *values 'XFADIT9248fDSKFD';*
> **
> Is it possible to see changing passwords as encrypted?

Nope.

>  How should I change password or what is the correct sql statement to change user password?
>

There's only one way to change a password: ALTER USER... PASSWORD...

You have to trust the people who have access to the PostgreSQL logs. I
you don't trust them, you should deny access to the logs for them.


--
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com
  PostgreSQL Sessions #3: http://www.postgresql-sessions.org

On Monday, December 19, 2011 5:01:28 am MURAT KOÇ wrote:
> Hi List,
>
> When I try to change my db password like below SQL statement from psql or
> pgAdmin tool, it outputs to server logs as like this:
>
>  *postgres=# alter user mkoc password 'dummy';
> ALTER ROLE
> postgres=# alter user mkoc with password 'dummy';
> ALTER ROLE
> *
>
>  *Server Logs must be;
> *2011-12-19 14:35:31
> EET--postgres--postgres--[local]--psql--idle--00000LOG:  statement: alter
> user mkoc password *values 'XFADIT9248fDSKFD';*
> **
> Is it possible to see changing passwords as encrypted? How should I change
> password or what is the correct sql statement to change user password?

http://www.postgresql.org/docs/9.0/interactive/sql-alterrole.html
"Caution must be exercised when specifying an unencrypted password with this
command. The password will be transmitted to the server in cleartext, and it
might also be logged in the client's command history or the server log. psql
contains a command \password that can be used to change a role's password
without exposing the cleartext password."

The above is incorrect in that encrypted passwords will also be sent as clear
text.

http://www.postgresql.org/docs/9.0/interactive/app-psql.html
"\password [ username ]

    Changes the password of the specified user (by default, the current user).
This command prompts for the new password, encrypts it, and sends it to the
server as an ALTER ROLE command. This makes sure that the new password does not
appear in cleartext in the command history, the server log, or elsewhere.
"

>
> Best Regards,
> Murat KOC

--
Adrian Klaver
adrian.klaver@gmail.com

On 19 December 2011 16:26, MURAT KOÇ <m.koc21@gmail.com> wrote:
> Hi Adrian,
>
> I wrote a desktop application on Windows by using "Npgsql.dll". So, I send
> SQL statement to database from this application code. I can't use psql
> command line (I know "\password" command changes password encrypted text).
>
> Because of this, I have to use "ALTER USER" statement from application code.
> Or what could you give another advice?

Apparently psql doesn't send a plain ALTER ROLE statement. It probably
uses the binary protocol. Perhaps your application can do the same.

Alternatively, you can secure your (apparently insecure) connection to
the DB using SSL or an ssh tunnel or somesuch.

That said, if you are having issues with a fellow DBA, you should
create a policy that you don't use each others' login credentials when
it matters. You're DBA's, there is no security measure that will stop
you from obtaining private data from the databases you manage. Access
to the log files is only a small part of that.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

On Mon, 2011-12-19 at 17:19 +0200, MURAT KOÇ wrote:
> Thanks for reply. But it's not suitable to trust the people who have
> access to the logs.
>
> Think, I changed my DB password and other DBA colleague who has access
> to the PostgreSQL logs has seen my DB password. He started to use my
> DB password instead of his password.
>
> He executed all DDL and DML statements with my DB account. In fact, I
> did nothing but because of this gap I did all things.
>
> Is it a trustable situation ? How will we identify who is guilty he or
> me?
>

Do they have access to the pg_hba.conf file? because if they have, you
have no chance to stop them from connecting to the database with your
user account and without any need to know your password.


--
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com
  PostgreSQL Sessions #3: http://www.postgresql-sessions.org

On Monday, December 19, 2011 7:26:33 am MURAT KOÇ wrote:
> Hi Adrian,
>
> I wrote a desktop application on Windows by using "Npgsql.dll". So, I send
> SQL statement to database from this application code. I can't use psql
> command line (I know "\password" command changes password encrypted text).
>
> Because of this, I have to use "ALTER USER" statement from application
> code. Or what could you give another advice?

Well you could do what psql does which is generate the password and send it
encrypted.

http://www.postgresql.org/docs/9.0/static/catalog-pg-authid.html
"
Password (possibly encrypted); null if none. If the password is encrypted, this
column will contain the string md5 followed by a 32-character hexadecimal MD5
hash. The MD5 hash will be of the user's password concatenated to their username
(for example, if user joe has password xyzzy, PostgreSQL will store the md5 hash
of xyzzyjoe)."

What psql does:
\password test_user
Where password is 'test_pass'

Log entry:
ALTER USER test_user PASSWORD 'md5c326ab35c9353dd34801ecd7ab7b1d76'

What you can do:

Use md5 function:
SELECT md5('test_passtest_user');
               md5
----------------------------------
 c326ab35c9353dd34801ecd7ab7b1d76

Take 'md5'||'c326ab35c9353dd34801ecd7ab7b1d76'

ALTER USER test_user PASSWORD 'md5c326ab35c9353dd34801ecd7ab7b1d76'



>
> Best Regards
> Murat KOC
>


--
Adrian Klaver
adrian.klaver@gmail.com

> Of course, we could create login credentials, login configuration options
> for every DBA colleagues. But, as I said previous that big problem is
> "PostgreSQL logs include changing passwords on clear-text not encrypted"

No, the big problem is that you don't consider your fellow DBA's
reliable. That's a problem you need to solve rather sooner than later.

Another problem is that you are apparently logging all SQL statements.
Not only does that store the SQL for changing database users, it will
also slow down your database. You should really only log all
statements if you're debugging something, and only temporarily.

Of course it would be nice if those passwords would be encrypted, but
they are simply part of SQL statements - there is no means in SQL to
distinguish a password string literal from any other type of literal,
until the statement hits the database.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

On Mon, Dec 19, 2011 at 15:31, Guillaume Lelarge <guillaume@lelarge.info> wrote:
> On Mon, 2011-12-19 at 15:01 +0200, MURAT KOÇ wrote:
>> Is it possible to see changing passwords as encrypted?
>
> Nope.
>
>>  How should I change password or what is the correct sql statement to change user password?
>>
>
> There's only one way to change a password: ALTER USER... PASSWORD...

Not really. You can use "\password" in psql, or you can use
PQencryptPassword() in libpq. I don't know if that function is exposed
in other client drivers, but it's certainly been in libpq for quite a
while.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Monday, December 19, 2011 7:26:33 am MURAT KOÇ wrote:
> Hi Adrian,
>
> I wrote a desktop application on Windows by using "Npgsql.dll". So, I send
> SQL statement to database from this application code. I can't use psql
> command line (I know "\password" command changes password encrypted text).
>
> Because of this, I have to use "ALTER USER" statement from application
> code. Or what could you give another advice?

Another option would be to change logging behavior on the fly using SET:

http://www.postgresql.org/docs/9.0/interactive/sql-set.html

set log_statement= 'none';


This will need to be done as a superuser.

>
> Best Regards
> Murat KOC
>

--
Adrian Klaver
adrian.klaver@gmail.com

On Monday, December 19, 2011 11:46:05 am MURAT KOÇ wrote:
> Hi Adrian,
>
> Both of advices that are using md5 algorithm and setting log_statement for
> session specific worked well.
>
> Thanks for suggestions and solution advices.

Glad it worked. A thought came to mind just now. If you decide to use the SET
method  you may also want to look at the error configuration settings also.
Should there be a mistake in the SQL statement it is possible the password would
show up in an ERROR message.

>
> Best Regards,
> Murat KOC


--
Adrian Klaver
adrian.klaver@gmail.com