When PostgreSQL is installed, it has its own user added, usually postgres or pgsql. By default, just this user will be able to connect to a datatabase, and without a password. If you are running as root, you can su to the postgresql user using ‘su - postgres‘, then run ‘psql databasename‘ to connect.

You may want to allow other users to connect with a password, or possibly you’d like to disable the postgresql user from connecting without a password. To do this, you need to edit pg_hba.conf, located in /var/lib/pgsql/data.

For example, to require postgres to log in with a password and only from the local machine, use the following line in pg_hba.conf:

local all postgres password md5

This means: on the local machine allow access to all databases for the postgres user with a password.

If you wanted to allow all users from host to connect to database foo with a password, you would do:

host foo all md5

For your changes to take effect, log in as postgres and issue the command ‘psql reload‘.

The complete documentation for PostgreSQL authentication is located here.
Command line options for psql are given here.

Digg It! del.icio.us Bookmarks Stumble It! Google Bookmarks