0

I have the following pg_hba.conf:

# 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            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

With the above I assume host all all 127.0.0.1/32 trust would allow TCP connections from localhost. However this does not seem to be the case

[root@XenonKiloCranberry:~]# psql -U postgres
psql (11.7)
Type "help" for help.

postgres=# \q

[root@XenonKiloCranberry:~]# psql -U postgres -h 127.0.0.1
psql: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "postgres", database "postgres", SSL off

Where am I going wrong?

Output of select * from pg_hba_file_rules:

line_number | type  | database | user_name | address |                 netmask                 | auth_method | options | error 
-------------+-------+----------+-----------+---------+-----------------------------------------+-------------+---------+-------
           1 | local | {all}    | {all}     |         |                                         | trust       |         | 
           2 | host  | {all}    | {all}     | ::1     | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust       |         | 
(2 rows)
6
  • Did you reload the conf files after editing? Maybe you have multiple copies and edited the wrong (unused) one?
    – jjanes
    Commented Jul 3, 2020 at 16:16
  • I restarted postgres and that didn't make any difference. Is there a way I can verify it's using the config file I expect it to use? Commented Jul 3, 2020 at 16:19
  • 1
    If you can connect by some other means (eg the local socket), you can then select * from pg_hba_file_rules;
    – jjanes
    Commented Jul 3, 2020 at 16:29
  • 2
    And show hba_file ;
    – jjanes
    Commented Jul 3, 2020 at 16:41
  • Thank you, that pointed me in the right direction. It seems indeed there was an alternative configuration. Feel free to post your message as an answer. Commented Jul 4, 2020 at 10:36

1 Answer 1

1

If you have restarted the database and the contents of the view pg_hba_file_rules doesn't match your pg_hba.conf file, then you probably have more than one such file, and edited the one which is not being used. If you can connect via another means, you can use show hba_file; to get PostgreSQL to tell you which file is the one that is being used.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.