Connecting with psycopg2 without a username and password

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