Postgresql/psycopg2 password authentication error even though user and password are correct

Question

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)

Looks like we wrapped the chat around into a new day(UTC) and things jumped the rails. What I was asking was to do : sudo nano /etc/postgresql/10/main/postgresql.conf and report back the settings for port and listen_addresses. — Adrian Klaver
– Adrian Klaver, Commented Aug 13, 2020 at 0:38
I'll paste what I see in the file up above in the question ... it looks like maybe there are no listen_addresses? — Allison Johnson
– Allison Johnson, Commented Aug 13, 2020 at 1:11
You're using localhost in your code but 127.0.0.1 when running the psql command line. These may not be equivalent (at least one db driver assumes that localhost means use the unix socket, and I can't remember if that's postgres or something else). — larsks
– larsks, Commented Aug 13, 2020 at 1:20
@larsks. I would suggest going through the entire chat discussion. This is more involved then you think. — Adrian Klaver
– Adrian Klaver, Commented Aug 13, 2020 at 2:26

Adrian Klaver · Accepted Answer · 2020-08-13 13:46:00Z

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.

answered Aug 13, 2020 at 13:46

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user Over a year ago

Oh, I get it now... but my understanding is that there were two Postgres instances, one running on port 5432 and the other on port 5433. OP had the correct password for the one running on 5433, but the code was calling the one in 5432. It seems only a port change was necessary.

Adrian Klaver Over a year ago

Except the OP just wanted to work with the one in WSL and so it made more sense for that one to run with the default port and to shut down the other to keep the confusion level down. If they decide to run the Windows instance they can change the port number at that time.

Collectives™ on Stack Overflow

Postgresql/psycopg2 password authentication error even though user and password are correct

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related