Cannot connect to Postgres in my app

Questions / Help
1

I have an ASP.NET app, and to build a connection string from DATABASE_URL variable I used a following code:

var connUrl = Environment.GetEnvironmentVariable("DATABASE_URL");

                // Parse connection URL to connection string for Npgsql
                connUrl = connUrl.Replace("postgres://", string.Empty);
                var pgUserPass = connUrl.Split("@")[0];
                var pgHostPortDb = connUrl.Split("@")[1];
                var pgHostPort = pgHostPortDb.Split("/")[0];
                var pgDb = pgHostPortDb.Split("/")[1].Split("?")[0];
                var pgUser = pgUserPass.Split(":")[0];
                var pgPass = pgUserPass.Split(":")[1];
                var pgHost = pgHostPort.Split(":")[0];
                var pgPort = pgHostPort.Split(":")[1];

                connString = $"Server={pgHost};Port={pgPort};Username={pgUser};Password={pgPass};Database={pgDb};";

so it looks like something like this: “Server=app-name-db.flycast;Port=5432;Username=my-username;Password=my-password;Database=db-name;”, However when i run my application, and need to fetch data from database, there is an error in browser console

GET 500 (Internal Server Error)

After looking through logs in dashboard i found some errors:

2023-11-07T14:44:03.478 app[918571e9b30d48] waw [info] fail: api.Program[0]

2023-11-07T14:44:03.478 app[918571e9b30d48] waw [info] An error occured during migration.

2023-11-07T14:44:03.478 app[918571e9b30d48] waw [info] Npgsql.NpgsqlException (0x80004005): Exception while performing SSL handshake

2023-11-07T14:44:03.478 app[918571e9b30d48] waw [info] ---> System.IO.IOException: Received an unexpected EOF or 0 bytes from the transport stream.

2023-11-07T14:44:06.167 app[918571e9b30d48] waw [info] fail: Microsoft.EntityFrameworkCore.Database.Connection[20004]

2023-11-07T14:44:06.167 app[918571e9b30d48] waw [info] An error occurred using the connection to database 'db-name' on server ''.

2023-11-07T14:44:06.178 app[918571e9b30d48] waw [info] fail: Microsoft.EntityFrameworkCore.Query[10100]

2023-11-07T14:44:06.178 app[918571e9b30d48] waw [info] An exception occurred while iterating over the results of a query for context type 'api.Data.DataContext'.

2023-11-07T14:44:06.178 app[918571e9b30d48] waw [info] System.InvalidOperationException: An exception has been raised that is likely due to a transient failure.

If I use fly postgres connect -a <postgres-app-name> it works fine.

So what could be the issue?

2

Was the server string empty in the original, or was that anonymization applied afterward?

3

No, server string was not empty. I parsed it from DATABASE_URL, and it was app-name-db.flycast

4

How about connecting via psql from an ephemeral machine?

Along the lines of…

$ fly console -a app-name  # without the "-db" suffix.
root@fabc# apt-get update
root@fabc# apt-get install postgresql-client
root@fabc# psql "$DATABASE_URL"

(fly postgres connect is a little special, in that it connects from within the app-name-db machine itself—after SSHing in—instead of from one of the app-name nodes. This other approach is closer to what the latter would see.)

5

It worked and I connected to the database. So does it mean that the problem is not in DATABASE_URL?

6

Right, and it also confirms that your database machine can accept connections from outside its own loopback device. (The log excerpt suggested that it might be otherwise.)

My guess is that your connString is either unexpectedly ending up with an empty string in one of its components or is missing a flag to turn off SSL/TLS (i.e., a knob analogous to the ?sslmode=disable suffix in the URL style).

2 Likes
7

You are right, after adding SSL Mode=Disable; to the end of the connection string, it works. Thank you a lot!

8

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