I'm looking for the best way to connect to a Heroku Postgres database from an outside application. The application is in asp.net. I am trying to use NpgSQL but am getting a certificate unknown error. Has anyone done that? Do you have any pointers?
2
-
Hi. Welcome to SO, what have you at least tried? Can you give us some starting code to help you?acostela– acostela2016-05-17 12:58:28 +00:00Commented May 17, 2016 at 12:58
-
Is PostgreSQL running SSL=on?d1ll1nger– d1ll1nger2016-05-17 13:00:08 +00:00Commented May 17, 2016 at 13:00
Add a comment
|
2 Answers
I use this to "translate" the DATABASE_URL to a connection string
var databaseUrl = Environment.GetEnvironmentVariable("DATABASE_URL");
var databaseUri = new Uri(databaseUrl);
var userInfo = databaseUri.UserInfo.Split(':');
var builder = new NpgsqlConnectionStringBuilder
{
Host = databaseUri.Host,
Port = databaseUri.Port,
Username = userInfo[0],
Password = userInfo[1],
Database = databaseUri.LocalPath.TrimStart('/')
};
return builder.ToString();
1 Comment
ibrahimozgon
Thank you very much for the answer it worked for me! I just want to add, I needed to add to connect from my local to heroku server.
SslMode = SslMode.Require, TrustServerCertificate = trueI am using the following connection string to connect to Heroku Postgres Database:
string connString = "User ID=<user>;Password=<pass>;Host=<host>;Port=<port>;Database=<database>;Pooling=true;Use SSL Stream=True;SSL Mode=Require;TrustServerCertificate=True;"
where <user>, <pass>, <host>, <port> and <database> are values from heroku postgres app settings. Port is 5432 by default.
Then using NpgSql I create instance of NpgsqlConnection:
var conn = new NpgsqlConnection(connString);
Dafault sample: http://www.npgsql.org/doc/index.html
1 Comment
BenKoshy
For those reading in the future I highly recommend you obtain the database URI programmatically by using Heroku's Platform API - you can curl to get the database URI you'll have to pass in your authentication token as a header. @Set 's solution will work but is liable to break under certain circumstances.