PostgreSql - Create user and DB

To create a user:

createuser -d -P -l username

To create a database:

createdb -W -U username dbname

Test with:

psql -d dbname -U username -W

if you get an error message saying "psql: FATAL: Ident authentication failed for user 'username'"

vi /etc/postgresql/8.4/main/pg_hba.conf

By default Postgresql uses IDENT-based authentication. All you have to do is allow username and password based authentication for your network or webserver. IDENT will never allow you to login via -U and -W options. Append following to allow login via localhost only:

local all all trust
host all all 127.0.0.1/32 trust

for any host:

local all all trust
host all all 0.0.0.0/0 trust

You will have to restart the service

/etc/init.d/postgresql-8.4 restart

NOTE: If you want the server to prompt for a password instead of trust use password but this method sends the password in cleartext over the network.