Thread: Location of certs -Windows 7 SSL mode?

On Wed, Jul 7, 2010 at 01:37, Lou Picciano <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 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\<username>\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\<username>\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/

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

On Wed, Jul 7, 2010 at 16:28, Lou Picciano <loupicciano@comcast.net> wrote:
> 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?)

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

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 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?)

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.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/