Message-ID: <45351C3E.7030200@007Marketing.com>
Date: Wed, 18 Oct 2006 03:39:02 +0930
From: Shane Ambler <pgsql@007Marketing.com>
User-Agent: Thunderbird 1.5.0.7 (Macintosh/20060909)
MIME-Version: 1.0
To: Jeff Davis <pgsql@j-davis.com>
CC: DEV <dev@umpa-us.com>, pgsql-general@postgresql.org
Subject: Re: Database users Passwords
References: <002c01c6f1fa$652d3f60$0b01a8c0@LT003>
	<1161102985.31645.100.camel@dogma.v10.wvs>
In-Reply-To: <1161102985.31645.100.camel@dogma.v10.wvs>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

Jeff Davis wrote:
> On Tue, 2006-10-17 at 10:41 -0400, DEV wrote:
>> Hello all,
>>
>>       I have user information in a table that I want to use to add
>> users to the user roles tables that are part of postgresql.  My
>> question is this: the passwords in my user table are in there as a
>> text file with the data being encrypted using the crypt function, is
>> there a way I can use this crypt password when I do a “CREATE ROLE
>> userid LOGIN PASSWORD 'crypt password' NOSUPERUSER INHERIT NOCREATEDB
>> NOCREATEROLE”  I know that in the current CREATE ROLE I have listed
>> will take a clear text password and encrypt it for me.  What do I need
>> to change to use an encrypted password?
>>
> 
> If user is foo and password is bar, do:
> 
> =# select md5('barfoo');
> LOG:  duration: 0.140 ms  statement: select md5('barfoo');
>                md5
> ----------------------------------
>  96948aad3fcae80c08a35c9b5958cd89
> (1 row)
> 
> =# create role foo login password 'md596948aad3fcae80c08a35c9b5958cd89'
> nosuperuser inherit nocreatedb nocreaterole;
> 
> This seems to be lacking in the docs. At least, the only place I found
> this information was a user comment in the 8.0 docs. Is this already in
> the 8.1 docs? Should we add a description of the way postgresql does the
> md5 hashes in the CREATE ROLE section?
> 

That works the way you have done it - what you have done is calculate 
the encrypted password the same way that postgres encrypts it (using 
md5) instead of using ENCRYPTED within the create role.

The issue is that the 'crypted' version will not work if entered in 
create role that way. The entered password at login will be md5ed which 
won't match the crypt version stored.

What Dev would want to look for (probably create) is a small script that 
will read his list of crypt passwords and un-crypt them into a create 
role string that is fed to psql.

I am going on the assumption that the crypt function you refer to is the 
system level crypt (also called enigma).

something along the lines of (just in pseudo code)

for each user {
$userid = SELECT userid FROM table;
$userPass = crypt < SELECT userCryptedPasswordText FROM table;

$psqlCommand = "CREATE ROLE $userid LOGIN ENCRYPTED PASSWORD $userPass 
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE"

psql < $psqlCommand
}

Not sure if you can achieve this from an sql command - my guess is you 
may get it if you setup a function in say pl/Perlu to do the 
un-crypting. But that would mean using INSERT INTO pg_authid.... which 
is not the recommended way (CREATE ROLE doesn't support sub-selects).
Creating a client that reads, un-crypts, then sends the CREATE ROLE 
commands would be the best and simplest way.


-- 

Shane Ambler
Postgres@007Marketing.com

Get Sheeky @ http://Sheeky.Biz