PostgreSQL Add Database

rc4:~# su -p postgres
rc4:~$ createdb appname_production
CREATE DATABASE
rc4:~$

Then give your database user the ability to edit it.

rc4:~$ psql appname_production
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

forrest=# GRANT ALL ON DATABASE appname_production to tarzan;
GRANT
forrest=#

At this point you can login as tarzan

tarzan@rc4:~$ psql -U tarzan --password appname_production
Password for user appname_production:
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

But if you try to login as jane even if you tell psql that you are tarzan, and use tarzan’s password you will be locked out.

jane@rc4:~$ psql -U tarzan appname_production --password
Password for user tarzan:
psql: FATAL:  Ident authentication failed for user "tarzan"

So you need to edit some postgress permissions. Switch to the directory with the file to edit.

rc4:~# cd /etc/postgresql/8.3/main/
rc4:cd /etc/postgresql/8.3/main# ls
environment  log  pgdata  pg_hba.conf  pg_ident.conf  postgresql.conf  start.conf

Backup the config file first in case you accidentally break something. When in doubt always backup config files no matter how simple the edit.

rc4:/etc/postgresql/8.3/main# cp -a pg_hba.conf pg_hba.conf.backup2007Aug15

change this…

# "local" is for Unix domain socket connections only
local   all         all                               ident sameuser

to this…

# "local" is for Unix domain socket connections only
local   all         all                               md5

Then restart PostgreSQL

rc4:/etc/postgresql/8.3/main# invoke-rc.d postgresql-8.3 restart
Restarting PostgreSQL 8.3 database server: main.

<< PostgreSQL Add Users
Deploying your Rails application >>