[postgresql] How do I login and authenticate to Postgresql after a fresh install?

Did a new install of postgres 8.4 on mint ubuntu. How do I create a user for postgres and login using psql?

When I type psql, it just tells me

psql: FATAL: Ident authentication failed for user "my-ubuntu-username"

The main difference between logging in with a postgres user or any other user created by us, is that when using the postgres user it is NOT necessary to specify the host with -h and instead for another user if.

Login with postgres user

$ psql -U postgres 

Creation and login with another user

# CREATE ROLE usertest LOGIN PASSWORD 'pwtest';
# CREATE DATABASE dbtest WITH OWNER = usertest;
# SHOW port;
# \q

$ psql -h localhost -d dbtest -U usertest -p 5432

GL

Source

If your database client connects with TCP/IP and you have ident auth configured in your pg_hba.conf check that you have an identd installed and running. This is mandatory even if you have only local clients connecting to "localhost".

Also beware that nowadays the identd may have to be IPv6 enabled for Postgresql to welcome clients which connect to localhost.

by default you would need to use the postgres user:

sudo -u postgres psql postgres

you can also connect to database as "normal" user (not postgres):

postgres=# \connect opensim Opensim_Tester localhost;

Password for user Opensim_Tester:    

You are now connected to database "opensim" as user "Opensim_Tester" on host "localhost" at port "5432"

The error your are getting is because your-ubuntu-username is not a valid Postgres user.

You need to tell psql what database username to use

psql -U postgres

You may also need to specify the database to connect to

psql -U postgres -d <dbname>