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!
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)
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.
sslmode works for me using RPostgres v1.2.0.
sslmode='require' error due to a bug in the release. See github.com/r-dbi/RPostgres/issues/291Instead 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')
dbDriver('PostgreSQL') and as RPostgreSQL::PostgreSQL() in both cases I got the following error: Error in postgresqlNewConnection(drv, ...) : RS-DBI driver: (could not connect <user>@<host>:<port> on dbname "<dbname>": sslmode value "require" invalid when SSL support is not compiled inThis 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.
I rely on R DBI and odbc packages. Here is setup I used with help from @Floris
~/.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
chmod 600 ~/.odbc.ini ~/.odbcinst.ini
library(odbc)
foodb <- dbConnect(odbc::odbc(), "foodb", timeout = 10)
Done!
