Postgres authentication and configuration

Updated: 14 Mar 2014

Postgres authentication can be a pain. This is how to check everything for a local, insecure database that listens on a TCP port and allows a simple password login. Do more reading to secure proper production databases.

This has been tested on Debian 7 (Wheezy), Ubuntu 12.x and Postgres 9.1.

New Postgres setup checklist

1. Install Postgres with brew or apt

2. Be aware that authentication defaults are different depending on the platform package

3. If you want to connect from outside the current box, check that Postgres is listening on something other than localhost in /etc/postgresql/.../postgresql.conf:

listen_addresses = 'localhost'   # listen_addresses = '*'  for all

1. Check authentication settings in /etc/postgresql/.../pg_hba.conf:

host    all             all             127.0.0.1/32            md5

127.0.0.1/32 means only local connections. Change to 0.0.0.0/0 to listen for connections from other machines. The md5 means a client needs to hash passwords it sends. Rails seems to work with this. If you want insecure plain text local passwords, use 'password'.

Newer postgres uses 'peer' instead of 'ident', both of which make use of the local UNIX username for authentication - only peer is more secure than ident. More details: Postgres auth doc

SO: If you change the line to this:

host    all             all             0.0.0.0/0    password

You should be able to log in with psql, as long as you specify the username with -U, host with -h and port with -p. The -h tells psql to use TCP.

1. Of course this assumes you have a username in the first place!

Most installations come with ident/peer authentication for the system user 'postgres'. This means you can sudo su postgres and psql -d postgres to log in with a super user. If that works you can create another postgres super user like so:

sudo su postgres
createuser -h hostname -p port -s -P

Initial Postgis setup

Installing Postgres 9.1 and Postgis 1.5 involves installing the packages or building them with brew and then creating a template database to hold all the Postgis goodies. It will be a little different in Postgres 9.2.

On Ubuntu 12/13 or Debian 7 you install them like this:

wajig install postgresql-9.1
wajig install postgis
wajig install postgresql-9.1-postgis

You create the template database like this:

sudo su postgres
createdb template_postgis
createlang plpgsql template_postgis
psql -d template_postgis -f /usr/share/postgresql/8.4/contrib/postgis.sql
psql -d template_postgis -f /usr/share/postgresql/8.4/contrib/spatial_ref_sys.sql

After that you can import your exported database.