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:

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

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? — Happy-Monad
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. — Michael Harris

kurtisvg kurtisvg · Accepted Answer · 2020-07-06T15:29:34

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:

Instance doesn't have a public IP (private IP connection steps are different)
Cloud SQL Admin API isn't enabled in the project App Engine is in
The service account (default is service-PROJECT_NUMBER@gae-api-prod.google.com.iam.gserviceaccount.com) used doesn't have the Cloud SQL Client IAM role or higher for the project the database is located in