Connect to local PostgreSQL server on Linux

Question

How can I connect to a local PostgreSQL database server on Ubuntu Linux from a .NET Core application with Npgsql?

I'm using this connection string on Windows:

Server=localhost;User ID=webdev;Password=webdev;Database=dbname

I'm using this shell command on Linux:

psql dbname

Now I've tried with this connection string on Linux:

Server=/var/run/postgresql;Database=dbname

But I got this error:

Cannot assign requested address /var/run/postgresql/.s.PGSQL.5432

If I try this connection string on Linux:

Server=localhost;Database=dbname

Then I get this error:

Connection refused

How does this work? I'd like to take advantage of password-less login from the same local system account.

Postgres can't write to that socket. You need to use 127.0.0.1 or localhost and update pg_hba to accept connection from there with trust. You can also use peer if there is a user locally. — 123
The psql command can connect to the server locally just fine. Why can't Npgsql do the same? — ygoe
I don't know what Npgsql is, can you post the contents of pg_hba? Is connection refused the only thing it says? I'd expect the reason to also be shown, i.e the host,username and connection method — 123
@123 Npgsql is the .NET connector to Postgres. See the question's tags. It throws an exception of which I posted the only relevant information. — ygoe
@VaoTsun The .NET app is running as the same user as I can run psql and by whom the database is owned. I don't know whether the socket path is correct. psql --help told it to me as its default, so I took it. Actually I'd rather not specify anything at all, just like for psql. But Npgsql documents that the Host/Server parameter is required. — ygoe

ygoe ygoe · Accepted Answer · 2017-12-05T15:46:55

I haven't changed the port on which PostgreSQL listens. And I wouldn't have thought that the TCP port has anything to do with the local UNIX socket connection. But in Postgres it does, and the package maintainers at Postgres for their official Ubuntu package have changed the port in their config from 5432 to 5433. I have no idea why, but I've corrected that in /etc/postgresql/10/main/postgresql.conf and restarted the server:

port = 5432

Now I can connect to the server with the intended connection string. And with psql and with psql -h /var/run/postgresql as mentioned in the comments.

This seems to be a bug in the package configuration. Easy to fix, hard to find.

Connect to local PostgreSQL server on Linux

1 Answers