Can't connect to postgres server over SSH through python

Question

I'm trying to SSH into a server and connect to an instance of postgres running on it. I'm able to SSH into the server and access the postgres server via termianl, and the port is 5432 in pg_settings. If it makes any difference, after SSH'g into the server with SSH_USER I have to switch users ('sudo su - postgres').

The error:

psycopg2.OperationalError: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

The code:

SSH_HOST = 'xxx.xxx.xxx.xxx'
SSH_FOREIGN_PORT = 22
SSH_USER = 'user1'
SSH_PASS = 'pass'
PORT = 5432
DBNAME = 'testdb'
USER = 'postgres'
PASS = ''

def main_psql():
    #try:
    with SSHTunnelForwarder((SSH_HOST, SSH_FOREIGN_PORT), ssh_username=SSH_USER, ssh_password=SSH_PASS, remote_bind_address=('localhost', PORT)) as server:
        server.start()
        cnx = psycopg2.connect(dbname=DB_NAME, user=USER, password=PASS)

EDIT: Error 2:

File "/Users/me/PycharmProjects/proj/src/import_psql.py", line 285, in main_psql
  cnx = psycopg2.connect(dbname=DB_NAME, user=USER, password=PASS, host='localhost', port=PORT) #, host='/var/run/postgresql')
File "/Users/me/PycharmProjects/proj/venv/lib/python2.7/site-packages/psycopg2/__init__.py", line 130, in connect
  conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?

could not connect to server: Connection refused Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432?`

If I run netstat from the postgres user after SSH'g through terminal, I get:

-bash-4.2$ netstat -nl |grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN     
tcp6       0      0 :::5432                 :::*                    LISTEN     
unix  2      [ ACC ]     STREAM     LISTENING     26541    /var/run/postgresql/.s.PGSQL.5432
unix  2      [ ACC ]     STREAM     LISTENING     26543    /tmp/.s.PGSQL.5432

Zorg Zorg · Accepted Answer · 2018-03-07T01:41:11

You have to specify HOST and PORT into your psycopg2.connect(dbname=DB_NAME, user=USER, password=PASS, host=..., port=...). By default postgres client connects to Unix domain socket not TCP socket.

Can't connect to postgres server over SSH through python

1 Answers