Connecting with psycopg2 without a username and password

24 February 2011   7 comments   Python

Mind That Age!

This blog post is 6 years old! Most likely, its content is outdated. Especially if it's technical.

Powered by Fusion×

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

Comments

Marius Gedminas
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.
Rasjid Wilcox
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.
Marius Gedminas
*headslap*

Thanks for correcting me. I don't know how I managed to type "UDP" when I meant "Unix domain".
Eliyahu
thx!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

u so helped me. what dozens of sites didn't managed to do..
Dav Clark
Likewise, very helpful. Thanks!
Adrian
Have you ever done this without a password for an Amazon Redshift instance?
Michael
Outdated? 6 years old and still helping users (me :) ) Thanks!
Thank you for posting a comment

Your email will never ever be published


Related posts

Previous:
Optimization of getting random rows out of a PostgreSQL in Django 23 February 2011
Next:
Eloquent Javascript by Marijn Haverbeke 25 February 2011
Related by Keyword:
How do log ALL PostgreSQL SQL happening 20 July 2015
From Postgres to JSON strings 12 November 2013
Fastest database for Tornado 09 October 2013
Postgres collation errors on CITEXT fields when upgrading to 9.1 21 May 2012
Optimization of getting random rows out of a PostgreSQL in Django 23 February 2011
Related by Text:
How do log ALL PostgreSQL SQL happening 20 July 2015
crontabber now supports locking, both high- and low-level 04 March 2017
Migration of Postgres 9.2 to 9.3 with Homebrew and json_enhancements 30 April 2014
Creating a user for postgresql 10 January 2004
Running simple SQL commands on the command line 08 January 2005