Accessing cockroachcloud db from python app running in Heroku

Hi,

Does any know if a python app running in Heroku can access cockroachcloud DB using the psycopg2?

I just updated my Heroku app to point to the cockroachcloud DB, but it is unable to connect. The same app runs fine on a non-Heroku service.

Thanks,
Saqib

I think we can get this figured out. Can you post the code that you’re using to connect (with sensitive details redacted, of course)? Also, are you trying to connect to a free-tier cluster or a dedicated cluster?

Hi @kernfeld-cockroach .

I am using the following syntax:
postgres://<username>:<password>@<globalhost>:26257/<cluster-name>.bank?sslmode=verify-full&sslrootcert=<certs_directory>/cc-ca.crt

as described here:

And I am using the free-tier cluster.

Thanks,
Saqib

All right, I think I have it. I was just now able to connect to a free-tier cluster from psycopg2. The trick is that you have to URL-encode the equals sign as %3D. I notice you don’t have the cluster option in your connection string, so you’ll need to add the one that you get from the “connect” dialog box on the Cockroach Cloud website. Sorry that this experience is so bumpy, we’re prioritizing making it better!

"postgres://myuser:mypassword@free-tier8.gcp-europe-west1.crdb.io:26257/defaultdb?sslmode=verify-full&sslrootcert=/path/to/my/cc-ca.crt&options=--cluster%3Dmy-cluster-name"

Hello @kernfeld-cockroach . Sorry for the late response. I tried encoding the URL as well. But I get the same exception when connecting to the cockroachdb cloud from an heroku app. Here is the exception:

2021-06-16T13:07:40.857022+00:00 app[web.1]: Oops! An exception has occured: FATAL:  CodeProxyRefusedConnection: Connection attempt throttled
2021-06-16T13:07:40.857074+00:00 app[web.1]: 
2021-06-16T13:07:40.857077+00:00 app[web.1]: Exception TYPE: <class 'psycopg2.OperationalError'>
2021-06-16T13:07:40.857078+00:00 app[web.1]: I am unable to connect to the database
2021-06-16T13:07:40.860470+00:00 app[web.1]: [2021-06-16 13:07:40 +0000] [7] [ERROR] Exception in worker process
2021-06-16T13:07:40.860471+00:00 app[web.1]: Traceback (most recent call last):

Please advise.

Thanks.

Hey Saqib, based on the string Connection attempt throttled that makes me think that your login attempt was temporarily throttled because there were too many failed logins in a short window of time. So, if you try the same connection string again it may work. Otherwise, feel free to post the error that you’re seeing.

Hi @kernfeld-cockroach . That’s what I thought as well. So I re-tried after almost 2 days. I also made sure that the same app is running successfully in gunicorn/flask, with the exact same connection string.

Ah interesting, are you saying that the app can connect successfully from your own computer?

Additionally, can you share the IP address that you’re trying to connect from on Heroku? That way, we can try to match it up against our own logs.

yes, i am able to run the same app on my own server.

I don’t think it is possible to get the outbound IP of a heroku app since it is a PaaS service. see here:
https://help.heroku.com/JS13Y78I/i-need-to-add-heroku-dynos-to-our-allowlist-what-are-ip-address-ranges-in-use-at-heroku

@kernfeld-cockroach , here is the app that I was trying to deploy to heroku:

https://github.com/saqib-ali/hivemined

It is currently deployed in Digital Ocean App Platform, and connects to CockroachCloud without any issues.

Thanks,
Saqib

Thanks for posting your code!

For finding your IP address on Heroku, I was originally thinking you could just do something like, heroku run -a my-app "curl https://ifconfig.me/", but when I tried it, I realized that the IP address was different each time, and anyways it wouldn’t necessarily be the same as the IP address that that app itself connects from.

Right now my best guess is that our IP rate-limiting code is being triggered because there are so many users connecting from the same set of Heroku IP addresses. We’re currently working on debugging this issue, thanks for your patience!

I am glad to hear that deploying on Digital Ocean seems to fix the problem.

Hi @kernfeld-cockroach . Just wanted to let you know that the connection from Heroku started working last night. The following app is now deployed in heroku:

Thanks,
Saqib

Thanks for keeping us updated, Saqib. If you do encounter more issues we’d like to hear about them!

Hi @kernfeld-cockroach , I am still seeing occasional timeouts when connecting to cockroachcloud from Heroku. 95% of the time it works. 5% of time it times out.

I didn’t see the same issue in Digital Ocean App Platform