Connecting to PostgreSQL from command line can be a bit confusing.
For starter, just like MySQL,
psql command defaults to connecting to socket instead of tcp. To make matter confusing, most PostgreSQL installation defaults to
ident (also called
peer)authentication for socket connection: it basically matches current user’s username (ssh login, etc) with PostgreSQL equivalent.
So, instead of using this to login from root to PostgreSQL superuser (usually named postgres or pgsql):
# psql -U postgres
you do this (assuming sudo installed):
# sudo -u postgres psql
The configuration for this is located in
pg_hba.conf of PostgreSQL data (or config in Debian) directory (
/etc/postgresql/$version/main in Debian,
/usr/local/pgsql/data in FreeBSD,
/opt/PostgreSQL/$version/data in EnterpriseDB PostgreSQL).
To switch to password based authentication for all methods just replace
md5 in respective lines and reload/restart the service. Don’t forget to set password for postgres user first before changing this otherwise you won’t be able to connect. You can then connect using
psql to any user using password.