PostgreSQL/psycopg2 Password Authentication using SSH Tunneling

Question

I am trying to connect to a PostgreSQL Database via ssh tunnel. It is set up to listen on port 3333 and forward to port 5432 on the machine with the database. I am able to connect using the psql command with password authentication via the tunnel, but for some reason when I attempt to connect using psycopg2 via the tunnel, I get the error FATAL: password authentication failed for user database_user. I have tried putting quotes around user names and passwords to no avail.

Successful psql command:

psql -h localhost -p 3333 -U database_name database_user 
#This command brings up password prompt

Failed pscyopg2 command:

psycopg2.connect("dbname='database_name' user='database_user' host='localhost' password='database_password' port=3333")

Output:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/database_user/.local/share/virtualenvs/project-QNhT-Vzg/lib/python3.7/site-packages/psycopg2/__init__.py", line 126, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: FATAL:  password authentication failed for user "database_user"
FATAL:  password authentication failed for user "database_user"

Here is part of my pg_hba.conf for reference:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

This will not be enough to find out your issue. Is there a firewall? Is postgres listening on the interface? Could you address those questions and add the specific python code and the trace of the error as it certainly contains information of what is going wrong. — jlandercy
– jlandercy, Commented Feb 16, 2020 at 20:24
Well it seems your password is not what you think it is. You are able to reach postgres, and ACL (pg_hba) is correct. The error is typical: the password does not match. Are you sure there is no typo? Have you set this password? When you try using psql are you in local or host mode? — jlandercy
– jlandercy, Commented Feb 16, 2020 at 23:01
Somehow it ended up working by making an environment variable with the password in single quotes. If you make an answer about checking the password I will mark it as the accepted answer. Thanks for your help! @jlandercy — gsundberg
– gsundberg, Commented Feb 17, 2020 at 2:32
Hi Gunnar, added an answer with more details and check list. Cheers — jlandercy
– jlandercy, Commented Feb 17, 2020 at 15:39

jlandercy · Accepted Answer · 2020-02-17 10:26:31Z

When debugging a connection issue it is always worthy to remember what layers we must go through before reaching the service. When you connect PostgreSQL service there will be at least three layers:

Networking: Firewall, NAT, Port Forwarding
PostgreSQL ACL
PostgreSQL login

It is important to understand what layer cause the issue, the PostgreSQL client (wrapped in psycopg2 in your scenario) error will help you to resolve this by issuing an ad-hoc error message:

Network issue will generally raise a typical: Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?which means you did not succeed to connect the PostgreSQL service at all, problem relies before the service;
ACL issue will generally raise a typical: No pg_hba.conf entry for host <hostname>, user <username>, database <database> which means you did connect the PostgreSQL service but the connection is not referenced as valid in ACL;
Login issue will generally raise the error you have got: password authentication failed for user "<user>" which means you did connect the PostgreSQL service and the connection complies with an ACL entry but the authentication failed.

In the later scenario, it is important to know which entry triggered, because it defines the authentication mode. In your case, it was a md5 entry (because there is no password in peer mode and your SSH tunnel should map the localhost so you are seen as host instead of local for a postgreSQL perspective):

host    all             all             127.0.0.1/32            md5

Apparently your password is not what you expect it to be. To solve this, ensure:

you have set the password to the postgreSQL user and checked the LOGIN privileges (not the unix/SSH user, there are different concepts);
you use the same password in your psycopg2 connection, then you must be able to connect;

Reading your comment, it seems you may have ' quote in your password as well. So your password in your connection might be:

psycopg2.connect("dbname='database_name' user='database_user' host='localhost' password="'database_password'" port=3333")

Or if the quote are required it may indicate that you use some special characters that need to be escaped. You can also try simpler password to debug and then fallback on a stronger one.

Collectives™ on Stack Overflow

PostgreSQL/psycopg2 Password Authentication using SSH Tunneling

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related