Unable to connect to postgres in Google Cloud SQL from App Engine running a .NET Core app in Docker with Custom runtime

Question

I've tried numerous things, and can't seem to get past an Exception while connecting when trying to connect to a Google Cloud PostgreSQL instance from my Google Cloud App Engine.

This is probably the most frustrating thing I've ever dealt with as a developer. It shouldn't be this difficult to connect to a database.

What am I doing wrong?

Things that didn't work:

• How to connect Google Cloud SQL via Google App engine Flex to botnet core app?
• Unable to connect to postgres in cloud sql from google app engine nodes
• https://cloud.google.com/sql/docs/postgres/connect-app-engine-flexible
• https://github.com/GoogleCloudPlatform/dotnet-docs-samples/tree/master/appengine/flexible/CloudSql

app.yaml:

runtime: custom
env: flex
beta_settings:
  cloud_sql_instances: "<project-id>:<region>:<sql-instance>=tcp:5432"

eventual connection string used:

Uid=<db_user>;Pwd=<db_password>;Host=cloudsql;Database=<db_name>

// other attempts:
Uid=<db_user>;Pwd=<db_password>;Host=cloudsql;Database=<db_name>;Port=5432
Uid=<db_user>;Pwd=<db_password>;Host=/cloudsql/<project-id>:<region>:<sql-instance>;Database=<db_name>
Uid=<db_user>;Pwd=<db_password>;Host='/cloudsql/<project-id>:<region>:<sql-instance>';Database=<db_name>
Uid=<db_user>;Pwd=<db_password>;Server='/cloudsql/<project-id>:<region>:<sql-instance>';Database=<db_name>

Usage:

var connectionString = new NpgsqlConnectionStringBuilder(<connection string>)
{
    SslMode = SslMode.Disable
};

NpgsqlConnection connection =
    new NpgsqlConnection(connectionString.ConnectionString);

connection.Open();

Relevant Stack Trace:

Exception while connecting
   at Npgsql.NpgsqlConnector.Connect(NpgsqlTimeout timeout)
   at Npgsql.NpgsqlConnector.RawOpen(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.ConnectorPool.AllocateLong(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnection.<>c__DisplayClass32_0.<g__OpenLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnection.Open()

Dockerfile (maybe it matters since I'm using custom in my app.yaml runtime?):

FROM mcr.microsoft.com/dotnet/core/aspnet:3.1-buster-slim AS base
WORKDIR /app
EXPOSE 80
EXPOSE 8080
EXPOSE 443
ENV ASPNETCORE_URLS=http://*:8080

FROM mcr.microsoft.com/dotnet/core/sdk:3.1-buster AS build
COPY . /src
WORKDIR /src
RUN dotnet restore --packages /packages
RUN dotnet publish -c Release -o /published

FROM base AS final
COPY --from=build /published /app/
WORKDIR /app
ENTRYPOINT [ "dotnet", "myapp.dll" ]

Deploy script:

gcloud beta app deploy --project <project-id>

EDIT:

Checking the PostgreSQL errors from the cloud console for my sql instance, I see the following error just before the final Exception while connecting error:

System.Net.Internals.SocketExceptionFactory+ExtendedSocketException (99): Cannot assign requested address /cloudsql/<my connection instance id>/.s.PGSQL.5432

Rumrunner

Just to rule out a few common issues: Have you checked that the SQL Admin API is enabled? Also does the App Engine service account has permissions outlined in the docs? Have you experienced the same issue with the aspnet GCP provided runtime instead of the custom one? – Sporophore

Yes, SQL Admin API is enabled for the project. To rule out the App Engine service account permissions, I currently have the Cloud SQL Client role assigned to every account listed in my IAM listing. I've also tried with the Cloud SQL Admin role. Finally, hoping that it was an issue of using the custom runtime instead of aspnetcore runtime in my app.yaml, I also changed it to use aspnetcore, but still saw the same result. – Rumrunner

Answer 1

Have you taken a look at the sample applications for Cloud SQL here?

Update:

It's hard to say for sure what is wrong since you've provided a couple of different options, and it's unclear which combinations are providing which errors. However, there are essentially 2 different ways to connect and it's important your code matches the configuration in your app.yaml:

Connecting via TCP port

To connect via a TCP port, make use the following in your app.yaml:

beta_settings:
  cloud_sql_instances: "<PROJECT_ID>:<REGION_ID>:<INSTANCE_ID>=tcp:5432"

Then use the following format for your connection string:

"Host=172.17.0.1;Uid=<DB_USER>;Pwd=<DB_PASS>;Database=<DB_NAME>"

Notice that Host=172.17.0.1; is the IP when deployed to Flex, but will be 127.0.0.1 if using the Cloud SQL proxy locally.

Connecting via Unix Domain Socket

To connect via a Unix socket, make use the following in your app.yaml:

beta_settings:
  cloud_sql_instances: "<PROJECT_ID>:<REGION_ID>:<INSTANCE_ID>"

Then use the following format for your connection string:

"Server=/cloudsql/<PROJECT_ID>:<REGION_ID>:<INSTANCE_ID>/.s.PGSQL.5432;Uid=<DB_USER>;Pwd=<DB_PASS>;Database=<DB_NAME>"

Note: Your driver might automatically add the /.s.PGSQL.5432 - some do, some don't.

Troubleshooting

If you are sure your app.yaml and code match correctly but are still getting timeout messages, the next step would be to check your applications logs. You can use Stackdriver and filter by appengine.googleapis.com/cloud_sql_proxy to see just the logs for your instance.

Some common mistakes are:

1. Instance doesn't have a public IP (private IP connection steps are different)
2. Cloud SQL Admin API isn't enabled in the project App Engine is in
3. The service account (default is [e-mail]) used doesn't have the Cloud SQL Client IAM role or higher for the project the database is located in

Mosqueda

Yes. After adding in my specific details to the connection string (Uid, Pwd, Database) found in appsettings.json, and leaving Host=cloudsql;, I get an error that says Cannot find service or name. If I qualify the value for Host, like so: Host=/cloudsql/<project-id>:<region>:<sql-instance>;, then I get the same error mentioned in the original post. – Rumrunner

@MichaelHarris - I've added some additional context that might be helpful. Please let me know if that's helpful. – Mosqueda

Answer 2

After quintuple-checking that I was following all the steps given in kurtisvg's answer and still making no progress, the solution for me turned out to be as simple as stopping the App Engine Version and starting it back up again. Presumably whatever settings I had changed to fix the issue weren't applied until the version was restarted.

Banger