I spent quite some time debugging a test connection for Postgres using the python psycopg2 library. Here’s the tutorial that I followed:
#!/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