Обсуждение: crypt vs password in pg_hba.conf

I just compiled and installed version 7.3.3 and am having a problem with
encrypted logins.

I built it using "--with-openssl" on the .configure command.

All seemed to go OK during the build and install. I created a user with a
password and am able to connect with psql using the password if pg_hba.conf has
a line like this:

        host all all 192.168.0.0 255.255.255.0 password

but if I change that line to this:

        host all all 192.168.0.0 255.255.255.0 crypt

the connection fails with the message:

        psql: FATAL:  Password authentication failed for user "robert"

I am pretty sure I don't have the password wrong since I tried redoing it
several times using "ALTER USER".

I am using SuSE Linux 8.0 which comes with postgres 7.2. If I use the stock
SuSE installation I have no problem with encrypted passwords.

ldd `which psql` shows the following:

        libpq.so.3 => /usr/local/pgsql/lib/libpq.so.3 (0x40017000)
        libssl.so.0.9.6 => /usr/lib/libssl.so.0.9.6 (0x40043000)
        libcrypto.so.0.9.6 => /usr/lib/libcrypto.so.0.9.6 (0x40070000)
        libz.so.1 => /lib/libz.so.1 (0x40141000)
        libreadline.so.4 => /lib/libreadline.so.4 (0x40150000)
        libcrypt.so.1 => /lib/libcrypt.so.1 (0x4017a000)
        libresolv.so.2 => /lib/libresolv.so.2 (0x401ad000)
        libnsl.so.1 => /lib/libnsl.so.1 (0x401be000)
        libdl.so.2 => /lib/libdl.so.2 (0x401d4000)
        libm.so.6 => /lib/libm.so.6 (0x401d9000)
        libc.so.6 => /lib/libc.so.6 (0x401fc000)
        libncurses.so.5 => /lib/libncurses.so.5 (0x40324000)
        /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x40000000)

This is my first experience with postgres. Can anyone give me a hint?

--
Robert C. Paulsen, Jr.
robert@paulsenonline.net

On Sat, 14 Jun 2003, Robert C. Paulsen Jr. wrote:

> I just compiled and installed version 7.3.3 and am having a problem with
> encrypted logins.
>
> I built it using "--with-openssl" on the .configure command.
>
> All seemed to go OK during the build and install. I created a user with a
> password and am able to connect with psql using the password if pg_hba.conf has
> a line like this:
>
>         host all all 192.168.0.0 255.255.255.0 password
>
> but if I change that line to this:
>
>         host all all 192.168.0.0 255.255.255.0 crypt

You need to tell postgresql that the password is to be encrypted when you set
it.

>
> the connection fails with the message:
>
>         psql: FATAL:  Password authentication failed for user "robert"

Because it is encrypting the password and comparing it against an unencrypted
one.

>
> I am pretty sure I don't have the password wrong since I tried redoing it
> several times using "ALTER USER".

Check the syntax for the alter user statement, whereever it says you may use
the word ENCRYPTED use it and you should then be able to use 'crypt' in the
pg_hba.conf.


SSL is nothing to do with this. To use SSL you have to give the server a
certificate, either a proper well known third party registered one or one that
you've generated yourself. There is documentation on how to do this, I'm not
sure which chapter it is though, do a search on ssl in the documentation.


--
Nigel J. Andrews

"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> Check the syntax for the alter user statement, whereever it says you may use
> the word ENCRYPTED use it and you should then be able to use 'crypt' in the
> pg_hba.conf.

Actually I think this advice is backwards.  If you want to use crypt
authentication mode then you have to store *unencrypted* passwords in
pg_shadow, because encrypted passwords will be stored using MD5
encryption which is not compatible with crypt-style encryption.

But probably better advice is "don't use crypt auth mode, use md5".
The crypt mode is only still there to support legacy clients that
haven't been updated to handle md5 yet.

            regards, tom lane

On Sat, Jun 14, 2003 at 11:42:11PM +0100, Nigel J. Andrews wrote:
> On Sat, 14 Jun 2003, Robert C. Paulsen Jr. wrote:
>
> > I just compiled and installed version 7.3.3 and am having a problem with
> > encrypted logins.
> >
> > I built it using "--with-openssl" on the .configure command.
> >
> > All seemed to go OK during the build and install. I created a user with a
> > password and am able to connect with psql using the password if pg_hba.conf
+has
> > a line like this:
> >
> >         host all all 192.168.0.0 255.255.255.0 password
> >
> > but if I change that line to this:
> >
> >         host all all 192.168.0.0 255.255.255.0 crypt
>
> You need to tell postgresql that the password is to be encrypted when you set
> it.
>
> >
> > the connection fails with the message:
> >
> >         psql: FATAL:  Password authentication failed for user "robert"
>
> Because it is encrypting the password and comparing it against an unencrypted
> one.
>
> >
> > I am pretty sure I don't have the password wrong since I tried redoing it
> > several times using "ALTER USER".
>
> Check the syntax for the alter user statement, whereever it says you may use
> the word ENCRYPTED use it and you should then be able to use 'crypt' in the
> pg_hba.conf.
>

I'm using the Stones and Matthew book (Beginning Databases with
PostgreSQL) to learn and its description of the create and alter user
commands doesn't mention the "encrypted" option.  So, based on your
comments I looked at the online documentation and found the following
interesting comment regarding md5:

        This is the only method that allows encrypted passwords to be
        stored in pg_shadow.

So, for crypt it seems the "encrypted" option of create and alter user
should *not* be used.

There is also a statement in the docs saying that passwords will be
encrypted in pg_shadow even *without* the "encrypted" option if the
"password_encryption" server parameter is "true". According to comments
in postgresql.conf this defaults to "false" in 7.2 and "true" in 7.3 --
that would explain my problem!

So, I switched to md5 and all is now working.

--
Robert C. Paulsen, Jr.
robert@paulsenonline.net