0

I'm working on a c# xamarin mobile application in Visual Studio Community 2019. I have a Postgres 14 database running on localhost with testdata. Here is my class constructor and method for connecting to the db:

public static NpgsqlConnection GetConnection()
        {
            return new NpgsqlConnection(@"Host=localhost;Port=5432;Database=TestDB;User Id=UserId;Password=Password;");
        }

private static void TestConnection()
        {
            using (NpgsqlConnection con = GetConnection())
            {
                try
                {

                    con.Open();
                    if (con.State == ConnectionState.Open)
                    {
                        Console.WriteLine("Connected!");
                    }
                    else
                    {
                        Console.WriteLine("Connection Failed :(");
                    }
                }catch(Exception ex)
                {
                    Console.WriteLine(ex.ToString());
                }

Here is the full traceback when executed:

Npgsql.NpgsqlException (0x80004005): Failed to connect to 192.168.xxx.x:5432 ---> System.Net.Sockets.SocketException (0x80004005): Connection refused
  at Npgsql.Internal.NpgsqlConnector.Connect (Npgsql.Util.NpgsqlTimeout timeout) [0x00137] in <381bd97f3a0243528d3a176414e11f70>:0 
  at Npgsql.Internal.NpgsqlConnector.Connect (Npgsql.Util.NpgsqlTimeout timeout) [0x001b1] in <381bd97f3a0243528d3a176414e11f70>:0 
  at Npgsql.Internal.NpgsqlConnector.RawOpen (Npgsql.SslMode sslMode, Npgsql.Util.NpgsqlTimeout timeout, System.Boolean async, System.Threading.CancellationToken cancellationToken) [0x005bc] in <381bd97f3a0243528d3a176414e11f70>:0 
  at Npgsql.Internal.NpgsqlConnector.<Open>g__OpenCore|193_1 (Npgsql.Internal.NpgsqlConnector conn, Npgsql.SslMode sslMode, Npgsql.Util.NpgsqlTimeout timeout, System.Boolean async, System.Threading.CancellationToken cancellationToken) [0x00096] in <381bd97f3a0243528d3a176414e11f70>:0 
  at Npgsql.Internal.NpgsqlConnector.Open (Npgsql.Util.NpgsqlTimeout timeout, System.Boolean async, System.Threading.CancellationToken cancellationToken) [0x002aa] in <381bd97f3a0243528d3a176414e11f70>:0 
  at Npgsql.ConnectorPool.OpenNewConnector (Npgsql.NpgsqlConnection conn, Npgsql.Util.NpgsqlTimeout timeout, System.Boolean async, System.Threading.CancellationToken cancellationToken) [0x00162] in <381bd97f3a0243528d3a176414e11f70>:0 
  at System.Threading.Tasks.ValueTask`1[TResult].get_Result () [0x0001b] in /Users/builder/jenkins/workspace/archive-mono/2020-02/android/release/external/corefx/src/Common/src/CoreLib/System/Threading/Tasks/ValueTask.cs:813 
  at Npgsql.ConnectorPool.<Get>g__RentAsync|27_0 (Npgsql.NpgsqlConnection conn, Npgsql.Util.NpgsqlTimeout timeout, System.Boolean async, System.Threading.CancellationToken cancellationToken) [0x0008d] in <381bd97f3a0243528d3a176414e11f70>:0 
  at System.Threading.Tasks.ValueTask`1[TResult].get_Result () [0x0001b] in /Users/builder/jenkins/workspace/archive-mono/2020-02/android/release/external/corefx/src/Common/src/CoreLib/System/Threading/Tasks/ValueTask.cs:813 
  at Npgsql.NpgsqlConnection.<Open>g__OpenAsync|45_0 (System.Boolean async, System.Threading.CancellationToken cancellationToken) [0x0025f] in <381bd97f3a0243528d3a176414e11f70>:0 
  at Npgsql.NpgsqlConnection.Open () [0x00012] in <381bd97f3a0243528d3a176414e11f70>:0 
  at App.LoginPage.TestConnection () [0x00009] in C:\Users\User\source\repos\App\App\App\LoginPage.xaml.cs:54

I'm able to connect to this db just fine using the same credentials in a python test script I constructed using psycopg2. Port 5432 is confirmed as listening. I added host all all 0.0.0.0/0 trust to the bottom of my pg_hba.conf file, as suggested elsewhere (for testing purposes only). Seems to me like it must be something unique to visual studio, though I can't seem to figure out what.

Any suggestions or input would be most appreciated.

RMA Dev
  • 166
  • 5
  • Looks to me you are using the wrong connection parameter, in particular `User Id`. See [Parameters](https://www.npgsql.org/doc/connection-string-parameters.html). Also look at [Getting started](https://www.npgsql.org/doc/index.html) – Adrian Klaver Jan 10 '22 at 18:27
  • Thanks for the reply. User Id is a known valid connection parameter, interchangeable with Username https://www.connectionstrings.com/npgsql/ – RMA Dev Jan 10 '22 at 18:36
  • From [Parameters](https://www.npgsql.org/doc/connection-string-parameters.html): 'Below are the connection string parameters which Npgsql understands, ...'. `User Id` is not one of them. Or you can believe random Web sites. How hard would it be to change to `Username` to test? – Adrian Klaver Jan 10 '22 at 18:46
  • No, changing it to Username doesn't change anything. I did that very early on in my debugging. – RMA Dev Jan 10 '22 at 18:48
  • It seems like connection is denied on purpose. Is `192.168.xxx.x` equal to `localhost`? As per this answer: https://stackoverflow.com/questions/32190881/how-to-solve-err-connection-refused-when-trying-to-connect-to-localhost-running, it is worth to make sure you create Virtual Directory in project properties for your url. Have you tried running Visual Studio as Administrator, it oftentimes is required to work correctly with databases. – Korfu Jan 10 '22 at 18:54

1 Answers1

0

I got this figured out. In case anyone else runs into the same issue:

When trying to connect via the localhost parameter within Visual Studio, it kept failing due to localhost defaulting to the last IP within the IP block for some reason, rather than the specific IP address running the postgresql service. I had to hard wire my specific local IPv4 address into the connection query rather than using localhost

RMA Dev
  • 166
  • 5