Connection to AWS DB with RPostgres on M1 Mac

[samterfa]

Please briefly describe your problem and what output you expect. If you have a question, please don't use this form. Instead, ask on https://stackoverflow.com/ or https://community.rstudio.com/.

I have the exact same issue as this one that's been closed. I can connect to an AWS Redshift database just fine using RPostgreSQL but not RPostgres on my M1 MacBook Pro. I have copy and pasted the same code to a linux docker image running RStudio and I can connect just fine using RPostgres, but not on my M1 Mac using RPostgres. My colleague can connect using my exact same code on his Intel Mac. I can also connect using the RJDBC package. I can connect to the database using psql on the command line of my M1 Mac and run queries successfully so the connection between my laptop and the database seems solid. I have tried RPostgres::Redshift() but it didn't help. I simply receive this error immediately upon trying to connect.

Error: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

The same error happens when running R at the command line too so it's not an RStudio issue I don't think but I do primarily work inside RStudio.

Changing the port or host to an invalid one, or turning off wifi makes it think and think and end up failing to connect. If port and host are correct and it's connected to wifi and I use an invalid user, password, or database name, it still gives the above error. So it definitely reaches out to the server but then doesn't actually try to authenticate.

Please include a minimal reproducible example (AKA a reprex). If you've never heard of a reprex before, start by reading https://www.tidyverse.org/help/#reprex.

RPostgres::dbConnect(RPostgres::Postgres(), host = host, port = port, user = user, password = password, dbname = dbname, sslmode = 'require'). I've tried with and without specifying sslmode.

This definitely seems operating system specific; I'm just hoping you can point me in the right direction. I tried the fix from the related issue but to no avail.

Thank you!

[krlmlr]

Thanks for the detailed report. A Redshift connection works with my M1 Mac, but I never tried sslmode .

Would you be comfortable cloning the repository and running a tweaked version for debugging?

[samterfa]

Sure, I'm happy to dig in. Thanks for the quick response.

[krlmlr]

Thanks. Could you please try to find out which line of the dbConnect() call fails?

[samterfa]

It fails here with length(opts) > 0.
if (length(opts) == 0) { ptr <- connection_create(character(), character(), check_interrupts) } else { ptr <- connection_create(names(opts), as.vector(opts), check_interrupts) }

[samterfa]

Printing opts shows the correct dbname, user, host, and port. client_encoding is also set to "utf8". I tried setting it manually to utf16 and tried using check_interrupts as TRUE and FALSE but same error each time.

[samterfa]

I've further identified the issue happens in the DbConnection::DbConnection() function in the file DbConnection.cpp at pConn_ = PQconnectdbParams(&c_keys[0], &c_values[0], false);. This returns CONNECTION_BAD.

[krlmlr]

Thanks. I wonder what RPostgreSQL is doing differently. Perhaps we can slightly change what we do here, to improve matters.

[samterfa]

I'm happy to try anything. I don't think anything's likely wrong with the package. I imagine I have some symlink issue or something preventing this from working correctly.

[krlmlr]

If you could clone the RPostgreSQL code and take a look what the connection call is looking like there, and with what arguments?

[samterfa]

The connection call in RPostgreSQL is my_connection = PQsetdbLogin(host, port, options, tty, dbname, user, password); . I then changed the connection code for RPostgres to use PQsetdbLogin instead of PQconnectdbParams and received the same error as before for Redshift. Also, as before, I was able to successfully connect to an AWS Aurora database. I can't tell any difference between RPostgreSQL and RPostgres when both are using PQsetdbLogin to create the connection. The only question I have now is if RPostgres isn't using the same version of libpq on my system as RPostgreSQL. Any ideas on a good way to test that?

[krlmlr]

Thanks. When I run R CMD INSTALL . for RPostgres, I'm seeing:

Using PKG_CFLAGS=-I/Users/kirill/git/R/r-dbi/RPostgres/.deps/include
Using PKG_LIBS=-L/Users/kirill/git/R/r-dbi/RPostgres/.deps/lib -lbrewpq -lbrewpgport -lbrewpgcommon -lbrewssl -lbrewcrypto -lldap -dynamic -Wl,-search_paths_first -lkrb5
Using PKG_PLOGR=-DPLOGR_ENABLE

Perhaps RPostgreSQL installation shows similar information?

[samterfa]

I installed RPostgreSQL from source and noticed this in the install logs.
system -lpq don't appear to work; use internal configure: Using internal package libpq-fe.h checking for "src/libpq/libpq-fe.h"... yes configure: creating ./config.status config.status: creating src/Makevars

Above this, there were other checks that seem like they should have been successful, but weren't. For example, I can run pg_config on in terminal and get information, so checking for pg_config... no seems odd to me.

checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking build system type... aarch64-apple-darwin21.5.0
checking host system type... aarch64-apple-darwin21.5.0
checking target system type... aarch64-apple-darwin21.5.0
checking for pg_config... no
configure: checking for PostgreSQL header files
configure: Checking include /usr/include.
configure: Checking include /usr/include/postgresql.
configure: Checking include /usr/local/include.
configure: Checking include /usr/local/include/postgresql.
configure: Checking include /usr/local/pgsql/include.
configure: Checking include /usr/local/postgresql/include.
configure: Checking lib /usr/lib.
configure: Checking lib /usr/lib/postgresql.
configure: Checking lib /usr/local/lib.
configure: Checking lib /usr/local/lib/postgresql.
configure: Checking lib /usr/local/postgresql/lib.
gcc -g -O2 -I -L conftest.c -lpq -o pqconftest

[samterfa]

Also when I run DBI::dbGetInfo(RPostgres::Postgres()), I should get the version of libpq it's using, right? When I run it I get $client.version [1] ‘13.2’. However, I brew installed version 14.5 which is the one returned by which psql. Do I have a rogue version of libpq on my machine?

[krlmlr]

Good catch: 13.2 might come from our autobrew script. Can you try setting the DISABLE_AUTOBREW environment variable to 1, and rerun?

[samterfa]

Success! I had to set DISABLE_AUTOBREW to 1, and add /opt/homebrew/opt/libpq/bin to my PATH in RStudio, then install from source. RPostgres::dbGetInfo(RPostgres::Postgres()) now shows 14.5 as expected, and I can connect to the Redshift database!

Thanks so much for your patience with this!

[krlmlr]

Thank you for pushing through!

@jeroen: the autobrew version of libpq seems to be too old to support Redshift. What's the best way to proceed?

[jeroen]

I'll look into this. I upgraded the autobrew versions for MacOS 11.0+ (including arm) to libpq 14.5. So you can test those from:

install.packages("RPostgres", repos = 'https://r-dbi.r-universe.dev')

However CRAN still tarets macos high-sierra (10.13) and I'm having difficulty getting the new libpq to work on there.

[jeroen]

I've now also updated the other autobrew versions. So when you submit the next version to CRAN, new MacOS binaries will automatically be built with the new version.

[krlmlr]

@samterfa: Is this still an issue with current CRAN binaries?

[samterfa]

I no longer use or have access to Redshift at work so I can't test this. You can close it. Thank you for your follow up!