Subject: Database users Passwords

From: "DEV" <[email-masked]>
Date: Tue, 17 Oct 2006 10:41:56 -0400

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?

Brian

---

From: Jeff Davis <[email-masked]>
Date: Tue, 17 Oct 2006 09:36:25 -0700

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?

Regards,
	Jeff Davis

---

From: "DEV" <[email-masked]>
Date: Tue, 17 Oct 2006 12:54:51 -0400

Okay but the issue I have is that I have the passwords already generated and
in crypt() format and would love to just use them if at all possible?

-----Original Message-----
From: [email-masked]
[mailto:[email-masked]] On Behalf Of Jeff Davis
Sent: Tuesday, October 17, 2006 12:36 PM
To: DEV
Cc: [email-masked]; [email-masked]
Subject: Re: [GENERAL] Database users Passwords

On Tue, 2006-10-17 at 10:41 -0400, DEV wrote:
> there a way I can use this crypt password when I do a "CREATE ROLE
> NOCREATEROLE"  I know that in the current CREATE ROLE I have listed

=# select md5('barfoo');

Regards,
	Jeff Davis

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

---

From: Jeff Davis <[email-masked]>
Date: Tue, 17 Oct 2006 10:54:41 -0700

> in crypt() format and would love to just use them if at all possible?

PostgreSQL won't doesn't recognize crypt passwords, as far as I know.
That means that it's pretty much impossible.

Use a password recovery tool to get the plain text back, and encrypt it
using md5.

Regards,
	Jeff Davis

---

From: Shane Ambler <[email-masked]>
Date: Wed, 18 Oct 2006 03:39:02 +0930

> LOG:  duration: 0.140 ms  statement: select md5('barfoo');

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
[email-masked]

Get Sheeky @ http://Sheeky.Biz

---

From: Jorge Godoy <[email-masked]>
Date: Tue, 17 Oct 2006 16:00:05 -0300

Shane Ambler <[email-masked]> writes:

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

Except that the hash used is unidirectional, i.e., there's no way to decrypt
it besides a brute force attack or something like that. 

If he's got, e.g., 10 users with strong passwords this kind of thing can take
some weeks.

-- 
Jorge Godoy      <[email-masked]>

---

From: Shane Ambler <[email-masked]>
Date: Wed, 18 Oct 2006 05:05:45 +0930

> If he's got, e.g., 10 users with strong passwords this kind of thing can take

crypt may be a custom function (or what Dev calls something else 
altogether) which is one way and complex - that info wasn't given.

The only crypt I know of is the crypt command (FreeBSD has it at 
/usr/bin/crypt) and is also known as enigma. This is a two way 
encryption and is fast.
If that is what he is using then decrypting will not be part of the time 
issue and is the basis of the advice I gave.

According to time - decrypting a 3K file takes about .002 seconds

If a strong one way encryption has been used then he is out of luck and 
will need the users to re-enter their passwords after the accounts are 
created with another password of some sort.
Which is also another option for him even if he can decrypt what is 
currently stored.

-- 

Shane Ambler

---

From: Peter Eisentraut <[email-masked]>
Date: Tue, 17 Oct 2006 21:35:55 +0200

Jeff Davis wrote:
> 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?

This has been in the documentation at least as far back as 7.3.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---

From: Jorge Godoy <[email-masked]>
Date: Tue, 17 Oct 2006 17:15:13 -0300

Shane Ambler <[email-masked]> writes:

> /usr/bin/crypt) and is also known as enigma. This is a two way encryption
> and is fast.

---

From: Jeff Davis <[email-masked]>
Date: Tue, 17 Oct 2006 14:15:23 -0700

> This has been in the documentation at least as far back as 7.3.

I took a second look, and I am not seeing anything in the CREATE ROLE
docs that say you need to concatenate the user to the password before
encrypting with md5. I saw that in a comment in the 8.0 docs, but I
can't find it in the 8.1 docs.

Can you please point me to a specific section?

Regards,
	Jeff Davis

---

From: Shane Ambler <[email-masked]>
Date: Wed, 18 Oct 2006 07:35:54 +0930

Jorge Godoy wrote:
> Well...  I suppose DES is not Enigma, but I may be wrong.  I just quoted this
> "extension" because it shows both algorithms used here.
> 
%man crypt
ENIGMA(1)               FreeBSD General Commands Manual

NAME
      enigma, crypt -- very simple file encryption

SYNOPSIS
      enigma [-s] [-k] [password]
      crypt [-s] [-k] [password]

DESCRIPTION
The enigma utility, also known as crypt is a very simple encryption pro-
gram, working on a ``secret-key'' basis.  It operates as a filter, i.e.,
it encrypts or decrypts a stream of data from standard input, and writes
the result to standard output.  Since its operation is fully 
symmetrical,feeding the encrypted data stream again through the engine 
(using the same secret key) will decrypt it.

Which is all irrelevant and guesses unless Dev specifies which crypt he 
actually used to create his data.

-- 

Shane Ambler