Cannot connect to unmanaged Postgres via SSL

1

(Yes I know its unmanaged postgres :slight_smile: )

Create a brandnew postgres fly machine

fly pg create

Postgres cluster liquido-db-fly-int created
  Username:    postgres
  Password:    XXXXXXXXXXXX
  Hostname:    liquido-db-fly-int.internal
  Flycast:     fdaa:1:b0e4:0:1::7
  Proxy port:  5432
  Postgres port:  5433
  Connection string: postgres://postgres:XXXXXXX@liquido-db-fly-int.flycast:5432

As per documentation This Is Not Managed Postgres · Fly Docs

Create a new fly.toml and then redeploy

fly config save --app liquido-db-fly-int
fly deploy . --app liquido-db-fly-int --image flyio/postgres-flex:17

Remark: The fly.toml has two [[services]] in it. One for port 5432 and a second one for port 5433. Both mapped. I didn’t touch it.

fly services list                                                                  
Services
PROTOCOL	PORTS       	HANDLERS	FORCE HTTPS	PROCESS GROUP	REGIONS	MACHINES 
TCP     	5432 => 5432	[PG_TLS]	False      	app          	fra    	1       	
TCP     	5433 => 5433	[PG_TLS]	False      	app          	fra    	1

DB is running. Health check ok. Now how to connect to it?

With shared-IP

  • Assigned shared-IP => does not work
  • Connect via public hostname liquido-db-fly-int.fly.dev => did not work
  • Via comand line
   >  psql "sslmode=require host=liquido-db-fly-int.fly.dev dbname=postgres user=postgres"  
    psql: error: connection to server at "liquido-db-fly-int.fly.dev" (66.241.125.63), port 5432 failed: server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.

Same test with dedicated-IP

(Some other forum post mentioned that this might be neccessary.)

    > psql "sslmode=require host=137.66.21.126 dbname=postgres user=postgres"
    psql: error: connection to server at "137.66.21.126", port 5432 failed: SSL error: unexpected eof while reading

… I am running out of ideas. Any tips? Any more information I can provide?

2

Aahh fly postgres connect -a liquido-db-fly-int works. Mmh but what is it doing differntly. Can I somehow see the sql connection string that it creates internally?

Hey fly team your network setup needs work. psql postgresql://postgres@213.188.223.237 (static assigned-IP address) works. But SQL clients with exactly the same connection string, complain with a SSL SYSCALL error: EOF detected. Seems like someone (proxy?) interrups the connection.

This is really weired. psql command line works. But for example java-quarkus also throws the same error:

javax.net.ssl.SSLHandshakeException: Remote host terminated the handshake
Caused by: java.io.EOFException: SSL peer shut down incorrectly
4

Your doc says your postgres image supports SSL Connect With flyctl · Fly Docs

But it doesn’t. If you set sslmode=disable only then it works.

5

I’ve not tried this, but I wonder if there is something that one would need to do to make the SSL cert valid in Postgres. This might explain why it works when you disable SSL on the client side; it is avoiding an invalid/absent certificate.

If your db server is offering ports for plaintext and SSL, could this command just be plumping for the unencrypted one?

I assume this is from an external connection. Which port did you specify here?

1 Like
6

That’s exactly what I also had in mind.

At least for my backend, the TLS terminates at the Fly Proxy. I assumed this would be the same for the DB image provided by Fly.io For testing my backend this is quite handy. Fly offers a valid letsencrypt cert for *.fly.dev So I can connect to the fly proxy via HTTPS on port 443 and that forwards an unencrypted connection via the private intranet to my backend. => But I just realize this only seems to be the case for webapps on Port 443 according to that doc.

The connection to postgres by default goes over port 5432. There is this pg_tls handler but I couldn’t get it to work.

Only unencrypted connection worked.

1 Like
7

Can you opt out of the proxy TLS termination, per the brief section on “terminate TLS yourself” in your first link?

8

It now works. I think mostly because DNS bindings are now up to date and have propagated.

My main learnings are (sharing for everyone):

Your client (e.g. JDBC, Postico)
        |
        |  TLS handshake (Postgres-native)
        v
Fly Global Edge Proxy (pg_tls)
        |
        v
Postgres process inside your VM (usually plain TCP)
  • A postgres server has its own native TLS handshake (not HTTPS!)
  • The fly proxy handles that with the pg_tls handler

If you want to connect to your database from the outside via TLS you need:

  • a static IP adress assigned to your machine
  • fly.toml with both ports mapped (5432 and 5433)
  • must use .fly.dev as host in the connection string (not the IP! otherwise the cert is rejected
  • and you must wait (a minute up to hours) until that sub domain is associated with your static-IP also in your local uplink providers DNS.

Example command lines

Using Fly Tools: Connect to db CLI
  fly postgres connect -a <your-db-app-name>

Forward local 5400 (just an example) to the remote 5432
Then you can connect your DB client to postgres://localhost:5400
  fly proxy 5400:5432

Plain psql client CLI 
  psql postgresql://postgres@<your-db-app-name>.fly.dev:5432

Example JDBC connection string for Quarkus

quarkus.datasource.jdbc.url=jdbc:postgresql://213.188.223.237/postgres?sslmode=disable

All this is only if you need to securely connect to your database from the outside, from the public internet. A backend running on another fly machine can simply directly connect to your DB without TLS and without the need for a static IP. Just internal private flycast.

1 Like
9

Thanks for writing it up! :trophy:

10

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.