Issue connecting to CrateDB Cloud cluster from local machine

CrateDB Cloud
1

Hi there,

I set up a CrateDB Cloud cluster on the free trial yesterday. I can access the admin console just fine, but can’t run crash or connect to the cluster from node-crate code running on my local machine. Do I need to do something to explicitly enable remote access? It looks like there’s no initial allow list set up so the cluster appears to be accessible across the internet.

For example when I do:

crash --hosts 'https://MY-CLUSTER-NAME.eks1.eu-west-1.aws.cratedb.net:4200' -U 'admin' -W

Then enter my admin password, I get “CONNECT ERROR”. Similarly when using node-crate to try and connect from a Node.js project.

I’ve moved forward with my project by using CrateDB locally in the Docker container but I’d like to shift to the cloud.

Any help much appreciated.

Thanks,

Simon.

1 Like
2

Dear Simon,

thank you for writing in, and sorry to hear about those hiccups you are observing when connecting to CrateDB Cloud.

I’ve tried to reproduce the problem by just creating a new CRFREE cluster on https://console.cratedb.cloud/, and exercised connecting to it on behalf of crash, and your example application at GitHub - simonprickett/cratedb-demo: Spatial data demo application using CrateDB and the Express framework..

In both cases, connecting to the database instance worked well.

crash shell

Connecting with crash works both non-interactively, or, when omitting the password, interactively by prompting for it.

crash --host 'https://admin:REDACTED@beige-grievous.aks1.westeurope.azure.cratedb.net:4200' < test.sql

Express application

When configuring the same connection URL within your .env file, …

CRATE_URL=https://admin:REDACTED@beige-grievous.aks1.westeurope.azure.cratedb.net:4200

… and loading data into the database, that command, using HTTPie, also works well for me.

http localhost:3000/search 'point[lng]=2' 'point[lat]=60'

{
    "data": [
        {
            "boundaries": {
                "coordinates": [
                   ... COMPRESSED ...
                ],
                "type": "Polygon"
            },
            "forecast": {
                "sea": "Slight or moderate.",
                "visibility": "Good, occasionally poor.",
                "weather": "Rain or thundery showers.",
                "wind": "Variable 2 to 4, becoming cyclonic 4 to 6."
            },
            "name": "Viking"
        }
    ]
}

Q&A

Can you tell us more about your operating system environment, or other setup conditions, and maybe about the software version of your crash command? My installation, on macOS/Python3.11, is:

$ crash --version
0.30.2

However, as you are apparently observing the same connectivity problem also on your Node.js/Express application, the root cause is probably something which causes havoc to both variants, and is most likely not related to any software version problem.

Apologies that my response probably does not help you to debug the problem at all. Let’s also hear back from our engineers whether they can observe any anomalies on our cloud infrastructure, which could lead to connectivity problems in one way or another.

With kind regards,
Andreas.

3

Hi again,

I just validated the behaviour when connecting to an unknown host name, and it looks like it is just the same. [1]

$ crash --host 'http://foo-unknown-domain-foo.net:4200'
CONNECT ERROR

Can you verify it is not a DNS issue?

Thanks,
Andreas.

  1. Could/should UX be improved on crash in this regard? ↩︎

4

It doesn’t appear to be a DNS issue.

% dig elite-krait.eks1.eu-west-1.aws.cratedb.net

; <<>> DiG 9.10.6 <<>> elite-krait.eks1.eu-west-1.aws.cratedb.net
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 6998
;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 512
;; QUESTION SECTION:
;elite-krait.eks1.eu-west-1.aws.cratedb.net. IN A

;; ANSWER SECTION:
elite-krait.eks1.eu-west-1.aws.cratedb.net. 60 IN A 54.77.61.126

Here’s my crash version:

crash --version
0.30.2

I wonder if this is something to do with the admin password containing ‘)’ and it needing encoding?

I can confirm that using this in my .env does work with my node-crate application, so I suspect I am seeing some sort of issue with crash here or I don’t understand what crash does… I’m expecting to be able to connect to the cluster with crash and enter SQL commands etc?

CRATE_URL=https://admin:MYPASSWORDHERE@elite-krait.eks1.eu-west-1.aws.cratedb.net:4200
5

I’ve also now successfully deployed the application to Google Cloud Run using a cloud instance of CrateDB. If you want to try it out, draw a point, polyline or polygon on the map, hit “Search” and find out…

  • For a point, which shipping forecast region the point falls in.
  • For a polyline, which shipping forecast regions the line passes through.
  • For a polygon, which shipping forecast regions interseced with the polygon.

https://shippingforecast.simonprickett.dev/

1 Like
6

Thanks for letting us know. Excellent.

We will investigate that. Thanks. However, connecting also fails when supplying the password interactively using -W, right?

You may want to probe a third variant. For crash, the environment variable CRATEPW can be used to supply the password, see Running Crash — CrateDB: Crash CLI.

7

Using -w also fails in the same way. CRATEPW environment variable also doesn’t fix this.

8

Hi Simon,

Sorry, that you are experiencing that issue. Could you try changing your password in the console:

image
image1638×784 46 KB

Just to be certain, you can access the cluster using
https://MY-CLUSTER-NAME.eks1.eu-west-1.aws.cratedb.net:4200 (or the Admin UI button) in the browser, correct?

1 Like
9

Hi there - I can confirm that I can access the cluster using both the URL and the admin console. I’ll try a password without “special” characters in it… so far both passwords I’ve used were generated for me by your admin portal.

I’ve now also changed the password to 23 characters containing only A-Z a-9 and 0-9 and can confirm that I can still connect in the browser and using my node-crate application but that crash continues to refuse to connect when supplied with the new password:

crash --hosts 'https://elite-krait.eks1.eu-west-1.aws.cratedb.net:4200' -U 'admin' -W
Password:
CONNECT ERROR

So this seems to be a crash issue? I am on macOS Sonoma 14.2.1 with crash version 0.30.2.

Simon.

1 Like
10

could you add the -v verbose flag

crash --hosts 'https://elite-krait.eks1.eu-west-1.aws.cratedb.net:4200' -U 'admin' -W -v

and post the output

So this seems to be a crash issue? I am on macOS Sonoma 14.2.1 with crash version 0.30.2.

I am also on 14.2.1 and I just tested 0.30.0 and 0.31.0 with an aws cluster, which works

1 Like
11

Interesting, looks like a SSL verify issue:

| https://elite-krait.eks1.eu-west-1.aws.cratedb.net:4200 | NULL      | 0.0.0   | FALSE     | Server not available, exception: HTTPSConnectionPool(host='elite-krait.eks1.eu-west-1.aws.cratedb.net', port=4200): Max retries exceeded with url: / (Caused by SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1006)'))) |

Regards,

Simon.

1 Like
12

Thank you, we need to look into that.

Can you verify that connecting to testdrive.aks1.westeurope.azure.cratedb.net works well from your machine? I’ve shared corresponding credentials with you on behalf of a private message.

13

Could it be that you missed installing the certificates at the end of the Python installation on your Mac and crash therefore can’t verify the Let’s Encrypt certificate from the CrateDB Cloud server?

image
image1464×1112 174 KB

Could you try disabling the ssl verification in crash with

crash --hosts 'https://elite-krait.eks1.eu-west-1.aws.cratedb.net:4200' -U 'admin' -W --verify-ssl False -v
1 Like
14

Ah, it might be the venerable missing link to the system root certificates, which Python still leans on?

If we discovered this to be an issue here, I am sure it will not be a single occasion. So, if it proves to be the root cause for this hiccup, we may want to look into using certifi and/or truststore, probably on behalf of crate-python. It would be sad if Python would be behind the onboarding UX of Node.js in such cases, right?

I guess this problem does not happen to users on macOS/Homebrew, because the Homebrew environment will properly supply root certificates through vanilla OpenSSL?

However, without aiming to hijack the conversation too much, Windows users might also find themselves in the same boat, so I think we should improve the situation in general.

15

Disabling the SSL verification worked yes. I don’t recall installing Python per se, here’s my setup:

python3 --version
Python 3.11.6

which python3
/usr/local/bin/python3

ls -l /usr/local/bin/python3
lrwxr-xr-x  1 root  wheel  70 Nov 29 15:18 /usr/local/bin/python3 -> ../../../Library/Frameworks/Python.framework/Versions/3.11/bin/python3
1 Like
16

You might find the “Install Certificates.command” within your Applications Folder:

image
image1008×386 56.3 KB

1 Like
17

Generalizing the problem, and learning from your report, I think the chance it becomes an issue is likely higher on vanilla installations of Python on non-GNU machines without corresponding polyfills, like vanilla macOS and Windows.

To fill the gap for Python, there are those packages like certifi and truststore mentioned above. We will carry that forward into corresponding downstream issues, to either add those into the mix, or improve the documentation correspondingly, like outlined by @proddata.

Thanks again!

18

Hi there - running “Install Certificates.command” fixed it for me - I guess this was needed for a vanilla MacOS install.

Thanks,

Simon.

1 Like
19

Hi. We added corresponding guidelines to the documentation how to debug and resolve this issue. Thank you again for the excellent report and conversation around it.