R RPostgreSQL使用SSL连接到远程Postgres数据库 [英] R RPostgreSQL Connect to Remote Postgres Database with SSL

问题描述

我正在尝试使用 RPostgreSQL 包从R中连接到远程PostgreSQL数据库，并且出现与SSL设置有关的错误。连接。我已验证可以使用 psql 从命令行进行连接，因此我知道该连接有效并且可以从我的计算机访问。

I am trying to connect to a remote PostgreSQL database from within R using the RPostgreSQL package, and I am getting errors that appear to be related to the SSL settings for the connection. I have verified that I can connect from the command line using psql, so I know the connection is valid and accessible from my computer.

我在R中进行首次连接的尝试如下（其中< MyHost> 和< MyPass> 已为我的连接正确填写）：

My first attempt at connecting in R was the following (where <MyHost> and <MyPass> were filled out appropriately for my connection):

library(RPostgreSQL)
pg <- dbDriver( 'PostgreSQL' )
con = dbConnect( pg, host=<MyHost>, dbname='warehouse', 
                 user='warehouse', password=<MyPass>, port=5432 )

我在R中收到的错误是：

The error I received in R was:

RS-DBI driver: (could not connect warehouse@<MyHost>.com:5432 on 
dbname "warehouse": FATAL:  no pg_hba.conf entry for host "75.166.243.177", 
user "warehouse", database "warehouse", SSL off

表示我需要对连接应用SSL加密，因此我尝试了以下内容，似乎适用于此处的OP：通过SSL连接到Postgres使用R

I understood this to mean that I needed to apply SSL encryption to my connection, and so I tried the following, which seems to have worked for the OP here: Connect to Postgres via SSL using R

library(RPostgreSQL)
pg <- dbDriver( 'PostgreSQL' )
con = dbConnect( pg, host=<MyHost>, dbname='dbname=warehouse sslmode=require', 
                 user='warehouse', password=<MyPass>, port=5432 )

然后我收到的错误是：

RS-DBI driver: (could not connect warehouse@<MyHost>:5432 on dbname 
"warehouse": sslmode value "require" invalid when SSL support is not 
compiled in

我发现了这篇文章（ Postgres SSLMode值 require当未使用Foreign Data Wrapper编译SSL支持时无效），这表明我的PostgreSQL构建版本可能未包含-with-openssl 标志编译，但运行pg_config显示此标志确实在我的构建中设置。这是有道理的，因为使用psql命令行工具时我能够使用SSL连接到数据库。

I found this post (Postgres SSLMode Value "require" Invalid When SSL Support Is Not Compiled Using Foreign Data Wrapper) that suggested my build of PostgreSQL may not have had the --with-openssl flag included when it was compiled, but running pg_config revealed that this flag was indeed set in my build. This makes sense because I was able to connect to the database using SSL when I used the psql command line tool.

由于担心PostgreSQL安装可能出错，因此我将其完全删除它。然后，我运行了以上两个R代码片段（仅用于踢球），并且收到了完全相同的错误。我没想到这一点，但这似乎表明（a）我不需要本地安装PostgreSQL即可使用 RPostgreSQL 软件包，或（b）我在我的计算机上其他地方安装了混杂的PostgreSQL，该计算机不是 用 --with-openssl 标志构建的。我曾在命令行中尝试过 locate postgresql ，但没有发现明显的东西看起来像是单独安装的。无论如何，我仍然无法从R内部成功连接到远程PostgreSQL数据库。

Fearing my PostgreSQL installation may be at fault, I completely removed it. I then ran both of the above snippets of R code, just for kicks, and I received exactly the same errors. I was not expecting this, but this seems to indicate that either (a) I do not need a local install of PostgreSQL in order to use the RPostgreSQL package or (b) I have a confounding install of PostgreSQL somewhere else on my computer that was not built with the --with-openssl flag included. I have tried locate postgresql at the command line and didn't see anything obvious that looked like a separate install. In any case, I still haven't been able to successfully connect to the remote PostgreSQL database from within R.

我一直在这里检查Stack Overflow以获取见识，并且它似乎有许多类似性质的未解决问题，其中用户由于某种原因无法与远程服务器建立连接。我尝试在此处安装KirillMüller维护的RPostgres软件包（ https://github.com/rstats-db / RPostgres ），但是即使尝试使用 R CMD INSTALL ，如提示的结果错误消息所示（尽管我可能在这里错过了一些东西...）。听起来有些用户使用 RPostgres 比使用 RPostgreSQL 更好，但是再次，我无法安装此软件包。

I've scoured Stack Overflow for insight here, and it seems there are a number of unresolved questions of a similar nature, where users are unable to establish connections with a remote server for some reason. I tried to install the RPostgres package that Kirill Müller is maintaining here (https://github.com/rstats-db/RPostgres), but I could not get the R package install to recognize my local PostgreSQL install, even after trying to specify the include and lib paths manually using R CMD INSTALL, as the resulting error message suggested (I may have missed something here though...). It sounds like some users have had better luck with RPostgres than with RPostgreSQL, but again, I wasn't able to install this package.

我对其他事情一无所知，如果我不知道该怎么做，就不得不求助于其他技术。尽快建立连接。任何帮助将不胜感激。

I'm at a loss for what else to do, and am going to have to resort to other technology if I can't figure out how to establish a connection soon. Any help would be greatly appreciated.

推荐答案

感谢发布！您实际上帮助我们解决了问题。这是我们的操作方法：

Thanks for posting! You actually helped us solve it. Here is how we did it:

• 通过重新启动计算机并按住 Command 将Mac重新引导到恢复模式+ R ，直到Apple徽标出现在屏幕上。


• 单击实用工具> 终端。


• 在终端窗口中，键入 csrutil disable 并按Enter。


• 重新启动Mac。


• 打开终端。


• 运行 sudo rm / usr / lib / libpq.5.dylib


• 运行 sudo ln -s /Applications/Postgres.app/Contents/Versions/9.6/lib /libpq.5‌.dylib


• 现在您可以连接到数据库了。

• Reboot your Mac into Recovery Mode by restarting your computer and holding down Command+R until the Apple logo appears on your screen.
• Click Utilities > Terminal.
• In the Terminal window, type in csrutil disable and press Enter.
• Restart your Mac.
• Open a Terminal.
• Run sudo rm /usr/lib/libpq.5.dylib
• Run sudo ln -s /Applications/Postgres.app/Contents/Versions/9.6/lib/libpq.5‌​.dylib
• Now you can connect to the DB.

这篇关于R RPostgreSQL使用SSL连接到远程Postgres数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！