Database users Passwords

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

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: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jeff Davis
Sent: Tuesday, October 17, 2006 12:36 PM
To: DEV
Cc: pgsql-general@postgresql.org; pgsql-docs@postgresql.org
Subject: Re: [GENERAL] Database users Passwords

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

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

On Tue, 2006-10-17 at 12:54 -0400, DEV wrote:

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?

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

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

Shane Ambler <pgsql@007Marketing.com> 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 <jgodoy@gmail.com>

Jorge Godoy wrote:

Shane Ambler <pgsql@007Marketing.com> 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.

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
Postgres@007Marketing.com

Get Sheeky @ http://Sheeky.Biz

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/

Shane Ambler <pgsql@007Marketing.com> writes:

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.

From the man page of the crypt that I have here:

================================================================================
GNU EXTENSION
The glibc2 version of this function has the following additional
features. If salt is a character string starting with the three
characters "$1$" followed by at most eight characters, and optionally
terminated by "$", then instead of using the DES machine, the glibc
crypt function uses an MD5-based algorithm, and outputs up to 34 bytes,
namely "$1$<string>$", where "<string>" stands for the up to 8
characters following "$1$" in the salt, followed by 22 bytes chosen
from the set [a-zA-Z0-9./]. The entire key is significant here
(instead of only the first 8 bytes).

Programs using this function must be linked with -lcrypt.

CONFORMING TO
SVID, X/OPEN, 4.3BSD, POSIX 1003.1-2001
================================================================================

Well... I suppose DES is not Enigma, but I may be wrong. I just quoted this
"extension" because it shows both algorithms used here.

--
Jorge Godoy <jgodoy@gmail.com>

On Tue, 2006-10-17 at 21:35 +0200, Peter Eisentraut wrote:

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.

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

Jorge Godoy wrote:

Shane Ambler <pgsql@007Marketing.com> writes:

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

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
Postgres@007Marketing.com

Get Sheeky @ http://Sheeky.Biz