A fair number of programs require that you give a password to access a PostgreSQL database. This should be considered a bug, since it is trivially easy to write code that works with or without password authentication. This is simply laziness on the part of the programmers. (Examples include phppgadmin and qgis, although the qgis folks were kind enough to fix this bug at my request.) What this means is you often have to set up password authentication for PostgreSQL whether you want to or not, and setting up password authentication in PostgreSQL is a real pain.
A convenient option is to use PAM for authentication. That way, users will be queried for their login password and you will not have to create additional passwords stored in PostgreSQL. The problem is that it does not work, at least without a small fix. (This is specific to Ubuntu, but should apply to other Linux distros as well.)
First find the pg_hba.conf file. Edit the file and change entries like
# "local" is for Unix domain socket connections only
local all all md5
to this
# "local" is for Unix domain socket connections only
local all all pam other
This will use the "other" service of PAM which in Ubuntu just calls the default PAM modules. Now reload your PostgreSQL config and try to start a client session with psql. It will fail.
The problem is that the PostgreSQL backend runs without root privileges (on purpose as it would be a security issue). The fix is to add the postgres user to the shadow group. Note that this will only give read access to /etc/shadow. Still, it is a security risk, so do not do this on your open use server. To update the shadow group, type
sudo usermod -G shadow -a postgres
in the console. Now try psql. Ahhh.