After I installed PostgreSQL 9.1 on Ubuntu 12.04 I set the password for the "postgres" superuser account. I want all users to have to enter their password when loging in. This is why I configured pg_hba.conf like so:
#Database administrative login by Unix domain socket
local all postgres md5
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
I restarted postgresql after making those changes. When I do this psql -U testuser I get asked for a password, but when I log in with the "postgres" account like so psql -U postgres I get no password prompt and am logged in.
If I force the password prompt with psql -U postgres -W I can log in by typing the correct password or by typing nothing at all. Typing a wrong password gets rejected.
Can anybody please explain to me why this is happening?
On a related note: I see a lot of example where people use ident as authentication method for the "postgres" user, arguing that to become the "postgres" user one needs the root password of the machine. I assume that the reasoning is that if an attacker gets root access, your done anyways. I would prefer to log in with a password though, one which is not the same as the root password. I prefere having different passwords for different things. Is this reasonable?
Output of grep '^[^#]' pg_hba.conf
local all postgres md5
local all all md5
host all all 127.0.0.1/32 md5