Can't connect to postgres instance from .net app running on a GCP Cloud Run

Question

I have an app running in a GCP cloud run instance, and it uses EF Core mapped on a pgsql db, but I can't connect to the database for some reason

Here's the code used to connect to the database :

Startup.cs :

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<postgresContext>(options =>
        options.UseNpgsql(SecretManagerAccessor.GetConnectionString())
    );
}

Controller :

public MyController(IConfiguration configuration, postgresContext context, IMapper mapper)
{
    _configuration = configuration;
    _context = context;
    _mapper = mapper;
}

[HttpPost]
public IActionResult Post([FromBody] Body body)
{
    try
    {
        var repo = new SourceRepository(_context);
        var sources = repo.GetAll();
        foreach (var source in sources)
        {
            Console.WriteLine($"{source.SrcId} : {source.SrcInfo}");
        }
    }
    catch (Exception e)
    {
        Console.WriteLine(e);
    }
    
    return Ok();
}

And finally SourceRepository :

private readonly postgresContext _db;
private readonly DbSet<Source> _source;

public SourceRepository(postgresContext db)
{
    _db = db;
    _source = db.Set<Source>();
}

public IEnumerable<Source> GetAll()
{
    return _source;
}

I tried 2 types of connection string :

Server=xx.xx.xxx.xx;Port=5432;Database=MyDb;Username=username;Password=pass using this website

and Host=xx.xx.xxx.xx;Database=MyDb;Username=username;Password=pass using this documentation

The strings are hosted in GCP's Secret Manager, and I used a Database First approach to generated the store objects and the context. The Cloud Run service account has a "Cloud SQL Admin" permission (even if it should work with Client level).

The error I get is this :

A 2020-08-26T13:58:49.877242Z An error occurred using the connection to database 'MyDb' on server 'tcp://xx.xx.xxx.xxx:5432'. 
A 2020-08-26T13:58:49.877242Z System.InvalidOperationException: An exception has been raised that is likely due to a transient failure. 
A 2020-08-26T13:58:49.877286Z  ---> Npgsql.NpgsqlException (0x80004005): Exception while connecting 
A 2020-08-26T13:58:49.877295Z  ---> System.TimeoutException: Timeout during connection attempt 
A 2020-08-26T13:58:49.877485Z    at Npgsql.NpgsqlConnector.Connect(NpgsqlTimeout timeout) 
A 2020-08-26T13:58:49.877514Z    at Npgsql.NpgsqlConnector.Connect(NpgsqlTimeout timeout) 
A 2020-08-26T13:58:49.877528Z    at Npgsql.NpgsqlConnector.RawOpen(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken) 
A 2020-08-26T13:58:49.877540Z    at Npgsql.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken) 
A 2020-08-26T13:58:49.877553Z    at Npgsql.ConnectorPool.AllocateLong(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken) 
A 2020-08-26T13:58:49.878103Z    at Npgsql.NpgsqlConnection.<>c__DisplayClass32_0.<<Open>g__OpenLong|0>d.MoveNext() 
A 2020-08-26T13:58:49.878128Z --- End of stack trace from previous location where exception was thrown --- 
A 2020-08-26T13:58:49.878136Z    at Npgsql.NpgsqlConnection.Open() 
A 2020-08-26T13:58:49.878145Z    at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(Boolean errorsExpected) 
A 2020-08-26T13:58:49.878152Z    at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected) 
A 2020-08-26T13:58:49.878161Z    at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject) 
A 2020-08-26T13:58:49.878170Z    at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result) 
A 2020-08-26T13:58:49.878180Z    at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded) 
A 2020-08-26T13:58:49.878188Z    --- End of inner exception stack trace --- 
A 2020-08-26T13:58:49.878197Z    at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded) 
A 2020-08-26T13:58:49.878203Z    at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext() 
A 2020-08-26T13:58:49.878211Z    at MyProject.Controllers.MyController.Post(Body body) in /app/Controllers/MyController.cs:line 62 

I simplified the test by using this code instead, still have the same issue though :

        //GET : api/MyController
[HttpGet]
public IActionResult Get()
{
    Console.WriteLine("Get route called");
    try
    {
        _context.Database.CanConnect();
    }
    catch (Exception e)
    {
        Console.WriteLine(e);
    }

    return Ok();
}

Answer 1

You can use flag --add-cloudsql-instances when creating instance. Cloud Run will activate and configure the Cloud SQL proxy for you.

Connection string should look similar to this one "Server=/cloudsql/your-project-id:us-central1:instance-name;Uid=aspnetuser;Pwd=;Database=votes"

You can take a look at the documentation

There is also a repo showing how to build C# application on Cloud Run with Postgres repo