How to allow postgres login without password prompt on local connection?

Question

How do you configure PostgreSQL to allow the default postgres user to login without a password from localhost?

There are several similar questions, such as this and this, but none of the suggestions to modify pg_hba.conf have worked for me. However, based on my understanding of the pg_hba.conf rules, I should be able to accomplish this with either the peer or trust options.

I'm trying to run a command like:

sudo psql --user=postgres --no-password --command="blah;" -h 127.0.0.1

If I try this line in my pg_hba.conf:

local   all             postgres                                trust

my command fails with the error:

psql: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "postgres", database "postgres", SSL on
FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "postgres", database "postgres", SSL off

If I tried this line:

local   all             postgres                                peer

my command fails with the same error.

How do I fix this?

Answer 1

When you specify -h 127.0.0.1 that is not a "local" connection - it's a host (=TCP based) connection. So a line starting with local will not match for such a connection.

To make that entry work, do not specify a hostname or port for psql, then it will use a "local" connection through named pipes.

Alternatively, you can local with host and then add 127.0.0.1 as the (client) IP address to "trust".