PostgreSQL: Why can I connect to a user with a set password without having to give the password?

Question

After creating a new user john with:

CREATE USER john PASSWORD 'test';
CREATE DATABASE johndb OWNER john;

I can connect to the PostgreSQL server with:

psql -U john johndb

The problem is that psql never asks me for the password. I realy need to know what's wrong, because of obvious reasons.

Answer 1

Your pg_hba.conf file probably has local connections set to "trust". The default contains a section like this:

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
host    all         all         ::1/128               trust

This means that for all connections from the local machine, trust whatever the client says. If the client says "I am user john", then the server will permit that.

The PostgreSQL documentation has a whole section on the pg_hba.conf file.