Обсуждение: passwords and 7.3

I have upgraded from 7.2.1 to 7.3.1, but I am having trouble with user
authentication. I have in my pg_hba.conf:

host    all         all          127.0.0.1     255.255.255.255    password passwords

This is exactly what I had for 7.2.1, with the addition of the extra
"all" column. I made sure to do a pg_dumpall from the 7.2 database and
restored on the 7.3 database. I also copied the passwords file I had in
the 7.2 data directory to the 7.3 data directory. It seems, however,
that the passwords file is not being read or is being interpreted
incorrectly. When I connect to the database server over TCP using the
same username and password that worked previously, it fails. The
postmaster spits out an error (elided is not the actual username):

FATAL:  Password authentication failed for user "elided"

Clearly, the TCP connection is being made properly and some username and
password is being sent. The connection is being made by the latest and
greatest JDBC drivers (downloaded from jdbc.postgresql.org less than a
week ago), and it had been working fine under 7.2.1. I compiled the
postmaster myself with the following configuration:

./configure --prefix=/space/postgresql7.3.1 --with-includes=/usr/local/include --with-libraries=/usr/local/lib
--enable-depend--enable-recode --enable-odbc --without-java --enable-syslog --enable-unicode-conversion
--enable-multibyte--with-openssl --with-unixodbc 

make install install-all-headers

I can't find any reference to anything changing with password
authentication from 7.2 to 7.3, but something is clearly wrong. I also
don't seem to have a pg_passwd executable, and I'm not sure why that is.

Is there some way to get 7.3 to believe in the existing passwords file?
Is there some way to take the existing passwords file and put the
encrypted passwords in the database somewhere that authentication can
find it?

--Greg

Gregory Seidman <gss+pg@cs.brown.edu> writes:
> I have in my pg_hba.conf:

> host    all         all          127.0.0.1     255.255.255.255    password passwords

Secondary password files aren't supported anymore as of 7.3.  If that's
not in the release notes, it's a serious oversight :-(

            regards, tom lane

Tom Lane sez:
} Gregory Seidman <gss+pg@cs.brown.edu> writes:
} > I have in my pg_hba.conf:
}
} > host    all         all          127.0.0.1     255.255.255.255    password passwords
}
} Secondary password files aren't supported anymore as of 7.3.  If that's
} not in the release notes, it's a serious oversight :-(

It certainly isn't mentioned in README or INSTALL. Anyhow, how do I take
the existing external password file (which has encrypted passwords) and
put it into the DB? Can I just update the passwd field in pg_shadow with
the password string in the external file? Does it use the same
encryption? Is it still called password in the pg_hba.conf, or something
else?

}             regards, tom lane
--Greg

Gregory Seidman <gss+pg@cs.brown.edu> writes:
> Tom Lane sez:
> } Secondary password files aren't supported anymore as of 7.3.  If that's
> } not in the release notes, it's a serious oversight :-(

> It certainly isn't mentioned in README or INSTALL. Anyhow, how do I take
> the existing external password file (which has encrypted passwords) and
> put it into the DB?

I'm afraid you don't.  The encryption method that was used in external
files was crypt(3), which we're migrating away from for various reasons,
chiefly lack of cross-platform portability.  The encryption method
that's now supported in pg_shadow entries is MD5.

I'd counsel issuing temporary new passwords to all your users and
advising them to change them to something of their own choice...

            regards, tom lane

Tom Lane sez:
} Gregory Seidman <gss+pg@cs.brown.edu> writes:
} > Tom Lane sez:
} > } Secondary password files aren't supported anymore as of 7.3.  If that's
} > } not in the release notes, it's a serious oversight :-(
}
} > It certainly isn't mentioned in README or INSTALL. Anyhow, how do I take
} > the existing external password file (which has encrypted passwords) and
} > put it into the DB?
}
} I'm afraid you don't.  The encryption method that was used in external
} files was crypt(3), which we're migrating away from for various reasons,
} chiefly lack of cross-platform portability.  The encryption method
} that's now supported in pg_shadow entries is MD5.
}
} I'd counsel issuing temporary new passwords to all your users and
} advising them to change them to something of their own choice...

Hmph. I'm not thrilled that the upgrade path here is a dead end. Since
this particular installation is my own private install, I can (and have)
put the passwords in plaintext in the pg_shadow table. In the general
case, however, this disenfranchises anyone relying on the external
password file to support external users.

Incidentally, how do I make an md5 password? I assume the authentication
method in pg_hba.conf has to be set to md5, but how do I encrypt the
password to put in the passwd field in pg_shadow? Am I expected to have
an md5 app on my system somewhere (I don't)? Is there a tool installed
with postgresql (I don't see such a thing)?

}             regards, tom lane
--Greg

Gregory Seidman <gss+pg@cs.brown.edu> writes:
> Incidentally, how do I make an md5 password? I assume the authentication
> method in pg_hba.conf has to be set to md5, but how do I encrypt the
> password to put in the passwd field in pg_shadow?

The system does it for you, if you specify ENCRYPTED in the CREATE or
ALTER USER command that sets the password.  There is also a
postgresql.conf option that makes ENCRYPTED the default behavior of
these commands --- which we did not turn on for 7.3, but it will be on
by default for 7.4, IIRC.

As of current sources, an MD5-stored password can be used with either
password or md5 auth protocol --- but AFAICS the only reason you'd want
to use password (cleartext) protocol would be backwards compatibility
with ancient client libraries.  (Due to an oversight, 7.3 release
required you to use md5 protocol with an md5 password; this is fixed for
7.3.2, but I don't recall whether it got into 7.3.1 or not.)

            regards, tom lane

tgl@sss.pgh.pa.us wrote:
>...
> As of current sources, an MD5-stored password can be used with either
> password or md5 auth protocol --- but AFAICS the only reason you'd want
> to use password (cleartext) protocol would be backwards compatibility
> with ancient client libraries.  (Due to an oversight, 7.3 release
> required you to use md5 protocol with an md5 password; this is fixed for
> 7.3.2, but I don't recall whether it got into 7.3.1 or not.)

Yes it is in 7.3.1: you can use cleartext password authentication protocol
with MD5 stored passwords. Which is good, because among those "ancient"
client libraries is the only available copy of libpq.dll and libpgtcl.dll
I'm aware of.

Gregory Seidman wrote:
> Tom Lane sez:
> } Gregory Seidman <gss+pg@cs.brown.edu> writes:
> } > Tom Lane sez:
> } > } Secondary password files aren't supported anymore as of 7.3.  If that's
> } > } not in the release notes, it's a serious oversight :-(
> }
> } > It certainly isn't mentioned in README or INSTALL. Anyhow, how do I take
> } > the existing external password file (which has encrypted passwords) and
> } > put it into the DB?
> }
> } I'm afraid you don't.  The encryption method that was used in external
> } files was crypt(3), which we're migrating away from for various reasons,
> } chiefly lack of cross-platform portability.  The encryption method
> } that's now supported in pg_shadow entries is MD5.
> }
> } I'd counsel issuing temporary new passwords to all your users and
> } advising them to change them to something of their own choice...
>
> Hmph. I'm not thrilled that the upgrade path here is a dead end. Since
> this particular installation is my own private install, I can (and have)
> put the passwords in plaintext in the pg_shadow table. In the general
> case, however, this disenfranchises anyone relying on the external
> password file to support external users.
>
> Incidentally, how do I make an md5 password? I assume the authentication
> method in pg_hba.conf has to be set to md5, but how do I encrypt the
> password to put in the passwd field in pg_shadow? Am I expected to have
> an md5 app on my system somewhere (I don't)? Is there a tool installed
> with postgresql (I don't see such a thing)?

It isn't mentioned in the MIGRATION section (bad), but is mentioned as
change in the HISTORY file:

   Remove secondary password file capability and pg_password utility
   (Bruce)

We honestly didn't think anyone was using that secondary password file
anymore.  You are the first to report the problem.

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

Tom Lane wrote:
> Gregory Seidman <gss+pg@cs.brown.edu> writes:
> > Incidentally, how do I make an md5 password? I assume the authentication
> > method in pg_hba.conf has to be set to md5, but how do I encrypt the
> > password to put in the passwd field in pg_shadow?
>
> The system does it for you, if you specify ENCRYPTED in the CREATE or
> ALTER USER command that sets the password.  There is also a
> postgresql.conf option that makes ENCRYPTED the default behavior of
> these commands --- which we did not turn on for 7.3, but it will be on
> by default for 7.4, IIRC.

It was not enabled in 7.2 but _was_ enabled in 7.3, so if you just
create the user and specify the clear-text password, you will get it
stored MD5 encrypted in the database.  The only way to store it
plaintext is to specify UNENCRYPTED.

> As of current sources, an MD5-stored password can be used with either
> password or md5 auth protocol --- but AFAICS the only reason you'd want
> to use password (cleartext) protocol would be backwards compatibility
> with ancient client libraries.  (Due to an oversight, 7.3 release
> required you to use md5 protocol with an md5 password; this is fixed for
> 7.3.2, but I don't recall whether it got into 7.3.1 or not.)

Fixed in 7.3.1.

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

> It isn't mentioned in the MIGRATION section (bad), but is mentioned as
> change in the HISTORY file:
>
>    Remove secondary password file capability and pg_password utility
>    (Bruce)
>
> We honestly didn't think anyone was using that secondary password file
> anymore.  You are the first to report the problem.

Our PostgreSQL installation heavily depends on this external passwords
file. This one is generated from a global NIS (standard crypt method)
database, and thus allows users to connect to the databases without
the need to change yet another password. This behaviour is really
appreciated here.

My 2 euro-cents.

--
Nicolas

On Mon, 27 Jan 2003, Nicolas Kowalski wrote:

> Our PostgreSQL installation heavily depends on this external passwords
> file. This one is generated from a global NIS (standard crypt method)
> database, and thus allows users to connect to the databases without
> the need to change yet another password.

I know it's a bunch of programming work, but if you use "unencrypted"
passwords (i.e., crypt-style rather than MD5) for the users in the db,
could you not write a program that reads the pg_user table directly, and
then updates those passwords that need updating?

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

On Mon, Jan 27, 2003 at 09:33:34 +0100,
  Nicolas Kowalski <Nicolas.Kowalski@imag.fr> wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>
> > It isn't mentioned in the MIGRATION section (bad), but is mentioned as
> > change in the HISTORY file:
> >
> >    Remove secondary password file capability and pg_password utility
> >    (Bruce)
> >
> > We honestly didn't think anyone was using that secondary password file
> > anymore.  You are the first to report the problem.
>
> Our PostgreSQL installation heavily depends on this external passwords
> file. This one is generated from a global NIS (standard crypt method)
> database, and thus allows users to connect to the databases without
> the need to change yet another password. This behaviour is really
> appreciated here.

Won't PAM allow you to do something like this?

Curt Sampson <cjs@cynic.net> writes:

> I know it's a bunch of programming work, but if you use "unencrypted"
> passwords (i.e., crypt-style rather than MD5) for the users in the db,
> could you not write a program that reads the pg_user table directly, and
> then updates those passwords that need updating?

Currently, our external password file looks like this :

kowalski:<crypted-password-here>
...
phppgadmin:+
webcalendar:+
...

And so on. Regular Unix users have their passwords set from the NIS
passwd database (standard crypt method), and PostgreSQL-specific users
have their passwords defined in pg_shadow (no encryption there). This
last use prevents us from using PAM-style authentication I presume.

The pg_hba config file contains the following line :

host         all         <network-address>   255.255.255.0       password passwd


If the passwords are stored in their "crypt form" in pg_shadow, yes I
can write such a script, then call it through cron; but as I
understand the docs, passwords are stored using MD5 method, which
makes all our passwords unusable, am I wrong ?

--
Nicolas

Nicolas Kowalski <Nicolas.Kowalski@imag.fr> writes:
> And so on. Regular Unix users have their passwords set from the NIS
> passwd database (standard crypt method), and PostgreSQL-specific users
> have their passwords defined in pg_shadow (no encryption there). This
> last use prevents us from using PAM-style authentication I presume.

I don't see why.  You could write a PAM plugin to do whatever you want
(that's the whole point of PAM, isn't it)?

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Nicolas Kowalski <Nicolas.Kowalski@imag.fr> writes:
>> And so on. Regular Unix users have their passwords set from the NIS
>> passwd database (standard crypt method), and PostgreSQL-specific users
>> have their passwords defined in pg_shadow (no encryption there). This
>> last use prevents us from using PAM-style authentication I presume.
>
> I don't see why.  You could write a PAM plugin to do whatever you want
> (that's the whole point of PAM, isn't it)?

Hm, you mean PostgreSQL asking PAM asking PostgreSQL for some non-UNIX
users passwords ? This looks like a loop, but yes, this is perhaps a
solution.

[Later...]

You are right, this works well. :-)


For those who are interested, I did the following on a Debian 3.0
GNU/Linux server running PostgreSQL 7.2.1 (any comment appreciated):

1) install pam_pgsql module,

2) create a view for extracting usefull PAM information from pg_shadow:

intranet=# \d pam_auth
           View "pam_auth"
    Column     |  Type   | Modifiers
---------------+---------+-----------
 user_name     | name    |
 user_password | text    |
 acc_expired   | boolean |
 acc_new_pwreq | boolean |
View definition: SELECT pg_shadow.usename AS user_name, pg_shadow.passwd AS user_password, 'f'::bool AS acc_expired,
'f'::boolAS acc_new_pwreq FROM pg_shadow; 


3) create a config file for pam_pgsql (/etc/pam_pgsql.conf):

database = intranet
user = postgres
table = pam_auth
user_column = user_name
pwd_column = user_password
expired_column = acc_expired
newtok_column = acc_new_pwreq
debug


4) create a postgresql service config file for pam (/etc/pam.d/postgresql):

auth        sufficient    pam_pgsql.so
auth        required      pam_unix.so
account     sufficient    pam_pgsql.so
account     required      pam_unix.so
password    sufficient    pam_pgsql.so
password    required      pam_unix.so



4) change the appropriate line in pg_hba.conf:

host         all         129.88.43.0   255.255.255.0       pam


5) set the server to do no encryption
(/etc/postgresql/postgresql.conf):

password_encryption = false


6) Enjoy.


--
Nicolas

Salut Nicolas,

I was searching for the same thing...

Thanks
Michelle
from Strasbourg

Am 18:21 2003-01-27 +0100 hat Nicolas Kowalski geschrieben:


>For those who are interested, I did the following on a Debian 3.0
>GNU/Linux server running PostgreSQL 7.2.1 (any comment appreciated):

>6) Enjoy.


##################  Get the Power of Debian/GNU-Linux
##################

Hi,

Please let me know how to unsubscribe from the list I have sent mail to 
majordomo@postgresql.org

--    Best Regards - Savita ---------------------------------------------------- Hewlett Packard (India) +91 80 2051288 (Phone) 847 1288(HP Telnet) ----------------------------------------------------

Hi Savita,

Taking care of it for you now.

:-)

Regards and best wishes,

Justin Clift


savita wrote:
> Hi,
>
> Please let me know how to unsubscribe from the list I have sent mail to
> _*majordomo@postgresql.org*_
>
> 100 times but I am still getting the mails
>
> --
>     Best Regards
>   - Savita
>   ----------------------------------------------------
>   Hewlett Packard (India)
>   +91 80 2051288 (Phone)
>   847 1288(HP Telnet)
>   ----------------------------------------------------


--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

On Tue, 4 Feb 2003, savita wrote:

> Hi,
>
> Please let me know how to unsubscribe from the list I have sent mail to
> majordomo@postgresql.org
>
> 100 times but I am still getting the mails

You probably are already doing this but...you absolutely have to make sure that
the emails you send to majordomo appear to be from the exact same address that
the list is sending to you at. I'm not sure whether it's just the bit between
the < >  which is important or everything but it can't harm you to get all of
it the same.

I do know the troubles you are having. I managed to subscribe to the admin list
twice at the start because my outgoing From field gets a very slightly
different address for me than the one I quote people. I managed to unsubscribe
only one of the addresses. To this day I get emails from the list but all my
emails to it get moderated because I'm not on the list. That's life as they
say. Although as things go that's not as bad as life can be so I don't worry
about it.


--
Nigel J. Andrews