title: No password prompt for postgresql superuser
body: <p>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:</p>
  
  <pre><code>#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
  </code></pre>
  
  <p>I restarted postgresql after making those changes. When I do this <code>psql -U testuser</code> I get asked for a password, but when I log in with the "postgres" account like so <code>psql -U postgres</code> I get no password prompt and am logged in.
  If I force the password prompt with <code>psql -U postgres -W</code> I can log in by typing the correct password or by typing nothing at all. Typing a wrong password gets rejected.</p>
  
  <p>Can anybody please explain to me why this is happening?</p>
  
  <p>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?</p>
  
  <p>Output of <code>grep '^[^#]' pg_hba.conf</code></p>
  
  <pre><code>local   all             postgres                                md5
  local   all             all                                     md5
  host    all             all             127.0.0.1/32            md5
  </code></pre>