ralreegorganon

psql doesn’t let you pass it a password at the command line (and with good reason–otherwise everyone would be able to see it with ps) but it can be somewhat frustrating if you’re trying to use psql in a script, as it will prompt for the password. One way to deal with this is to create a .pgpass file in your home directory. The lines in the file should be in the following format:

hostname:port:database:username:password

All values but the password can have * to match anything. Additionally, the file needs to have its permissions restricted, otherwise it will be ignored.

chmod 0600 ~/.pgpass

With that in place, you should be able to run psql from the command line without being prompted for a password.

More of a note to myself than anything:

# log in to the database template1:
psql template1

# issue command to create new database:
create database template_postgis with template = template1;

# update pg_database table to indicate that new database is a template
UPDATE pg_database SET datistemplate = TRUE where datname = 'template_postgis';

# connect to new database
\c template_postgis

# add PostGIS extensions and grant access to everyone to spatial tables.
CREATE LANGUAGE plpgsql ;
\i /usr/share/postgresql-8.3-postgis/lwpostgis.sql;
\i /usr/share/postgresql-8.3-postgis/spatial_ref_sys.sql;
GRANT ALL ON geometry_columns TO PUBLIC;
GRANT ALL ON spatial_ref_sys TO PUBLIC;

# prevent further changes to this database
VACUUM FREEZE;