CODEWITHSUNDEEP
c#postgresqldockergoogle-app-enginegoogle-cloud-sql
Michael Harris
Michael Harris

Reputation: 265

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

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

Upvotes: 7

Views: 3068

Answers (2)

Ben Sutton
Ben Sutton

Reputation: 202

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.

Upvotes: 0

kurtisvg
kurtisvg

Reputation: 3565

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 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

Upvotes: 4

Related Questions