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:

http://wiki.postgresql.org/wiki/Psycopg2_Tutorial

#!/usr/bin/python2.4
#
# Small script to show PostgreSQL and Pyscopg together
#

import psycopg2

try:
    conn = psycopg2.connect("dbname='template1' user='dbuser' host='localhost' password='dbpass'")
except:
    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
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s