How can I call a postgresql db stored in DigitalOcean from ASP.NET Core web api?

Question

I'm tying to use the connection string provided by DigitalOcean directly on the appsettings.json file, but shows error

ArgumentException: Keyword not supported: postgresql://XXXXX_user:xxxxxxxxxxxxx@db-trackbus-nyc3-xxxx-do-user-xxxxx-0.b.db.ondigitalocean.com:xxxxx/trackbus?sslmode (Parameter 'keyword') Npgsql.NpgsqlConnectionStringBuilder.GetProperty(string keyword)

This is the exact connection string provided by DigitalOcean:

postgresql://xxxx_user:xxxxx@db-trackbus-nyc3-xxxx-do-user-xxxxx-0.b.db.ondigitalocean.com:xxxxx/TrackBus?sslmode=require

I'm using Npgsql nuget package, when the db was local was working fine using th regular connection string

"Server=xxx.0.0.x;Port=xxxx;Database=TrackBus;User Id=postgres;Password=xxxx;"

I've also tried this (private and public):

"Server=private-db-trackbus-nyc3-xxxxx-do-user-xxxxx-0.b.db.ondigitalocean.com;Port=xxxxx;Database=TrackBus;User Id=xxxxx_user;Password=xxxxxxxxxx;Integrated Security=true;"

Error:

An unhandled exception occurred while processing the request. PostgresException: 28000: no pg_hba.conf entry for host "xxx.xxx.xxx.xxx", user "xxxx", database "TrackBus", SSL off

I searched this last 28000 exception but couldn't apply any solution found.

Estefania Hernandez · Accepted Answer · 2021-05-19 19:01:15Z

1

The connection string provided by DigitalOcean is for CLI:

postgresql://xxxx_user:xxxxx@db-trackbus-nyc3-xxxx-do-user-xxxxx-0.b.db.ondigitalocean.com:xxxxx/TrackBus?sslmode=require

You have to convert it to the format that appsettings.json uses and it will be like this:

"Server=db-trackbus-nyc3-xxxx-do-user-xxxxx-0.b.db.ondigitalocean.com;Port=xxxxx;Database=TrackBus;User Id=xxxxxxxx;Password=xxxxxxxxx;sslmode=Require;Trust Server Certificate=true;"

The sslmode=Require;Trust Server Certificate=true; is super important because the managed database in DigitalOcean has it that that one.

Also remember that all postgresql database by default are blocking remote connection, you have to add the permission on the pg_hba.conf file from the Postgresql installation folder.

answered May 19, 2021 at 19:01

Estefania Hernandez

2682 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

How can I call a postgresql db stored in DigitalOcean from ASP.NET Core web api?

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