Why pg_dump 'trust' authentication fails under root and works under another user?

Question

I have pg_dump command in cron that accesses PostgreSQL under user postgres and does not provide a password.

pg_dump --username=postgres ...

I have set in pg_hba.conf that user postgres should be trusted for all databases from localhost.

local all postgres trust

When I run the command under regular user it works.
When I run it under root (sudo -i) it requires password, and therefore the crontab task fails to authenticate.

Could someone explain to me what is happening here?

Disclaimer: I am Linux n00b.

Answer 1

This guy is my hero: http://archives.postgresql.org/pgsql-admin/2011-03/msg00228.php

The "--host=localhost" option says you're using TCP/IP connection (so the "host lines" of pg_hba.conf), so it's asking for md5 authentication. Hence the password.

If one user is not asked for a password, it could be that this user has a .pgpass file telling pg_dump which password to use.

In other words the --host switch (which I did not mention) was the reason why my 'local' lines in pg_hba.conf were irrelevant for this command.