Thread: Log of CREATE USER statement
<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/>
"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
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
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/
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
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
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
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/
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/
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/
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
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
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
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
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
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
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
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
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/
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