Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id BFC06650188 for ; Fri, 15 Aug 2008 23:48:18 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 57908-04 for ; Fri, 15 Aug 2008 23:48:08 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from momjian.us (momjian.us [70.90.9.53]) by postgresql.org (Postfix) with ESMTP id 9FA2C650107 for ; Fri, 15 Aug 2008 23:48:14 -0300 (ADT) Received: (from bruce@localhost) by momjian.us (8.11.6/8.11.6) id m7G2mFV08927; Fri, 15 Aug 2008 22:48:15 -0400 (EDT) From: Bruce Momjian Message-Id: <200808160248.m7G2mFV08927@momjian.us> Subject: Re: md5 password authentication does not work when db_user_namespace = on In-Reply-To: <8834b7bc0806230713q573bfcdav98f361425dd72e92@mail.gmail.com> To: Sava Chankov Date: Fri, 15 Aug 2008 22:48:15 -0400 (EDT) CC: pgsql-admin@postgresql.org X-Mailer: ELM [version 2.4ME+ PL124 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset="US-ASCII" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200808/69 X-Sequence-Number: 29914 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@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@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 http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +