LibPQ and local peer trust ("ident")

[octonion]

LibPQ doesnt' seem to allow connections via local peer trust ("ident").

i.e., this fails:

using LibPQ
conn = LibPQ.Connection("dbname=rugby")

Here the database "rugby" is owned by me, and the PostgreSQL server uses "ident". The same connection, however, works with Julia's ODBC and Python's Psycopg 3.

[iamed2]

Can you please post the error you see? And can you add some details about the environment (e.g. OS, PostgreSQL server version, the auth method specified in pg_hba.conf)?

Does it work in Python's psycopg2? With psql? Do you have any PG____ environment variables set?

[octonion]

Using

using LibPQ
conn = LibPQ.Connection("dbname=rugby")

I get

[error | LibPQ]: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?
ERROR: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?
Stacktrace:
 [1] error(logger::Memento.Logger, exc::LibPQ.Errors.PQConnectionError)
   @ Memento ~/.julia/packages/Memento/h8kge/src/loggers.jl:463
 [2] handle_new_connection(jl_conn::LibPQ.Connection; throw_error::Bool)
   @ LibPQ ~/.julia/packages/LibPQ/6itxv/src/connections.jl:127
 [3] LibPQ.Connection(str::String; throw_error::Bool, connect_timeout::Int64, options::Dict{String, String}, kwargs::Base.Pairs{Symbol, Union{}, Tuple{}, NamedTuple{(), Tuple{}}})
   @ LibPQ ~/.julia/packages/LibPQ/6itxv/src/connections.jl:320
 [4] LibPQ.Connection(str::String)
   @ LibPQ ~/.julia/packages/LibPQ/6itxv/src/connections.jl:265
 [5] top-level scope
   @ REPL[2]:1

This is on Linux Ubuntu 22.04, PostgreSQL 14.5 and Julia 1.8.0. No environmental variables related to PostgreSQL are set. The authentication method in pg_hba.conf is peer.

local   all             all                                     peer

Works as expected with "psql rugby" on the command line, Python with Psycopg 3, and Julia and ODBC using the following

using ODBC, DataFrames
ODBC.adddriver("PostgreSQL Unicode", "/usr/lib/x86_64-linux-gnu/odbc/psqlodbcw.so";)
conn = ODBC.Connection("Driver={PostgreSQL Unicode};Database=rugby")
df = DBInterface.execute(conn, "select * from nrl.games;") |> DataFrame

I can get LibPQ.jl to work if I create a .pgpass and connect via

using LibPQ
conn = LibPQ.Connection("dbname=rugby host=localhost")

[iamed2]

Great, thanks, that was enough information to replicate!

Replication steps for my future reference:

• used ubuntu:jammy docker image
• apt install postgresql
• su postgres -c '/usr/lib/postgresql/14/bin/pg_ctl start -D /etc/postgresql/14/main'
• su - postgres then download Julia and test

The difference in behaviour comes from using a libpq built by Ubuntu (as all the other libraries are doing) vs one built remotely with BinaryBuilder and distributed with LibPQ.jl. There is a build option to set the default UNIX domain socket location, and Ubuntu sets it to where Ubuntu puts those things (/var/run/postgresql), while in BinaryBuilder we use the default (/tmp).

You can pass host=/var/run/postgresql as part of your DSN to connect using the socket and peer auth.

[octonion]

Thank you! This works perfectly.