Feature Request: Wanted: a checkbox to disable SSL instead of needing to resort to writing shell script.

[robacarp]

I'm using fly.io to host a postgres database. Fly.io runs all of an application in a "flycast" wireguard vpn, which you can join with their fly wireguard cli tool.

After joining my flycast network, I'm able to connect to my database with command line psql, but not with Postico. The connection string is trivial -- no query string options about ssl or anything else. Just a garden variety connection string: postgres://user:[email protected]:5432/db

The error I seen in Postico is this:

connection to server at "app-name.flycast" (<ipv6 address here>), port 5432 failed: SSL SYSCALL error: Undefined error: 0

I opened Console and streamed messages filtered down to Postico while I attempted to connect, but to my eye there isn't anything relevant or useful in the logs. I'm happy to track down logs elsewhere but I don't know where to look.

I created the connection in Postico by copying the connection string to my clipboard and clicking "New Favorite," which by the way is an excellent feature. I poked around in the dialog for a little bit but couldn't find any settings which seemed like they might be relevant -- I was specifically looking to disable SSL on the hunch that would help.

I suspect one of the many low level fiddly network settings which can affect these things is out of order, but that's as far as my knowledge goes. For now, and for well over a year since I started using fly.io, my workaround has just been to use fly proxy to setup a connection. I've recently upgraded the database to a read-write configuration, and I'd like to be able to query both nodes to understand replication status better. Separately and fiddling with two proxy commands is more than I'd like to deal with.

Postico version: 1.5.22
macOS version: Sonoma 14, but I can verify the problem predates Sonoma

[robacarp]

This issue is also present in Postico2:



[robacarp]

Alrighty well in the absence of any support here I reached out to the folks at fly.io for help. Here's what they said, and how they were able to help me resolve the error:

I haven't used Postico before, but I downloaded it just now and was able to reproduce the same error. I checked out the docs for Postico and found the following:

Postico always tries to connect to PostgreSQL servers via an encrypted connection (SSL). If the server does not support SSL, Postico will show a warning. You should only connect without SSL if you are on a trusted network. Never connect without encryption when you are on a public network!

I think the issue is due to the .flycast domain not being HTTPS-compatible. Flycast domains work over HTTP, and HTTPS is not needed in this case because data travels over an encrypted WireGuard connection. I tried looking around for information on how to disable HTTPS for Postico, and adding the snippet below as a preconnect shell script seems to work.

cat <<eof
{
"sslmode":"disable",
}
eof

I pasted that in and promptly got an error: "Fish expected string but found redirection":

The problem here is that Postico is running the pre-connect shell script in the shell, and it's not Bash. I use fish-shell. I'm surprised that Postico would be executing this in whatever arbitrary shell shows up, but maybe I don't know what this pre-connect script is supposed to be used for. Regardless, this snippet should work under either most shells:

echo '{ "sslmode":"disable" }'

And it did result in being able to connect to my database over fly.io's wireguard connection.

I'm going to leave this issue open, and I think the feature request is now:

• Wanted: a checkbox to disable SSL instead of needing to resort to writing shell script.