Connecting via default unix socket without username/password

[phdru]

Hello! I have the following settings in my pg_hba.conf:

local   all             all                                     peer
host    all             all             127.0.0.1/32            md5

That allows me to connect to any database via unix socket without authentication, but when I connect via localhost I am asked for password.

I.e., I can run

$ createdb test
$ psql test
connected

and be connected. Please note that by default psql (libpq, perhaps?) connects via unix socket; also note psql doesn't require username. If I want to connect via localhost I have to do it explicitly:

$ psql -h localhost test
Password:

I can also connect via non-standard unix socket, the option is also -h host:

$ psql -h /run/postgresql test
connected

Only directory is required. Driver psycopg2 follows the conventions (perhaps because they are imposed by libpq and psycopg2 uses libpq):

con = psycopg2.connect(database="test")  # connected

con = psycopg2.connect(database="test", host='/run/postgresql')  # connected

Unfortunately, py-postgresql doesn't follow these conventions:

from postgresql.driver import dbapi20
con = dbapi20.connect(database="test")

Traceback (most recent call last):
  File "/home/phd/.tox/SQLObject/py34-pypostgresql/lib/python3.4/site-packages/postgresql/driver/pq3.py", line 2422, in connect
    self._establish()
  File "/home/phd/.tox/SQLObject/py34-pypostgresql/lib/python3.4/site-packages/postgresql/driver/pq3.py", line 2548, in _establish
    self.typio.raise_client_error(could_not_connect, creator = self, cause = exc)
  File "/home/phd/.tox/SQLObject/py34-pypostgresql/lib/python3.4/site-packages/postgresql/driver/pq3.py", line 514, in raise_client_error
    raise client_error
postgresql.exceptions.ClientCannotConnectError: could not establish connection to server
  CODE: 08001
  LOCATION: CLIENT
CONNECTION: [failed]
  failures[0]:
    SSL socket('127.0.0.1', 5432)
    postgresql.exceptions.AuthenticationSpecificationError: password authentication failed for user "phd"
      CODE: 28P01
      LOCATION: File 'auth.c', line 285, in auth_failed from SERVER
  failures[1]:
    socket('127.0.0.1', 5432)
    postgresql.exceptions.AuthenticationSpecificationError: password authentication failed for user "phd"
      CODE: 28P01
      LOCATION: File 'auth.c', line 285, in auth_failed from SERVER
CONNECTOR: [Host] pq://phd@localhost:5432/test
  category: None
DRIVER: postgresql.driver.pq3.Driver

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "./test_pypostgresql.py", line 5, in <module>
    con = dbapi20.connect(database="test")
  File "/home/phd/.tox/SQLObject/py34-pypostgresql/lib/python3.4/site-packages/postgresql/driver/dbapi20.py", line 409, in connect
    return driver.connect(**params)
  File "/home/phd/.tox/SQLObject/py34-pypostgresql/lib/python3.4/site-packages/postgresql/driver/pq3.py", line 3032, in connect
    c.connect()
  File "/home/phd/.tox/SQLObject/py34-pypostgresql/lib/python3.4/site-packages/postgresql/driver/dbapi20.py", line 354, in connect
    super().connect(*args, **kw)
  File "/home/phd/.tox/SQLObject/py34-pypostgresql/lib/python3.4/site-packages/postgresql/driver/pq3.py", line 2425, in connect
    self.close()
  File "/home/phd/.tox/SQLObject/py34-pypostgresql/lib/python3.4/site-packages/postgresql/driver/dbapi20.py", line 363, in close
    creator = self
postgresql.exceptions.Error: connection already closed
  LOCATION: CLIENT
CONNECTION: [failed]
  failures[0]:
    SSL socket('127.0.0.1', 5432)
    postgresql.exceptions.AuthenticationSpecificationError: password authentication failed for user "phd"
      CODE: 28P01
      LOCATION: File 'auth.c', line 285, in auth_failed from SERVER
  failures[1]:
    socket('127.0.0.1', 5432)
    postgresql.exceptions.AuthenticationSpecificationError: password authentication failed for user "phd"
      CODE: 28P01
      LOCATION: File 'auth.c', line 285, in auth_failed from SERVER
CONNECTOR: [Host] pq://phd@localhost:5432/test
  category: None
DRIVER: postgresql.driver.pq3.Driver

Seems like I'm connecting via localhost, not unix socket.

from postgresql.driver import dbapi20
con = dbapi20.connect(host=None, port=None,
    unix='/var/run/postgresql/.s.PGSQL.5432', database="test")  # connected

Can py-postgresql be fixed so that

con = dbapi20.connect(database="test")

connects via default unix socket, without username/password?

[jwp]

py-postgresql defaults to localhost:5432 for connection attempts as you can see from the connection failure message. The location of the socket directory is entirely subjective, often dependent on OS, and impossible for py-postgresql to know without the user designating it directly via explicit path or indirectly via extracting the information from some libpq installation which may or may not be even be reasonable to implement unless postgresql.org wants to make guarantees about the information's availability. This inconsistency with libpq based drivers does not end with localhost vs unix socket connections. PostgreSQL can be configured with a custom default port which py-postgresql will be completely unaware. It will always presume "5432" is the default port as that is the 'default default'.

If you want connection parameter consistency across drivers, I would suggest configuring your database to accept connections from localhost. That is the easy way that is possible without patching anything and waiting for a new release.

I know it's an annoying inconsistency, but considering the status quo, I don't think it's worth spending time trying to figure out a simple way to extract this information. To further illuminate the issue, if we were to extract the information from a libpq installation, the user would still need to designate which one and we might like to add some libpq detection code which will have to presume libpq will be available under its default name. Do we do this at compile/install time and somewhat pretend to be like a libpq based driver or at runtime using an environment variable or parameter? In the latter case, you still inherited an inconsistency.

I think it's also worth noting that libpq drivers may suffer from the same issue given the context of multiple inconsistently configured PostgreSQL installations.