12

I have the same question that was asked here Connect to Redshift via SSL using R

However, the answer given requires certificate validation. I'm wondering if there is a way to do this without certificate validation? When I connect via a sql client, I just add this

?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

How can I add these parameters in R?

Thanks in advance for any help!

Community
  • 1
  • 1
MariaS
  • 325
  • 1
  • 4
  • 13

4 Answers4

12

https://github.com/r-dbi/RPostgres seems to be the more modern and maintained package at this point. This is what worked for me...

install.packages("RPostgres")
require(RPostgres)

db = dbConnect(
  Postgres(), 
  user = 'user',
  password = 'password',
  dbname = 'dbname',
  host = 'host',
  port = port,
  sslmode = 'require'
)

dbListTables(db)
chrowe
  • 762
  • 6
  • 6
  • This gave me and my colleague the error `SSL SYSCALL error: Connection reset by peer`, until we upgraded `RPostgres` to version `1.2.0` (which was only available using the compile-from-source option and not the binary) and then it worked. – dcc310 Aug 03 '20 at 18:27
  • Can verify that the `sslmode` works for me using `RPostgres` v`1.2.0`. – Brian D Jan 19 '21 at 23:03
  • Perfect solution, THANK YOU! – Hantlowt Mar 10 '21 at 12:02
  • update: RPostgres v1.3.1 returns the same `sslmode='require'` error due to a bug in the release. See https://github.com/r-dbi/RPostgres/issues/291 – Brian D Mar 16 '21 at 18:05
6

Instead of passing verify-full to sslmode, try require or allow:

dbConnect(dbDriver('PostgreSQL'),
    dbname   = 'dbname=foobar sslmode=require',
    host     = 'foobar.redshift.amazonaws.com',
    port     = 5439,
    user     = 'foobar',
    password = 'foobar')
daroczig
  • 28,004
  • 7
  • 90
  • 124
  • 4
    adding the sslmode=require to the dbname causes crah of Rstudio for me – ilFonta May 16 '18 at 14:36
  • This didn't work for me neither, but @chrowe's solution did – moodymudskipper Mar 08 '19 at 10:15
  • I attempted this solution with drv argument as above `dbDriver('PostgreSQL')` and as `RPostgreSQL::PostgreSQL()` in both cases I got the following error: `Error in postgresqlNewConnection(drv, ...) : RS-DBI driver: (could not connect @: on dbname "": sslmode value "require" invalid when SSL support is not compiled in` – Brian D Jan 19 '21 at 23:09
3

This worked for me:

connection <- DBI::dbConnect(RPostgres::Postgres(),
                             dbname = "dbname",
                             host = "host",
                             port = "port",
                             password = "password",
                             user = "user_name",
                             base::list(sslmode="require", connect_timeout="10"),
                             service = NULL)

So the parameter key words can be passed as a list. This worked for RPostgres 1.1.1 and 1.2.0, as well.

Viktor Varallyay
  • 41
  • 3
0

I rely on R DBI and odbc packages. Here is setup I used with help from @Floris

  • First, create client SSL certificate and key, sign client certificate using postgres server root certificate, and also keep postgres server root certificate on client side at ~/.postgresql/. A client ssl key is owned and read-only by owner only. Here is a guide from jealastic.com that I followed to create server and client SSL certificates.

ls -alh ~/.postgresql/

drwx------  2 foo staff      122 Jul 23 10:45 ./
drwx------ 54 foo staff 2.1K Jul 23 10:45 ../
-rw-r--r--  1 foo staff      875 Jul 21 17:42 postgresql.crt
-rw-r--r--  1 foo staff      631 Jul 21 17:41 postgresql.csr
-r--------  1 foo staff      891 Jul 21 17:41 postgresql.key
-rw-r--r--  1 foo staff     1.1K Jul 21 17:40 root.crt
  • nano ~/.odbcinst.ini and add following:
[PostgreSQL Driver]
Driver              = /home/foo/anaconda3/envs/sql/lib/psqlodbcw.so

where odbc driver was installed in a conda env using conda create --name sql -c conda-forge psqlodbc.

  • nano ~/.odbc.ini and add one or more entry for database you like to connect with valid credentials:
[foodb]
Driver              = PostgreSQL Driver
Database            = foodb
Servername          = db.example.com
UserName            = foo
Password            = mypassword
Port                = 5432
sslmode             = require

Note that PostgreSQL Driver matches corresponding entry in ~/.odbcinst.ini For type of sslmode vs security and overhead, here is a good read: https://www.postgresql.org/docs/9.4/libpq-ssl.html

  • Fix permissions
chmod 600 ~/.odbc.ini ~/.odbcinst.ini
  • In RStudio GUI, either create a new connection and it should show an entry foodb from ~/.odbc.ini or type this into R console.
library(odbc)
foodb <- dbConnect(odbc::odbc(), "foodb", timeout = 10)

Done!

Samir
  • 724
  • 10
  • 18