Postgresql pg_hba.conf changes are ignored or not loaded

pg-hba.confpostgresqlpostgresql-9.3Ubuntu

Using Postgresql 9.3 on Ubuntu 14.04.4 LTS

I've been changing the /etc/postgresql/9.3/main/pg_hba.conf config file madly trying to make a change that will allow me to log in with any user remotely.

My current configuration looks like so:

host all postgres <remote-ip>/32 trust
asdf asdf asdf
1 1
1-1-1-1-

This is, of course, a completely bogus config file. However, when I run

sudo service postgresql restart
sudo service postgresql reload

Both commands succeed with status [ OK ] – there are no parse errors. I have also disabled all entries that start with local and yet I am still able to login as the postgres user.

Any attempts to log into the server remotely from the host
using:

psql -c 'SELECT version()' -h pg -U postgres

Result in:

psql: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"

I am clearly not trying to use password authentication.

EDIT:

Running SHOW hba_file; as the postgres user yeilds

                hba_file
--------------------------------------
 /etc/postgresql/9.3/main/pg_hba.conf
(1 row)

Edit 2:

Checking the logs shows that the parse errors were happening. I have cleaned out the bogus lines, leaving

host all postgres <remote-ip>/32 trust

I am still getting a password prompt for some reason. There is nothing in the log after a failed login.

Best Answer

Figured it out. This was a long shot, but it may help someone who has exhausted the normal avenues or is not used to postgres.

The server I was working on had another instance running through puppet, and so the config I was modifying was not for the instance I was connecting to.

run find / -name 'postgresql.conf' to see if there are any other locations where you might have a postgres instance.

Related Solutions

Postgresql – How to dump PostgreSQL database whose owner has no password

See this part of the pg_dump manpage:

   -W, --password
       Force pg_dump to prompt for a password before connecting to a database.
       This option is never essential, since pg_dump will automatically prompt
       for a password if the server demands password authentication.

Don't use -W at all. In your case, it's just confusing.

Also, you need to know that the fact that the server asks for a password or not is not driven by the existence of this password.

It's driven by the server-side pg_hba.conf file that you need to study and possibly modify according to your needs (don't forget to reload the server after modifying it).

EDIT: reviewing your pg_hba.conf. The relevant lines are:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

The 1st line concerns the postgres user. It's irrelevant for your pg_dump command since you're using the santa user with -U santa

The 2nd line concerns any other connection through Unix domain sockets (TYPE column is local). From the client, it means when you do not use -h localhost. It says that if the OS user is the same name than the db user, he doesn't need a password.

The 3rd line says that if -h localhost is used (IPv4 TCP connection), a password will always be asked to the client. The 4th line says the same with IPv6.

Based on this, this command run by the santa OS user should not ask or need a password:

 pg_dump --no-owner myapp_db > myapp_db_backup.sql

-U santa is optional because the db username is taken as the OS user by default.

Postgresql – Authentification failure for PostgreSQL server (9.3)

I found a temporary solution to my problem. I edited the pg_hba.conf of the 9.3 server to say trust in the first two active lines. And after restarting (sudo service postgresql restart) I can connect to the server again using pgadmin. The downside is that the server might now not be password protected (I will open a separate question if I encounter any problems with that).

    # Database administrative login by Unix domain socket
    local   all             postgres                                trust

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   all             all                                     trust
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
    # IPv6 local connections:
    host    all             all             ::1/128                 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local   replication     postgres                                peer
    #host    replication     postgres        127.0.0.1/32            md5
    #host    replication     postgres        ::1/128                 md5