PostgreSQL: Making it listen and trust

PostgreSQL is one of the easiest and best databases to set up. When compiling from sources it requires little to no dependencies and often a 'configure; make; make install' is sufficient to get it installed. Over the years it's gotten even easier with RPM's and Windows installers. Typically managing a server instance is just as easy. Despite all this there are some things that can make people stumble if you do not know why it is not working or how to change it. One of the most common problems, even when using installers, is that PostgreSQL will not accept remote connections. 

It is a dangerous world out there and PostgreSQL most often comes out of the box listening to, and trusting, only localhost. We need to change both of these to be able to connect from remote machines. The good news is this can easily be done but we need to deal with each separately. 

Listen -- but to whom? 

We all listen to different things at different times and sometimes to everything going on around us. PostgreSQL has the ability to do the same thing. Often PostgreSQL listens to localhost. This would be like sitting in your home or office and listening to other people in the same room but ignoring the telephone or people knocking at the door. We can tell PostgreSQL to listen to one, several, or everything available. Often the easiest and desired behavior is to have everything. 

The setting for this is located in the postgresql.conf file and is the option listen_addresses. The default is 'localhost' and we can change it to '*' if we want to listen to everything. This file is located in the data directory for the server instance of PostgreSQL. If you do not know where that is you can get the database to tell you if you can log in locally. To do this execute the sql query 'show config_file;' and it will return the full path and name of the file you need to change. 

Additional information on the listen_addresses option can be found at: http://www.postgresql.org/docs/8.3/interactive/runtime-config-connection.html. 

Trust -- him, her, them, but not people wearing straight jackets 

Trust has varying degrees. Some people can be trusted without question while others can be trusted but only when they can prove themselves. Some can not be trusted at all. PostgreSQL provides a way for you to tell it who can be trusted using Host Based Authentication. Host Based Authentication allows you to authenticate people based on the network host they are coming from. The config file is the pg_hba.conf file and we can find it in the data directory or using the command 'show hba_file;' as we did previously. 

When you open the hba file the first thing you will notice is a vast amount of comments documenting the file. Reading these comments can tell you a great deal of the many things you can do here. If you ignore all that for now though you will likely find just 2 or 3 actual lines near the bottom. These lines often tell PostgreSQL to only trust connections from localhost. Even if we change the listen addresses above we still need to add others to the trusted list. 

Each line has several pieces of information. The first is the type of connect we are dealing with. The type host is the most common one used and represents all unencrypted connections coming over a network. The second option is the database or databases that the rule applies to and you can use the keyword all for all databases. The third option is the user or users that this rule applies to and the keyword all is also available. The fourth option is the host or network the connection is coming from and often the most unique aspect to you and is called the cidr-address which we will talk about next. Finally the last option is the method of authentication. This can be the word trust if do not want to enforce passwords or password if you do. Each of these has additional values you can use and you can find these by reading the comments and documentation provided by PostgreSQL. 

A cidr-address is a Classless Inter-Domain Routing address which is used in routing and network applications. This is an ip address followed by a / and number of significant bits; for example 127.0.0.1/32. An ip address has 32 bits and in the previous example we are telling that all the bits, or the entire address, must match. You can specify less bits and a different number such as 192.168.0.0/24. In this example the first 24 bits are 192.168.0 so we would match any address from 192.168.0.0 all the way to 192.168.0.255. If you want to match everything then you can use 127.0.0.1/0  or any other ip-address/0. The last example is the quickest way but is not considered safe and should never be used with the trust method. 

Given what we now know about the hba config we can add a line to allow connections from any host if they can provide a password: 

host all all 127.0.0.1/0 password 

With this line added PostgreSQL will trust anyone who can provide a valid password. If you added the line above the others than all network connections will require a password even those on localhost. If you added the line at the bottom of the file then localhost connections will still be trusted and not require a password. The reason for this is because the lines are evaluated in order and the first one to match all the criteria is the one applied. This can useful to know if you find that things are not working as expected or if you need to add a rule to the top of the file to reject a specific host while still keeping the approved lines simple. 

More information about cidr-address can be found at: http://en.wikipedia.org/wiki/Classless_Inter-Domain_Routing. 
More information about the pg_hba.conf file can be found at: http://www.postgresql.org/docs/8.3/interactive/client-authentication.html. 

Finish -- restarting and testing 

Now that we have made the appropriate changes to the config files we just need to restart PostgreSQL. After the restart you can test connecting to the database to make sure it is now doing what you want. If you have problems you can check the log file for the database and it should give you some indication as to why it is not working. Also double check your changes to make sure they are correct. 

If you find you are making multiple changes for one reason or another it is interesting to note that you do not have to restart the database each time you change the pg_hba.conf file. You can get PostgreSQL to reread the pg_hba.conf file by issuing a reload command. This way you do not have to kick out users in the system to make a change. Note that changes the listen_addresses option does require a restart. 

Hope this helps everyone out there getting their server configured.