Unable to connect to PostgreSQL from an application

Questions / Help Build debugging
1

Hello gang!

I’ve made an application which uses a PostgreSQL database, accepting the credentials from the DATABASE_URL environment variable.

I’ve deploy it and have added a PostgreSQL instance to it using the step documented here: Fly Postgres · Fly Docs

flyctl postgres create
flyctl postgres attach --postgres-app my-postgresql-app

However after doing this I get errors in my application saying that it was unable to connect.

I think the issue may be related to fly’s private networking as the page says this:

Note that for the app to be able to attach to the database it must have private_network enabled in fly.toml . This enables it to look up .internal addresses like the database URL. See Using Fly DNS for further details.

Unfortunately the page linked for more information about the private_network config option doesn’t actually mention private_network, so I’m not sure what to do next. It’s also not included in this page on fly.toml: Fly Launch configuration (fly.toml) · Fly Docs

How might I resolve this issue and connect to the PostgreSQL instance from my application? It would be grand to get all the steps required to connect to PostgreSQL added to this page: Fly Postgres · Fly Docs

Thanks,
Louis

1 Like
2

I had a similar issue, adding private networking to the experimental section resolved it

[experimental]
allowed_public_ports =
auto_rollback = true
private_network = true

1 Like
3

Thanks! I tried that but unfortunately it doesn’t seem to make the difference for me.

I also tried private_network = "true" to match an example I found in the Fly GitHub, though I suspect that may have been outdated.

I’ve added some logging to my application and it is correctly loading and parsing the DATABASE_URL, so I’m not sure what else it might be :thinking:

4

What error are you getting? BEAM + ipv6 is a little bit of a disaster. For Elixir apps we need:

export ELIXIR_ERL_OPTIONS="-proto_dist inet6_tcp"

And then Ecto needs:

socket_options: [:inet6]
5

Ah wonderful, that sounds very useful.

Unfortunately the Erlang PostgreSQL client I’m using doesn’t seem to give an error beyond the atom none_available when you attempt to checkout a connection.

Looks like I’m going to have to do some digging and see how I can replicate that Elixir config in the Erlang world.

6

One thing you can try is using an IP address directly. If you run fly dig aaaa <dbname>.internal -a <appname> you’ll get a list of IPv6 addresses back. Connecting directly to any of those will bypass the DNS lookup.

7

Fab, thanks. I tried putting the IP address as the value for the database client’s host parameter which unfortunately didn’t work.

It doesn’t seem like there’s a way to specify socket options for this library, so I might be stuck. I’ve opened a issue with the project, hopefully they’ll get back to me.

8

In the mean time is it possible to connect to PostgreSQL over an IPv4 public network? Similar to how Heroku and Render’s managed PostgreSQL?

It’s obviously a much worse solution, but I’m not sure how long it might before I’ll be able to use IPv6 here and it’d be grand to publish this Gleam + PostgreSQL + Fly demo app sooner rather than later.

9

How do you feel about running an haproxy process in your app VMs? If you launch haproxy with this config, you can connect to localhost:5432: postgres-ha/haproxy.cfg at main · fly-apps/postgres-ha · GitHub

Just make sure you change the $FLY_APP_NAME to your Postgres app name: postgres-ha/haproxy.cfg at main · fly-apps/postgres-ha · GitHub

You can expose Postgres over a public IP but we really, really don’t suggest it. You’re much better off running a proxy with your app processes if it’s easy.

1 Like
10

I think that’d be good for a real project, but for an example application I think it might distract from the content I actually want to show. I wouldn’t want to give the impression that you need to know how to install and configure haproxy to use this collection of technologies.

I think I may have to dig into the Erlang client (though not right now, I’m feeling a little under the weather :sleeping: )

Thanks again for the help!

11

Hello again!

We’ve got the database client connecting to a local postgresql instance listening on ::1, so IPv6 seems to be working.

Unfortunately I’m still not able to connect on Fly. I created an application using this Erlang client and also the Elixir Postgrex one and the Elixir one succeeded while the Erlang one didn’t. Could there be something else beyond IPv6 here?

Edit: I’ve added some more logging and discovered an error message that was being discarded

{error,{unknown_message,authentication,<<0,0,0,10,83,67,82,65,77,45,83,72,65,45,50,53,54,0,0>>}}

Where the binary decodes to

^@^@^@
SCRAM-SHA-256^@^@

I’ll go back to the library maintainers with this.

Is there a way to use older authentication methods with Fly’s PostgreSQL?

12

Huh, that’s surprising!

There might be a way to enabled older auth methods but it’ll take us a bit to look into it.

Do you know what they support? md5 or just plaintext?

13

Looks like most everything except SCRAM

Hopefully we’ll be able to bring it up to date soon. Tristan the maintainer seems keen but he is a very busy man!

14

Hey there,

So you should be able to change the password encryption back to md5 by performing the following:

# SSH into your one of your VM's. 
fly ssh console --app <app-name>

# Export required stolonctl env
export $(cat /data/.env | xargs)

# Reconfigure password_encryption through stolonctl
stolonctl update --patch '{"pgParameters": { "password_encryption": "md5"} }'

You can verify this by connecting to your cluster via psql and running:

postgres=# show password_encryption;
 password_encryption
---------------------
 md5
(1 row)

Note: This will only impact newly created users/roles.

1 Like
15

You champ, thank you!

I’m going to see how long it takes us to get SCRAM support, but if it takes too long I’ll take this route.

1 Like