Les Is More
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.
Install Postgres with brew or apt
Be aware that authentication defaults are different depending on the platform package
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
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.
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
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.
BackThis is the website of Leslie Viljoen. More info
2021
March
2015
September
2014
December
September
July
April
March
February
January
2013
April
March
January
2012
July
2011
April
2008
January