Re: Database users Passwords

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