[osmosis-dev] SSL-protected Postgres database
Marty Alchin
marty at martyalchin.com
Tue Feb 3 04:16:25 UTC 2015
For my case, the keystore stuff is unnecessary, because the database's
certificate is already signed with a trusted authority. But I agree, that'd
be outside the scope of Osmosis regardless. I think this page is a bit more
useful for what Osmosis would need:
https://jdbc.postgresql.org/documentation/81/connect.html
which does mention adding an "ssl=true" parameter to the connection URL as
you said. I believe it should be pretty simple to add here:
https://github.com/openstreetmap/osmosis/blob/fa8ff0e3/osmosis-apidb/src/main/java/org/openstreetmap/osmosis/apidb/common/DatabaseContext.java#L103
But then there's also the matter of specifying the need for SSL on the
command line. That's where my lack of Java experience leaves me though. I
could probably work my way through a simple PR, but it wouldn't be tested,
because I can't be confident that I'd get a sufficient environment up and
running for it. I'm willing to give it a shot though, if you (or someone
else) would be willing to test it.
If you're interested in getting a database set up with SSL, you can sign up
for a free account with Heroku and add a free app and database to it, which
will be row-limited, but it'll have the full SSL setup in place. Or I could
set it all up and add someone as a collaborator to grant access to the
database. I could share credentials for the database itself, but not on an
open channel, even if it is just a throwaway app.
-Marty
On Mon, Feb 2, 2015 at 7:08 PM, Brett Henderson <brett at bretth.com> wrote:
> I've never attempted to connect to PostgreSQL via SSL so I don't have any
> direct experience.
>
> This page is somewhat informative.
> https://jdbc.postgresql.org/documentation/81/ssl-client.html
>
> I suspect Osmosis needs to be enhanced to include the ssl=true connection
> parameter. It *should* be possible to do the keystore config and
> additional command line arguments without code changes.
>
> On 23 January 2015 at 15:13, Marty Alchin <marty at martyalchin.com> wrote:
>
>> I have a mapping project that I've been working on for a while now, using
>> my own installation of the Rails port, hosted on Heroku. It's been working
>> well, but the time has come to move from the API database to a PostGIS
>> database so I can use tools like TileMill to actually produce the maps I
>> need. Osmosis seems like exactly the tool I need!
>>
>> The trouble I'm running into is that Heroku serves all of its databases
>> through SSL, and Osmosis doesn't seem to provide an option to use SSL to
>> connect to the database. I've verified that I can connect to the database
>> using psql, and I've verified that it is reaching the database server,
>> because I get a different error if I change the host or port.
>>
>> I expect adding an SSL option would be relatively simple, but
>> unfortunately, I don't have the Java expertise necessary to find and apply
>> the patch, so I admit I may not understand the complexities involved. I
>> hate opening discussions like this without having code to address the
>> issue, but I'm a Python guy, and it would take me way too long to get a
>> Java environment up and running and get up to speed on the codebase, much
>> less do things the right way for Java.
>>
>> Am I just missing an option somewhere? If not, is this something that
>> could get added to Osmosis? If not, I can duplicate my database locally
>> using psql, but I figured I'd at least see if this is a possibility.
>> Anyway, here's the output I get when I try to run Osmosis with the proper
>> credentials (I've replaced the actual credentials with "..."). Note that
>> the pg_hba.conf response explicitly states that it's trying to connect with
>> SSL off.
>>
>> $ osmosis --read-apidb host="..." user="..." password="..."
>> database="..." --write-xml file="planet.osm"
>> Jan 22, 2015 4:03:36 PM org.openstreetmap.osmosis.core.Osmosis run
>> INFO: Osmosis Version 0.42-6-gf39a160-dirty
>> Jan 22, 2015 4:03:37 PM org.openstreetmap.osmosis.core.Osmosis run
>> INFO: Preparing pipeline.
>> Jan 22, 2015 4:03:37 PM org.openstreetmap.osmosis.core.Osmosis run
>> INFO: Launching pipeline execution.
>> Jan 22, 2015 4:03:37 PM org.openstreetmap.osmosis.core.Osmosis run
>> INFO: Pipeline executing, waiting for completion.
>> Jan 22, 2015 4:03:37 PM
>> org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager
>> waitForCompletion
>> SEVERE: Thread for task 1-read-apidb failed
>> org.springframework.transaction.CannotCreateTransactionException: Could
>> not open JDBC Connection for transaction; nested exception is
>> org.apache.commons.dbcp.SQLNestedException: Cannot create
>> PoolableConnectionFactory (FATAL: no pg_hba.conf entry for host "...", user
>> "...", database "...", SSL off)
>> at
>> org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:240)
>> at
>> org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)
>> at
>> org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:127)
>> at
>> org.openstreetmap.osmosis.apidb.common.DatabaseContext2.executeWithinTransaction(DatabaseContext2.java:89)
>> at
>> org.openstreetmap.osmosis.apidb.v0_6.ApidbReader.run(ApidbReader.java:105)
>> at java.lang.Thread.run(Thread.java:695)
>> Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create
>> PoolableConnectionFactory (FATAL: no pg_hba.conf entry for host "...", user
>> "...", database "...", SSL off)
>> at
>> org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
>> at
>> org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
>> at
>> org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
>> at
>> org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:202)
>> ... 5 more
>> Caused by: org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry
>> for host "...", user "...", database "...", SSL off
>> at
>> org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:293)
>> at
>> org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:108)
>> at
>> org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
>> at
>> org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
>> at
>> org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
>> at
>> org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22)
>> at
>> org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:32)
>> at
>> org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
>> at org.postgresql.Driver.makeConnection(Driver.java:393)
>> at org.postgresql.Driver.connect(Driver.java:267)
>> at
>> org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
>> at
>> org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
>> at
>> org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
>> at
>> org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
>> ... 8 more
>> Jan 22, 2015 4:03:37 PM org.openstreetmap.osmosis.core.Osmosis main
>> SEVERE: Execution aborted.
>> org.openstreetmap.osmosis.core.OsmosisRuntimeException: One or more tasks
>> failed.
>> at
>> org.openstreetmap.osmosis.core.pipeline.common.Pipeline.waitForCompletion(Pipeline.java:146)
>> at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:92)
>> at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:37)
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at
>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>> at
>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>> at java.lang.reflect.Method.invoke(Method.java:597)
>> at
>> org.codehaus.plexus.classworlds.launcher.Launcher.launchStandard(Launcher.java:329)
>> at
>> org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:239)
>> at
>> org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:409)
>> at
>> org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:352)
>> at org.codehaus.classworlds.Launcher.main(Launcher.java:47)
>>
>>
>> _______________________________________________
>> osmosis-dev mailing list
>> osmosis-dev at openstreetmap.org
>> https://lists.openstreetmap.org/listinfo/osmosis-dev
>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/osmosis-dev/attachments/20150202/ba0a0f98/attachment-0001.html>
More information about the osmosis-dev
mailing list