Re: md5 password authentication does not work when db_user_namespace = on

Sava Chankov wrote:
> Hi,
> when I set db_user_namespace = on in postgresql.conf and my pg_hba.conf
> contains only the line:
> local samerole all password
> users can be authenticated (I have created users as username(at)dbname).
> However, when I change pg_hba's METHOD to md5, it doesn't work anymore. I
> attach my postgresql.conf.

This is one of those bug reports where I knew the cause as soon as I
heard it. Let me remind people how db_user_namespace works by adding
the database name to the client-supplied user name:

http://www.postgresql.org/docs/8.3/static/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SECURITY

$ psql -U bruce test
psql (8.4devel)
Type "help" for help.

test=> select current_user;
current_user
--------------
bruce(at)test
(1 row)

test=> \q
$ psql -U postgres@ test
psql (8.4devel)
Type "help" for help.

test=> select current_user;
current_user
--------------
postgres
(1 row)

The problem is that we use the username as part of the salt when we
store the md5-encrypted password. When the client encrypts the password
it thinks the username is different from what the server thinks is the
username.

I can't think of any clean way to fix this. Perhaps we should just
remove the db_user_namespace feature, or throw an error when MD5
encryption is used.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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