Npgsql connection string for local Postgres

Question

Good afternoon.

I'm having troubles getting connected to a Postgres database.

The app I'm working on has to run on .NET 4. I'm using Npgsql, and because I'm limited to .NET 4, I'm using Npgsql version 2.2.7 (I believe 3+ requires .NET 4.5).

The application will be running on the same machine as Postgres is. The database is installed and set up by a third party, and for that reason I'm unable to change the pg_hba.conf file.

My first stab at a connection string looked like this:

Server=localhost;Database=xyz;Integrated Security=true;

But I got this error:

FATAL: 28000: no pg_hba.conf entry for host "::1", user "SYSTEM", database "xyz", SSL off

Researched that error, and tried numerous other variations of the connection string to fix this, including:

Server=127.0.0.1;Database=xyz;Integrated Security=true;
Server=-h 127.0.0.1;Database=xyz;Integrated Security=true;
Server=127.0.0.1/32;Database=xyz;Integrated Security=true;
Server=::1;Database=xyz;Integrated Security=true;
Server=::1/128;Database=xyz;Integrated Security=true;

But nothing works. I either get the

FATAL: 28000 ...

error, or a simple

Failed to establish a connection to ...

The pg_hba.conf file looks like this:

host all postgres 127.0.0.1/32  trust
host all xyz      127.0.0.1/32  trust
host all xyz      ::1/128       trust
host all postgres ::1/128       trust

This is all running on a Windows 7 machine, and IPv6 is turned off on the network connection.

It's probably something simple, but what can I do? I can't change the pg_hba.conf file, so how can I tweak my connection string to work?

Solution 1

Try this: "Server=[your server];Port=[your port];Database=[your database];User ID=[your user];Password=[your password];"

For example: "Server=localhost;Port=5432;Database=BookStore;User ID=operator;Password=1234;"

Solution 2

at the beginning of your pg_hba.conf file add (the order of entries is important!):

  # Type    database      users      auth.method
  local     xyz           all        sspi

If you don't want user any password to connections. (For more info check: https://www.postgresql.org/docs/9.1/static/auth-methods.html#SSPI-AUTH https://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html (auth-method -> sspi))

Secound option is add to your ConnectionString as @Cecilia Fernández says. But then you need to add to your pg_hba.conf file:

  # Type    database      users      auth.method
  local     xyz           all        md5