From LouPicciano@comcast.net Tue Jul 6 23:37:56 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id D0DC4632B87 for ; Tue, 6 Jul 2010 20:37:56 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 24038-04 for ; Tue, 6 Jul 2010 23:37:47 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from qmta15.westchester.pa.mail.comcast.net (qmta15.westchester.pa.mail.comcast.net [76.96.59.228]) by mail.postgresql.org (Postfix) with ESMTP id 03A0D63296A for ; Tue, 6 Jul 2010 20:37:48 -0300 (ADT) Received: from omta17.westchester.pa.mail.comcast.net ([76.96.62.89]) by qmta15.westchester.pa.mail.comcast.net with comcast id emUJ1e0051vXlb85Fndoyb; Tue, 06 Jul 2010 23:37:48 +0000 Received: from imac.42.168.192.in-addr.arpa ([76.117.184.231]) by omta17.westchester.pa.mail.comcast.net with comcast id endn1e00G4zyFz83dndnmu; Tue, 06 Jul 2010 23:37:48 +0000 From: Lou Picciano X-Apple-Mail-Stationery: 45655888-E52F-42B7-A29F-41D13C34A678 Content-Type: multipart/alternative; boundary=Apple-Mail-20--495444454 Date: Tue, 6 Jul 2010 19:37:46 -0400 Subject: Location of certs -Windows 7 SSL mode? To: pgsql-testers@postgresql.org Message-Id: <323E0FB3-767F-457F-A041-908B0A805C68@comcast.net> Mime-Version: 1.0 (Apple Message framework v1081) X-Mailer: Apple Mail (2.1081) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.01 tagged_above=-10 required=5 tests=BAYES_20=-0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201007/1 X-Sequence-Number: 143 --Apple-Mail-20--495444454 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii [TEST REPORT] [Release]: 9.0Beta2 [Test Type]: Testing connection - pgAdmin - using certificate on Windows = 7 - Specifically, what is correct default placement of certs? [Test]: Attempt to connect - SSL required or better - using standard = AppData/Roaming location for certs, key. [Platform]: Windows 7 Ultimate 64-bit - attempting connection using = pgAdminIII as a test of SSL connectivity. [Parameters]: First, attempted connection, with SSL required and NO = certs, to determine default location based on error message! [Failure]: Yes [Results]: Installation of a pre-packaged PostgreSQL, using an installer = (in this case a b1 packaged by EnterpriseDB), did create a postgresql = folder in the expected location: = C:\Users\\AppData\Roaming\postgresql. After placement of = valid certs and key in this folder, cannot connect in any SSL mode. Interestingly, the error message reverses the pathname delimiter, = halfway through the message, from Windows-standard to UNIX-standard (is = this relevant?)... "Error connecting to the server: could not open certificate file = "C:\Users\\AppData\roaming/postgresql/potgresql.crt": No such = file or directory. [Comments]: Again, perhaps the joys of the Windows 'security' model?= --Apple-Mail-20--495444454 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
[TEST REPORT]
[Release]: 9.0Beta2
[Test Type]: Testing connection - = pgAdmin - using certificate on Windows 7 - Specifically, what is = correct default placement of certs?
[Test]: Attempt to connect - SSL = required or better - using standard AppData/Roaming location for certs, = key.
[Parameters]: First, = attempted connection, with SSL required and NO certs, to determine = default location based on error message!
[Failure]: Yes
Interestingly, the error message reverses the pathname = delimiter, halfway through the message, from Windows-standard to = UNIX-standard (is this relevant?)...
"Error connecting to the server: could not open = certificate file = "C:\Users\<username>\AppData\roaming/postgresql/potgresql.crt": No = such file or directory.
[Comments]: Again, perhaps the joys of the Windows = 'security' model?
= --Apple-Mail-20--495444454-- From magnus@hagander.net Wed Jul 7 08:50:20 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id B1ADE632A69 for ; Wed, 7 Jul 2010 05:50:20 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 09139-01; Wed, 7 Jul 2010 08:50:09 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f46.google.com (mail-bw0-f46.google.com [209.85.214.46]) by mail.postgresql.org (Postfix) with ESMTP id BFFE063296A; Wed, 7 Jul 2010 05:50:09 -0300 (ADT) Received: by bwz1 with SMTP id 1so4038979bwz.19 for ; Wed, 07 Jul 2010 01:50:04 -0700 (PDT) MIME-Version: 1.0 Received: by 10.204.85.89 with SMTP id n25mr4753760bkl.105.1278492603949; Wed, 07 Jul 2010 01:50:03 -0700 (PDT) Received: by 10.204.61.208 with HTTP; Wed, 7 Jul 2010 01:50:03 -0700 (PDT) In-Reply-To: <323E0FB3-767F-457F-A041-908B0A805C68@comcast.net> References: <323E0FB3-767F-457F-A041-908B0A805C68@comcast.net> Date: Wed, 7 Jul 2010 10:50:03 +0200 Message-ID: Subject: Re: [TESTERS] Location of certs -Windows 7 SSL mode? From: Magnus Hagander To: Lou Picciano Cc: pgsql-testers , pgsql-bugs Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201007/34 X-Sequence-Number: 27378 On Wed, Jul 7, 2010 at 01:37, Lou Picciano wrote: > [TEST REPORT] > [Release]:=A09.0Beta2 > [Test Type]:=A0Testing=A0connection - pgAdmin - using=A0certificate on Wi= ndows 7 - > Specifically, what is correct default placement of certs? > [Test]:=A0Attempt to connect - SSL required or better - using standard > AppData/Roaming location for certs, key. > [Platform]:=A0Windows 7 Ultimate 64-bit - attempting connection using > pgAdminIII as a test of SSL connectivity. > [Parameters]:=A0First, attempted connection, with SSL required and NO cer= ts, > to determine default location based on error message! > [Failure]:=A0Yes > [Results]:=A0Installation of a pre-packaged PostgreSQL, using an installe= r (in > this case a b1 packaged by EnterpriseDB), did create a postgresql folder = in > the expected location:=A0C:\Users\\AppData\Roaming\postgresql. > =A0After placement of valid certs and key in this folder, cannot connect = in > any SSL mode. > Interestingly, the error message reverses the pathname delimiter, halfway > through the message, from Windows-standard to UNIX-standard (is this > relevant?)... > "Error connecting to the server: could not open certificate file > "C:\Users\\AppData\roaming/postgresql/potgresql.crt": No such f= ile > or directory. The reversed slashes are pretty normal, and should work just fine. What is your connection string? Are you specifying the cert file there as w= ell? Does the file actually exist in that directory? Does the permissions on the file and all the directories leading up to it let you read the file? The code itself should actually "never" do this - it specifically checks if the file doesn't exist, and should *not* show that error.. It should fail much later, when the server actually requests the cert.. And the API documentation for stat() on windows says it'll always return ENOENT on failure. Seems to me Windows is confused, but I'm not entirely sure how. Can you try specifying an explicit file say directly in c:\, just to see if that works? BTW, if you post bug reports to -bugs, it'll make a lot more people see the= m. --=20 Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ From loupicciano@comcast.net Wed Jul 7 14:28:56 2010 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id A5A766336EE for ; Wed, 7 Jul 2010 11:28:56 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 69208-09 for ; Wed, 7 Jul 2010 14:28:48 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from qmta13.westchester.pa.mail.comcast.net (qmta13.westchester.pa.mail.comcast.net [76.96.59.243]) by mail.postgresql.org (Postfix) with ESMTP id 1C46763359E for ; Wed, 7 Jul 2010 11:28:47 -0300 (ADT) Received: from omta23.westchester.pa.mail.comcast.net ([76.96.62.74]) by qmta13.westchester.pa.mail.comcast.net with comcast id eytB1e0091c6gX85D2UnhZ; Wed, 07 Jul 2010 14:28:47 +0000 Received: from sz0093.wc.mail.comcast.net ([76.96.58.151]) by omta23.westchester.pa.mail.comcast.net with comcast id f2Um1e00Q3FmDic3j2Umwp; Wed, 07 Jul 2010 14:28:47 +0000 Date: Wed, 7 Jul 2010 14:28:46 +0000 (UTC) From: Lou Picciano To: Magnus Hagander Cc: pgsql-testers , pgsql-bugs Message-ID: <1808543951.199438.1278512926661.JavaMail.root@sz0093a.westchester.pa.mail.comcast.net> In-Reply-To: <1727387452.198812.1278512357403.JavaMail.root@sz0093a.westchester.pa.mail.comcast.net> Subject: Re: [TESTERS] Location of certs -Windows 7 SSL mode? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_199437_765213341.1278512926660" X-Originating-IP: [76.117.184.231] X-Mailer: Zimbra 6.0.5_GA_2364.RHEL5_64 (ZimbraWebClient - SAF3 (Mac)/6.0.5_GA_2324.RHEL5_64) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201007/35 X-Sequence-Number: 27379 ------=_Part_199437_765213341.1278512926660 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit Magnus, Tks for your response. > What is your connection string? Are you specifying the cert file there as well? Well, no. Specifically, the exercise was to determine default locations of certs on Windows 7, as inferred from - the expected - error message from the PG client. In this case, the client was pgAdmin. (Is pgAdmin not a valid 'default' test?) > Does the file actually exist in that directory? Does the permissions on the file and all the directories leading up to it let you read the file? Yes, exists; and all files wer created by the 'administrator' user - the only user on the system, with the system having been installed fresh specifically to effect this test. We're trying to develop a 'recipe' for our Windows 7 users. > The code itself should actually "never" do this - it specifically checks if the file doesn't exist, and should *not* show that error.. > It should fail much later, when the server actually requests the cert.. Oh? I didn't realize this. In fact, past experience has been consistent with my findings; that pqlib will hiccup quickly if it cannot find a cert, and that this error message would appear before ever presenting that (non-)cert to the server. If a cert is found, on the other hand, error messages would be different, assuming it's an invalid cert in the context of pg_hba.cconf. > Can you try specifying an explicit file say directly in c:\, just to see if that works? Presumably you're proposing an environment variable approach? Sure, please propose an exact test, and we'll perform. (No one else here is using certs under Windows 7?) (BTW, specifically did not post to bugs list as, since we are testing betas, I don't know whether I'm uncovering bugs - or 'new behaviors'! Or, whether I'm uncovering a 'bug' in, uhh... 'userspace'.) Thanks again, Lou Picciano ----- Original Message ----- From: "Magnus Hagander" To: "Lou Picciano" Cc: "pgsql-testers" , "pgsql-bugs" Sent: Wednesday, July 7, 2010 4:50:03 AM Subject: Re: [TESTERS] Location of certs -Windows 7 SSL mode? On Wed, Jul 7, 2010 at 01:37, Lou Picciano wrote: > [TEST REPORT] > [Release]: 9.0Beta2 > [Test Type]: Testing connection - pgAdmin - using certificate on Windows 7 - > Specifically, what is correct default placement of certs? > [Test]: Attempt to connect - SSL required or better - using standard > AppData/Roaming location for certs, key. > [Platform]: Windows 7 Ultimate 64-bit - attempting connection using > pgAdminIII as a test of SSL connectivity. > [Parameters]: First, attempted connection, with SSL required and NO certs, > to determine default location based on error message! > [Failure]: Yes > [Results]: Installation of a pre-packaged PostgreSQL, using an installer (in > this case a b1 packaged by EnterpriseDB), did create a postgresql folder in > the expected location: C:\Users\\AppData\Roaming\postgresql. > After placement of valid certs and key in this folder, cannot connect in > any SSL mode. > Interestingly, the error message reverses the pathname delimiter, halfway > through the message, from Windows-standard to UNIX-standard (is this > relevant?)... > "Error connecting to the server: could not open certificate file > "C:\Users\\AppData\roaming/postgresql/potgresql.crt": No such file > or directory. The reversed slashes are pretty normal, and should work just fine. What is your connection string? Are you specifying the cert file there as well? Does the file actually exist in that directory? Does the permissions on the file and all the directories leading up to it let you read the file? The code itself should actually "never" do this - it specifically checks if the file doesn't exist, and should *not* show that error.. It should fail much later, when the server actually requests the cert.. And the API documentation for stat() on windows says it'll always return ENOENT on failure. Seems to me Windows is confused, but I'm not entirely sure how. Can you try specifying an explicit file say directly in c:\, just to see if that works? BTW, if you post bug reports to -bugs, it'll make a lot more people see them. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ ------=_Part_199437_765213341.1278512926660 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable <= div style=3D'font-family: Verdana; font-size: 12pt; color: #000000'>Magnus,=  

Tks for your response.

<= meta charset=3D"utf-8">> What is your connection string? Are you specify= ing the cert file there as well?

Well, no.  S= pecifically, the exercise was to determine default locations of certs on Wi= ndows 7, as inferred from - the expected - error message from the PG client= .  In this case, the client was pgAdmin.  (Is pgAdmin not a valid= 'default' test?)

> Doe= s the file actually exist in that directory? Does the permissions on t= he file and all the directories leading up to it let you read the file= ?

Yes, exists; and all files wer created by the 'a= dministrator' user - the only user on the system, with the system ha= ving been installed fresh specifically to effect this test.  We're try= ing to develop a 'recipe' for our Windows 7 users.

> The code itself should actually "never" do th= is - it specifically checks if the file doesn't exist, and should *not= * show that error..
> It should fail much later, when the server actu= ally requests the cert..

Oh?  I didn't r= ealize this.  In fact, past experience has been consistent with my fin= dings; that pqlib will hiccup quickly if it cannot find a cert, and that th= is error message would appear before ever presenting that (non-)cert to the= server.  If a cert is found, on the other hand, error messages would = be different, assuming it's an invalid cert in the context of pg_hba.cconf.=

> Can you try specifyi= ng an explicit file say directly in c:\, just to see if that works?

Presumably you're proposing an environment variable = approach?  Sure, please propose an exact test, and we'll perform. &nbs= p;(No one else here is using certs under Windows 7?)

(BTW, specifically did not post to bugs list as, since we are testing be= tas, I don't know whether I'm uncovering bugs - or 'new behaviors'!  O= r, whether I'm uncovering a 'bug' in, uhh... 'userspace'.)

Thanks again, Lou Picciano

----- Original Message --= ---
From: "Magnus Hagander" <magnus@hagander.net>
To: "Lou Picc= iano" <LouPicciano@comcast.net>
Cc: "pgsql-testers" <pgsql-test= ers@postgresql.org>, "pgsql-bugs" <pgsql-bugs@postgresql.org>
S= ent: Wednesday, July 7, 2010 4:50:03 AM
Subject: Re: [TESTERS] Location = of certs -Windows 7 SSL mode?

On Wed, Jul 7, 2010 at 01:37, Lou Picc= iano <LouPicciano@comcast.net> wrote:
> [TEST REPORT]
> [= Release]: 9.0Beta2
> [Test Type]: Testing connection -= pgAdmin - using certificate on Windows 7 -
> Specifically, what= is correct default placement of certs?
> [Test]: Attempt to con= nect - SSL required or better - using standard
> AppData/Roaming loca= tion for certs, key.
> [Platform]: Windows 7 Ultimate 64-bit - a= ttempting connection using
> pgAdminIII as a test of SSL connectivity= .
> [Parameters]: First, attempted connection, with SSL required= and NO certs,
> to determine default location based on error message= !
> [Failure]: Yes
> [Results]: Installation of a pre= -packaged PostgreSQL, using an installer (in
> this case a b1 package= d by EnterpriseDB), did create a postgresql folder in
> the expected = location: C:\Users\<username>\AppData\Roaming\postgresql.
>= ;  After placement of valid certs and key in this folder, cannot conne= ct in
> any SSL mode.
> Interestingly, the error message revers= es the pathname delimiter, halfway
> through the message, from Window= s-standard to UNIX-standard (is this
> relevant?)...
> "Error c= onnecting to the server: could not open certificate file
> "C:\Users\= <username>\AppData\roaming/postgresql/potgresql.crt": No such file> or directory.

The reversed slashes are pretty normal, and shou= ld work just fine.

What is your connection string? Are you specifyin= g the cert file there as well?

Does the file actually exist in that = directory? Does the permissions
on the file and all the directories lead= ing up to it let you read the
file?

The code itself should actual= ly "never" do this - it specifically
checks if the file doesn't exist, a= nd should *not* show that error..
It should fail much later, when the se= rver actually requests the
cert.. And the API documentation for stat() o= n windows says it'll
always return ENOENT on failure. Seems to me Window= s is confused, but
I'm not entirely sure how.

Can you try specify= ing an explicit file say directly in c:\, just to
see if that works?
=
BTW, if you post bug reports to -bugs, it'll make a lot more people see= them.

--
 Magnus Hagander
 Me: http://www.hagander= .net/
 Work: http://www.redpill-linpro.com/
<= /html> ------=_Part_199437_765213341.1278512926660-- From josh@agliodbs.com Wed Jul 7 17:54:50 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 0A7F8632E8E for ; Wed, 7 Jul 2010 14:54:50 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 04126-03; Wed, 7 Jul 2010 17:54:41 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from glacier.frostconsultingllc.com (glacier.frostconsultingllc.com [69.36.227.170]) by mail.postgresql.org (Postfix) with ESMTP id DE63D63258B; Wed, 7 Jul 2010 14:54:41 -0300 (ADT) Received: from dsl081-245-111.sfo1.dsl.speakeasy.net ([64.81.245.111] helo=Sidney-Stratton.local) by glacier.frostconsultingllc.com with esmtpsa (TLSv1:CAMELLIA256-SHA:256) (Exim 4.69) (envelope-from ) id 1OWYpN-0006PQ-So; Wed, 07 Jul 2010 10:54:37 -0700 Message-ID: <4C34BF59.7070700@agliodbs.com> Date: Wed, 07 Jul 2010 10:54:33 -0700 From: Josh Berkus User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:1.9.1b3pre) Gecko/20090223 Thunderbird/3.0b2 MIME-Version: 1.0 To: Magnus Hagander CC: Lou Picciano , pgsql-testers@postgresql.org, pgsql-bugs@postgresql.org Subject: Re: [TESTERS] Location of certs -Windows 7 SSL mode? References: <323E0FB3-767F-457F-A041-908B0A805C68@comcast.net> In-Reply-To: Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201007/38 X-Sequence-Number: 27382 On 7/7/10 1:50 AM, Magnus Hagander wrote: > BTW, if you post bug reports to -bugs, it'll make a lot more people see them. Sure, we just want to verify that it *is* a possible bug (and not pilot error) first. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com From craig@postnewspapers.com.au Thu Jul 8 01:10:47 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 153A063161C for ; Wed, 7 Jul 2010 22:10:47 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 38173-06 for ; Thu, 8 Jul 2010 01:10:38 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from www.postnewspapers.com.au (www.postnewspapers.com.au [202.4.227.11]) by mail.postgresql.org (Postfix) with ESMTP id 84613632D8E for ; Wed, 7 Jul 2010 22:10:39 -0300 (ADT) Received: from mail.postnewspapers.com.au (202-89-185-120.static.dsl.amnet.net.au [202.89.185.120]) by www.postnewspapers.com.au (Postfix) with ESMTP id C84BF7924B for ; Thu, 8 Jul 2010 09:10:30 +0800 (WST) Received: from localhost (localhost [127.0.0.1]) by mail.postnewspapers.com.au (Postfix) with ESMTP id 4D961AEF26 for ; Thu, 8 Jul 2010 09:08:58 +0800 (WST) X-Virus-Scanned: Debian amavisd-new at postnewspapers.com.au Received: from mail.postnewspapers.com.au ([127.0.0.1]) by localhost (mail.postnewspapers.com.au [127.0.0.1]) (amavisd-new, port 10024) with LMTP id IHoThpzbERtd for ; Thu, 8 Jul 2010 09:08:53 +0800 (WST) Received: from [10.1.1.6] (124-168-255-56.dyn.iinet.net.au [124.168.255.56]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client CN "Craig Ringer", Issuer "POST Certificate Authority" (verified OK)) by mail.postnewspapers.com.au (Postfix) with ESMTPSA id F044AAE681 for ; Thu, 8 Jul 2010 09:08:52 +0800 (WST) Message-ID: <4C352585.40206@postnewspapers.com.au> Date: Thu, 08 Jul 2010 09:10:29 +0800 From: Craig Ringer User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.9) Gecko/20100423 Lightning/1.0b1 Thunderbird/3.0.4 MIME-Version: 1.0 To: pgsql-bugs@postgresql.org Subject: Re: [TESTERS] Location of certs -Windows 7 SSL mode? References: <1808543951.199438.1278512926661.JavaMail.root@sz0093a.westchester.pa.mail.comcast.net> In-Reply-To: <1808543951.199438.1278512926661.JavaMail.root@sz0093a.westchester.pa.mail.comcast.net> X-Enigmail-Version: 1.0.1 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.79 tagged_above=-5 required=5 tests=BAYES_50=0.8, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201007/39 X-Sequence-Number: 27383 On 07/07/10 22:28, Lou Picciano wrote: > Well, no. Specifically, the exercise was to determine default locations of certs on Windows 7, as inferred from - the expected - error message from the PG client. In this case, the client was pgAdmin. (Is pgAdmin not a valid 'default' test?) I'm coming in half-way into this conversation, so forgive any confusion, but are you referring to server certs (that live in the datadir) or client certificates to authenticate a client to the server? If server certs, the certs live in the same place on Windows as every other platform: in the data dir. IIRC on Windows client certificates live in %APPDATA%\.postgresql\ I'll verify that shortly as I have a win7 machine here now. > (No one else here is using certs under Windows 7?) I use client certificates on Windows, but only via a Java app with JDBC, so I haven't been too worried about libpq's client cert handling. -- Craig Ringer From magnus@hagander.net Thu Jul 8 10:31:44 2010 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id E5E0163280F for ; Thu, 8 Jul 2010 07:31:44 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 15611-08 for ; Thu, 8 Jul 2010 10:31:37 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f46.google.com (mail-bw0-f46.google.com [209.85.214.46]) by mail.postgresql.org (Postfix) with ESMTP id E4AC86321BB for ; Thu, 8 Jul 2010 07:31:36 -0300 (ADT) Received: by bwz1 with SMTP id 1so334747bwz.19 for ; Thu, 08 Jul 2010 03:31:34 -0700 (PDT) MIME-Version: 1.0 Received: by 10.204.127.75 with SMTP id f11mr6092432bks.85.1278585090897; Thu, 08 Jul 2010 03:31:30 -0700 (PDT) Received: by 10.204.61.208 with HTTP; Thu, 8 Jul 2010 03:31:30 -0700 (PDT) In-Reply-To: <4C352585.40206@postnewspapers.com.au> References: <1808543951.199438.1278512926661.JavaMail.root@sz0093a.westchester.pa.mail.comcast.net> <4C352585.40206@postnewspapers.com.au> Date: Thu, 8 Jul 2010 12:31:30 +0200 Message-ID: Subject: Re: [TESTERS] Location of certs -Windows 7 SSL mode? From: Magnus Hagander To: Craig Ringer Cc: pgsql-bugs Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201007/40 X-Sequence-Number: 27384 On Thu, Jul 8, 2010 at 03:10, Craig Ringer wr= ote: > On 07/07/10 22:28, Lou Picciano wrote: > >> Well, no. Specifically, the exercise was to determine default locations = of certs on Windows 7, as inferred from - the expected - error message from= the PG client. In this case, the client was pgAdmin. (Is pgAdmin not a val= id 'default' test?) > > I'm coming in half-way into this conversation, so forgive any confusion, > but are you referring to server certs (that live in the datadir) or > client certificates to authenticate a client to the server? > > If server certs, the certs live in the same place on Windows as every > other platform: in the data dir. > > IIRC on Windows client certificates live in > > =A0%APPDATA%\.postgresql\ That's a typo, it should be %APPDATA%\postgresql\ (no leading period). >> (No one else here is using certs under Windows 7?) > > I use client certificates on Windows, but only via a Java app with JDBC, > so I haven't been too worried about libpq's client cert handling. It'd be great if you can check that it works in your environment, since you clearly have a working server-side and client-in-java env. If it doesn't work for you, that's a whole lot of thinsg we can rule out as the culprits then :-) --=20 Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ From magnus@hagander.net Thu Jul 8 10:34:36 2010 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 9CC5B633172 for ; Thu, 8 Jul 2010 07:34:36 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 63168-02; Thu, 8 Jul 2010 10:34:28 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f46.google.com (mail-bw0-f46.google.com [209.85.214.46]) by mail.postgresql.org (Postfix) with ESMTP id 96FCF6330D7; Thu, 8 Jul 2010 07:34:27 -0300 (ADT) Received: by bwz1 with SMTP id 1so336200bwz.19 for ; Thu, 08 Jul 2010 03:34:25 -0700 (PDT) MIME-Version: 1.0 Received: by 10.204.63.72 with SMTP id a8mr6089688bki.127.1278585264954; Thu, 08 Jul 2010 03:34:24 -0700 (PDT) Received: by 10.204.61.208 with HTTP; Thu, 8 Jul 2010 03:34:24 -0700 (PDT) In-Reply-To: <1808543951.199438.1278512926661.JavaMail.root@sz0093a.westchester.pa.mail.comcast.net> References: <1727387452.198812.1278512357403.JavaMail.root@sz0093a.westchester.pa.mail.comcast.net> <1808543951.199438.1278512926661.JavaMail.root@sz0093a.westchester.pa.mail.comcast.net> Date: Thu, 8 Jul 2010 12:34:24 +0200 Message-ID: Subject: Re: [TESTERS] Location of certs -Windows 7 SSL mode? From: Magnus Hagander To: Lou Picciano Cc: pgsql-testers , pgsql-bugs Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201007/41 X-Sequence-Number: 27385 On Wed, Jul 7, 2010 at 16:28, Lou Picciano wrote: > Magnus, > Tks for your response. >> What is your connection string? Are you specifying the cert file there a= s >> well? > Well, no. =A0Specifically, the exercise was to determine default location= s of > certs on Windows 7, as inferred from - the expected - error message from = the > PG client. =A0In this case, the client was pgAdmin. =A0(Is pgAdmin not a = valid > 'default' test?) Ah, ok. pgAdmin should be a valid test. Though in general, it's always appreciated if you can try to reproduce the issue using psql. It *could* be a bug in pgAdmin - in which case that also has to be fixed of course, but it's a good way to narrow down where it is. >> The code itself should actually "never" do this - it specifically=A0chec= ks >> if the file doesn't exist, and should *not* show that error.. >> It should fail much later, when the server actually requests the=A0cert.= . > Oh? =A0I didn't realize this. =A0In fact, past experience has been consis= tent > with my findings; that pqlib will hiccup quickly if it cannot find a cert= , > and that this error message would appear before ever presenting that > (non-)cert to the server. =A0If a cert is found, on the other hand, error > messages would be different, assuming it's an invalid cert in the context= of > pg_hba.cconf. If there is no cert, and the server doesn't request one, it's not an error, and shouldn't be. Non-existant cert should only be an error if the server requires one, and that should AFAICS give a different error message. >> Can you try specifying an explicit file say directly in c:\, just to=A0s= ee >> if that works? > Presumably you're proposing an environment variable approach? =A0Sure, pl= ease > propose an exact test, and we'll perform. =A0(No one else here is using c= erts > under Windows 7?) Either environment variable or connection string parameter. See http://www.postgresql.org/docs/9.0/static/libpq-connect.html, for the parameters sslcert and sslkey. Or http://www.postgresql.org/docs/9.0/static/libpq-envars.html for the respective environment vars. --=20 Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ From craig@postnewspapers.com.au Fri Jul 9 02:58:44 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 86DDA632668 for ; Thu, 8 Jul 2010 23:58:44 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 74177-05 for ; Fri, 9 Jul 2010 02:58:35 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from www.postnewspapers.com.au (www.postnewspapers.com.au [202.4.227.11]) by mail.postgresql.org (Postfix) with ESMTP id 2136C6325BF for ; Thu, 8 Jul 2010 23:58:37 -0300 (ADT) Received: from mail.postnewspapers.com.au (202-89-185-120.static.dsl.amnet.net.au [202.89.185.120]) by www.postnewspapers.com.au (Postfix) with ESMTP id 3D0877924C; Fri, 9 Jul 2010 10:58:27 +0800 (WST) Received: from localhost (localhost [127.0.0.1]) by mail.postnewspapers.com.au (Postfix) with ESMTP id 31ACFAF3CF; Fri, 9 Jul 2010 10:56:52 +0800 (WST) X-Virus-Scanned: Debian amavisd-new at postnewspapers.com.au Received: from mail.postnewspapers.com.au ([127.0.0.1]) by localhost (mail.postnewspapers.com.au [127.0.0.1]) (amavisd-new, port 10024) with LMTP id Ke7ZN-IJXlp3; Fri, 9 Jul 2010 10:56:46 +0800 (WST) Received: from [10.1.1.3] (124-168-255-56.dyn.iinet.net.au [124.168.255.56]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client CN "Craig Ringer", Issuer "POST Certificate Authority" (verified OK)) by mail.postnewspapers.com.au (Postfix) with ESMTPSA id AD3D4AF1D2; Fri, 9 Jul 2010 10:56:46 +0800 (WST) Message-ID: <4C369051.3030308@postnewspapers.com.au> Date: Fri, 09 Jul 2010 10:58:25 +0800 From: Craig Ringer User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-GB; rv:1.9.1.10) Gecko/20100512 Thunderbird/3.0.5 MIME-Version: 1.0 To: Magnus Hagander CC: pgsql-bugs Subject: Re: [TESTERS] Location of certs -Windows 7 SSL mode? References: <1808543951.199438.1278512926661.JavaMail.root@sz0093a.westchester.pa.mail.comcast.net> <4C352585.40206@postnewspapers.com.au> In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201007/51 X-Sequence-Number: 27395 On 8/07/2010 6:31 PM, Magnus Hagander wrote: > On Thu, Jul 8, 2010 at 03:10, Craig Ringer wrote: >> IIRC on Windows client certificates live in >> >> %APPDATA%\.postgresql\ > > That's a typo, it should be %APPDATA%\postgresql\ (no leading period). You're quite right. Sorry - I thought it used .postgresql as it does on *nix, but a quick test confirms that it's: %APPDATA%\postgresql\postgresql.crt %APPDATA%\postgresql\postgresql.key >>> (No one else here is using certs under Windows 7?) >> >> I use client certificates on Windows, but only via a Java app with JDBC, >> so I haven't been too worried about libpq's client cert handling. > > It'd be great if you can check that it works in your environment, > since you clearly have a working server-side and client-in-java env. > If it doesn't work for you, that's a whole lot of thinsg we can rule > out as the culprits then :-) I just set up a test, and it works fine with my win7 test box connecting to a Linux server that requires a client cert to permit connections. -- Craig Ringer From magnus@hagander.net Fri Jul 9 17:18:09 2010 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 933156327D0 for ; Fri, 9 Jul 2010 14:18:09 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 22826-02 for ; Fri, 9 Jul 2010 17:18:01 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f46.google.com (mail-bw0-f46.google.com [209.85.214.46]) by mail.postgresql.org (Postfix) with ESMTP id 4C877632510 for ; Fri, 9 Jul 2010 14:18:01 -0300 (ADT) Received: by bwz1 with SMTP id 1so1257858bwz.19 for ; Fri, 09 Jul 2010 10:17:59 -0700 (PDT) MIME-Version: 1.0 Received: by 10.204.141.16 with SMTP id k16mr7658503bku.177.1278695879683; Fri, 09 Jul 2010 10:17:59 -0700 (PDT) Received: by 10.204.61.208 with HTTP; Fri, 9 Jul 2010 10:17:59 -0700 (PDT) In-Reply-To: <4C369051.3030308@postnewspapers.com.au> References: <1808543951.199438.1278512926661.JavaMail.root@sz0093a.westchester.pa.mail.comcast.net> <4C352585.40206@postnewspapers.com.au> <4C369051.3030308@postnewspapers.com.au> Date: Fri, 9 Jul 2010 19:17:59 +0200 Message-ID: Subject: Re: [TESTERS] Location of certs -Windows 7 SSL mode? From: Magnus Hagander To: Craig Ringer Cc: pgsql-bugs Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201007/63 X-Sequence-Number: 27407 On Fri, Jul 9, 2010 at 04:58, Craig Ringer wr= ote: > On 8/07/2010 6:31 PM, Magnus Hagander wrote: >> >> On Thu, Jul 8, 2010 at 03:10, Craig Ringer >> =A0wrote: >>> >>> IIRC on Windows client certificates live in >>> >>> =A0%APPDATA%\.postgresql\ >> >> That's a typo, it should be %APPDATA%\postgresql\ (no leading period). > > You're quite right. Sorry - I thought it used .postgresql as it does on > *nix, but a quick test confirms that it's: > > =A0%APPDATA%\postgresql\postgresql.crt > =A0%APPDATA%\postgresql\postgresql.key > >>>> (No one else here is using certs under Windows 7?) >>> >>> I use client certificates on Windows, but only via a Java app with JDBC= , >>> so I haven't been too worried about libpq's client cert handling. >> >> It'd be great if you can check that it works in your environment, >> since you clearly have a working server-side and client-in-java env. >> If it doesn't work for you, that's a whole lot of thinsg we can rule >> out as the culprits then :-) > > I just set up a test, and it works fine with my win7 test box connecting = to > a Linux server that requires a client cert to permit connections. Ok, that's good. Then we k now it's not a generic issue on Win7, but must be something specific to the OP's problem. Did you also, or could you also, test the connection parameters and/or environment variables that tells libpq to look for the certificate in a different location? --=20 Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/