PostgreSQL OperationalError in Django

One of the common exceptions I came across while using PostgreSQL with Django is OperationalError. The error description appears as follow:

• FATAL: Ident authentication failed for user “username”

The reason for this error according to my findings is you may have given ‘ident’ authentication for all local users. Ident authentication is one of the methods used by PostgreSQL to authenticate the user. The ident authentication method works by obtaining the client’s operating system user name, then determining the allowed database user names using a map file that lists the permitted corresponding pairs of names(courtesy-PostgreSQL). You may have created the user, who can access the database with full power, with a password. And this password is generally hashed by md5.
By default all the local access are set to ident authentication in PostgreSQL. You can find the details in the file ‘pg_hba.conf’.

Solution

First, locate the ‘pg_hba.conf’ file in your linux machine. You can use the command:

• locate pg_hba.conf

In my system it was located in ‘/etc/postgresql/8.4/main/’ folder. For you it may be different according to the directory in which you installed PostgreSQL.
Now open the file(pg_hba.conf) in edit mode. You will find a line similar to the following:

• # “local” is for Unix domain socket connections only
• local all all ident

You have to change the line to:

• local all all md5

Restart PostgreSQL:

• /etc/init.d/postgresql restart

Now the exception should vanish.

PostgreSQL Installation in Linux(Python)

PostgreSQL

PostgreSQL is a powerful, open source object-relational database system. It runs on all major operating systems, including Linux, UNIX and Windows. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC.

Install PostgreSQL in your Linux Machine

If you are using Ubuntu you can use the apt command

• apt-get install postgresql

and if you are using Pardus you can use pisi

• pisi it postgresql

Create a Database and grant permissions to Users

Add a Linux/UNIX user called odol

• adduser odol
• passwd odol

This’ll create the user odol. This is not a required step. This is only for testing purpose. You can use any existing user also.

Becoming a superuser and connect to database server

• su – postgres

This user will get created automatically when we install PostgreSQL.

• psql template1

If you are not becoming a superuser, you can do the following

• psql -d template1 -U postgres

Add a user called odol and Create a database called mydb and Grant Permissions

By the execution of last command, you’ll enter the postgres shell. As the db is template1, the prompt will be template=#

• CREATE USER odol WITH PASSWORD ‘myPassword’;
• CREATE DATABASE mydb;
• GRANT ALL PRIVILEGES ON DATABASE mydb to odol;
• \q

Enter as odol and test

• su – odol
• psql -d mydb -U odol

This will let you get into the shell for mydb database. In which you can create table and do other database activities and queries.

Using PostgreSQL with Django

In order to use Python with PostgreSQl we need an adapter. Psycopg is the most popular PostgreSQL adapter for the Python programming language.
Install the following requirements first

• apt-get install python-dev libpq-dev

We can get pyscopg using pip or easy_install

• pip install pyscopg2

Common Errors while installing pyscopg2

The most common error(the one I came across) while installing pyscopg2:

• Error: pg_config executable not found.

Make sure you have installed python-dev and libpq-dev in your system. The step is already mentioned above.

Admin for PostgreSQL(Graphical)

pgAdmin is an Open Source administration and development platform for PostgreSQL. It provide the graphical interface. And also have an editor in which you can create SQL query and execute it.