Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

psql can connect to a unix domain socket, but py-postgresql with the same parameters gets 'Permission denied'

Problem description:

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.

Configuration:

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.

Environment:

  • Ubuntu 13.10
  • Python 3.3
  • PostgreSQL 9.3
  • py-postgresql 1.1.0
like image 875
Milosz Avatar asked May 17 '14 05:05

Milosz


People also ask

Can't connect to PostgreSQL database?

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.

What is the psql command?

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.

Is Postgres connection TCP?

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.


1 Answers

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!

like image 116
Milosz Avatar answered Oct 12 '22 22:10

Milosz