Say you want to set up postgres for use with a web application. If you are running on the same server here’s what you need to do:

If the technology you are using is smart enough to use the domain socket for local connections, in

/var/lib/pgsql/data/pg_hba.conf
Apply the following diff:

# “local” is for Unix domain socket connections only
+local myapp-db myapp-user password
local all all ident sameuser

If, on the other hand, you need tcp connections (which is the case for jdbc)  you probably want this:

# IPv4 local connections:
-host    all         all         127.0.0.1/32          ident sameuser
+host    all         all         127.0.0.1/32          md5

Although, again, you should probably change “all all” to be specific to your application.

You need to restart the postgres database server to have these changes take effect.

To test this, you want to run the following command

sudo -u postgres /usr/bin/createuser –pwprompt  myapp-user

This will create the user you want, and prompt you for a password.  To log in locally, use.  To create the database itself:

sudo -u postgres /usr/bin/createdb myapp-db “My App backend data
storage” -O myapp-user

To test local connections (domain socket) run

psql myapp-db -U myapp-user

You should be prompted for the password

To test tcp connectivity, run

psql -h localhost myapp-db -U myapp-user

And again, you should be prompted for your password.  Some alternative tests to try, to make sure you “get it.”

• Create an alternative database as the same user as you application user.  Make sure that Postgres rejects that account from psql when connection using a domain socket.
• Attempt to connect to the alternative database as a remote user.  You should be allowed in.
• Try this from a remote machine.  You should be rejected across the board.

To add an entry to the pg_hba.conf file allowing a specific remote machine to connect should look  like this:

host    myapp-db         myapp-user         192.168.1.1/32          md5

Tested only on RHEL5 and Fedora11, but this should work for Linux based PostgreSQL setups.  I suspect Windows as well, but I have not tested it.   The path to the config file will be very different.