Обсуждение: 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

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. +
Index: doc/src/sgml/config.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.194
diff -c -c -r1.194 config.sgml
*** doc/src/sgml/config.sgml    9 Nov 2008 00:28:34 -0000    1.194
--- doc/src/sgml/config.sgml    11 Nov 2008 02:27:39 -0000
***************
*** 706,711 ****
--- 706,720 ----
          before the user name is looked up by the server.
         </para>

+        <para>
+         Keep in mind all authentication checks are done with
+         the server's representation of the user name, not the client's.
+         Because of this, <literal>MD5</> authentication will not work
+         when <literal>db_user_namespace</> is enabled because the
+         client and server have different representations of the user
+         name.
+        </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.170
diff -c -c -r1.170 auth.c
*** src/backend/libpq/auth.c    28 Oct 2008 12:10:43 -0000    1.170
--- src/backend/libpq/auth.c    11 Nov 2008 02:27:42 -0000
***************
*** 368,373 ****
--- 368,377 ----
              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;

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