Thread: Database users Passwords
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
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
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>
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