PostgresSQL Psycopg2 library for Python

I spent quite some time debugging a test connection for Postgres using the python psycopg2 library. Here’s the tutorial that I followed:

# Small script to show PostgreSQL and Pyscopg together

import psycopg2

    conn = psycopg2.connect("dbname='template1' user='dbuser' host='localhost' password='dbpass'")
    print "I am unable to connect to the database"

But the connection would keep failing. I pulled out a connection from a different server where I had done this earlier – but that didn’t work either. I removed the host parameter and the connection succeeded…! I had no idea how this worked. Eventually, a buddy of mine figured out the problem. It’s still too advanced for me to understand and I will go into much details later but for now – it has something to do with the type of connection. If host is specified, the system tries to connect using a Unix socket and would require Unix credentials. Since I supplied a DB password, things were not working. After supplying the Unix password for the user (given that the user exists on the linux box and DB) – it worked. The following line in the pg_hba.conf has something to do with this that I need to research further:

# Database administrative login by Unix domain socket
local all postgres peer

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s