LarsBlog

I recently had to set up a new development database machine.  The database needed to be PostgreSQL and I chose a base operating system of Ubuntu 8.04.1 server.  This was not quite as simple as I'd hoped.  After I got the machine up and running with SSH access, I needed to go through quite a few steps to get my database created, accessible from other machines on the network and use the excellent admin front-end phpPgAdmin.  So I thought I'd record these here in case they might help someone.

First of all, I think it's good to update the system, then install PostgreSQL and PhpPgAdmin:

sudo apt-get update
sudo apt-get upgrade
sudo apt-get install postgresql
sudo apt-get install phppgadmin

Then I needed to make sure Apache picked up the PhpPgAdmin web config by creating a symbolic link to PhpPgAdmin 's Apache config include:

sudo ln -s /etc/phppgadmin/apache.conf /etc/apache2/conf.d/phppgadmin.conf
sudo /etc/init.d/apache2 restart

Also, if you want to be able to access phppgadmin from any other machine, you'll need to edit /etc/phppgadmin/apache.conf to comment out the "allow from 127.0.0.1/255.0.0.0" restriction and uncomment "allow from all", as per below:

#allow from 127.0.0.0/255.0.0.0
allow from all

Next, create my development database:

sudo -u postgres createdb mydatabasename

Then create a super-user with the same user name as my linux username.  Substitute $USER with some other username if you want it to be different.  Note that the --pwprompt is required to set a password for this user, which is required for remote login.  (For an excellent reference on createuser and other postgres commands, check out this link)

sudo -u postgres createuser --superuser --pwprompt $USER
Enter password for new role:
Enter it again:
createdb $USER

Then I needed to edit PostgreSQL's config to allow connections from IP addresses on my LAN:

sudo pico /etc/postgresql/8.3/main/pg_hba.conf

by adding the line:

host    all         all         192.168.0.0 255.255.255.0 md5

Update: I found recently on an Ubuntu Lucid install that phpPgAdmin was trying to access PostgreSQL via a local socket connection, so I had to change the default configuration for "local" in pg_hba.conf to allow phpPgAdmin to authenticate using a password.  Try this if you find you're having trouble authenticating (but it means you'll have to provide a password when using 'psql' from the command-line too):

# Default setting for "local": (Unix domain socket connections)
#local   all         all                               ident sameuser
# My updated version, for phpPgAdmin, which requires md5 so that it can authenticate using a password)
local   all         all                               md5

You may also need to change the listen_addresses value in postgresql.conf in the same directory, as this may default to localhost:

listen_addresses = '*' 

Then finally I needed to restart PostgreSQL:

sudo /etc/init.d/postgresql-8.3 restart

After all this, I could finally see PhpPgAdmin at the URL http://192.168.0.5/phppgadmin/ and log in with the user/password details from the createuser step above. (Note that of course your PostgreSQL machine may have an IP address different to 192.168.0.5!  So don't forget to substitute your correct IP!)