Re: Log of CREATE USER statement

Hi,

I need to log any statement in my postgresql server.
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.

For example, if the user submits the statement:
CREATE USER test ENCRYPTED PASSWORD 'test';
in the log file it would be registered as:
CREATE USER test ENCRYPTED PASSWORD
'md505a671c66aefea124cc08b76ea6d30bb'

Thus, the password would be protecting against attacks in log files.

Is it possible?

Thanks,

Ricardo Vaz
Tribunal de Contas do Estado de São Paulo
Diretoria de Tecnologia - DTEC
* Rua Venceslau Brás 183 - 2º andar - Centro
01016-000 São Paulo SP
* Fone: (+5511) 3292 3266 ramal 3640
* e-mail: jrvaz(at)tce(dot)sp(dot)gov(dot)br

"Ricardo Vaz - TCESP" <jrvaz(at)tce(dot)sp(dot)gov(dot)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(at)tce(dot)sp(dot)gov(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)candle(dot)pha(dot)pa(dot)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(at)gmx(dot)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(at)gmx(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Fri, 2005-12-09 at 13:03 -0500, Bruce Momjian wrote:
> Tom Lane wrote:
> > Peter Eisentraut <peter_e(at)gmx(dot)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/

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=> ALTER USER 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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Peter Eisentraut <peter_e(at)gmx(dot)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(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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(at)gmx(dot)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