public inbox for [email protected]
help / color / mirror / Atom feedDatabase users Passwords
11+ messages / 6 participants
[nested] [flat]
* Database users Passwords
@ 2006-10-17 14:41 DEV <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: DEV @ 2006-10-17 14:41 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [GENERAL] Database users Passwords
@ 2006-10-17 16:36 Jeff Davis <[email protected]>
parent: DEV <[email protected]>
0 siblings, 3 replies; 11+ messages in thread
From: Jeff Davis @ 2006-10-17 16:36 UTC (permalink / raw)
To: DEV <[email protected]>; +Cc: [email protected]; pgsql-docs
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Database users Passwords
@ 2006-10-17 16:54 DEV <[email protected]>
parent: Jeff Davis <[email protected]>
2 siblings, 1 reply; 11+ messages in thread
From: DEV @ 2006-10-17 16:54 UTC (permalink / raw)
To: [email protected]
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 protected]
[mailto:[email protected]] On Behalf Of Jeff Davis
Sent: Tuesday, October 17, 2006 12:36 PM
To: DEV
Cc: [email protected]; [email protected]
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Database users Passwords
@ 2006-10-17 17:54 Jeff Davis <[email protected]>
parent: DEV <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Jeff Davis @ 2006-10-17 17:54 UTC (permalink / raw)
To: DEV <[email protected]>; +Cc: [email protected]
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Database users Passwords
@ 2006-10-17 18:09 Shane Ambler <[email protected]>
parent: Jeff Davis <[email protected]>
2 siblings, 1 reply; 11+ messages in thread
From: Shane Ambler @ 2006-10-17 18:09 UTC (permalink / raw)
To: Jeff Davis <[email protected]>; +Cc: DEV <[email protected]>; [email protected]
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
[email protected]
Get Sheeky @ http://Sheeky.Biz
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Database users Passwords
@ 2006-10-17 19:00 Jorge Godoy <[email protected]>
parent: Shane Ambler <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Jorge Godoy @ 2006-10-17 19:00 UTC (permalink / raw)
To: Shane Ambler <[email protected]>; +Cc: Jeff Davis <[email protected]>; DEV <[email protected]>; [email protected]
Shane Ambler <[email protected]> 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 protected]>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Database users Passwords
@ 2006-10-17 19:35 Shane Ambler <[email protected]>
parent: Jorge Godoy <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Shane Ambler @ 2006-10-17 19:35 UTC (permalink / raw)
To: Jorge Godoy <[email protected]>; +Cc: Jeff Davis <[email protected]>; DEV <[email protected]>; [email protected]
Jorge Godoy wrote:
> Shane Ambler <[email protected]> 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
[email protected]
Get Sheeky @ http://Sheeky.Biz
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [GENERAL] Database users Passwords
@ 2006-10-17 19:35 Peter Eisentraut <[email protected]>
parent: Jeff Davis <[email protected]>
2 siblings, 1 reply; 11+ messages in thread
From: Peter Eisentraut @ 2006-10-17 19:35 UTC (permalink / raw)
To: pgsql-docs; +Cc: Jeff Davis <[email protected]>; DEV <[email protected]>
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/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Database users Passwords
@ 2006-10-17 20:15 Jorge Godoy <[email protected]>
parent: Shane Ambler <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Jorge Godoy @ 2006-10-17 20:15 UTC (permalink / raw)
To: Shane Ambler <[email protected]>; +Cc: Jorge Godoy <[email protected]>; Jeff Davis <[email protected]>; DEV <[email protected]>; [email protected]
Shane Ambler <[email protected]> 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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [GENERAL] Database users Passwords
@ 2006-10-17 21:15 Jeff Davis <[email protected]>
parent: Peter Eisentraut <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Jeff Davis @ 2006-10-17 21:15 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: pgsql-docs; DEV <[email protected]>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Database users Passwords
@ 2006-10-17 22:05 Shane Ambler <[email protected]>
parent: Jorge Godoy <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Shane Ambler @ 2006-10-17 22:05 UTC (permalink / raw)
To: Jorge Godoy <[email protected]>; +Cc: DEV <[email protected]>; [email protected]
Jorge Godoy wrote:
> Shane Ambler <[email protected]> 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
[email protected]
Get Sheeky @ http://Sheeky.Biz
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2006-10-17 22:05 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-17 14:41 Database users Passwords DEV <[email protected]>
2006-10-17 16:36 ` Jeff Davis <[email protected]>
2006-10-17 16:54 ` DEV <[email protected]>
2006-10-17 17:54 ` Jeff Davis <[email protected]>
2006-10-17 18:09 ` Shane Ambler <[email protected]>
2006-10-17 19:00 ` Jorge Godoy <[email protected]>
2006-10-17 19:35 ` Shane Ambler <[email protected]>
2006-10-17 20:15 ` Jorge Godoy <[email protected]>
2006-10-17 22:05 ` Shane Ambler <[email protected]>
2006-10-17 19:35 ` Peter Eisentraut <[email protected]>
2006-10-17 21:15 ` Jeff Davis <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox