no pg_hba.conf entry for host "my-ip", user "postgres", database "db-name", no encryption

[Juan_Manuel_Truffa]

Hi, I have a PostgreSQL v15 running on AWS Instance.
I can access it from python and using pgadmin4 with no problem.

When I use package RPostgreSQL I ran into:

no pg_hba.conf entry for host "my-ip", user "postgres", database "db-name", no encryption

I changed on the server the rds.force_sslto 0, with no luck.

Any other one stumbled with this?

[technocrat]

This bot take may help, although I’ve not tested it.

[Juan_Manuel_Truffa]

Can't fix IP address of the client as the post suggests because it runs on my travel laptop.

It's weird that python implementation works and R doesn't.

Need to keep researching. Thanks for your help

Jmt

[technocrat]

Can’t set 127.0.0.1?

[Juan_Manuel_Truffa]

Server runs in an RDS instance on AWS. I'm using RPostgreSQL package from my laptop.

What would be the purpose of setting localhost there? (not very much sophisticated from my side. )

Thanks,

[technocrat]

You're right--that wouldn't work at all. I was foolishly confused in thinking that not only was the SQL server running in AWS, but it was listening to an AWS RServer connection. I blame holiday stupor. Does looking at the form of the Python request provide any clues?

[andresrcs]

Can you share your pg_hba.conf content, have in mind that the first matching rule in this file is going to be applied regardless of the next ones. Other things to consider are that IPv6 only clients require a specific rule to match and lately the postgres user is not allowed to be passwordless and uses scram-sha-256 for encoding by default

[Juan_Manuel_Truffa]

The server runs on an rds instance. I think there's no access to the file. Instead some parameters in the console.

On a local server i've changed the password encryption to md5 (i guess) and worked. Dont know how to do it in this rds instance.

Any hints?

Thanks

[andresrcs]

I believe RDS enforces SSL connections, try adding sslmode="require" to your connection code. Something like this.

con <- odbc::dbConnect(RPostgreSQL::PostgreSQL(),
                       host = config::get('server'),
                       dbname = config::get('database'),
                       port = config::get('port'),
                       user = Sys.getenv('MY_UID'),
                       password = Sys.getenv('MY_PWD'),
                       list(sslmode="require")
)

[Juan_Manuel_Truffa]

Well, the first problem was solved.

Now running into:

Error in postgresqlNewConnection(drv, ...) :
RPosgreSQL error: could not connect postgres@databasespot1.c1jl5uf8n4xl.us-east-1.rds.amazonaws.com:5432 on dbname "basedatos": SCRAM authentication requires libpq version 10 or above

Strange since:

$ brew info libpq                                                                                                                 [10:35:06]
==> libpq: stable 16.1 (bottled) [keg-only]

I tried reinstalling libpq with no luck.

I'm running R from a Mac (M1). Don't have the same problem with an R installation in a linux (ubuntu 22).

[Juan_Manuel_Truffa]

Well, finally, after wiping out R, and all of the packages, I started installing everything from scratch and installed RPostgreSQL package and I was able to connect to the server.

for some strange reason, libpq, besides from having the 16.1 version, RPostgreSQL links to a previous version (that I couldn't find but got rid of it wiping out everything)

Thanks for your help. Now I'm able to execute SQL queries on the AWS Postgres Server from my mac laptop!