[gdal-dev] Can I use ogr2ogr to postgresql with security? Yes I can!

Wed Feb 2 10:13:23 EST 2011

Success!  So here are some notes on this in hopes it may help some others trying to do the same.

Assuming you don’t want to maintain you’re own builds and you just want to use the windows binaries of GDAl for tools like ogr2ogr, etc. enabling SSL postgres should be as simple as dropping in a replacement for libpq.dll.  But of course it is not.

Dependencies, dependencies dependences

You can get a tool like dependency walker an/or just painfully sit and watch the errors pop up as you bring in the other needed dlls.  After you bring in the other dlls then things start to work and you can use sslmode=require in your postgress connection string to create a secure connection.  (this assumes you set up your postfgres server, you’ll have to rtfm on that(, but that’s not too bad,  on unix just make sure your permissions and ownership on server.key are correct as this is not well documented).

Also you may want to be careful about which version of libpq.dll you bring in.  In my case I was successful with libpq.dll version 8.4.7.1127 obtained from the windows binary bundle from the postgres web site.  I can’t confirm if the 9.x version work in this scenario.  (This where I first ran into trouble, but I need to do more testing on this)

Finally I had to add the following dependant dlls; comerr32.dll, gssapi32.dll, k5sprt32.dll, krb5_32.dll, libiconv-2.dll, libintl-8.dll along with libpq.dll and then all was good.

Mike

Mike Axelrod, Software Engineer
Pictometry International Corp.
Suite A, 100 Town Centre Drive, Rochester, NY 14623
Phone: 585-775-7711
E-mail: mike.axelrod at pictometry.com<mailto:mike.axelrod at pictometry.com> Web: http://www.Pictometry.com/<http://www.pictometry.com/>

________________________________
From: Chaitanya kumar CH [mailto:chaitanya.ch at gmail.com]
Sent: Thursday, January 27, 2011 4:12 PM
To: Mike Axelrod
Cc: gdal-dev at lists.osgeo.org; szekerest at gmail.com
Subject: Re: [gdal-dev] Can I use ogr2ogr to postgresql with security?

Mike,

To use SSL mode with OGR, your pqlib should be built with SSL support.
On Fri, Jan 28, 2011 at 2:09 AM, Mike Axelrod <mike.axelrod at pictometry.com<mailto:mike.axelrod at pictometry.com>> wrote:
So it seems the build I’m using may not support ssl, I run ogrinfo with sslmode=prefer I connect ok, but when I set sslmode=require I get this error:

---------------------------
Details...
---------------------------
call to ogrinfo failed: ERROR 1: PQconnectdb failed.
sslmode value "require" invalid when SSL support is not compiled in

________________________________
From: gdal-dev-bounces at lists.osgeo.org<mailto:gdal-dev-bounces at lists.osgeo.org> [mailto:gdal-dev-bounces at lists.osgeo.org<mailto:gdal-dev-bounces at lists.osgeo.org>] On Behalf Of Mike Axelrod
Sent: Thursday, January 27, 2011 3:21 PM
To: Chaitanya kumar CH

Cc: gdal-dev at lists.osgeo.org<mailto:gdal-dev at lists.osgeo.org>
Subject: RE: [gdal-dev] Can I use ogr2ogr to postgresql with security?

Thank you, I’ll be trying that out as soon as I get our postgresql server configured with ssl.  Do you know if the postgresql public key is required on the client side?  I see references to a ~/.postgresql/postgresql.key being available to the client. But I’m not clear if this is required or an option.

BTW I’m currently using the win32 SDK version of ogr2ogr that is distributed here => http://vbkto.dyndns.org/sdk/, I’m hoping these builds support SSL.  Can anybody confirm?

Mike

________________________________
From: Chaitanya kumar CH [mailto:chaitanya.ch at gmail.com<mailto:chaitanya.ch at gmail.com>]
Sent: Thursday, January 27, 2011 2:56 PM
To: Mike Axelrod
Cc: gdal-dev at lists.osgeo.org<mailto:gdal-dev at lists.osgeo.org>
Subject: Re: [gdal-dev] Can I use ogr2ogr to postgresql with security?

Mike,

OGR's postgresql/postgis driver makes the connection using PQconnectdb() method from the libpq library.
You can set the option 'sslmode' to 'require', 'verify-ca' or 'verify-full' for a secure connection. Look for the documentation of PQconnectdb() for further details.
On Fri, Jan 28, 2011 at 12:13 AM, Mike Axelrod <mike.axelrod at pictometry.com<mailto:mike.axelrod at pictometry.com>> wrote:
Does ogr2ogr (and ogrinfo) natively support secure connections to postgresql?

I need to run ogrinfo and ogr2ogr where the target is a postgresql server elsewhere on the network (in a different domain) and secure the communication.

Mike

Mike Axelrod, Software Engineer
Pictometry International Corp.
Suite A, 100 Town Centre Drive, Rochester, NY 14623
Phone: 585-775-7711
E-mail: mike.axelrod at pictometry.com<mailto:mike.axelrod at pictometry.com> Web: http://www.Pictometry.com/<http://www.pictometry.com/>

NOTICE: This message is covered by the Electronic Communications Privacy Act, Title 18, United States Code, Sections 2510-2521. This e-mail and any attached files are the exclusive property of Pictometry International Corp., are deemed privileged and confidential, and are intended solely for the use of the individual(s) or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or believe that you have received this message in error, please delete this e-mail and any attachments and notify the sender immediately. Any other use, re-creation, dissemination, forwarding or copying of this e-mail is strictly prohibited and may be unlawful.

_______________________________________________
gdal-dev mailing list
gdal-dev at lists.osgeo.org<mailto:gdal-dev at lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/gdal-dev

--
Best regards,
Chaitanya kumar CH.
/tʃaɪθənjə/ /kʊmɑr/
+91-9494447584
17.2416N 80.1426E
NOTICE: This message is covered by the Electronic Communications Privacy Act, Title 18, United States Code, Sections 2510-2521. This e-mail and any attached files are the exclusive property of Pictometry International Corp., are deemed privileged and confidential, and are intended solely for the use of the individual(s) or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or believe that you have received this message in error, please delete this e-mail and any attachments and notify the sender immediately. Any other use, re-creation, dissemination, forwarding or copying of this e-mail is strictly prohibited and may be unlawful.
NOTICE: This message is covered by the Electronic Communications Privacy Act, Title 18, United States Code, Sections 2510-2521. This e-mail and any attached files are the exclusive property of Pictometry International Corp., are deemed privileged and confidential, and are intended solely for the use of the individual(s) or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or believe that you have received this message in error, please delete this e-mail and any attachments and notify the sender immediately. Any other use, re-creation, dissemination, forwarding or copying of this e-mail is strictly prohibited and may be unlawful.

--
Best regards,
Chaitanya kumar CH.
/tʃaɪθənjə/ /kʊmɑr/
+91-9494447584
17.2416N 80.1426E

NOTICE: This message is covered by the Electronic Communications Privacy Act, Title 18, United States Code, Sections 2510-2521. This e-mail and any attached files are the exclusive property of Pictometry International Corp., are deemed privileged and confidential, and are intended solely for the use of the individual(s) or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or believe that you have received this message in error, please delete this e-mail and any attachments and notify the sender immediately. Any other use, re-creation, dissemination, forwarding or copying of this e-mail is strictly prohibited and may be unlawful.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/gdal-dev/attachments/20110202/42d26d52/attachment-0001.html