My system user is milosz
, which is mapped to the PostgreSQL user project_great
in pg_ident.conf
. I am using peer authentication to connect to a PostgreSQL database over a unix domain socket. This connection method works when using psql
, but fails to work when using py-postgresql
using the same parameters from within a Python script.
Here I am successfully connecting to the database using psql
:
$ psql -U project_great \
> -d project_great \
> -h /var/run/postgresql
psql (9.3.4)
Type "help" for help.
project_great=>
Here is database_test.py
:
#!/usr/bin/env python3
import postgresql
params = {
'user': 'project_great',
'database': 'project_great',
'unix': '/var/run/postgresql',
}
connection = postgresql.open(**params)
Here I am attempting to connect to the database by running ./database_test.py
:
$ ./database_test.py
Traceback (most recent call last):
File "./database_test.py", line 11, in <module>
sys.exit(main(sys.argv))
File "./database_test.py", line 13, in main
connection = postgresql.open(**params)
File "/home/milosz/devel/project_great/.virtualenv/lib/python3.3/site-packages/postgresql/__init__.py", line 94, in open
c.connect()
File "/home/milosz/devel/project_great/.virtualenv/lib/python3.3/site-packages/postgresql/driver/pq3.py", line 2422, in connect
self._establish()
File "/home/milosz/devel/project_great/.virtualenv/lib/python3.3/site-packages/postgresql/driver/pq3.py", line 2548, in _establish
self.typio.raise_client_error(could_not_connect, creator = self, cause = exc)
File "/home/milosz/devel/project_great/.virtualenv/lib/python3.3/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]:
socket'/var/run/postgresql'
Traceback (most recent call last):
File "/home/milosz/devel/project_great/.virtualenv/lib/python3.3/site-packages/postgresql/protocol/client3.py", line 136, in connect
self.socket = self.socket_factory(timeout = timeout)
File "/home/milosz/devel/project_great/.virtualenv/lib/python3.3/site-packages/postgresql/python/socket.py", line 64, in __call__
s.connect(self.socket_connect)
PermissionError: [Errno 13] Permission denied
The above exception was the direct cause of the following exception:
postgresql.exceptions.ConnectionRejectionError: Permission denied
CODE: 08004
LOCATION: CLIENT
CONNECTOR: [Unix] pq://project_great@[unix::var:run:postgresql]/project_great
category: None
DRIVER: postgresql.driver.pq3.Driver
Since the parameters to the two connections are ostensibly the same and my permissions on the socket and its containing directory are fairly open, I do not know what the issue is. Using TCP is not a solution; I want to use unix domain sockets. The py-postgresql documentation indicates that connecting using unix domain sockets should work.
pg_hba.conf:
# TYPE DATABASE USER ADDRESS METHOD OPTION
local all all peer map=default
pg_ident.conf:
# MAPNAME SYSTEM-USERNAME PG-USERNAME
default postgres postgres
default milosz project_great
postgresql.conf:
...
port = 5432
unix_socket_directories = '/var/run/postgresql'
...
Here are the permissions on my socket directory:
$ ll /var/run/postgresql/
total 8
drwxrwsr-x 2 postgres postgres 100 May 17 00:20 ./
drwxr-xr-x 31 root root 900 May 17 00:41 ../
-rw-r--r-- 1 postgres postgres 5 May 17 00:20 9.3-main.pid
srwxrwxrwx 1 postgres postgres 0 May 17 00:20 .s.PGSQL.5432=
-rw------- 1 postgres postgres 70 May 17 00:20 .s.PGSQL.5432.lock
The PostgreSQL user project_great
has been granted all privileges on the database project_great
and both the user and the database exist.
I do not have a ~/.pgpass
.
First, double check that the Postgres process is running where you expect it to be. If you are trying to connect to a Postgres instance on the same host as your terminal, you can run lsof -p :5432 which will show which, if any, processes are listening on that port. The postgres process should be connected there.
psql is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, input can be from a file or from command line arguments.
PostgreSQL uses a message-based protocol for communication between frontends and backends (clients and servers). The protocol is supported over TCP/IP and also over Unix-domain sockets.
Craig Ringer suggested to run both programs under strace
to find out if there is a relevant difference in the system calls. I grepped for /var/run/postgresql
and found that while psql
ran connect
like so:
connect(4, {sa_family=AF_LOCAL, sun_path="/var/run/postgresql/.s.PGSQL.5432"}, 110) = 0
./database_test.py
ran connect
like so:
connect(4, {sa_family=AF_LOCAL, sun_path="/var/run/postgresql"}, 21) = -1 EACCES (Permission denied)
wherein lay the issue.
While psql
expects the path to the directory in which the unix domain socket is located, py-postgresql
expects the full path to the socket. Therefore, the fix was to modify database_test.py
to read like so:
#!/usr/bin/env python3
import postgresql
params = {
'user': 'project_great',
'database': 'project_great',
'unix': '/var/run/postgresql/.s.PGSQL.5432',
}
connection = postgresql.open(**params)
Honestly, I feel silly for not having tried the full path as an earlier debugging step!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With