Postgresql datasource setup with lucee

Hi,

I just set up a postgress (version 10) db on a digital ocean box, attempted to set up a datasource with all the provided db info such as host, dbname, port, user/pwd etc. with lucee via admin, however, I got err msg [ FATAL: no pg_hba.conf entry for host “206.189.237.123”, user “myuser”, database “mydb”, SSL off ]; also, by default, they set sslmode:require.

In the meantime, I was able to connect to it via psql client without specifying ssl or sslmode at command line.

What do I need to do to create a datasource for this postgres db via lucee on ubuntu?

Thanks

Did you even Google that error?

YES to no avail.

https://confluence.atlassian.com/jirakb/error-connecting-to-database-fatal-no-pg_hba-conf-entry-for-host-x-x-x-x-user-jiradbuser-database-jiradb-ssl-off-950801726.html

1 Like

That’s helpful, which I appreciate.
Now, the thing is, since the postgresql db I created was via digital ocean’s create db service, I don’t seem to have terminal access to the host server/computer of the db server, and DO’s admin panel does not seem to have a way to allow one to edit the pg_hba_conf file. So, I’m stuck, unable to edit this file per the suggested solution. Or ?

It’s all on the same box? Try 127.0.0.1?

No, 127.0.0.1 does not make sense because it implies that the current DO box is the same as the postgresql server while DO’s info about the newly created postgresql db has a different host name for the db server. So, it didn’t work.

Show the connection string that you used from command line

It was my first time using psql client.
Something like
psql -h <host> -p <port> -U <username> -W <password> <database>

I then proceeded to create a test table, “mytable” and inserted a record and then retrieved it.

hi justaguy

you must add a line like this to /home/postgres/data/pg_hba.conf
to enable your to access to postgres db

host all all 206.189.237.123/32 md5

the values are relative to (from docs):
#TYPE DATABASE USER ADDRESS METHOD

1 Like

Good to know, but too bad we are unable to touch any file on the db server rented from DO because we do not have direct access to the db server’s file system.

If you’re renting the service, you need to be able to use it, and they need to provide a way for you to use it, otherwise, why are you paying for it?

It may be through cPanel. You might have to open a ticket. It sounds like you need to talk to DO, not us.

1 Like

Roberto is right. You also have to ensure that Postgres is listening on an IP other than the localhost loopback, which is default and listens only on 127.0.0.1 and ::1. That setting is in the postgresql.conf file.

Joe is right. If they do not enable access for you then what’s the point?

1 Like

@joe.gooch @isapir you both are right, if memory serves I raised the issue with DO and they didn’t respond, so, I’ve advised my client to cancel the postgresSQL server from DO.

Sounds like they didn’t go through the setup procedure properly. I had a look this morning and it asks you which droplets/IPs you want to allow access to Postgres which they might have missed.

1 Like