2

Initially, I executed these commands to set up a docker container:

docker pull postgres:latest

docker run --name postgres -e POSTGRES_USER=myusername -e POSTGRES_PASSWORD=mypassword -p 5432:5432 -v /data:/var/lib/postgresql/data -d postgres:latest

docker exec -it postgres bash

Things have been operating as expected. Then, I tried to create and connect an SQLAlchemy engine in Python:

from sqlalchemy import create_engine

DATABASE_URL = "postgresql://myusername:mypassword@localhost/postgres"

engine = create_engine(DATABASE_URL)

try:
    engine.connect()
except Exception as e: print(str(e))

however, the interpreter always complains:

OperationalError: (psycopg2.OperationalError) connection to server at "localhost" (::1), port 5432 failed: FATAL:  password authentication failed for user "myusername"

I tried to directly connect it from local using

psql -h localhost -U myusername

\c postgres

and have succeeded. So I thought it should be a configuration problem. Thus I modified my pg_hba.conf to be like the following:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

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

but that did not work too.

Why can't I connect from running the above python code, provided that I could connect from local without difficulty?

Improve this question
8
  • 1
    Is something in the host's /data directory before you start? Environment variables like $POSTGRES_PASSWORD are only processed if the container initialization is running on an empty database directory; otherwise the users and passwords that were previously in the database state are kept, and no new users are created and no passwords are changed. Commented Aug 28 at 10:22
  • you mean the /data: dir or the /data:/var/lib/postgresql/data dir? Commented Aug 28 at 11:18
  • 1
    The /data directory on the host; what is mounted on /var/lib/postgresql/data in the container. Commented Aug 28 at 11:23
  • okay. I'll try to delete the cache there. Thx a lot! Commented Aug 28 at 11:36
  • 1
    1) Your initial error was straight forward, the password you sent did not match what the server was expecting. Reasons for that: a) Just wrong password. b) You where using restored data and ran into md5 vs scram-256 mismatch c) You are not connecting to the correct server. 2) What error did you get when you changed everything to trust? Also did you remember to reload/restart server after you did that? Commented Aug 28 at 14:17

3 Answers 3

Reset to default
0 
DATABASE_URL = "postgresql://myusername:mypassword@localhost/postgres"

This line seems weird.

If myusername and mypassword are variable，you should use format-string like below

DATABASE_URL = f"postgresql://{myusername}:{mypassword}@localhost/postgres"

Hope it works.

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

3 Comments

Sorry but you may refer to this line: docker run --name postgres -e POSTGRES_USER=myusername -e POSTGRES_PASSWORD=mypassword -p 5432:5432 -v /data:/var/lib/postgresql/data -d postgres:latest. I explicitly SET the username and password to be 'myusername' and 'mypassword'.
Rather than a format string, use the create function as described here. (I'm not saying this will fix the problem in the question, just that string formatting is not the best way to construct the URL from variables).
it's just a test script
0

I'm not sure about it, but this is my guess:

Since the error message says "localhost" (::1), port 5432 i think Python is attempting to connect via IPv6 first, which can sometimes be blocked or improperly configured, especially when dealing with Docker.

The psycopg2 driver, which SQLAlchemy uses, is likely trying the IPv6 connection and failing on it before it can even attempt the IPv4 connection that would succeed.

The password authentication error may be a generic failure message returned by the server: it's not impossible that it's measleading us.

Give it a try with:

DATABASE_URL = "postgresql://myusername:[email protected]/postgres"

or

DATABASE_URL = "postgresql://myusername:[email protected]:5432/postgres"

since 5432 is the default one

EDIT: Also consider the fact that you're connecting to the database with

psql -h localhost -U myusername

so we cannot be sure "mypassword" is correct because we never saw it working...

I would also try

DATABASE_URL = postgresql://[email protected]:5432/postgres

without password...

and if it's the case, we strongly suggest to set one.

Improve this answer

2 Comments

duh won't work either :( thanks a lot for your advice though:) the complaint of the interpreter is so elusive that I could not get the details of the error, which frustrates me...
from local you're connectin with "psql -h localhost -U myusername" there's no "mypassword" there. Are we sure you actually need you passrword to connect to it (also considering the configuration you provided)? You should try DATABASE_URL = "postgresql://[email protected]:5432/postgres" inside your code
0

Environment variables like $POSTGRES_PASSWORD are only processed if the container initialization is running on an empty database directory; otherwise the users and passwords that were previously in the database state are kept, and no new users are created and no passwords are changed. You should clear the cache in the /data directory, I.e. the /data directory on the host; what is mounted on /var/lib/postgresql/data in the container.

PS: Thanks for @David Maze for posting the comment that finally worked as a correct answer! I’m not familiar with the community and can only pay tribute in this way :( If there’re proper ways to cite this, please inform me.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.