Instructions for configuring lostgres for localhost:5433 access on a vagrant ubuntu lucid32 image

gistfile1.sh

# bring up the vagrant box
vagrant up
=> [default] Importing base box 'lucid32'...

# ssh into the vagrant box
vagrant ssh
=> Linux lucid32 2.6.32-38-generic #83-Ubuntu SMP Wed Jan 4 11:13:04 UTC 2012 i686...

# take special note of the ip address of your host machine that ssh reports here
# in my case it is 10.0.2.2, a value that I will use later to allow connections
# from my host machine to postgres
=> Last login: Fri Sep 14 07:26:29 2012 from 10.0.2.2

# become the root user
sudo su
=> root@lucid32:/home/vagrant#

# update the packages installed already
apt-get -y update
=> Hit http://us.archive.ubuntu.com lucid Release.gpg ...

# reset the locale for the machine to UTF
nano /etc/bash.bashrc

# add the following lines to the bottom of this file

  export LANGUAGE=en_US.UTF-8
  export LANG=en_US.UTF-8
  export LC_ALL=en_US.UTF-8

# quit and save the file
# generate the locale files for the system
locale-gen en_US.UTF-8
=> Generating locales...
dpkg-reconfigure locales
=> Generating locales...

# exit the system and log back in to set locale
exit
=> root@lucid32:/home/vagrant#
exit 
=> vagrant@lucid32:~$
exit
=> [host prompt]
vagrant ssh

# test that the locale is properly set by shell script
locale
=> LANG=en_US.UTF-8
=> LANGUAGE=en_US.UTF-8
=> LC_CTYPE="en_US.UTF-8"
=> LC_NUMERIC="en_US.UTF-8"
=> LC_TIME="en_US.UTF-8"
=> LC_COLLATE="en_US.UTF-8"
=> LC_MONETARY="en_US.UTF-8"
=> LC_MESSAGES="en_US.UTF-8"
=> LC_PAPER="en_US.UTF-8"
=> LC_NAME="en_US.UTF-8"
=> LC_ADDRESS="en_US.UTF-8"
=> LC_TELEPHONE="en_US.UTF-8"
=> LC_MEASUREMENT="en_US.UTF-8"
=> LC_IDENTIFICATION="en_US.UTF-8"
=> LC_ALL=en_US.UTF-8

# move over to the root account again
sudo su

# now install the postgres packages 
apt-get install postgresql postgresql-contrib

# pay special attention to the package installation and 
# confirm that pg_createcluster was run automatically
# looking for output like the following containing config
# file locations and other details of the default installation
=> Setting up postgresql-8.4 (8.4.17-0ubuntu10.04) ...
=> ... important configuration details  ...
=> * Starting PostgreSQL 8.4 database server                               [ OK ]

# and confirm existance of default tables by loading 
# psql and listing the tables as the postgres user
sudo -u postgres psql -l
=>                                  List of databases
=>   Name    |  Owner   | Encoding | Collation  |   Ctype    |   Access privileges   
=> -----------+----------+----------+------------+------------+-----------------------
=>  postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
=>  template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres
=>                                                            : postgres=CTc/postgres
=>  template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres
=>                                                            : postgres=CTc/postgres

# if you did not log out and log back in between those commands
# you will see the encoding of the postgres tables are in LATIN1 -- this
# is not a good default for UTF-8 based Rails projects. This would look
# as follows -- note the LATIN1

=>                               List of databases
=>    Name    |  Owner   | Encoding | Collation | Ctype |   Access privileges   
=> -----------+----------+----------+-----------+-------+-----------------------
=>  postgres  | postgres | LATIN1   | en_US     | en_US | 
=>  template0 | postgres | LATIN1   | en_US     | en_US | =c/postgres
=>                                                      : postgres=CTc/postgres
=>  template1 | postgres | LATIN1   | en_US     | en_US | =c/postgres
=>                                                      : postgres=CTc/postgres
=> (3 rows)

# this is a pain over the long haul so either vagrant destroy your image
# and start again or you can try drop and recreating the server
 
# now you have a running postgres 8.4 database on 
# the ubuntu machine.  Vagrant is configured to forward
# 5432, the default port, to 5433 or whatever free port
# you set up in your Vagrantfile.  However, security policies
# on the default postgres installation will cause these connections
# to be dropped and applications like Rails will be unable
# to connect to the database on localhost:5433 as planned

# open up the postgres installation to remote connections
nano /etc/postgresql/8.4/main/postgresql.conf

# find the "#listen_address='localhost'" line, uncomment it, and change it to
# * to accept all hosts

  listen_address='*'

# save and quit, then edit the pghosts file to allow your host 
# machine to connect to the server
nano /etc/postgresql/8.4/main/pg_hba.conf

# find the line "# IPv4 local connections:" and add the following 
# after the default loopback line for 127.0.0.1
# using the IP address that you noted after logging in with vagrant/ssh
host    all         all         10.0.2.2/24           md5

# at this point, we are almost there, but even after a restart attempts to connect 
# from the host  machine at localhost:5433 will fail instantly do to the iptables 
# firewall set up by default on the lucid32 box
==> Connection Failed
==> server closed the connection unexpectedly
==> This probably means the server terminated abnormally
==> before or while processing the request.

# so lets open up the tables -- wide open was acceptable to our team for these
# disposable vms accessible through local host
iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d 127.0.0.0 --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s 127.0.0.0 --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

# the last little catch was to give the postgres user a password so that remote 
# clients like navacat or Rails could login, keeping the insecure 'vagrant' 
# convention alive for these disposable boxes
su postgres
=> postgres@lucid32:/home/vagrant$
psql
=> psql (8.4.17)
ALTER USER Postgres WITH PASSWORD 'vagrant';
=> ALTER ROLE
\q
=> postgres@lucid32:/home/vagrant$

# now exit vagrant, and reload the virtual machine
exit
=> root@lucid32:/home/vagrant#
exit 
=> vagrant@lucid32:~$
exit
=> [host prompt]
vagrant reload
=> [default] Attempting graceful shutdown of VM...

# When your machine comes back up, you should 
# be able to connect from your host machine
# to the guest machine at port 5433
psql -h localhost -p 5433 -U postgres -W -d postgres
# password if "vagrant"
=> psql (9.2.2, server 8.4.17) ...
# quit the server with its exit command
\q 

# export the vagrant box to a package and the Vagrantfile 
# that sets the default memory at 512MB -- this can be
# overridden by the new Vagrantfile when the box is used
cd [project directory with Vagrantfile]
vagrant package --vagrantfile Vagrantfile
=> [default] Clearing any previously set forwarded ports...
 
# now add the package to the vagrant boxes on your system
vagrant box add lucid32-pg-8.4-utf package.box
=> Downloading or copying the box...
 
# at this point, your vagrant box is ready of use in a 
# new project with just a few commands
mkdir -p db/pg84/
cd db/pg84/
vagrant init lucid32-pg-8.4-utf
vagrant up
=> Bringing machine 'default' up with 'virtualbox' provider...
 
# at this point your server should be up and accessible
psql -h localhost -p 5433 -U postgres -W -d postgres
# password is "vagrant"
\l
\q
 
# happy databasing!