passwords in pg_shadow (duplicate).

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.

Are you using a recent ODBC driver? That may help. Please report back
your pg_hba.conf setting for the host. Also, MD5 is now the preferred
method for client connections. Crypt doesn't work anymore (unless you
modify postgresql.conf). There is no reason to use 'password' plaintext
anymore.

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

Terry Yapt wrote:

========================== Hello all,

I have "migrate" a test server from 7.2.x to 7.3. All was ok
(without regression tests yet), but I have a problem to connect
to the server from all my client-odbc applications.

I have a: "FATAL: Password authentication failed for user
xxxxxx"

I have SELECT * from pg_shadow and I noticed about a difference
with my 7.2 installation:

7.2 -> Password is text plain.
7.3 -> Password is md5 crypt or
something similar.

If I change my md5 password from its encryption to plain text,
I can connect fine.

It is the normal behavior or not ? The users and their passwords
come from a pg_dumpall.

Thanks a lot.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an
appropriate subscribe-nomail command to majordomo@postgresql.org
so that your message can get through to the mailing list cleanly

--
  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

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

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

Bruce Momjian wrote:

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.

Honestly I don't understand your intention.
For example, if some one would like to use
crypt authentication what should he do ?
Certainly he can store a plain password using
'with unencrypted password ....'. But pg_dump
would dump it as 'with password ....' and as
a result the password would be restored as MD5
encrypted password by default.

regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/

First, I have update to the last ODBC driver in odbc.postgresql.org.

My pg_hba.conf was with 'password' method. I have changed it to 'md5', and all was fine now. Even I can connect with a user that has a plain text password (no md5) in pg_shadow... :-O

Thanks...

-----Original Message-----
From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Terry Yapt <pgsql@technovell.com>
Date: Wed, 4 Dec 2002 14:44:06 -0500 (EST)
Subject: Re: [GENERAL] passwords in pg_shadow (duplicate).

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.

Are you using a recent ODBC driver? That may help. Please report back
your pg_hba.conf setting for the host. Also, MD5 is now the preferred
method for client connections. Crypt doesn't work anymore (unless you
modify postgresql.conf). There is no reason to use 'password' plaintext
anymore.

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

Terry Yapt wrote:

========================== Hello all,

I have "migrate" a test server from 7.2.x to 7.3. All was ok
(without regression tests yet), but I have a problem to connect
to the server from all my client-odbc applications.

I have a: "FATAL: Password authentication failed for user
xxxxxx"

I have SELECT * from pg_shadow and I noticed about a difference
with my 7.2 installation:

7.2 -> Password is text plain.
7.3 -> Password is md5 crypt or
something similar.

If I change my md5 password from its encryption to plain text,
I can connect fine.

It is the normal behavior or not ? The users and their passwords
come from a pg_dumpall.

Thanks a lot.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an
appropriate subscribe-nomail command to majordomo@postgresql.org
so that your message can get through to the mailing list cleanly

--
  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

Great. We are thinking of how to deal with 'password' when pg_shadow is
MD5 encrypted. I have a patch that allows it to work and it may be in
7.3.1.

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

Terry Yapt wrote:

First, I have update to the last ODBC driver in odbc.postgresql.org.

My pg_hba.conf was with 'password' method. I have changed it to 'md5', and all was fine now. Even I can connect with a user that has a plain text password (no md5) in pg_shadow... :-O

Thanks...

-----Original Message-----
From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Terry Yapt <pgsql@technovell.com>
Date: Wed, 4 Dec 2002 14:44:06 -0500 (EST)
Subject: Re: [GENERAL] passwords in pg_shadow (duplicate).

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.

Are you using a recent ODBC driver? That may help. Please report back
your pg_hba.conf setting for the host. Also, MD5 is now the preferred
method for client connections. Crypt doesn't work anymore (unless you
modify postgresql.conf). There is no reason to use 'password' plaintext
anymore.

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

Terry Yapt wrote:

========================== Hello all,

I have "migrate" a test server from 7.2.x to 7.3. All was ok
(without regression tests yet), but I have a problem to connect
to the server from all my client-odbc applications.

I have a: "FATAL: Password authentication failed for user
xxxxxx"

I have SELECT * from pg_shadow and I noticed about a difference
with my 7.2 installation:

7.2 -> Password is text plain.
7.3 -> Password is md5 crypt or
something similar.

If I change my md5 password from its encryption to plain text,
I can connect fine.

It is the normal behavior or not ? The users and their passwords
come from a pg_dumpall.

Thanks a lot.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an
appropriate subscribe-nomail command to majordomo@postgresql.org
so that your message can get through to the mailing list cleanly

--
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

-- 
  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

Hiroshi Inoue wrote:

Bruce Momjian wrote:

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.

Honestly I don't understand your intention.
For example, if some one would like to use
crypt authentication what should he do ?
Certainly he can store a plain password using
'with unencrypted password ....'. But pg_dump
would dump it as 'with password ....' and as
a result the password would be restored as MD5
encrypted password by default.

Well, the idea of the postgresql.conf setting password_encryption was
to easily migrate people to a system that stored passwords encrypted in
the database, and it seems to have worked well for that. (Security
folks were complaining about our non-encrypted pg_shadow passwords.)

I see now there should have been a mention in the release notes about
it to warn folks who still want to use crypt. However, I question why
people would want to use crypt unless they have an interface that
doesn't support MD5. There also is a plan someday to remove support
from crypt once all clients are MD5 capable.

Let me apply that patch I posted to current and 7.3 so the fix will be
in 7.3.1. That will allow 'password' to work again. We can't really do
crypt unless they load using password_encryption set to false.

-- 
  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

OK, I have applied the following patch to CVS and 7.3. The fix will be
in 7.3.1 to allow 'password' even if pg_shadow contains MD5 passwords.

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

Hiroshi Inoue wrote:

Bruce Momjian wrote:

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.

Honestly I don't understand your intention.
For example, if some one would like to use
crypt authentication what should he do ?
Certainly he can store a plain password using
'with unencrypted password ....'. But pg_dump
would dump it as 'with password ....' and as
a result the password would be restored as MD5
encrypted password by default.

regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 
  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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

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.)

I think we should fix this for 7.3.1.

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

This part of your patch seems awfully fragile though. Better style
would be to add a boolean:

bool free_pgpass = false;

...
{
palloc pgpass here;
free_pgpass = true;
}

if (free_pgpass)
free(pg_pass);

This is less fragile and easily extends to more cases that palloc
pg_pass in future.

regards, tom lane

You were looking at last night's patch. The one attached is the one
applied, and it addresses exactly the ugly issue you saw. It compares
to see if pgpass was reassigned with new memory, and frees only in that
case.

Also, in CVS HEAD, the variables are renamed client_pass and shadow_pass
so it is clear which is which.

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

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

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.)

I think we should fix this for 7.3.1.

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

This part of your patch seems awfully fragile though. Better style
would be to add a boolean:

bool free_pgpass = false;

...
{
palloc pgpass here;
free_pgpass = true;
}

if (free_pgpass)
free(pg_pass);

This is less fragile and easily extends to more cases that palloc
pg_pass in future.

regards, tom lane

-- 
  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