Message-ID: <4E8FD681.6050409@ringerc.id.au>
Date: Sat, 08 Oct 2011 12:50:09 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:6.0.2) Gecko/20110906 Thunderbird/6.0.2
MIME-Version: 1.0
To: "Alexander James Spence [axs]" <axs@aber.ac.uk>
CC: "'pgsql-admin@postgresql.org'" <pgsql-admin@postgresql.org>
Subject: Re: password problems
References: 
 <8EDB6E83019BEC4B9153122CFB928AF001F646319E89@MAILBOX.staff.aber.ac.uk>
In-Reply-To: 
 <8EDB6E83019BEC4B9153122CFB928AF001F646319E89@MAILBOX.staff.aber.ac.uk>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 10/07/2011 10:14 PM, Alexander James Spence [axs] wrote:

> We have updated our server to 9.1 the previous version used am external
> file for authentication (unix crypt)

PostgreSQL's basic auth scheme hasn't changed in a long time, so it's 
pretty unlikely that the previous version inherently used an external 
file for auth. Is it possible the previous version was _configured_ to, 
via pg_hba.conf ?

If not: Which previous version, exactly? On what OS and (if linux) 
distro? What was the external file called, where was it and what is its 
format? Are you talking about /etc/passwd or something else?

Is it possible you were authenticating via OS usernames and passwords 
before, by using the pluggable authentication modules (PAM) subsystem in 
your OS via pg_hba.conf?

> Can I continue to use this file if so how is it referenced in pg_hba.conf?

Alas, my psychic powers are insufficient to answer this question. 
Perhaps if you said something about what the file is and where it is...

See the manual:

   http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

for more info on pg_hba.conf, in particular the auth-method section and 
the elaboraton of it given in this manual page, which the pg_hba.conf 
page links to:

   http://www.postgresql.org/docs/current/static/auth-methods.html


> If I want to continue to use unix crypt passwords how do I get them into
> the database. I have seen references to crypt and gen_salt but cannot
> get these to work.

Do you refer to pgcrypto? (Tip: If you refer to something, link to it so 
others know for certain what you're talking about).

   http://www.postgresql.org/docs/current/interactive/pgcrypto.html

If so: that's for application-level crypto work, when your app wants to 
do crypto in the database. For database user authentication you probably 
want ALTER USER:

   http://www.postgresql.org/docs/current/interactive/sql-alteruser.html

As far as I know, PostgreSQL's default built-in user database uses a 
salted md5 hash for user passwords. This will prevent you from copying 
existing `crypt'ed passwords over directly into PostgreSQL's internal 
user list, as it's a different hash algorithm. You should still be able 
to use other auth methods like PAM to use them, though.

--
Craig Ringer