From dev@umpa-us.com Thu Jun 4 20:28:45 2026 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 58F7D9FB398 for ; Tue, 17 Oct 2006 11:42:13 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 95864-07 for ; Tue, 17 Oct 2006 11:42:05 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.umpa-us.com (mail.umpa-us.com [64.9.53.20]) by postgresql.org (Postfix) with ESMTP id 30A459FB21B for ; Tue, 17 Oct 2006 11:42:03 -0300 (ADT) Received: (qmail 18497 invoked by uid 511); 17 Oct 2006 10:42:02 -0400 Received: from 64.9.53.17 by umpa.us (envelope-from , uid 509) with qmail-scanner-1.25-st-qms (clamdscan: 0.83/792. spamassassin: 3.0.2. perlscan: 1.25-st-qms. Clear:RC:0(64.9.53.17):SA:0(-4.0/4.0):. Processed in 0.431691 secs); 17 Oct 2006 14:42:02 -0000 X-Antivirus-UMPA-Mail-From: dev@umpa-us.com via umpa.us X-Antivirus-UMPA: 1.25-st-qms (Clear:RC:0(64.9.53.17):SA:0(-4.0/4.0):. Processed in 0.431691 secs Process 18487) Received: from scutum.umpa-us.com (HELO LT003) (dev@umpa-us.com@64.9.53.17) by mail.umpa-us.com with SMTP; 17 Oct 2006 10:42:01 -0400 From: "DEV" To: Subject: Database users Passwords Date: Tue, 17 Oct 2006 10:41:56 -0400 Message-ID: <002c01c6f1fa$652d3f60$0b01a8c0@LT003> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002D_01C6F1D8.DE1B9F60" X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 Thread-Index: Acbx+mTG2lQMmtbXTPmHf+HzI3Yh6A== X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.299 tagged_above=0 required=5 tests=AWL, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200610/822 X-Sequence-Number: 102348 This is a multi-part message in MIME format. ------=_NextPart_000_002D_01C6F1D8.DE1B9F60 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_002D_01C6F1D8.DE1B9F60 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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

 

 

------=_NextPart_000_002D_01C6F1D8.DE1B9F60-- From pgsql@j-davis.com Thu Jun 4 20:28:45 2026 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 95A469FB428; Tue, 17 Oct 2006 13:41:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 73782-01; Tue, 17 Oct 2006 16:40:39 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from servata.com (ip-216-152-249-241.servata.com [216.152.249.241]) by postgresql.org (Postfix) with ESMTP id 8F1A59FB559; Tue, 17 Oct 2006 13:36:38 -0300 (ADT) Received: from [209.162.219.253] (helo=dogma.v10.wvs) by servata.com with esmtpa (Exim 4.50) id 1GZrvU-0006wy-Po; Tue, 17 Oct 2006 09:36:24 -0700 Subject: Re: [GENERAL] Database users Passwords From: Jeff Davis To: DEV Cc: pgsql-general@postgresql.org, pgsql-docs@postgresql.org In-Reply-To: <002c01c6f1fa$652d3f60$0b01a8c0@LT003> References: <002c01c6f1fa$652d3f60$0b01a8c0@LT003> Content-Type: text/plain; charset=UTF-8 Date: Tue, 17 Oct 2006 09:36:25 -0700 Message-Id: <1161102985.31645.100.camel@dogma.v10.wvs> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 (2.0.4-7) Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.134 tagged_above=0 required=5 tests=FORGED_RCVD_HELO, SPF_HELO_PASS X-Spam-Level: X-Archive-Number: 200610/29 X-Sequence-Number: 3750 On Tue, 2006-10-17 at 10:41 -0400, DEV wrote: > Hello all, >=20 > 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 =E2=80=9CCREATE ROL= E > userid LOGIN PASSWORD 'crypt password' NOSUPERUSER INHERIT NOCREATEDB > NOCREATEROLE=E2=80=9D I know that in the current CREATE ROLE I have list= ed > will take a clear text password and encrypt it for me. What do I need > to change to use an encrypted password? >=20 If user is foo and password is bar, do: =3D# select md5('barfoo'); LOG: duration: 0.140 ms statement: select md5('barfoo'); md5 ---------------------------------- 96948aad3fcae80c08a35c9b5958cd89 (1 row) =3D# 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@umpa-us.com Thu Jun 4 20:28:45 2026 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 65A479F9ED0 for ; Tue, 17 Oct 2006 13:55:10 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 73876-05 for ; Tue, 17 Oct 2006 16:55:02 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.umpa-us.com (mail.umpa-us.com [64.9.53.20]) by postgresql.org (Postfix) with ESMTP id 105CA9F93A0 for ; Tue, 17 Oct 2006 13:54:59 -0300 (ADT) Received: (qmail 6886 invoked by uid 511); 17 Oct 2006 12:54:59 -0400 Received: from 64.9.53.17 by umpa.us (envelope-from , uid 509) with qmail-scanner-1.25-st-qms (clamdscan: 0.83/792. spamassassin: 3.0.2. perlscan: 1.25-st-qms. Clear:RC:0(64.9.53.17):SA:0(-4.0/4.0):. Processed in 2.157801 secs); 17 Oct 2006 16:54:59 -0000 X-Antivirus-UMPA-Mail-From: dev@umpa-us.com via umpa.us X-Antivirus-UMPA: 1.25-st-qms (Clear:RC:0(64.9.53.17):SA:0(-4.0/4.0):. Processed in 2.157801 secs Process 6864) Received: from scutum.umpa-us.com (HELO LT003) (dev@umpa-us.com@64.9.53.17) by mail.umpa-us.com with SMTP; 17 Oct 2006 12:54:56 -0400 From: "DEV" To: Subject: Re: Database users Passwords Date: Tue, 17 Oct 2006 12:54:51 -0400 Message-ID: <008101c6f20c$f6babb80$0b01a8c0@LT003> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 11 In-Reply-To: <1161102985.31645.100.camel@dogma.v10.wvs> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 Thread-Index: AcbyC+/lT5T/iRlPR1KNCZsNzFr6EgAAOlIg X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200610/840 X-Sequence-Number: 102366 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 From pgsql@j-davis.com Thu Jun 4 20:28:46 2026 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 78CE49FA60A for ; Tue, 17 Oct 2006 14:54:58 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 58526-06 for ; Tue, 17 Oct 2006 14:54:54 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from servata.com (ip-216-152-249-241.servata.com [216.152.249.241]) by postgresql.org (Postfix) with ESMTP id C83ED9FA4C9 for ; Tue, 17 Oct 2006 14:54:53 -0300 (ADT) Received: from [209.162.219.253] (helo=dogma.v10.wvs) by servata.com with esmtpa (Exim 4.50) id 1GZt9F-0000nF-Ht; Tue, 17 Oct 2006 10:54:41 -0700 Subject: Re: Database users Passwords From: Jeff Davis To: DEV Cc: pgsql-general@postgresql.org In-Reply-To: <008101c6f20c$f6babb80$0b01a8c0@LT003> References: <008101c6f20c$f6babb80$0b01a8c0@LT003> Content-Type: text/plain Date: Tue, 17 Oct 2006 10:54:41 -0700 Message-Id: <1161107681.31645.142.camel@dogma.v10.wvs> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 (2.0.4-7) Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.231 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, SPF_HELO_PASS X-Spam-Level: X-Archive-Number: 200610/843 X-Sequence-Number: 102369 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 From pgsql@007Marketing.com Thu Jun 4 20:28:46 2026 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 88DE29FA5F0 for ; Tue, 17 Oct 2006 15:09:20 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 89712-05 for ; Tue, 17 Oct 2006 18:09:16 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from ash25e.internode.on.net (ash25e.internode.on.net [203.16.214.182]) by postgresql.org (Postfix) with ESMTP id 0F1B09FA3C0 for ; Tue, 17 Oct 2006 15:09:15 -0300 (ADT) Received: from [192.168.8.200] (ppp247-71.static.internode.on.net [203.122.247.71]) by ash25e.internode.on.net (8.13.6/8.13.5) with ESMTP id k9HI96ao040156; Wed, 18 Oct 2006 03:39:07 +0930 (CST) (envelope-from pgsql@007Marketing.com) Message-ID: <45351C3E.7030200@007Marketing.com> Date: Wed, 18 Oct 2006 03:39:02 +0930 From: Shane Ambler User-Agent: Thunderbird 1.5.0.7 (Macintosh/20060909) MIME-Version: 1.0 To: Jeff Davis CC: DEV , pgsql-general@postgresql.org Subject: Re: Database users Passwords References: <002c01c6f1fa$652d3f60$0b01a8c0@LT003> <1161102985.31645.100.camel@dogma.v10.wvs> In-Reply-To: <1161102985.31645.100.camel@dogma.v10.wvs> Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.012 tagged_above=0 required=5 tests=BIZ_TLD, SPF_HELO_PASS X-Spam-Level: ** X-Archive-Number: 200610/845 X-Sequence-Number: 102371 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 From jgodoy@gmail.com Thu Jun 4 20:28:46 2026 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 7A1059FB1CA for ; Tue, 17 Oct 2006 16:00:26 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 98787-09 for ; Tue, 17 Oct 2006 19:00:21 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.190]) by postgresql.org (Postfix) with ESMTP id 33D109FA4C9 for ; Tue, 17 Oct 2006 16:00:21 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id i2so411839nfe for ; Tue, 17 Oct 2006 12:00:19 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:from:to:cc:subject:in-reply-to:references:date:message-id:user-agent:mime-version:content-type:x-virus-scanned; b=MEQ9Q8TmH9ZtfhnY6mbN98fPhUV51cXzFpjWEjxgcWwweOnv4/5Y3kHVoU3OQ6ZdWW2BxllexvA0re59sBv6Bf8chtdvzROafMWhmJrTlAMI53RWmZheaVtH5LwESIYFyIxtOOfGko+eGER815FSZmlpcKsq26iyVH6us9I8ymw= Received: by 10.48.210.16 with SMTP id i16mr2093288nfg; Tue, 17 Oct 2006 12:00:19 -0700 (PDT) Received: from strongwill.g2ctech ( [201.47.19.248]) by mx.google.com with ESMTP id p43sm1951131nfa.2006.10.17.12.00.15; Tue, 17 Oct 2006 12:00:19 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by strongwill.g2ctech (Postfix) with ESMTP id 4A764E5672; Tue, 17 Oct 2006 17:00:09 -0200 (BRST) Received: from strongwill.g2ctech ([127.0.0.1]) by localhost (strongwill.g2ctech [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 32708-01; Tue, 17 Oct 2006 17:00:08 -0200 (BRST) Received: from jupiter.g2ctech (unknown [10.0.0.240]) by strongwill.g2ctech (Postfix) with ESMTP id 7EA07E5697; Tue, 17 Oct 2006 17:00:08 -0200 (BRST) Received: by jupiter.g2ctech (Postfix, from userid 1000) id ADF7317C701; Tue, 17 Oct 2006 16:00:05 -0300 (BRST) From: Jorge Godoy To: Shane Ambler Cc: Jeff Davis , DEV , pgsql-general@postgresql.org Subject: Re: Database users Passwords In-Reply-To: <45351C3E.7030200@007Marketing.com> (Shane Ambler's message of "Wed\, 18 Oct 2006 03\:39\:02 +0930") References: <002c01c6f1fa$652d3f60$0b01a8c0@LT003> <1161102985.31645.100.camel@dogma.v10.wvs> <45351C3E.7030200@007Marketing.com> Date: Tue, 17 Oct 2006 16:00:05 -0300 Message-ID: <873b9mlqdm.fsf@gmail.com> User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/21.3 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: amavisd-new at g2ctech.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.199 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/849 X-Sequence-Number: 102375 Shane Ambler 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 From pgsql@007Marketing.com Thu Jun 4 20:28:46 2026 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 382B69F9DAD for ; Tue, 17 Oct 2006 16:36:10 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 07397-09 for ; Tue, 17 Oct 2006 19:35:59 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp1.adl2.internode.on.net (smtp1.adl2.internode.on.net [203.16.214.181]) by postgresql.org (Postfix) with ESMTP id D1EAA9F98FA for ; Tue, 17 Oct 2006 16:35:58 -0300 (ADT) Received: from [192.168.8.200] (ppp247-71.static.internode.on.net [203.122.247.71]) by smtp1.adl2.internode.on.net (8.13.6/8.13.5) with ESMTP id k9HJZjtE025991; Wed, 18 Oct 2006 05:05:45 +0930 (CST) (envelope-from pgsql@007Marketing.com) Message-ID: <45353091.2080102@007Marketing.com> Date: Wed, 18 Oct 2006 05:05:45 +0930 From: Shane Ambler User-Agent: Thunderbird 1.5.0.7 (Macintosh/20060909) MIME-Version: 1.0 To: Jorge Godoy CC: Jeff Davis , DEV , pgsql-general@postgresql.org Subject: Re: Database users Passwords References: <002c01c6f1fa$652d3f60$0b01a8c0@LT003> <1161102985.31645.100.camel@dogma.v10.wvs> <45351C3E.7030200@007Marketing.com> <873b9mlqdm.fsf@gmail.com> In-Reply-To: <873b9mlqdm.fsf@gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.012 tagged_above=0 required=5 tests=BIZ_TLD, SPF_HELO_PASS X-Spam-Level: ** X-Archive-Number: 200610/851 X-Sequence-Number: 102377 Jorge Godoy wrote: > Shane Ambler 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 From peter_e@gmx.net Thu Jun 4 20:28:46 2026 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 440F69FA38B for ; Tue, 17 Oct 2006 16:36:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 87262-06 for ; Tue, 17 Oct 2006 16:36:00 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from mail.gmx.net (mail.gmx.net [213.165.64.20]) by postgresql.org (Postfix) with SMTP id 458BC9F9DAD for ; Tue, 17 Oct 2006 16:35:59 -0300 (ADT) Received: (qmail invoked by alias); 17 Oct 2006 19:35:57 -0000 Received: from dslb-084-063-028-198.pools.arcor-ip.net (EHLO colt.pezone.net) [84.63.28.198] by mail.gmx.net (mp027) with SMTP; 17 Oct 2006 21:35:57 +0200 X-Authenticated: #495269 From: Peter Eisentraut To: pgsql-docs@postgresql.org Subject: Re: [GENERAL] Database users Passwords Date: Tue, 17 Oct 2006 21:35:55 +0200 User-Agent: KMail/1.9.4 Cc: Jeff Davis , DEV References: <002c01c6f1fa$652d3f60$0b01a8c0@LT003> <1161102985.31645.100.camel@dogma.v10.wvs> In-Reply-To: <1161102985.31645.100.camel@dogma.v10.wvs> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200610172135.55731.peter_e@gmx.net> X-Y-GMX-Trusted: 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200610/30 X-Sequence-Number: 3751 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 godoy@ieee.org Thu Jun 4 20:28:46 2026 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id D29C99FB1CA for ; Tue, 17 Oct 2006 17:15:33 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 22264-01 for ; Tue, 17 Oct 2006 20:15:29 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.184]) by postgresql.org (Postfix) with ESMTP id 8A1439FA4A7 for ; Tue, 17 Oct 2006 17:15:29 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id i2so433269nfe for ; Tue, 17 Oct 2006 13:15:28 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:from:to:cc:subject:in-reply-to:references:date:message-id:user-agent:mime-version:content-type:x-virus-scanned:sender; b=hNK6nixs29EP/ouuO+ISZqfpicPl6aF2AQqZfJuQV8HvoVQvwMjnElS0wmWEk8Y9M4oczOrUPuX8dHJRQbwRF95hTOpPE6v6gytMUqDTc0ML9/i0GL0t3ctQ1kYnFBQ34de3b/p/Onjv31KuEci41gK+Ieco+VasHETd7WSnSoM= Received: by 10.49.19.5 with SMTP id w5mr784565nfi; Tue, 17 Oct 2006 13:15:27 -0700 (PDT) Received: from strongwill.g2ctech ( [201.47.19.248]) by mx.google.com with ESMTP id l22sm951741nfc.2006.10.17.13.15.24; Tue, 17 Oct 2006 13:15:27 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by strongwill.g2ctech (Postfix) with ESMTP id 340C8E56D5; Tue, 17 Oct 2006 18:15:19 -0200 (BRST) Received: from strongwill.g2ctech ([127.0.0.1]) by localhost (strongwill.g2ctech [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 04704-01; Tue, 17 Oct 2006 18:15:17 -0200 (BRST) Received: from jupiter.g2ctech (unknown [10.0.0.240]) by strongwill.g2ctech (Postfix) with ESMTP id 5856BE5655; Tue, 17 Oct 2006 18:15:17 -0200 (BRST) Received: by jupiter.g2ctech (Postfix, from userid 1000) id 23F811404C6; Tue, 17 Oct 2006 17:15:14 -0300 (BRST) From: Jorge Godoy To: Shane Ambler Cc: Jorge Godoy , Jeff Davis , DEV , pgsql-general@postgresql.org Subject: Re: Database users Passwords In-Reply-To: <45353091.2080102@007Marketing.com> (Shane Ambler's message of "Wed\, 18 Oct 2006 05\:05\:45 +0930") References: <002c01c6f1fa$652d3f60$0b01a8c0@LT003> <1161102985.31645.100.camel@dogma.v10.wvs> <45351C3E.7030200@007Marketing.com> <873b9mlqdm.fsf@gmail.com> <45353091.2080102@007Marketing.com> Date: Tue, 17 Oct 2006 17:15:13 -0300 Message-ID: <878xjek8by.fsf@ieee.org> User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/21.3 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: amavisd-new at g2ctech.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.199 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/852 X-Sequence-Number: 102378 Shane Ambler 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 pgsql@j-davis.com Thu Jun 4 20:28:46 2026 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 6317D9FA3A3 for ; Tue, 17 Oct 2006 18:15:42 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 19193-10 for ; Tue, 17 Oct 2006 18:15:37 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from servata.com (ip-216-152-249-241.servata.com [216.152.249.241]) by postgresql.org (Postfix) with ESMTP id 383EA9FA38F for ; Tue, 17 Oct 2006 18:15:37 -0300 (ADT) Received: from [209.162.219.253] (helo=dogma.v10.wvs) by servata.com with esmtpa (Exim 4.50) id 1GZwHV-0006mF-Tt; Tue, 17 Oct 2006 14:15:26 -0700 Subject: Re: [GENERAL] Database users Passwords From: Jeff Davis To: Peter Eisentraut Cc: pgsql-docs@postgresql.org, DEV In-Reply-To: <200610172135.55731.peter_e@gmx.net> References: <002c01c6f1fa$652d3f60$0b01a8c0@LT003> <1161102985.31645.100.camel@dogma.v10.wvs> <200610172135.55731.peter_e@gmx.net> Content-Type: text/plain Date: Tue, 17 Oct 2006 14:15:23 -0700 Message-Id: <1161119723.31645.186.camel@dogma.v10.wvs> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 (2.0.4-7) Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.23 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, SPF_HELO_PASS X-Spam-Level: X-Archive-Number: 200610/31 X-Sequence-Number: 3752 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 From pgsql@007Marketing.com Thu Jun 4 20:28:46 2026 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id EE12A9FB3C8 for ; Tue, 17 Oct 2006 19:06:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 48422-10 for ; Tue, 17 Oct 2006 22:06:13 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from ipmail01.adl2.internode.on.net (ipmail01.adl2.internode.on.net [203.16.214.140]) by postgresql.org (Postfix) with ESMTP id 062F89FB3C5 for ; Tue, 17 Oct 2006 19:06:12 -0300 (ADT) Received: from ppp247-71.static.internode.on.net (HELO [192.168.8.200]) ([203.122.247.71]) by ipmail01.adl2.internode.on.net with ESMTP; 18 Oct 2006 07:36:10 +0930 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: AQAAACvwNEUNHA X-IronPort-AV: i="4.09,321,1157293800"; d="scan'208"; a="31002210:sNHT339446205" Message-ID: <453553C2.4070903@007Marketing.com> Date: Wed, 18 Oct 2006 07:35:54 +0930 From: Shane Ambler User-Agent: Thunderbird 1.5.0.7 (Macintosh/20060909) MIME-Version: 1.0 To: Jorge Godoy CC: DEV , pgsql-general@postgresql.org Subject: Re: Database users Passwords References: <002c01c6f1fa$652d3f60$0b01a8c0@LT003> <1161102985.31645.100.camel@dogma.v10.wvs> <45351C3E.7030200@007Marketing.com> <873b9mlqdm.fsf@gmail.com> <45353091.2080102@007Marketing.com> <878xjek8by.fsf@ieee.org> In-Reply-To: <878xjek8by.fsf@ieee.org> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.57 tagged_above=0 required=5 tests=BIZ_TLD, RCVD_IN_BL_SPAMCOP_NET, SPF_HELO_PASS X-Spam-Level: *** X-Archive-Number: 200610/859 X-Sequence-Number: 102385 Jorge Godoy wrote: > Shane Ambler 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