'Postgresql/psycopg2 password authentication error even though user and password are correct
I am new to web development in Python and would really appreciate some help. I am trying to set up psycopg2/peewee on WSL (Windows machine). In my Python code I store all of the info I'll need to access a Postgres database, and then attempt to connect to the database as follows:
#lib/main.py
from peewee import *
db = PostgresqlDatabase('people', user='postgres', password='postgres', host='localhost', port=5432)
db.connect()
I am certain that I have a Postgres user named 'postgres' with a password of 'postgres'. Those are the credentials I use to start up a Postgres server once I have changed into the postgres user on my machine using su - postgres.
When I launch my Python virtual environment using pipenv shell and then run python3 main.py I get the following error:
// ♥ python3 main.py
Traceback (most recent call last):
File "/home/allison_johnson/.local/share/virtualenvs/lib-jEOreobP/lib/python3.6/site-packages/peewee.py", line 3035, in connect
self._state.set_connection(self._connect())
File "/home/allison_johnson/.local/share/virtualenvs/lib-jEOreobP/lib/python3.6/site-packages/peewee.py", line 3730, in _connect
conn = psycopg2.connect(database=self.database, **self.connect_params)
File "/home/allison_johnson/.local/share/virtualenvs/lib-jEOreobP/lib/python3.6/site-packages/psycopg2/__init__.py", line 127, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
**psycopg2.OperationalError: FATAL: password authentication failed for user "postgres"**
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "main.py", line 18, in <module>
db.connect()
File "/home/allison_johnson/.local/share/virtualenvs/lib-jEOreobP/lib/python3.6/site-packages/peewee.py", line 3038, in connect
self._initialize_connection(self._state.conn)
File "/home/allison_johnson/.local/share/virtualenvs/lib-jEOreobP/lib/python3.6/site-packages/peewee.py", line 2873, in __exit__
reraise(new_type, new_type(exc_value, *exc_args), traceback)
File "/home/allison_johnson/.local/share/virtualenvs/lib-jEOreobP/lib/python3.6/site-packages/peewee.py", line 183, in reraise
raise value.with_traceback(tb)
File "/home/allison_johnson/.local/share/virtualenvs/lib-jEOreobP/lib/python3.6/site-packages/peewee.py", line 3035, in connect
self._state.set_connection(self._connect())
File "/home/allison_johnson/.local/share/virtualenvs/lib-jEOreobP/lib/python3.6/site-packages/peewee.py", line 3730, in _connect
conn = psycopg2.connect(database=self.database, **self.connect_params)
File "/home/allison_johnson/.local/share/virtualenvs/lib-jEOreobP/lib/python3.6/site-packages/psycopg2/__init__.py", line 127, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
peewee.OperationalError: FATAL: password authentication failed for user "postgres"
I am confused as to why the authentication is failing for a valid user/password combo!
I have tried testing the connection string in the Python3 shell as well:
>>>import psycopg2
>>>psycopg2.connect("dbname=postgres user=postgres host=localhost password=postgres port=5432")
and I get the same error. I would much appreciate any insight as to why I can't connect my Python code to Postgres!
My pg_hba.conf file has the following:
# 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
From the command line, ps ax |grep postgres shows:
3218 ? S 0:00 /usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/10/main/postgresql.conf
3220 ? Ss 0:00 postgres: 10/main: checkpointer process
3221 ? Ss 0:00 postgres: 10/main: writer process
3222 ? Ss 0:00 postgres: 10/main: wal writer process
3223 ? Ss 0:00 postgres: 10/main: autovacuum launcher process
3224 ? Ss 0:00 postgres: 10/main: stats collector process
3225 ? Ss 0:00 postgres: 10/main: bgworker: logical replication launcher
3337 tty1 S 0:00 grep --color=auto postgres
After running psql -d postgres -U postgres -h 127.0.0.1 and inspecting the Postgres log, I see:
2020-08-12 13:37:30.573 EDT [709] LOG: database system is shut down
2020-08-12 13:37:30.700 EDT [3218] LOG: listening on IPv4 address "127.0.0.1", port 5433 2020-08-12 13:37:30.714 EDT [3218] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2020-08-12 13:37:30.760 EDT [3219] LOG: database system was shut down at 2020-08-12 13:37:30 EDT
2020-08-12 13:37:30.816 EDT [3218] LOG: database system is ready to accept connections
2020-08-12 13:37:31.383 EDT [3226] [unknown]@[unknown] LOG: incomplete startup packet
2020-08-12 13:37:48.061 EDT [3254] postgres@postgres LOG: provided user name (postgres) and authenticated user name (allison_johnson) do not match
2020-08-12 13:37:48.061 EDT [3254] postgres@postgres FATAL: Peer authentication failed for user "postgres"
2020-08-12 13:37:48.061 EDT [3254] postgres@postgres DETAIL: Connection matched pg_hba.conf line 85: "local all postgres $2020-08-12 13:42:30.909 EDT [3220] WARNING: could not flush dirty data: Function not implemented 2020-08-12 14:08:00.001 EDT [3443] allison_johnson@allison_johnson FATAL: password authentication failed for user "allison_johnson"
2020-08-12 14:08:00.001 EDT [3443] allison_johnson@allison_johnson DETAIL: Password does not match for user "allison_johnson".
Connection matched pg_hba.conf line 90: "local all all md5"
2020-08-12 14:13:49.143 EDT [3514] postgres@postgres LOG: provided user name (postgres) and authenticated user name (allison_johnson) do not match
2020-08-12 14:13:49.143 EDT [3514] postgres@postgres FATAL: Peer authentication failed for user "postgres"
2020-08-12 14:13:49.143 EDT [3514] postgres@postgres DETAIL: Connection matched pg_hba.conf line 85: "local all postgres $
Lines 85-90 of pg_hba.conf:
# 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 md5
Settings for port and listen_addresses in /main/postgresql.conf
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5433 # (change requires restart)
Solution 1:[1]
The short version of the answer is that there where two instances of Postgres running in two different OS'es. The longer answer is as follows. In WSL(2) the Postgres instance pg_hba.conf had local set to peer for user postgres and md5 for all. There where md5 auth connections for localhost IPV4 and IPV6. This is why Allison could connect as database userpostgres user from system user postgres account. Changing the local
settings to trust allowed for connecting from any system account when not using -h. The password connection issue with localhost remained though. The strange part was there was no record of those connections in the Postgres logs. There was no other instance of Postgres running in WSL. After too long a thought process on my part it became apparent that there had to be a server running in Windows proper. That was the case and it was the one grabbing the localhost connections and throwing the password errors. Shutting it down was a step to solving this. As it turns the Windows Postgres was running on port 5432 and the WSL instance 5433 and the WSL instance was only listening on localhost. Changing the port to 5432 and listen_addresses to '*' allowed for localhost connection without specifying a port and local connections.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Adrian Klaver |