How To Set ConnectionString For Postgres In c#-CodePudding

My database is postgresql, which is placed on a Linux server. Now I want to communicate with it through the C# program that is on the Windows operating system.

to connect to this database, there is a public encrypted file on the server and I have to send my private file to establish the connection.

That is, communication is done through a .ppk file and through SSH.

This is a simple connection string without ssh and sending a private file :

public static NpgsqlConnection connOldSamin = new NpgsqlConnection("Host=192.168.0.16;Port=5432;Database=sale;Username=postgres;Password=123;SSL Mode=Require;Trust Server Certificate=true");

But I don't know how to send my ppk file along with this string !!

CodePudding user response：

You will need to use a library that can handle SSH connections. Multiple available one of which is Renci.SshNet. Install the Nuget package and then you can use it to create an SSH client, connect to the Linux server using the .ppk file and your connection string, and then use that SSH client to create a tunnel to the PostgreSQL server.

using (var sclient = new SshClient("ssh.mydomain.com", 22, "user", new PrivateKeyFile("[path to your private key.ppk]")))
{
    sclient .Connect();

    using (var portForward = new ForwardedPortLocal("127.0.0.1", 5432, "192.168.0.22", 5432))
    {
        client.AddForwardedPort(portForward);
        portForward.Start();
        
        // use the localhost and port 5432 to connect to postgresql
        using (var conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;Database=sale;Username=[your userid];Password=[Your pass]"))
        {
            conn.Open();
            // your code here
            conn.Close();
        }

        portForward.Stop();
    }

    sclient .Disconnect();
}

CodePudding user response：

you can use a library such as SSH.NET

NuGet\Install-Package SSH.NET -Version 2020.0.2

and

using (var client = new SshClient("hostname", "username", new PrivateKeyFile("path/to/privatekey.ppk")))
{
    client.Connect();
    using (var conn = new NpgsqlConnection("Host=192.168.0.16;Port=5432;Database=sale;Username=postgres;Password=123;SSL Mode=Require;Trust Server Certificate=true"))
    {
        conn.Open();
        // execute queries, etc.
    }
    client.Disconnect();
}