Setting up Postgresql for Accepting Password Connections

I was setting up a new machine with a fresh postgresql install, and I had just created a new user account. As I tried to login to psql from the terminal window, I got the dreaded message informing me that the authentication failed. I realized, I had not set up my environment to accept local connections for different users. So in this post, let us see how to do that.

While the steps shown here are for PostgreSQL 9.1, they are valid for 9.3 as well.

First, the following screen will show the kind of error I received. Note that the user 'nyadmin' had been created while logged in as the postgres user. You can also see that I tried multiple attempts to login before realizing that this was a fresh installation.

Allowing trusted connections to Postgresql

The file that allows the administrator to manage which users can connect and which cannot, is called pg_hba.conf. I have seen this move around to different locations for different versions of the database, but currently in my environment it was deployed under /etc/postgresql/9.1

We need to edit this file as an administrator, since my user does not have rights to save the file within the etc folder. Hence, we need to use sudo before the gedit command as follows.

> sudo gedit /etc/postgresql/9.1/main/pg_hba.conf

We need to edit the file to make following changes

1. Changed postgres authentication to identity from peer
2. Changed local authentication to md5 from peer

This means that I will need to become OS level postgres user to connect to Postgres. Also, locally connecting users can now enter their passwords to connect the database.

You can see the changes in the screen shot below. I commented the previously existing privileges to highlight the changes.

First, the line....

local all postgres peer

....was commented, with a “#' and the following line added below it

local all postgres ident

Then, the line....

local all all peer

....was commented, with a “#' and the following line added below it

local all all md5

With these changes, we can save the file and close the editor. Next we need to restart the database service.

Restarting Postgresql service

For restarting the service, we need to first switch to the postgres OS level user. This can be done by entering .....

> sudo -u postgres service postgresql restart

..... or you can do it in two steps, as shown below.

> su – postgres

System will prompt for the postgres user password, which is postgres by default. Once in, you can issue the restart command as follows

postgres > service postgresql restart

Enter command “exit” to come back to the original prompt. Now enter the command to login as the created user.

> psql -U nyadmin -d template1 -W

Here,

-U denotes the user,

-d tells the system which database to connect to. Since I have not yet configured a default database for this user, I need to explicitly mention template1

-W tells the system that use a password.

The system prompts for the password, which I entered, and we are in.

In the next post, we will create a default database for this user.