PostgreSQL authentication quick start

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 ident (or peer) with 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.

Leave a Reply

Your email address will not be published. Required fields are marked *