2

I'm standing up a new application and having some issues with host based authentication. I know there are other posts out there (espec this one) but nothing I try seems to work.

My error:

no pg_hba.conf entry for host "::1", user "root", database "db_name_here", SSL off

My pg_hba.conf file looks like:

# TYPE  DATABASE    USER  ADDRESS METHOD
local   all         all           peer map=usermap
local   replication all           peer map=usermap
hostssl all         all   all     md5
host    replication all   all     md5

I have a note to myself from the past where I said the following worked:

host   db_name_here   root  ::1  trust

But it doesn't.

I've even tried:

all     all     all     all     trust

even that didn't work, same error.

I've read the postgresql docs, and while the helped explain what was going on, (and made me try hostnossl, to no avail) I'm still getting the same error.

Do you see my mistake?

208_man
  • 1,440
  • 3
  • 28
  • 59
  • Either you have edited the wrong hba file, or have not restarted/reloaded the database after editing the right one. – jjanes Mar 12 '21 at 02:32
  • Thanks @jjanes, I have been cycling the app (on the same server), but *not* the databse. I think you've hit the nail on the head. – 208_man Mar 12 '21 at 14:10

4 Answers4

3

Your pg_hba.conf entry is missing the netmask; a plain IP address is a syntax error. This one should work:

host   db_name_here   root  ::1/128  trust

Don't use trust for anything but tests. As soon as you get that to work, replace it with a real authentication method like scram-sha-256.

Make sure you reload the database with

pg_ctl -D /path/to/datadir reload

and check the log file for errors (that is important, as a syntactically wrong file won't be loaded).

The alternative is to enable SSL on the server side and use it for the local connection.

The fastest way to connect would be via Unix sockets (if you are not on Windows or use the JDBC driver), perhaps that is the best thing to do.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • thanks @Laurenz-albe! I think this will do it! – 208_man Mar 12 '21 at 14:35
  • Been trying to execute this in various ways @laurenz-albe w/out success. After I edit the pg_hba.conf file to include the new entry, the postgresql service won't run. – 208_man Mar 12 '21 at 20:35
  • Strictly speaking, I was able to edit `pg_hba.conf`, then `su - username` into postgres, run the `pg_ctl...` command you shared - all without errors. But when I start the application using the db, I still get the error `no pg_hba.conf entry for host "::1", user "root", database "db_name_here", SSL off` – 208_man Mar 12 '21 at 20:44
  • It's probably noteworthy that if I try using `systemctl` to stop/start postgresql it errors out. Remove that entry from `pg_hba.config` and this issue goes away. Seems like everything choking on this entry. – 208_man Mar 12 '21 at 20:45
  • Ironically if I don't touch `systemctl ... postgresql` when making the edit to `pg_hba.conf` (and just use the pg_ctl to reload pg) then `systemctl status postgresql` returns an active status as though all is well. – 208_man Mar 12 '21 at 20:47
  • Argh, sorry about that. Your entry missed the netmask. I have fixed the answer. Always check the log file after reloading. – Laurenz Albe Mar 15 '21 at 06:57
  • Thank you @laurenz-albe, trying that currently – 208_man Mar 15 '21 at 16:17
  • (Late to the party response) - I wanted SQL Developer to connect. I was getting "Status : Failure -Test failed: FATAL: no pg_hba.conf entry for host user database , SSL encryption" I followed Laurenz Albe advice. Added "host all mydatabase XXX.XXX.XX.XXX/32 trust" I can now connect. Thanks – Marinaio Jan 17 '23 at 16:47
  • 1
    @Marinaio Use something other than `trust`. – Laurenz Albe Jan 18 '23 at 07:13
2

Have you tried allow IPv6 localhost seperately like this

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             ::1/128                 trust
ex4
  • 2,289
  • 1
  • 14
  • 21
0

Add sslmode=Require; and Trust Server Certificate=true; in your connection string.It will work for sure.

Arman Abdullah
  • 11
  • 2
0

In my case, the DB is hosted on a different server and we get access credentials for the DB server. So, I only had to use sslmode:require while creating the connection string, like this

dbURI := fmt.Sprintf("sslmode=require host=%s port=%s user=%s dbname=%s password=%s", dbHost, dbPort, username, dbName, password)
log.Println("Postgres connection string: " + dbURI)

conn, err := gorm.Open("postgres", dbURI)

return conn
simarmannsingh
  • 23
  • 1
  • 5