db_user_namespace, md5 and changing passwords

Fernando Moreno wrote:

Hi there, I'm going to use the db_user_namespace parameter to get a strong
relationship between roles and databases, multiple databases -users
included- residing in the same server without conflicts is my objective too.

Right now I'm working on the backup process, which ideally would let me
mirror a database and all of its users, keeping their passwords.
user01@database01 must not collide with user01@database02, this is why I
need db_user_namespace enabled.

Just before executing pg_dump, I will create a table to store roles
information: name and options like login, encrypted password (from
pg_authid) and connection limit. When restoring, I'll add the
current_database() value to the stored role names, in order to create them
correctly. The problem is that md5sums in postgresql passwords are not
created from "password", but "passworduser", and "user" is not likely to be
the same because it depends directly on the database name; therefore,
authentication will always fail even when trying with the same password.

Is there a way to avoid this problem without having to reset all passwords
or storing them in plain text?

I don't know of a way to make MD5 and db_user_namespace work cleanly so
we are considering removing db_user_namespace in 8.4.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:

Is there a way to avoid this problem without having to reset all passwords
or storing them in plain text?

I don't know of a way to make MD5 and db_user_namespace work cleanly so
we are considering removing db_user_namespace in 8.4.

I would +1 the removal.

Joshua D. Drake

Bruce Momjian <bruce@momjian.us> writes:

I don't know of a way to make MD5 and db_user_namespace work cleanly so
we are considering removing db_user_namespace in 8.4.

We are? It's no more or less ugly than the day it was put in (the
MD5 encryption option was already there).

If we had some improved replacement to offer, I'd be all for getting
rid of db_user_namespace; but without that I think we're just taking
away a feature that some people are using. At least, the argument
was made back in 2002 that people would use this if they had it;
do we have evidence to the contrary now?

regards, tom lane

Tom Lane escribi�:

Bruce Momjian <bruce@momjian.us> writes:

I don't know of a way to make MD5 and db_user_namespace work cleanly so
we are considering removing db_user_namespace in 8.4.

We are? It's no more or less ugly than the day it was put in (the
MD5 encryption option was already there).

If we had some improved replacement to offer, I'd be all for getting
rid of db_user_namespace; but without that I think we're just taking
away a feature that some people are using. At least, the argument
was made back in 2002 that people would use this if they had it;
do we have evidence to the contrary now?

I also disagree with removing it. I know some people (few and far
apart) are using it.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera wrote:

Tom Lane escribi?:

Bruce Momjian <bruce@momjian.us> writes:

I don't know of a way to make MD5 and db_user_namespace work cleanly so
we are considering removing db_user_namespace in 8.4.

We are? It's no more or less ugly than the day it was put in (the
MD5 encryption option was already there).

If we had some improved replacement to offer, I'd be all for getting
rid of db_user_namespace; but without that I think we're just taking
away a feature that some people are using. At least, the argument
was made back in 2002 that people would use this if they had it;
do we have evidence to the contrary now?

I also disagree with removing it. I know some people (few and far
apart) are using it.

Well, I posted about this in August with no one replying:

http://archives.postgresql.org/pgsql-admin/2008-08/msg00068.php

Basically, there is a mismatch between what libpq and the backend think
is the username, and that affects how MD5 uses the salt on the two sides
of the connection. The minimal solution would be to document this and
print a proper error message.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian escribi�:

Well, I posted about this in August with no one replying:

http://archives.postgresql.org/pgsql-admin/2008-08/msg00068.php

Basically, there is a mismatch between what libpq and the backend think
is the username, and that affects how MD5 uses the salt on the two sides
of the connection.

I totally agree that this needs a redesign, but we must provide
something to replace it with, not just rip it off.

The minimal solution would be to document this and print a proper
error message.

Seems fair.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

2008/10/7 Alvaro Herrera <alvherre@commandprompt.com>

Bruce Momjian escribió:

Well, I posted about this in August with no one replying:

http://archives.postgresql.org/pgsql-admin/2008-08/msg00068.php

Basically, there is a mismatch between what libpq and the backend think
is the username, and that affects how MD5 uses the salt on the two sides
of the connection.

I totally agree that this needs a redesign, but we must provide
something to replace it with, not just rip it off.

The minimal solution would be to document this and print a proper
error message.

Seems fair.

--
Alvaro Herrera
http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Thanks for the answers, I wasn't aware of the conflict between md5-auth and
db_user_namespace, but it seems highly related to my problem.

Could you suggest me another way to handle this? Managing users in the usual
way is likely to work fine most of the time, but not when an specific
database(+users) is backed up and restored in the same server, or when two
or more databases (corresponding to different applications) try to create
users with the same name.

Right now there's only one database in the server, but the backup module
should be ready to handle these situations in the future.

Cheers.

Fernando Moreno wrote:

Thanks for the answers, I wasn't aware of the conflict between md5-auth and
db_user_namespace, but it seems highly related to my problem.

Could you suggest me another way to handle this? Managing users in the usual
way is likely to work fine most of the time, but not when an specific
database(+users) is backed up and restored in the same server, or when two
or more databases (corresponding to different applications) try to create
users with the same name.

Right now there's only one database in the server, but the backup module
should be ready to handle these situations in the future.

No, I have no suggested workaround.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:

Alvaro Herrera wrote:

Tom Lane escribi?:

Bruce Momjian <bruce@momjian.us> writes:

I don't know of a way to make MD5 and db_user_namespace work cleanly so
we are considering removing db_user_namespace in 8.4.

We are? It's no more or less ugly than the day it was put in (the
MD5 encryption option was already there).

If we had some improved replacement to offer, I'd be all for getting
rid of db_user_namespace; but without that I think we're just taking
away a feature that some people are using. At least, the argument
was made back in 2002 that people would use this if they had it;
do we have evidence to the contrary now?

I also disagree with removing it. I know some people (few and far
apart) are using it.

Well, I posted about this in August with no one replying:

http://archives.postgresql.org/pgsql-admin/2008-08/msg00068.php

Basically, there is a mismatch between what libpq and the backend think
is the username, and that affects how MD5 uses the salt on the two sides
of the connection. The minimal solution would be to document this and
print a proper error message.

I have developed the attached patch, which documents the inability to
use MD5 with db_user_namespace, and throws an error when it is used:

psql: FATAL: MD5 authentication is not supported when "db_user_namespace" is enabled

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:

Bruce Momjian wrote:

Alvaro Herrera wrote:

Tom Lane escribi?:

Bruce Momjian <bruce@momjian.us> writes:

I don't know of a way to make MD5 and db_user_namespace work cleanly so
we are considering removing db_user_namespace in 8.4.

We are? It's no more or less ugly than the day it was put in (the
MD5 encryption option was already there).

If we had some improved replacement to offer, I'd be all for getting
rid of db_user_namespace; but without that I think we're just taking
away a feature that some people are using. At least, the argument
was made back in 2002 that people would use this if they had it;
do we have evidence to the contrary now?

I also disagree with removing it. I know some people (few and far
apart) are using it.

Well, I posted about this in August with no one replying:

http://archives.postgresql.org/pgsql-admin/2008-08/msg00068.php

Basically, there is a mismatch between what libpq and the backend think
is the username, and that affects how MD5 uses the salt on the two sides
of the connection. The minimal solution would be to document this and
print a proper error message.

I have developed the attached patch, which documents the inability to
use MD5 with db_user_namespace, and throws an error when it is used:

psql: FATAL: MD5 authentication is not supported when "db_user_namespace" is enabled

IMHO it would be much nicer to detect this when we load pg_hba.conf.
It's easy to do these days :-P

I don't think we need to worry about the "changed postgresql.conf after
we changed pg_hba.conf" that much, because we'll always reload
pg_hba.conf after the main config file.

I'd still leave the runtime check in as well to handle the "loaded one
but not the other" case, but let's try prevent the user from loading the
broken config file in the first place..

//Magnus

Magnus Hagander wrote:

I have developed the attached patch, which documents the inability to
use MD5 with db_user_namespace, and throws an error when it is used:

psql: FATAL: MD5 authentication is not supported when "db_user_namespace" is enabled

IMHO it would be much nicer to detect this when we load pg_hba.conf.
It's easy to do these days :-P

I don't think we need to worry about the "changed postgresql.conf after
we changed pg_hba.conf" that much, because we'll always reload
pg_hba.conf after the main config file.

I'd still leave the runtime check in as well to handle the "loaded one
but not the other" case, but let's try prevent the user from loading the
broken config file in the first place..

[ Thread moved to hackers. ]

OK, updated patch attached.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:

Magnus Hagander wrote:

I have developed the attached patch, which documents the inability to
use MD5 with db_user_namespace, and throws an error when it is used:

psql: FATAL: MD5 authentication is not supported when "db_user_namespace" is enabled

IMHO it would be much nicer to detect this when we load pg_hba.conf.
It's easy to do these days :-P

I don't think we need to worry about the "changed postgresql.conf after
we changed pg_hba.conf" that much, because we'll always reload
pg_hba.conf after the main config file.

I'd still leave the runtime check in as well to handle the "loaded one
but not the other" case, but let's try prevent the user from loading the
broken config file in the first place..

[ Thread moved to hackers. ]

OK, updated patch attached.

Looks a lot better.

I am unsure of exactly where this thing hacks into the authentication
stream, but is it really only MD5 that fails?

AFAICS, we rewrite what the user puts into the system *before* we do the
authentication. Which I think would break all authentication *except*
password (without md5) and trust, more or less.

//Magnus

Magnus Hagander <magnus@hagander.net> writes:

I am unsure of exactly where this thing hacks into the authentication
stream, but is it really only MD5 that fails?

The problem with md5 is that the username is part of the encryption salt
for the stored password, so changing it breaks that --- the client will
hash the password with what it thinks the username is, but the stored
password in pg_authid is hashed with what the server thinks the username
is.

You might be right that some other auth methods have an issue too,
but md5 is the only one anyone's ever reported a problem with. That
might or might not just represent lack of testing.

regards, tom lane

Tom Lane wrote:

Magnus Hagander <magnus@hagander.net> writes:

I am unsure of exactly where this thing hacks into the authentication
stream, but is it really only MD5 that fails?

The problem with md5 is that the username is part of the encryption salt
for the stored password, so changing it breaks that --- the client will
hash the password with what it thinks the username is, but the stored
password in pg_authid is hashed with what the server thinks the username
is.

You might be right that some other auth methods have an issue too,
but md5 is the only one anyone's ever reported a problem with. That
might or might not just represent lack of testing.

Right.

But say GSSAPI for example. It will get the username from an external
source, and compare this to whatever the user specified. If we rewrite
what the user specified, we loose.

But maybe you can work around that by using pg_ident.conf, so *both* the
identities gets rewritten.

Not sure I care enough to dive into what it would actually mean. My
guess is that it's very uncommon to use db_user_namespace in any of
these scenarios (in fact I think it's very uncommon to use it at all,
but even more uncommon in these cases)

//Magnus

Magnus Hagander wrote:

Tom Lane wrote:

Magnus Hagander <magnus@hagander.net> writes:

I am unsure of exactly where this thing hacks into the authentication
stream, but is it really only MD5 that fails?

The problem with md5 is that the username is part of the encryption salt
for the stored password, so changing it breaks that --- the client will
hash the password with what it thinks the username is, but the stored
password in pg_authid is hashed with what the server thinks the username
is.

You might be right that some other auth methods have an issue too,
but md5 is the only one anyone's ever reported a problem with. That
might or might not just represent lack of testing.

Right.

But say GSSAPI for example. It will get the username from an external
source, and compare this to whatever the user specified. If we rewrite
what the user specified, we loose.

But maybe you can work around that by using pg_ident.conf, so *both* the
identities gets rewritten.

Not sure I care enough to dive into what it would actually mean. My
guess is that it's very uncommon to use db_user_namespace in any of
these scenarios (in fact I think it's very uncommon to use it at all,
but even more uncommon in these cases)

The documentation changes highlight that we are going to validate for
most external authentications using the server username, so the external
authentication has to be set up to use that server username. Were the
docs not clear on that? Do I need a mention of db_user_namespace in the
authentication docs?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:

Magnus Hagander wrote:

Tom Lane wrote:

Magnus Hagander <magnus@hagander.net> writes:

I am unsure of exactly where this thing hacks into the authentication
stream, but is it really only MD5 that fails?

The problem with md5 is that the username is part of the encryption salt
for the stored password, so changing it breaks that --- the client will
hash the password with what it thinks the username is, but the stored
password in pg_authid is hashed with what the server thinks the username
is.

You might be right that some other auth methods have an issue too,
but md5 is the only one anyone's ever reported a problem with. That
might or might not just represent lack of testing.

Right.

But say GSSAPI for example. It will get the username from an external
source, and compare this to whatever the user specified. If we rewrite
what the user specified, we loose.

But maybe you can work around that by using pg_ident.conf, so *both* the
identities gets rewritten.

Not sure I care enough to dive into what it would actually mean. My
guess is that it's very uncommon to use db_user_namespace in any of
these scenarios (in fact I think it's very uncommon to use it at all,
but even more uncommon in these cases)

The documentation changes highlight that we are going to validate for
most external authentications using the server username, so the external
authentication has to be set up to use that server username. Were the
docs not clear on that? Do I need a mention of db_user_namespace in the
authentication docs?

AFAICS, the changes only say MD5 doesn't work. I think it should be made
more clear.

And yes, it probably makes sense to put it around the authentication
docs as well as a warning to people - that's where they'll go looking if
something doesn't work.

//Magnus

Magnus Hagander wrote:

Not sure I care enough to dive into what it would actually mean. My
guess is that it's very uncommon to use db_user_namespace in any of
these scenarios (in fact I think it's very uncommon to use it at all,
but even more uncommon in these cases)

The documentation changes highlight that we are going to validate for
most external authentications using the server username, so the external
authentication has to be set up to use that server username. Were the
docs not clear on that? Do I need a mention of db_user_namespace in the
authentication docs?

AFAICS, the changes only say MD5 doesn't work. I think it should be made
more clear.

And yes, it probably makes sense to put it around the authentication
docs as well as a warning to people - that's where they'll go looking if
something doesn't work.

OK, documentation updated stating that all authentication has to user
the server username, and added a mention in the client-auth docs too.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:

Magnus Hagander wrote:

Not sure I care enough to dive into what it would actually mean. My
guess is that it's very uncommon to use db_user_namespace in any of
these scenarios (in fact I think it's very uncommon to use it at all,
but even more uncommon in these cases)

The documentation changes highlight that we are going to validate for
most external authentications using the server username, so the external
authentication has to be set up to use that server username. Were the
docs not clear on that? Do I need a mention of db_user_namespace in the
authentication docs?

AFAICS, the changes only say MD5 doesn't work. I think it should be made
more clear.

And yes, it probably makes sense to put it around the authentication
docs as well as a warning to people - that's where they'll go looking if
something doesn't work.

OK, documentation updated stating that all authentication has to user
the server username, and added a mention in the client-auth docs too.

Applied to CVS HEAD. Not sure if it should be backpatched so I didn't.
We do have two bug reports for 8.3 but none for earlier releases where
it was also broken.

---------------------------------------------------------------------------

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Index: doc/src/sgml/client-auth.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/client-auth.sgml,v
retrieving revision 1.111
diff -c -c -r1.111 client-auth.sgml
*** doc/src/sgml/client-auth.sgml	18 Nov 2008 13:10:20 -0000	1.111
--- doc/src/sgml/client-auth.sgml	20 Nov 2008 03:56:43 -0000
***************
*** 702,707 ****
--- 702,709 ----
If you are at all concerned about password
<quote>sniffing</> attacks then <literal>md5</> is preferred.
Plain <literal>password</> should always be avoided if possible.
+     <literal>md5</> cannot be used with <xref
+     linkend="guc-db-user-namespace">.
</para>

<para>
Index: doc/src/sgml/config.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.195
diff -c -c -r1.195 config.sgml
*** doc/src/sgml/config.sgml	11 Nov 2008 02:42:31 -0000	1.195
--- doc/src/sgml/config.sgml	20 Nov 2008 03:56:44 -0000
***************
*** 706,711 ****
--- 706,722 ----
before the user name is looked up by the server.
</para>

+        <para>
+         <varname>db_user_namespace</> causes the client's and
+         server's user name representation to differ.
+         Authentication checks are always done with the server's user name
+         so authentication methods must be configured for the
+         server's user name, not the client's.  Because
+         <literal>md5</> uses the user name as salt on both the
+         client and server, <literal>md5</> cannot be used with
+         <varname>db_user_namespace</>.
+        </para>
+ 
<note>
<para>
This feature is intended as a temporary measure until a
Index: src/backend/libpq/auth.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/libpq/auth.c,v
retrieving revision 1.171
diff -c -c -r1.171 auth.c
*** src/backend/libpq/auth.c	18 Nov 2008 13:10:20 -0000	1.171
--- src/backend/libpq/auth.c	20 Nov 2008 03:56:44 -0000
***************
*** 371,376 ****
--- 371,380 ----
break;

case uaMD5:
+ 			if (Db_user_namespace)
+ 				ereport(FATAL,
+ 						(errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION),
+ 						 errmsg("MD5 authentication is not supported when \"db_user_namespace\" is enabled")));
sendAuthRequest(port, AUTH_REQ_MD5);
status = recv_and_check_password_packet(port);
break;
Index: src/backend/libpq/hba.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/libpq/hba.c,v
retrieving revision 1.172
diff -c -c -r1.172 hba.c
*** src/backend/libpq/hba.c	28 Oct 2008 12:10:43 -0000	1.172
--- src/backend/libpq/hba.c	20 Nov 2008 03:56:47 -0000
***************
*** 846,852 ****
--- 846,861 ----
else if (strcmp(token, "reject") == 0)
parsedline->auth_method = uaReject;
else if (strcmp(token, "md5") == 0)
+ 	{
+ 		if (Db_user_namespace)
+ 		{
+ 			ereport(LOG,
+ 					(errcode(ERRCODE_CONFIG_FILE_ERROR),
+ 					 errmsg("MD5 authentication is not supported when \"db_user_namespace\" is enabled")));
+ 			return false;
+ 		}
parsedline->auth_method = uaMD5;
+ 	}
else if (strcmp(token, "pam") == 0)
#ifdef USE_PAM
parsedline->auth_method = uaPAM;

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +