My colleague Lukas and I banged our heads against this for much too long today. So, our SQLAlchemy
is was configured like this:
ENV_DB_CONNECTION_DSN = postgresql://localhost:5432/mydatabase
And the database doesn't have a password (local) so I can log in to it like this on the command line:
$ psql mydatabase
Which assumes the username
peterbe which is what I'm logged in. So, this is a shortcut for doing this:
$ psql mydatabase -U peterbe
Which, assumes a blank/empty password.
Anyway, you try to connect to the database and you get this error:
(OperationalError) fe_sendauth: no password supplied
So we tried again using this DSN:
ENV_DB_CONNECTION_DSN = postgresql://peterbe@localhost:5432/mydatabase
But then you get the same error. Tried this:
ENV_DB_CONNECTION_DSN = postgresql://peterbe:@localhost:5432/mydatabase
Now it says:
(OperationalError) FATAL: password authentication failed for user "peterbe"
So (drum roll...) the answer is to not bother setting the host and port and it works. The right syntax is this:
ENV_DB_CONNECTION_DSN = postgresql:///mydatabase
It has nothing to with your pg_hda.conf or what type of trust you have set up. Just avoid the host and it doesn't do the password checking.
Even, better if you want to make sure it's using psycopg2 and not your old psycopg you can write this:
ENV_DB_CONNECTION_DSN = postgresql+psycopg2:///mydatabase
CommentsPost your own comment
postgresql:/// connects over a UDP socket and can use the Unix userid to authenticate.
postgresql://host:port/ connects over TCP, where the userid of the remote end is not known (or cannot be trusted).
It's a bit of a simplification to say pg_hba.conf doesn't matter: you need to allow Unix domain socket connections in that config file if you want paswordless logins. It's just that the default settings already allow that.
I think there is a typo in Marius' comment. Pretty sure the first line should say 'unix socket' and not 'UDP socket'. They are different things and I don't think it is possible to connect to postgres via UDP.
Thanks for correcting me. I don't know how I managed to type "UDP" when I meant "Unix domain".
u so helped me. what dozens of sites didn't managed to do..
Likewise, very helpful. Thanks!
Have you ever done this without a password for an Amazon Redshift instance?
Outdated? 6 years old and still helping users (me :) ) Thanks!
Damn I would have been so f**up if hadn't found you post!!!
What command do you run to configure psql with this syntax? I'm brand-new to sql and am running into this problem....looks like its the fix, just don't know how to implement :o
I would happily donate $5.
Delivered me after 12 hours of config hell.
Or, perhaps the $5 you spent in the swear-jar in quarters can be donated to a charity :)
still helping people in 2020! Thanks so much!!