2

Before I get the negs I know there's a lot of similar posts but I've read them and used them to get to this point but I don't think they answer my question.

Overview

I'm trying to connect my tester heroku site [EDIT: removed] to my PostgreSQL database. I previously tried MS Server but I'm on Windows and Heroku didn't like pyodbc.

Short version

I'm pretty sure I have my PostgreSQL set up correctly but trying to determine if I have the correct hostname for the PostgreSQL URL and if I need to do anything more in terms of allowing access through the Windows Firewall.

Long version

PostgreSQL

Following the accepted answer How to Allow Remote Access to PostgreSQL database my postgresql.conf has listen_addresses = '*' and port 5432. I've put an additional line

host all all 0.0.0.0/0 md5

on pg_hba.conf to get the following

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
host    all         all         0.0.0.0/0       md5

Now I can do this on the command line

C:\etc> psql -U postgres -h 192.XXX.XX.XXX -d ProductionData

Where 192.XXX.XX.XXX is my IPv4 address (found from Start>change Ethernet settings>MyCompany.local) and everything works. I know because if I undo the line on pg_hba.conf this I get

(venv) C:\etc> psql -U postgres -h 192.XXX.XX.XXX -d ProductionData
psql: FATAL:  no pg_hba.conf entry for host "192.XXX.XX.XXX", user "postgres", database "ProductionData", SSL off

Heroku

I understand postgres needs a URL of the form

postgresql://username:password@hostname/database

or

postgresql://username:password@hostname:port/database

I don't really understand hostname other than that is the IP network address. I've tried localhost and the same IPv4 address as above 192.XXX.XX.XXX and 192.XXX.XX.XXX:5432.

Logs section below has the netstat, heroku config setting, restarts of postgresql for each of these attempts done in git bash but I get the following with the two 192.XXX.XX.XXX as timeouts.

Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?

Is the server running on host "192.XXX.XX.XXX" and accepting
TCP/IP connections on port 5432?

Windows Firewall

I've created an inbound rule to allow access...

Protocol type - TCP

Local port - 5432

Remote port - All ports

Programs - This Program - %ProgramFiles%\PostgreSQL\11\bin\postgres.exe

Heroku Logs

Attempt #1 - localhost, result error

$ pg_ctl -D "C:\Program Files\PostgreSQL\11\data" restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2019-02-19 10:09:04.423 GMT [11952] LOG:  listening on IPv6 address "::", port 5432
2019-02-19 10:09:04.423 GMT [11952] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-02-19 10:09:04.460 GMT [11952] LOG:  redirecting log output to logging collector process
2019-02-19 10:09:04.460 GMT [11952] HINT:  Future log output will appear in directory "log".
 done
server started

$ netstat -ant | findstr 5432
  TCP    0.0.0.0:5432           0.0.0.0:0              LISTENING       InHost
  TCP    [::]:5432              [::]:0                 LISTENING       InHost

$ heroku restart -a pgtester
Restarting dynos on ? pgtester... done

$ heroku logs -t -a pgtester
...lots of stack errors...
2019-02-19T09:52:25.731597+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 437, in connect
2019-02-19T09:52:25.731599+00:00 app[web.1]: return self.dbapi.connect(*cargs, **cparams)
2019-02-19T09:52:25.731600+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/psycopg2/__init__.py", line 130, in connect
2019-02-19T09:52:25.731602+00:00 app[web.1]: conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
2019-02-19T09:52:25.731604+00:00 app[web.1]: sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: Connection refused
2019-02-19T09:52:25.731606+00:00 app[web.1]: Is the server running on host "localhost" (127.0.0.1) and accepting
2019-02-19T09:52:25.731607+00:00 app[web.1]: TCP/IP connections on port 5432?
2019-02-19T09:52:25.731646+00:00 app[web.1]: (Background on this error at: http://sqlalche.me/e/e3q8)

Attempt #2 - 192.XXX.XX.XXX, result timeout

$ heroku config:set DEV_DATABASE_URL=postgresql://postgres:mysecretpassword@192.XXX.XX.XXX/ProductionData
Setting DEV_DATABASE_URL and restarting ? pgtester... done, v47
DEV_DATABASE_URL: postgresql://postgres:mysecretpassword@192.XXX.XX.XXX/ProductionData

$ heroku restart -a pgtester
Restarting dynos on ? pgtester... done

$ netstat -ant | findstr 5432
  TCP    0.0.0.0:5432           0.0.0.0:0              LISTENING       InHost
  TCP    [::]:5432              [::]:0                 LISTENING       InHost

$ heroku logs -t -a pgtester
...lots of stack errors...
2019-02-19T10:00:57.273592+00:00 app[api]: Set DEV_DATABASE_URL config vars by user email@example.com
2019-02-19T10:00:57.273592+00:00 app[api]: Release v47 created by user email@example.com
2019-02-19T10:01:05.401378+00:00 heroku[web.1]: Starting process with command `waitress-serve --port=44666 flasky:app`
2019-02-19T10:01:05.672789+00:00 heroku[web.1]: Restarting
2019-02-19T10:01:08.193167+00:00 app[web.1]: [heroku-exec] Starting
2019-02-19T10:01:10.388293+00:00 app[web.1]: postgresql://postgres:mysecretpassword@192.XXX.XX.XXX/ProductionData
2019-02-19T10:01:10.897113+00:00 app[web.1]: Serving on http://0.0.0.0:44666
2019-02-19T10:01:12.089984+00:00 heroku[web.1]: Stopping all processes with SIGTERM
2019-02-19T10:01:12.196052+00:00 heroku[web.1]: Process exited with status 143
2019-02-19T10:01:12.358184+00:00 heroku[web.1]: Starting process with command `waitress-serve --port=18540 flasky:app`
2019-02-19T10:01:16.133191+00:00 app[web.1]: [heroku-exec] Starting
2019-02-19T10:01:18.520615+00:00 app[web.1]: postgresql://postgres:mysecretpassword@192.XXX.XX.XXX/ProductionData
2019-02-19T10:01:19.161158+00:00 app[web.1]: Serving on http://0.0.0.0:18540
2019-02-19T10:01:19.687131+00:00 heroku[web.1]: State changed from starting to up
2019-02-19T10:01:33.125392+00:00 heroku[router]: at=info method=GET path="/" host=pgtester.herokuapp.com request_id=7e65dc99-b99f-4b81-8bbe-a4b98adebd91 fwd="185.16.227.58" dyno=web.1 connect=1ms service=231ms status=200 bytes=2077 protocol=https
2019-02-19T10:02:11.389923+00:00 heroku[router]: at=error code=H12 desc="Request timeout" method=POST path="/" host=pgtester.herokuapp.com request_id=6837dbb0-b2e4-44ee-a67c-2f79a1c7c1b6 fwd="185.16.227.58" dyno=web.1 connect=1ms service=30000ms status=503 bytes=0 protocol=https

Attempt #3 - 192.XXX.XX.XXX:5432, result timeout

$ pg_ctl -D "C:\Program Files\PostgreSQL\11\data" restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2019-02-19 10:09:04.423 GMT [11952] LOG:  listening on IPv6 address "::", port 5432
2019-02-19 10:09:04.423 GMT [11952] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-02-19 10:09:04.460 GMT [11952] LOG:  redirecting log output to logging collector process
2019-02-19 10:09:04.460 GMT [11952] HINT:  Future log output will appear in directory "log".
 done
server started

$ netstat -ant | findstr 5432
  TCP    0.0.0.0:5432           0.0.0.0:0              LISTENING       InHost
  TCP    [::]:5432              [::]:0                 LISTENING       InHost

$ heroku config:set DEV_DATABASE_URL=postgresql://postgres:mysecretpassword@192.XXX.XX.XXX:5432/ProductionData
Setting DEV_DATABASE_URL and restarting ? pgtester... done, v48
DEV_DATABASE_URL: postgresql://postgres:mysecretpassword@192.XXX.XX.XXX:5432/ProductionData

$ netstat -ant | findstr 5432
  TCP    0.0.0.0:5432           0.0.0.0:0              LISTENING       InHost
  TCP    [::]:5432              [::]:0                 LISTENING       InHost

$ heroku restart -a pgtester
Restarting dynos on ? pgtester... done

$ heroku logs -t -a pgtester
...lots of stack errors...
2019-02-19T10:11:10.352241+00:00 app[api]: Release v48 created by user email@example.com
2019-02-19T10:11:10.352241+00:00 app[api]: Set DEV_DATABASE_URL config vars by user email@example.com
2019-02-19T10:11:17.316331+00:00 heroku[web.1]: Starting process with command `waitress-serve --port=56616 flasky:app`
2019-02-19T10:11:20.998316+00:00 app[web.1]: [heroku-exec] Starting
2019-02-19T10:11:21.724624+00:00 heroku[web.1]: Restarting
2019-02-19T10:11:25.043993+00:00 app[web.1]: postgresql://postgres:mysecretpassword@192.XXX.XX.XXX:5432/ProductionData
2019-02-19T10:11:25.797589+00:00 app[web.1]: Serving on http://0.0.0.0:56616
2019-02-19T10:11:26.872124+00:00 heroku[web.1]: Starting process with command `waitress-serve --port=51247 flasky:app`
2019-02-19T10:11:27.101119+00:00 heroku[web.1]: Stopping all processes with SIGTERM
2019-02-19T10:11:27.211928+00:00 heroku[web.1]: Process exited with status 143
2019-02-19T10:11:29.033774+00:00 app[web.1]: [heroku-exec] Starting
2019-02-19T10:11:30.261042+00:00 app[web.1]: postgresql://postgres:mysecretpassword@192.XXX.XX.XXX:5432/ProductionData
2019-02-19T10:11:30.570976+00:00 app[web.1]: Serving on http://0.0.0.0:51247
2019-02-19T10:11:31.054334+00:00 heroku[web.1]: State changed from starting to up
2019-02-19T10:11:34.448659+00:00 heroku[router]: at=info method=GET path="/" host=pgtester.herokuapp.com request_id=7498e3a1-5a09-419b-8038-fc4bf3ddd642 fwd="185.16.227.58" dyno=web.1 connect=1ms service=170ms status=200 bytes=2077 protocol=https
...lots of stack errors...
2019-02-19T10:16:14.079101+00:00 app[web.1]: sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: Connection timed out
2019-02-19T10:16:14.079102+00:00 app[web.1]: Is the server running on host "192.168.10.162" and accepting
2019-02-19T10:16:14.079104+00:00 app[web.1]: TCP/IP connections on port 5432?
2019-02-19T10:16:14.079144+00:00 app[web.1]: (Background on this error at: http://sqlalche.me/e/e3q8)
Community
  • 1
  • 1
Andrew Allen
  • 6,512
  • 5
  • 30
  • 73
  • You're trying to have your application run on Heroku and connect to a Postgres database running _locally_? May I ask why? Heroku provides [its own hosted Postgres service that works out of the box](https://www.heroku.com/postgres) and if you don't like that one for some reason there are [plenty of other](https://elements.heroku.com/addons#data-stores) [options](https://aws.amazon.com/rds/). – ChrisGPT was on strike Feb 19 '19 at 14:00
  • Anyway, you won't be able to have anything connect from outside your network to your local machine using a `192.x.y.z` IP address. That's net-local. If you actually have a good reason to do this you'll need a static IP address or dyamic DNS domain and port forwarding or similar. – ChrisGPT was on strike Feb 19 '19 at 14:02
  • 1
    I'm trying to keep costs down plus I'm uncomfortable having company information on the cloud. Maybe I'm being naive but I thought it would be simple to send data to a database i.e replace localhost with something else badaboombadabing hey presto it works. What is a static IP address, dyamic DNS domain, port forwarding or similar...I know nothing about these like I said in OP. Can you point towards any good sources or books on these? – Andrew Allen Feb 19 '19 at 14:32
  • 1
    Networking is a big topic. Basically, the IP address you've partially hidden in your question isn't visible outside of your local network. It's relatively easy to make outgoing connections, from your local network out to the cloud, but much more complicated to go the other way. Hosting the application locally and the database in the cloud would be much easier from a technical standpoint, but if you don't want to do that and you don't want data in the cloud I'd recommend hosting the application itself locally alongside your database. But your users will need to be local, too. – ChrisGPT was on strike Feb 19 '19 at 14:57
  • Needs to be an external website. So websites that takes data and puts in a database locally isn't a thing unless you're some killer networking specialist? I had in mind how you send an email, you just give an email and there might be some port things happening in the background but the email gets to said person. If it comes to it I might end up getting the website to email it the data in json and then manually put it in a database. I'd still like book recommendations on those aspects you mentioned. Btw Heroku is non-local. Its Python as a service (paas) platform, take cares of python – Andrew Allen Feb 19 '19 at 15:59
  • It's fairly simple as far as networking goes, but it does require some basic knowledge. I don't have any books to recommend, but start by reading about subnets, gateways, and [NAT](https://en.wikipedia.org/wiki/Network_address_translation). In any case, I don't see any point in running a service this way—if you make your database accessible to the internet so your application on Heroku can connect to it you're already opening up at least some access to your local network from the internet. Unless you lock the port down by IP address (not possible with Heroku) your database is still "public". – ChrisGPT was on strike Feb 19 '19 at 16:57
  • If your application needs to run externally (a reasonable requirement IMO) by far the most straightforward thing to do is to also have the database hosted externally. Heroku Postgres [uses SSL](https://devcenter.heroku.com/articles/heroku-postgresql#heroku-postgres-ssl), so the connection from the application to the database is encrypted. I encourage you to take some time to consider whether you really need your database to be hosted inside your network. (If you do, move your application there as well, and use the same networking skills I referenced to make your _app_ externally accessible). – ChrisGPT was on strike Feb 19 '19 at 16:59
  • (I'm not trying to be condescending with the "basic knowledge" comment. Networking is truly a huge, complicated topic. I only know the basics myself.) – ChrisGPT was on strike Feb 19 '19 at 17:06
  • no no you've answered my question, not the answer I wanted but the answer I needed. Can you want to copy and paste your 2nd post to an answer so I can give you the green tick, plus name a charity – Andrew Allen Feb 19 '19 at 18:03
  • Did you find maybe another provider (low cost) that make this possible? best. tx – Je Je Sep 25 '21 at 18:21
  • 1
    @JeJe I went with Heroku in the end and Firebase for some other apps i.e. cloud based. The road most travelled is easier. I've heard there are cheaper alternatives - digitalocean but I've not explored this. Good luck – Andrew Allen Sep 27 '21 at 07:15

1 Answers1

3

Networking is a big topic, but generally speaking

  • big networks (like the Internet) are made up of smaller networks (like your local network)
  • devices on smaller networks aren't usually accessible from outside of those networks¹
  • the IP address you've partly shown starts with 192 and I bet the next octet is 168, which means it's a private network and therefore can't be accessed from the Internet (as an example, there are many machines out there with the address 192.168.1.10—this can exist on any private network)
  • if you need to expose something on your local network, for example something with an IP address of 192.168.1.10, you must do some extra work²

Here are some options:

  • Host your database in the cloud, e.g. using Heroku's PostgreSQL service
  • Host your web service internally so it can connect to your database using its internal 192.168.x.y address
  • Host internally as with the previous option and expose your local web application to the Internet so users can access it from outside your network
  • Rent a VPS so you can host outside of your network but retain a measure of control about how your data is stored (and accept the administrative overhead and responsibility that comes with that)

I wouldn't suggest leaving the web application outside your network, your database inside your network, and exposing the database to the Internet. There's very little value in doing that, especially since Heroku's IP addresses change regularly so you can't easily lock access down to your application. Best case scenario you'll have a publicly available database only accessible over SSL (exactly what Heroku and other providers can offer) that you'll have to administer yourself.

Outgoing connections are relatively easy, e.g. if you wanted to connect a web service running on your local network to a database in the cloud, but I don't think that helps you.

¹This is a Good Thing™ since it provides a layer of protection against direct attacks on your personal devices. Wouldn't it be awful if your machine got hacked every time you waited a few days to install updates on Windows?

²An example would be to set up port forwarding on your network's public IP address, say 123.123.123.123, such that requests to 123.123.123.123:1234 are routed to a specific IP address and port on your internal network, for example 192.168.1.10:5432.

ChrisGPT was on strike
  • 127,765
  • 105
  • 273
  • 257