Thread: Log of CREATE USER statement

Log of CREATE USER statement

From
"Ricardo Vaz - TCESP"
Date:
<p><font face="Courier New" size="2">Hi,</font><p><font face="Courier New" size="2">I need to log any statement in my
postgresqlserver.</font><br /><font face="Courier New" size="2">However, I would like that the password defined in
CREATEUSER statement was registered in MD5 format, independent of the form as it was specified in that
statement.</font><p><fontface="Courier New" size="2">For example, if the user submits the statement:</font><br /><font
face="CourierNew" size="2">  CREATE USER test ENCRYPTED PASSWORD 'test';</font><br /><font face="Courier New"
size="2">inthe log file it would be registered as:</font><br /><font face="Courier New" size="2">  CREATE USER test
ENCRYPTEDPASSWORD 'md505a671c66aefea124cc08b76ea6d30bb'</font><p><font face="Courier New" size="2">Thus, the password
wouldbe protecting against attacks in log files.</font><p><font face="Courier New" size="2">Is it
possible?</font><p><fontface="Courier New" size="2">Thanks,</font><br /><p><font face="Tahoma" size="2">Ricardo
Vaz</font><br/><font face="Tahoma" size="2">Tribunal de Contas do Estado de São Paulo</font><br /><font face="Tahoma"
size="2">Diretoriade Tecnologia - DTEC</font><br /><font face="Wingdings" size="2">+</font>       <font face="Tahoma"
size="2">RuaVenceslau Brás 183 - 2º andar - Centro</font><br />        <font face="Tahoma" size="2">01016-000 São Paulo
SP</font><br/><font face="Wingdings" size="2">(</font>       <font face="Tahoma" size="2">Fone: (+5511) 3292 3266 ramal
3640</font><br/><font face="Wingdings" size="2">8</font>       <font face="Tahoma" size="2">e-mail:
jrvaz@tce.sp.gov.br</font><br/> 

Re: Log of CREATE USER statement

From
Tom Lane
Date:
"Ricardo Vaz - TCESP" <jrvaz@tce.sp.gov.br> writes:
> However, I would like that the password defined in CREATE USER statement
> was registered in MD5 format, independent of the form as it was
> specified in that statement.

Not going to happen --- we are not going to add that sort of analysis to
the statement logging code, as the overhead would be unacceptable and
the gain minimal.  This is hardly the only sensitive data that might be
found in the postmaster log!  I suggest taking care with the file
permissions on the log, instead.
        regards, tom lane


Re: Log of CREATE USER statement

From
Bruce Momjian
Date:
Tom Lane wrote:
> "Ricardo Vaz - TCESP" <jrvaz@tce.sp.gov.br> writes:
> > However, I would like that the password defined in CREATE USER statement
> > was registered in MD5 format, independent of the form as it was
> > specified in that statement.
> 
> Not going to happen --- we are not going to add that sort of analysis to
> the statement logging code, as the overhead would be unacceptable and
> the gain minimal.  This is hardly the only sensitive data that might be
> found in the postmaster log!  I suggest taking care with the file
> permissions on the log, instead.

One issue I have not heard is that CREATE USER, with the visible
password, is sent over the wire in cleartext, and does appear in the
logs, as we discussed, so while we MD5 the password in pg_shadow so
administrators do not see it, we do log the query if the administrator
has set it up that way.  I see no way to secure this really since the
administrator typically has control over the database installation.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Log of CREATE USER statement

From
Peter Eisentraut
Date:
Tom Lane wrote:
> > However, I would like that the password defined in CREATE USER
> > statement was registered in MD5 format, independent of the form as
> > it was specified in that statement.
>
> Not going to happen --- we are not going to add that sort of analysis
> to the statement logging code, as the overhead would be unacceptable
> and the gain minimal.  This is hardly the only sensitive data that
> might be found in the postmaster log!  I suggest taking care with the
> file permissions on the log, instead.

While I agree that the overhead in the logging code does not seem 
attractive, it's still a problem that the password handling in 
PostgreSQL continues to be relatively insecure.  When we introduced MD5 
passwords, one of the reasons was to prevent the administrator from 
simply reading the users' passwords.  Users who choose a password 
should have the assurance that the password cannot be seen in 
plain-text by anyone anywhere.  In a PostgreSQL system, the password 
can be seen in all kinds of places, like the psql history, the server 
log, the activity displays, and who knows where else.  I don't have a 
good solution for this, but it should be addressed sometime.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: Log of CREATE USER statement

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> One issue I have not heard is that CREATE USER, with the visible
> password, is sent over the wire in cleartext, and does appear in the
> logs, as we discussed, so while we MD5 the password in pg_shadow so
> administrators do not see it, we do log the query if the administrator
> has set it up that way.  I see no way to secure this really since the
> administrator typically has control over the database installation.

To put that more clearly: if the point is to keep the user's cleartext
password out of the hands of the DBA, then the user has already blown it
by sending the password in cleartext in the first place.  An
untrustworthy DBA could trivially insert code into CREATE USER to log
the original password in a place of his choosing.
        regards, tom lane


Re: Log of CREATE USER statement

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> Users who choose a password 
> should have the assurance that the password cannot be seen in 
> plain-text by anyone anywhere.  In a PostgreSQL system, the password 
> can be seen in all kinds of places, like the psql history, the server 
> log, the activity displays, and who knows where else.

As I said already, if the user wishes the password to be secure, he
needs to encrypt it on the client side.  Anything else is just the
illusion of security.
        regards, tom lane


Re: Log of CREATE USER statement

From
Bruce Momjian
Date:
Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Users who choose a password 
> > should have the assurance that the password cannot be seen in 
> > plain-text by anyone anywhere.  In a PostgreSQL system, the password 
> > can be seen in all kinds of places, like the psql history, the server 
> > log, the activity displays, and who knows where else.
> 
> As I said already, if the user wishes the password to be secure, he
> needs to encrypt it on the client side.  Anything else is just the
> illusion of security.

Should we document this?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Log of CREATE USER statement

From
"Joshua D. Drake"
Date:
On Fri, 2005-12-09 at 13:03 -0500, Bruce Momjian wrote:
> Tom Lane wrote:
> > Peter Eisentraut <peter_e@gmx.net> writes:
> > > Users who choose a password 
> > > should have the assurance that the password cannot be seen in 
> > > plain-text by anyone anywhere.  In a PostgreSQL system, the password 
> > > can be seen in all kinds of places, like the psql history, the server 
> > > log, the activity displays, and who knows where else.
> > 
> > As I said already, if the user wishes the password to be secure, he
> > needs to encrypt it on the client side.  Anything else is just the
> > illusion of security.
> 
> Should we document this?

That is a good question. One argument is, no. It should be fairly
obvious that if you don't turn on SSL then nothing is going to be
encrypted.

The other argument is that we should be explicit as possible...

Sincerely,

Joshua D. Drake


-- 
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: PLphp, PLperl, ODBCng - http://www.commandprompt.com/




Re: Log of CREATE USER statement

From
Peter Eisentraut
Date:
Tom Lane wrote:
> To put that more clearly: if the point is to keep the user's
> cleartext password out of the hands of the DBA, then the user has
> already blown it by sending the password in cleartext in the first
> place.  An untrustworthy DBA could trivially insert code into CREATE
> USER to log the original password in a place of his choosing.

With SELinux or similar systems, it might be the case that the DBA could 
not change or insert any code but could configure and read the server 
logs.  But this is admittedly a rare case currently.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: Log of CREATE USER statement

From
Peter Eisentraut
Date:
Tom Lane wrote:
> As I said already, if the user wishes the password to be secure, he
> needs to encrypt it on the client side.

Maybe we should provide a backslash command in psql for secure password 
entry, say, \password [username].  This would then ask for the password 
through a somewhat secure, unlogged channel, encrypt it, and send an 
ALTER ROLE command to the server.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: Log of CREATE USER statement

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> As I said already, if the user wishes the password to be secure, he
>> needs to encrypt it on the client side.  Anything else is just the
>> illusion of security.

> Thinking some more, does encoding on the client side really improve
> things?  It hides the user-typed password from prying eyes, but it
> doesn't prevent someone from using that md5 string to get into the
> database.

That's not the point --- anyone who can look at the md5 string is
already into the database.  The threat that (I suppose) the OP is
worried about is that the user might use the same cleartext password
for other things, which means that someone who is able to swipe his
cleartext password might be able to get into those other services.
But the md5 password will not give enough information for that.
        regards, tom lane


Re: Log of CREATE USER statement

From
Bruce Momjian
Date:
Peter Eisentraut wrote:
> Tom Lane wrote:
> > As I said already, if the user wishes the password to be secure, he
> > needs to encrypt it on the client side.
> 
> Maybe we should provide a backslash command in psql for secure password 
> entry, say, \password [username].  This would then ask for the password 
> through a somewhat secure, unlogged channel, encrypt it, and send an 
> ALTER ROLE command to the server.

I was thinking of something like:
test=> \set passwd '\'md5' `md5 -q -s aaapostgres` '\''test=> \echo :passwd'md536840d6d769e9e4b5ee644f4b9a8ce7e'test=>
ALTERUSER postgres PASSWORD :passwd;ALTER ROLE
 

What I have not figured out is how to easily prevent psql HISTORY from
being saved.  Also, ideally I would use:
test=> \set pass '\'md5' `md5 -q -s aaa:USER` '\''

but I can't figure out how to do ':' expansion inside a backquote
string:test=> \set pass `echo :USER`test=> \echo :pass:USER

However, they might want to change a different user's password if they
are the superuser, so maybe they should be required to type it out.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Log of CREATE USER statement

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> Maybe we should provide a backslash command in psql for secure password 
> entry, say, \password [username].  This would then ask for the password 
> through a somewhat secure, unlogged channel, encrypt it, and send an 
> ALTER ROLE command to the server.

Not a bad idea.  It'd at least keep the cleartext password firmly on the
client side.
        regards, tom lane


Re: Log of CREATE USER statement

From
Simon Riggs
Date:
On Fri, 2005-12-09 at 19:41 +0100, Peter Eisentraut wrote:
> Tom Lane wrote:
> > As I said already, if the user wishes the password to be secure, he
> > needs to encrypt it on the client side.
> 
> Maybe we should provide a backslash command in psql 

That is a good option, but not the only option required.

There are many reasons to need to supply the password as part of a
command, rather than an interactive input.

Best Regards, Simon Riggs



Re: Log of CREATE USER statement

From
Tom Lane
Date:
Simon Riggs <simon@2ndquadrant.com> writes:
> On Fri, 2005-12-09 at 19:41 +0100, Peter Eisentraut wrote:
>> Maybe we should provide a backslash command in psql 

> That is a good option, but not the only option required.
> There are many reasons to need to supply the password as part of a
> command, rather than an interactive input.

You miss the point entirely.  Normal *use* of a password is not part of
the SQL command language and is already adequately encrypted.  It's only
supplying a new password in CREATE/ALTER USER that has the security
hazard of exposing the password in command logs, pg_stat_activity, etc.
AFAICS, Peter's idea covers that case satisfactorily.
        regards, tom lane


Re: Log of CREATE USER statement

From
Simon Riggs
Date:
On Sat, 2005-12-10 at 11:15 -0500, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > On Fri, 2005-12-09 at 19:41 +0100, Peter Eisentraut wrote:
> >> Maybe we should provide a backslash command in psql 
> 
> > That is a good option, but not the only option required.
> > There are many reasons to need to supply the password as part of a
> > command, rather than an interactive input.
> 
> You miss the point entirely.  Normal *use* of a password is not part of
> the SQL command language and is already adequately encrypted.  It's only
> supplying a new password in CREATE/ALTER USER that has the security
> hazard of exposing the password in command logs, pg_stat_activity, etc.
> AFAICS, Peter's idea covers that case satisfactorily.

Peter's idea is great and I agree with everything he says. 

I meant that if we are helping psql users to encrypt the password, we
should help others as well, thats all.

At very least this should be documented better. At best we could change
the protocol to encrypt things client-side, so that plaintext never goes
across the wire in any circumstance. That would then be good security by
default. I'm not volunteering to write that code anytime soon, but I
could work on some docs to better explain this.

We could also change the logging and pg_stat_activity so that we never
output the password at all, plaintext or otherwise.

Best Regards, Simon Riggs




Re: Log of CREATE USER statement

From
"Ricardo Vaz"
Date:
Dear friends,

I thank you for all replies.
If you permit, I'd like to present my modest view of the problem.

I agree with Tom when he says:
>
>... if the user wishes the password to be secure, he
>needs to encrypt it on the client side.  Anything else is
>just the illusion of security.
>

and with Bruce:
>
>... I see no way to secure this really since the
>administrator typically has control over the database installation.
>

There isn't a 100% secure system.
So, I'm working in a framework to audit all operations over the
database. The rastreability is the only one tool to identify
actions of an untrustworthy DBA.
In this context, the identity of the user may be protected and
it's obvious that the protection of user password is extremely
important for preventing that someone can login as another user.

From there it came the concern with the register of the password
in plaintext in the archives and log files.
I had not thought about the history and the activity display. It´s
one another vulnerability...

I cannot see another solution not to be overhead in the logging code.

The idea of to provide a backslash command in psql is very good.
But, what about "pgAdmin", "phpPgAdmin" and other management tools?
I think that these tools, for its easiness of use, are important in
the use dissemination of PostgreSQL.

I know that I did not contribute with new facts to the discussion.
I would like, only, to stand out its importance and, one more time,
to be thankful for the attention of all.

Best regards,

Ricardo Vaz



Re: Log of CREATE USER statement

From
Marko Kreen
Date:
On 12/9/05, Peter Eisentraut <peter_e@gmx.net> wrote:
> Tom Lane wrote:
> > As I said already, if the user wishes the password to be secure, he
> > needs to encrypt it on the client side.
>
> Maybe we should provide a backslash command in psql for secure password
> entry, say, \password [username].  This would then ask for the password
> through a somewhat secure, unlogged channel, encrypt it, and send an
> ALTER ROLE command to the server.

Letting createuser.c hash the password would be the biggest win.

--
marko

Re: Log of CREATE USER statement

From
Peter Eisentraut
Date:
Marko Kreen wrote:
> > Maybe we should provide a backslash command in psql for secure
> > password entry, say, \password [username].  This would then ask for
> > the password through a somewhat secure, unlogged channel, encrypt
> > it, and send an ALTER ROLE command to the server.
>
> Letting createuser.c hash the password would be the biggest win.

Both of these things are now done, and I have sent notices to pgadmin 
and phppgadmin asking for equivalent adjustments.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: Log of CREATE USER statement

From
Andreas Pflug
Date:
Peter Eisentraut wrote:
>>
>>Letting createuser.c hash the password would be the biggest win.
> 
> 
> Both of these things are now done, and I have sent notices to pgadmin 
> and phppgadmin asking for equivalent adjustments.

Um, didn't receive a notice.
I'm a little uncomfortable using an arbitrary md5 algorithm to encrypt
the password before sending it to the backend. How about supplying an
official pg encryption routine through libpq?

Regards,
Andreas