Re: passwords in pg_shadow (duplicate). - Mailing list pgsql-general

Good catch.  Seems like a bug.  I assumed we still want to support
'password' even though pg_shadow contains MD5 encrypted passwords.  Is
that correct?  (We can't support crypt in those cases.)

The following patch fixes this.  I need to review it later, but we could
apply to 7.3.1.  I assume there are still some interfaces that don't
support md5 or crypt and we will need this patch to continue supporting
them, though I am sure there are some out there that want 'password' to
go away.

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

Hiroshi Inoue wrote:
> Bruce Momjian wrote:
> >
> >
> > This is normal behavior.  With 7.3, when you load your passwords into
> > the database, they are automatically converted to MD5 inside the
> > database.  You can disable this in postgresql.conf using by changing
> > password_encryption _before_ you load your data into the database.
> >
> > The big trick is what ia in your pg_hba.conf file for the ODBC host.  If
> > it is crypt, there is no way we can make those MD5 passwords match the
> > info coming from the client.  However, I didn't think ODBC even did
> > crypt.  Even though the server has MD5-encrypted password stored, the
> > 'password' pg_hba.conf method should still work because the server will
> > internally MD5 encrypt before comparing to pg_shadow, or at least it
> > should and worked in my testing.
>
> It doesn't work here and I see the following in src/libpq/crypt.c.
>
>         /* If they encrypt their password, force MD5 */
>         if (isMD5(passwd) && port->auth_method != uaMD5)
>         {
>                 elog(LOG, "Password is stored MD5 encrypted.  "
>                          "'password' and 'crypt' auth methods cannot be
> used.");
>                 return STATUS_ERROR;
>         }
>
> As far as I see, 7.3 forces the use of md5 authentication
> implicitly rather than recommending it. Please document
> it clearly on the top of the main documents. Otherwise
> users would be confused pointlessly.
>
> regards,
> Hiroshi Inoue
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: src/backend/libpq/crypt.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/libpq/crypt.c,v
retrieving revision 1.49
diff -c -c -r1.49 crypt.c
*** src/backend/libpq/crypt.c    4 Sep 2002 20:31:19 -0000    1.49
--- src/backend/libpq/crypt.c    5 Dec 2002 07:07:37 -0000
***************
*** 54,64 ****
      if (passwd == NULL || *passwd == '\0')
          return STATUS_ERROR;

!     /* If they encrypt their password, force MD5 */
!     if (isMD5(passwd) && port->auth_method != uaMD5)
      {
          elog(LOG, "Password is stored MD5 encrypted.  "
!              "'password' and 'crypt' auth methods cannot be used.");
          return STATUS_ERROR;
      }

--- 54,64 ----
      if (passwd == NULL || *passwd == '\0')
          return STATUS_ERROR;

!     /* We can't do crypt with pg_shadow MD5 passwords */
!     if (isMD5(passwd) && port->auth_method == uaCrypt)
      {
          elog(LOG, "Password is stored MD5 encrypted.  "
!              "'crypt' auth method cannot be used.");
          return STATUS_ERROR;
      }

***************
*** 110,115 ****
--- 110,126 ----
                  break;
              }
          default:
+             if (isMD5(passwd))
+             {
+                 char       *crypt_pwd2 = palloc(MD5_PASSWD_LEN + 1);
+                 if (!EncryptMD5(pgpass, port->user, strlen(port->user),
+                                 crypt_pwd2))
+                 {
+                     pfree(crypt_pwd2);
+                     return STATUS_ERROR;
+                 }
+                 pgpass = crypt_pwd2;
+             }
              crypt_pwd = passwd;
              break;
      }
***************
*** 136,141 ****
--- 147,155 ----

      if (port->auth_method == uaMD5)
          pfree(crypt_pwd);
+     if (port->auth_method != uaMD5 && port->auth_method != uaCrypt &&
+         isMD5(passwd))
+         pfree((char *)pgpass);

      return retval;
  }