无法使用SSL创建dbConnect到Postgres [英] Can't create dbConnect to Postgres with SSL

问题描述

我正在运行一个Postgres-9.4服务器，该服务器需要SSL.当我使用pgadmin或Windows odbc连接从笔记本电脑连接到Postgres服务器时，它可与SSL一起使用.但是，当我尝试使用SSL与R连接时，就会失败.

I'm running a Postgres-9.4 server that I would like to require SSL for. When I connect to the Postgres server from my laptop with either pgadmin or windows odbc connection, it works with SSL. However when I try to connect with R using SSL it fails.

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, 
                 user = "postgres", 
                 password = mypasswd, 
                 dbname = "dbname=postgres sslmode=prefer",
                 host = "192.168.1.179")

如果我将pg_hba.conf设置为允许非SSL连接，则可以使用.当我将其设置为仅允许SSL连接时，它将失败.不幸的是dbConnect没有verbose选项，所以我得到的除了could not connect postgres@192.168.1.179 on dbname "postgres"

If I set my pg_hba.conf to allow non-ssl connections then this will work. When I set it to only allow SSL connections then this will fail. Unfortunately dbConnect doesn't have a verbose option so I don't get anything more than could not connect postgres@192.168.1.179 on dbname "postgres"

我发现了这个问题，这似乎表明我在做正确的事，但走不走.

I found this question which seems to suggest I'm doing the right thing but, no go.

我做了一些进一步的挖掘，发现了此讨论这表明由于种种库/dll问题，这在Windows上将不起作用.目前，该讨论已经进行了几年，因此也许已经解决了.我可以确认从Linux执行上述操作确实可以.

I did some more digging and found this discussion which suggests that this won't work on windows due to various library/dll issues. That discussion is a few years old at this point so perhaps it has been resolved. I can confirm that doing the above from linux does work.

推荐答案

我对问题的理解是RPostgreSQL使用的客户端不支持SSL.

My understanding of the problem is that RPostgreSQL uses a client that doesn't support SSL.

我切换到软件包RPostgres并使它工作.我先用install.packages('RPostgres')安装了RPostgres，然后使用此软件包与sslmode ="require"连接.

I switched to the package RPostgres and got it to work. I first installed RPostgres with install.packages('RPostgres') then used this package to connect with sslmode="require".

library('RPostgres') #Instead of library('RPostgreSQL')
con <- dbConnect(RPostgres::Postgres(),
                 user = "postgres", 
                 password = mypasswd, 
                 dbname = "postgres",
                 host = "192.168.1.179",
                 sslmode = "require")
)

这篇关于无法使用SSL创建dbConnect到Postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！