search

mapnik / node-mapnik

Bindings to mapnik for node.js
http://mapnik.org/documentation/node-mapnik
BSD 3-Clause "New" or "Revised" License
533 stars 165 forks source link

Using postgis plugin with Heroku Postgres - needs SSL support #755

Open woudsma opened 7 years ago

woudsma commented 7 years ago

I'm struggling getting node-mapnik to work on Heroku when i try to connect to the Heroku Postgres database. SSL support seems to be disabled with the included binaries, libpq should be built with --with-openssl flag. This is why i'm getting an Error: Postgis Plugin: sslmode value "require" invalid when SSL support is not compiled in Connection string: 'host=... error, correct me if i'm wrong. Please see: https://github.com/mapnik/mapnik/issues/3656

2017-04-14T11:12:23.892651+00:00 app[worker.1]: Error: Postgis Plugin: sslmode value "require" invalid when SSL support is not compiled in
2017-04-14T11:12:23.892652+00:00 app[worker.1]: Connection string: 'host=ec2-1234.eu-west-1.compute.amazonaws.com dbname=dbname sslmode=require user=user connect_timeout=4'
2017-04-14T11:12:23.892652+00:00 app[worker.1]:   encountered during parsing of layer 'layer' in Layer of '/app/src/resources/stylesheet.xml'
2017-04-14T11:12:23.892653+00:00 app[worker.1]:     at Error (native)
woudsma commented 7 years ago

Solved (in this case) using https://github.com/heroku/heroku-buildpack-pgbouncer stunnel, this way node-mapnik doesn't need to use SSL to connect to the database. Remove Heroku config var PGSSLMODE=require if you've set it before.

Buildpacks:

https://github.com/heroku/heroku-buildpack-apt
https://github.com/heroku/heroku-buildpack-pgbouncer
heroku/nodejs

Aptfile:

libc6
libgcc1
libstdc++6
http://mirrors.kernel.org/ubuntu/pool/main/g/gcc-4.9/gcc-4.9_4.9.2-10ubuntu13_amd64.deb
http://mirrors.kernel.org/ubuntu/pool/main/g/gcc-4.9/libstdc%2b%2b6_4.9.2-10ubuntu13_amd64.deb

stylesheet-heroku.xml:

<Layer name="layer" srs="+init=epsg:4326">
  <StyleName>style</StyleName>
  <Datasource>
    <Parameter name="type">postgis</Parameter>
    <Parameter name="host">127.0.0.1</Parameter>
    <Parameter name="port">6000</Parameter>
    <Parameter name="dbname">db1</Parameter>
    <Parameter name="user">dbuser</Parameter>
    <Parameter name="password">dbpassword</Parameter>
    <Parameter name="table">(select id, coordinates from table where coordinates && !bbox! limit 100000) as world</Parameter>
    <Parameter name="extent">-180,-90,180,89.99</Parameter>
  </Datasource>
</Layer>
gadybadger commented 6 years ago

Thanks for putting this together, I'm very close to running node-mapnik on Heroku with Postgres. After trying all of the steps, I get the following error:

Postgis Plugin: FATAL:  no pg_hba.conf entry for host "1.1.1.1", user "user", database "database", SSL off
Connection string: 'host=1.1.1.1 port=5432 dbname=database user=user connect_timeout=4'

I also unset the PGSSLMODE config var (I get Error: Postgis Plugin: sslmode value "require" invalid when SSL support is not compiled in when it is set).

Is there additional config needed for the pgbouncer buildpack? Or is the only option to rebuild node-mapnik with SSL support in libpq?

Any help is much appreciated!

Here is my setup and output:

package.json

{
    "name": "...",
    "version": "0.0.1",
    "dependencies": {
        "mapnik": "3.5.x",
        "express": "2.5.x"
    },
    "engines": {
        "node": "0.10.x",
        "npm": "2.11.2"
    }
}

Aptfile (gcc 4.9 wasn't available, so I used 4.8 instead) 

libc6
libgcc1
libstdc++6
http://mirrors.kernel.org/ubuntu/pool/main/g/gcc-4.8/gcc-4.8_4.8.4-2ubuntu1~14.04.3_amd64.deb
http://mirrors.kernel.org/ubuntu/pool/main/g/gcc-4.8/libstdc%2B%2B6_4.8.4-2ubuntu1~14.04.3_amd64.deb

Procfile

web: bin/start-pgbouncer-stunnel node server.js

Buildpacks

Heroku build logs (everything looks good)

-----> Apt app detected
-----> Reusing cache
-----> Updating apt caches
       Hit:1 http://apt.postgresql.org/pub/repos/apt xenial-pgdg InRelease
       Hit:2 http://archive.ubuntu.com/ubuntu xenial InRelease
       Get:3 http://archive.ubuntu.com/ubuntu xenial-security InRelease [102 kB]
       Get:4 http://archive.ubuntu.com/ubuntu xenial-updates InRelease [102 kB]
       Fetched 204 kB in 0s (278 kB/s)
       Reading package lists...
-----> Fetching .debs for libc6
       Reading package lists...
       Building dependency tree...
       0 upgraded, 0 newly installed, 1 reinstalled, 0 to remove and 28 not upgraded.
       Need to get 0 B/2,580 kB of archives.
       After this operation, 0 B of additional disk space will be used.
       Download complete and in download only mode
W: --force-yes is deprecated, use one of the options starting with --allow instead.
-----> Fetching .debs for libgcc1
       Reading package lists...
       Building dependency tree...
       0 upgraded, 0 newly installed, 1 reinstalled, 0 to remove and 28 not upgraded.
       Need to get 0 B/38.5 kB of archives.
       After this operation, 0 B of additional disk space will be used.
       Download complete and in download only mode
W: --force-yes is deprecated, use one of the options starting with --allow instead.
-----> Fetching .debs for libstdc++6
       Reading package lists...
       Building dependency tree...
       The following additional packages will be installed:
         cpp-5 g++-5 gcc-5 gcc-5-base libasan2 libatomic1 libcc1-0 libcilkrts5
         libgcc-5-dev libgomp1 libitm1 liblsan0 libmpx0 libquadmath0 libstdc++-5-dev
         libtsan0 libubsan0
       Suggested packages:
         gcc-5-locales g++-5-multilib gcc-5-doc libstdc++6-5-dbg gcc-5-multilib
         libgcc1-dbg libgomp1-dbg libitm1-dbg libatomic1-dbg libasan2-dbg
         liblsan0-dbg libtsan0-dbg libubsan0-dbg libcilkrts5-dbg libmpx0-dbg
         libquadmath0-dbg libstdc++-5-doc
       The following packages will be upgraded:
         cpp-5 g++-5 gcc-5 gcc-5-base libasan2 libatomic1 libcc1-0 libcilkrts5
         libgcc-5-dev libgomp1 libitm1 liblsan0 libmpx0 libquadmath0 libstdc++-5-dev
         libstdc++6 libtsan0 libubsan0
       18 upgraded, 0 newly installed, 0 to remove and 10 not upgraded.
       Need to get 0 B/29.8 MB of archives.
       After this operation, 128 kB of additional disk space will be used.
       Download complete and in download only mode
W: --force-yes is deprecated, use one of the options starting with --allow instead.
-----> Fetching http://mirrors.kernel.org/ubuntu/pool/main/g/gcc-4.8/gcc-4.8_4.8.4-2ubuntu1~14.04.3_amd64.deb
-----> Fetching http://mirrors.kernel.org/ubuntu/pool/main/g/gcc-4.8/libstdc%2B%2B6_4.8.4-2ubuntu1~14.04.3_amd64.deb
-----> Installing cpp-5_5.4.0-6ubuntu1~16.04.6_amd64.deb
-----> Installing g++-5_5.4.0-6ubuntu1~16.04.6_amd64.deb
-----> Installing gcc-4.8_4.8.4-2ubuntu1~14.04.3_amd64.deb
-----> Installing gcc-5_5.4.0-6ubuntu1~16.04.6_amd64.deb
-----> Installing gcc-5-base_5.4.0-6ubuntu1~16.04.6_amd64.deb
-----> Installing libasan2_5.4.0-6ubuntu1~16.04.6_amd64.deb
-----> Installing libatomic1_5.4.0-6ubuntu1~16.04.6_amd64.deb
-----> Installing libc6_2.23-0ubuntu10_amd64.deb
-----> Installing libcc1-0_5.4.0-6ubuntu1~16.04.6_amd64.deb
-----> Installing libcilkrts5_5.4.0-6ubuntu1~16.04.6_amd64.deb
-----> Installing libgcc1_1%3a6.0.1-0ubuntu1_amd64.deb
-----> Installing libgcc-5-dev_5.4.0-6ubuntu1~16.04.6_amd64.deb
-----> Installing libgomp1_5.4.0-6ubuntu1~16.04.6_amd64.deb
-----> Installing libitm1_5.4.0-6ubuntu1~16.04.6_amd64.deb
-----> Installing liblsan0_5.4.0-6ubuntu1~16.04.6_amd64.deb
-----> Installing libmpx0_5.4.0-6ubuntu1~16.04.6_amd64.deb
-----> Installing libquadmath0_5.4.0-6ubuntu1~16.04.6_amd64.deb
-----> Installing libstdc%2B%2B6_4.8.4-2ubuntu1~14.04.3_amd64.deb
-----> Installing libstdc++-5-dev_5.4.0-6ubuntu1~16.04.6_amd64.deb
-----> Installing libstdc++6_5.4.0-6ubuntu1~16.04.6_amd64.deb
-----> Installing libtsan0_5.4.0-6ubuntu1~16.04.6_amd64.deb
-----> Installing libubsan0_5.4.0-6ubuntu1~16.04.6_amd64.deb
-----> Writing profile script
-----> Rewrite package-config files
-----> pgbouncer-stunnel app detected
       Using pgbouncer version: 1.7.2-heroku
       Using stunnel version: 5.40
-----> Fetching and vendoring pgbouncer into slug
-----> Fetching and vendoring stunnel into slug
-----> Moving the configuration generation script into app/bin
-----> Moving the start-pgbouncer-stunnel script into app/bin
-----> Moving the use-pgbouncer script into app/bin
-----> pgbouncer/stunnel done
-----> Node.js app detected
-----> Creating runtime environment

       NPM_CONFIG_LOGLEVEL=error
       NPM_CONFIG_PRODUCTION=true
       NODE_VERBOSE=false
       NODE_ENV=production
       NODE_MODULES_CACHE=true
-----> Installing binaries
       engines.node (package.json):  0.10.x
       engines.npm (package.json):   2.11.2

       Resolving node version 0.10.x...
       Downloading and installing node 0.10.48...
       Bootstrapping npm 2.11.2 (replacing 2.15.1)...
       npm 2.11.2 installed
-----> Restoring cache
       Loading 2 from cacheDirectories (default):
       - node_modules
       - bower_components (not cached - skipping)
-----> Building dependencies
       Installing node modules (package.json)
-----> Caching build
       Clearing previous node cache
       Saving 2 cacheDirectories (default):
       - node_modules
       - bower_components (nothing to cache)
-----> Build succeeded!
-----> Discovering process types
       Procfile declares types -> web
-----> Compressing...
       Done: 81.4M
-----> Launching...
       Released v23
       https://...herokuapp.com/ deployed to Heroku

Heroku logs (everything looks good)

2018-02-05T16:55:13.304039+00:00 heroku[web.1]: Starting process with command `bin/start-pgbouncer-stunnel node server.js`
2018-02-05T16:55:15.231313+00:00 app[web.1]: buildpack=pgbouncer at=pgbouncer-enabled
2018-02-05T16:55:15.231451+00:00 app[web.1]: buildpack=pgbouncer at=config-gen-start
2018-02-05T16:55:15.246312+00:00 app[web.1]: Setting DATABASE_URL_PGBOUNCER config var
2018-02-05T16:55:15.252018+00:00 app[web.1]: buildpack=pgbouncer at=config-gen-end
2018-02-05T16:55:15.252047+00:00 app[web.1]: buildpack=pgbouncer at=config-gen-override DATABASE_URL
2018-02-05T16:55:15.254609+00:00 app[web.1]: buildpack=pgbouncer at=stunnel-launched pid=28 signal=SIGINT
2018-02-05T16:55:15.254646+00:00 app[web.1]: buildpack=pgbouncer at=stunnel-start
2018-02-05T16:55:15.254945+00:00 app[web.1]: buildpack=pgbouncer at=pgbouncer-launched pid=29 signal=SIGINT
2018-02-05T16:55:15.254976+00:00 app[web.1]: buildpack=pgbouncer at=pgbouncer-start
2018-02-05T16:55:15.255321+00:00 app[web.1]: buildpack=pgbouncer at=app-launched pid=31
2018-02-05T16:55:15.255354+00:00 app[web.1]: buildpack=pgbouncer at=app-start
2018-02-05T16:55:15.258262+00:00 app[web.1]: 2018-02-05 16:55:15.258 32 LOG File descriptor limit: 10000 (H:10000), max_client_conn: 100, max fds possible: 111
2018-02-05T16:55:15.258720+00:00 app[web.1]: 2018-02-05 16:55:15.258 32 LOG listening on 127.0.0.1:6000
2018-02-05T16:55:15.258780+00:00 app[web.1]: 2018-02-05 16:55:15.258 32 LOG listening on unix:/tmp/.s.PGSQL.6000
2018-02-05T16:55:15.258785+00:00 app[web.1]: 2018-02-05 16:55:15.258 32 LOG process up: pgbouncer 1.7.2, libevent 2.0.21-stable (epoll), adns: evdns2, tls: OpenSSL 1.0.2g  1 Mar 2016
2018-02-05T16:55:15.265950+00:00 app[web.1]: 2018.02.05 16:55:15 LOG5[ui]: stunnel 5.40 on x86_64-unknown-linux-gnu platform
2018-02-05T16:55:15.265958+00:00 app[web.1]: 2018.02.05 16:55:15 LOG5[ui]: Compiled/running with OpenSSL 1.0.2g  1 Mar 2016
2018-02-05T16:55:15.265980+00:00 app[web.1]: 2018.02.05 16:55:15 LOG5[ui]: Threading:PTHREAD Sockets:POLL,IPv6,SYSTEMD TLS:ENGINE,FIPS,OCSP,PSK,SNI Auth:LIBWRAP
2018-02-05T16:55:15.266007+00:00 app[web.1]: 2018.02.05 16:55:15 LOG5[ui]: Reading configuration from file /app/vendor/stunnel/stunnel-pgbouncer.conf
2018-02-05T16:55:15.266015+00:00 app[web.1]: 2018.02.05 16:55:15 LOG5[ui]: UTF-8 byte order mark not detected
2018-02-05T16:55:15.266043+00:00 app[web.1]: 2018.02.05 16:55:15 LOG5[ui]: FIPS mode disabled
2018-02-05T16:55:15.266078+00:00 app[web.1]: 2018.02.05 16:55:15 LOG4[ui]: Service [DATABASE_URL] needs authentication to prevent MITM attacks
2018-02-05T16:55:15.266099+00:00 app[web.1]: 2018.02.05 16:55:15 LOG5[ui]: Configuration successful
2018-02-05T16:55:15.407084+00:00 app[web.1]: Server running on port 56550
2018-02-05T16:55:17.016569+00:00 heroku[web.1]: State changed from starting to up

In code, I am doing the following. All of the environment variables are set in Heroku Config, along with DATABASE_URL, which is what pgbouncer needs.

mapnik.register_default_fonts();
mapnik.register_default_input_plugins();
...
var postgis_settings = {
     'host': process.env.DB_HOST,
     'dbname': process.env.DB_NAME,
     'table': sql,
     'user': process.env.DB_USER,
     'password': process.env.DB_PW,
     'port': process.env.DB_PORT,
     'type': 'postgis',
     'geometry_field': 'geom',
     'srid': '3857',
     'extent': '-20005048.4188,-9039211.13765,19907487.2779,17096598.5401'
};
var layer = new mapnik.Layer('tile', mercator.proj4);
layer.datasource = new mapnik.Datasource(postgis_settings);
woudsma commented 6 years ago

It has been a while, but i can remember we did set some config vars for the pgbouncer buildpack.

PGBOUNCER_POOL_MODE=session
PGBOUNCER_URLS=DATABASE_URL

I'm not sure that will solve your problem. We used Sequelize and PostgreSQL, the trick there was to also set dialectOptions: { ssl: false } in the Sequelize setup to get it to work. Also we didn't create a layer programmatically using new mapnik.Datasource(postgis_settings). So i have no experience in that. We used the stylesheet.xml approach (and replaced the appropriate values in the stylesheet with node).

woudsma commented 6 years ago

And shouldn't no pg_hba.conf entry for host "1.1.1.1" be 127.0.0.1? You need to connect to the local database port created by pgbouncer-stunnel.

gadybadger commented 6 years ago

Thanks for the reply, @woudsma, I appreciate you taking the time to help here.

I went ahead and set those two config vars, but that didn't fix the issue. I think I might be missing how pgbouncer-stunnel works... in the logs, I see LOG listening on 127.0.0.1:6000, so as you brought up, I changed the connection in my app to match this by setting the DB_HOST and DB_PORT config vars to 127.0.0.1 and 6000. After doing that, I get:

Postgis Plugin: ERROR:  No such database: dbname1
Connection string: 'host=127.0.0.1 port=6000 dbname=dbname1 user=dbuser connect_timeout=4'

So I'm getting closer, as it seems it's connecting to Postgres now. But what should the database name, user, and password refer to? Is it the original Heroku Postgres credentials? That's what I'm using in the case where I get the error, so I'm not sure what to do there.

Again, any help is much appreciated!

woudsma commented 6 years ago

The pgbouncer-stunnel buildpack makes a connection to the Heroku Postgres addon, and provides a local database endpoint for you, e.g. postgres://user:pw@127.0.0.1:6000/db1. Maybe try to log what that database URL is, and use those values to connect to the database. I think in our case the database name was db1. (it could be that pgbouncer replaces the DATABASE_URL to the right endpoint inside the dyno, i can't remember)

gadybadger commented 6 years ago

Yep, you're right on. Running bin/start-pgbouncer-stunnel env shows that the username and password are unchanged from the Heroku Postgres credentials, but the host is 127.0.0.1, port is 6000, and database name is db1. After plugging this into my app, it's able to connect to Postgres without issues.

Thanks again for the help! 🍻