No password prompt for postgresql superuser

Question

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

Answer 1

Your pg_hba.conf should indeed require a password for unix socket connections, but there are still ways around it that you should verify:

1. a .pgpass file in the postgres home directory containing the password (also check the PGPASSFILE environment variable for a non-standard path).

2. the PGPASSWORD environment variable could be set.

And there's also the possibility that you're editing the wrong pg_hba.conf file. When connected as postgres, the correct path can be obtained for verification with the SHOW hba_file SQL command.

Also, you may want to check the log file, /var/log/postgresql/postgresql-9.1-main.log for confirmation that the configuration files are reloaded when you ask for it, and look for any suspect message during the authentication.

As for the reason why passwordless connections with the postgres user are common, the debian PG-9.1 pg_hba.conf has this comment about disallowing them:

# DO NOT DISABLE!  
# If you change this first entry you will need to make sure that the  
# database superuser can access the database using some other method.  
# Noninteractive access to all databases is required during automatic  
# maintenance (custom daily cronjobs, replication, and similar tasks).  
#  
# Database administrative login by Unix domain socket  
local   all             postgres                                peer  

Since Debian and Ubuntu use the same postgres packages, this applies to Ubuntu as well.

Answer 2

Re your odd behaviour, I think you've missed a line of pg_hba.conf that's specific to the postgres user. Please show the output of:

grep '^[^#]' pg_hba.conf 

As for ident vs md5; personally I prefer ident for interactive use in development, and it's fine for normal users, but I don't think giving access to the postgres user via sudo is a great idea. Both sudo -u postgres psql and psql -U postgres -W grant access to the postgres superuser role and thus file system access as the database user. Neither require a root password, and sudo can easily be constrained via sudoers to limit the invoking user to just running psql. However, with sudo -u postgres psql the client code runs as postgres too, so it's a bigger attack surface, and there's always the chance of the user finding a way to bypass your sudoer limits.

I use ident in dev, md5 in production.