psql -U someuser -W some_db doesn't respect the password?

Question

I've got a database and a role I set up through PSQL like...

CREATE ROLE someuser WITH LOGIN PASSWORD 'S0m3u53r';
CREATE DATABASE some_db OWNER someuser;

I noticed that I can enter any password I want when I try to connect as that user. Below is the output of a psql session where I entered "fizzbuzz" as the password. Psql just let me in, never bothering to check the password.

$ psql -U someuser -W some_db
Password for user someuser:
psql (9.4.1)
Type "help" for help.

some_db=> select current_user;
 current_user
--------------
 someuser
(1 row)
some_db=>

Why doesn't psql enforce that user password? I expected it to reject me somehow when I entered the wrong password but psql doesn't seem to care. I must be missing something here.

Answer 1

The postgres user authentication settings are stored in a file called pg_hba.conf.

Your current settings allow anyone connecting locally, via unix sockets to be trusted, i.e. no password will be required at login. As long as the user exists in the database, it will be allowed to login.

You can change that to md5 or password for local connections For connections over the network, password isn't sufficiently secure, as it sends the raw textual password.

Find your pg_hba.conf file. Near the bottom of the file is a line that reads like:

# "local" is for Unix domain socket connections only
local   all             all                                     trust

Change it to

# "local" is for Unix domain socket connections only
local   all             all                                     md5

The pg_hba.conf file's location depends on what system you're on. On Ubuntu, the file would be in

/etc/postgresql/9.4/main/pg_hba.conf

On OSX (installed via homebrew), the file's path is

/usr/local/var/postgres/pg_hba.conf

To find out more about authentication in postgres, see the official documentation