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();
}

Use SQL Server Management Studio and see if you can connect to the database? Check the login windows to see if Windows Credentials is used or a Username/Password is used. Most case you are using a Windows credential. So the connection string doesn't have Username/Password and instead use Integrated Security = True. The port number is not needed if you are using standard port number 1433. The connection string should include both the server IP and instance like in the login window.for SSMS — jdweng
– jdweng, Commented Aug 26, 2020 at 14:40
This simply seems like a connection error. Please include more details. Where does your Postgres run? How are you connecting it (over public IP or through VPC)? Does it work locally on your machine? — ahmet alp balkan
– ahmet alp balkan, Commented Aug 27, 2020 at 4:31
My Postgres runs on a Google Cloud SQL (both are in the same project), it is connecting to a public IP with authentification. I can't run it on my computer because the project uses docker and my company blocks me from using it — Adraekor
– Adraekor, Commented Aug 27, 2020 at 7:31

lukaszberwid · Accepted Answer · 2020-08-27 09:40:15Z

2

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

answered Aug 27, 2020 at 9:40

lukaszberwid

1,1879 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related