Connect to Postgres via SSL using R

r postgresql
sslmode value "require" invalid when ssl support is not compiled in
rpostgres
rpostgresql github
error in connection_create(names(opts), as vector opts fe_sendauth no password supplied)
rstudio odbc connection

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!

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')

How to add SSL Certificate to postgresql - dbi, I am trying to connect to a Postgres database which requires a client SSL I have the following code which when run, gives the error: "R Session Aborted. The session was terminated" in a white pop-up box, with the only  I can not connect PostgreSQL(on Heroku) with RPostgreSQL on Windows(64bit). I can connect local postgresql(not ssl) with RPostgreSQL on Windows. I can connect PostgreSQL on Heroku from PgAdmin on Windows. I can connect PostgreSQL on Heroku with RPostgreSQL on OSX and Linux. Only RPostgreSQL on Windows can not connect Heroku PostgreSQL.

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 Connection failing · Issue #122 · r-dbi/RPostgres · GitHub, Have you tried interacting with the SSL parameters directly? con <- dbConnect( RPostgres::Postgres(), host = "[]", port = 5432, user = "[]",  # create an PostgreSQL instance and create one connection. drv <- dbDriver("PostgreSQL") # open the connection using user, passsword, etc., as con <- dbConnect(drv, dbname = "postgres") and this is where you add additional user, password, host, arguments.

RS-DBI over SSL?, connecting to the PostgreSQL command line (with "psql") but can't make a connection from R. Is there a way in RS-DBI to make the connection using SSL, like  Using the RPostgres package. RPostgres is an DBI-compliant interface to the postgres database. It is a ground-up rewrite using C++ and Rcpp. This package acts as both the database driver and the DBI interface. The code, and additional information are available in its GitHub repository here: RPostgres. To connect use:

Connecting to your data with R, In order to connect to Direct Access using R, you will have to use an SSL connection. Configure the PostgreSQL ODBC driver using the ODBC Data Source  In this case, we will use the pgAdmin 3 tool for example, so get this application (or another preferred one) installed prior. 1. A Public IP or endpoint being attached to your PostgreSQL database container is required to connect to the database server via SSL.

Establish a Secure SSL Connection to PostgreSQL DB Server, Return to the terminal window with SSH connection to your PostgreSQL server you've operated through during server setup (or reconnect to it) –  Now we can already access and use the database, for example we can start the interface (pgAdmin III) that was automatically installed with PostgreSQL. To connect to the database double click on the server in pgAdmin III and type in your password. The server seems to run after the installation as well.

Ryan's Tech Blog › working with R, postgresql + SSL, and MSSQL, postgresql 8.4.5 on ubuntu server, requiring SSL; MS SQL Server 2005 on Windows 2003. R connects to databases via RJDBC, which (surprise)  >>Subject: [ADMIN] Connecting via SSL not working (except from psql) >> >> >>Hi, >> >>I'm trying to connect to SSL-enabled Postgres (started with >>-i -l) using >>both the openssl command line utility and with a modified JDBC driver >>using the built in JSSE API from Java 1.4. >> >>If I attempt to connect from a shell with the openssl test

Comments
  • adding the sslmode=require to the dbname causes crah of Rstudio for me
  • This didn't work for me neither, but @chrowe's solution did