title: R RPostgreSQL Connect to Remote Postgres Database with SSL
body: <p>I am trying to connect to a remote PostgreSQL database from within R using the <code>RPostgreSQL</code> 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 <code>psql</code>, so I know the connection is valid and accessible from my computer.</p>
  
  <p>My first attempt at connecting in R was the following (where <code>&lt;MyHost&gt;</code> and <code>&lt;MyPass&gt;</code> were filled out appropriately for my connection):</p>
  
  <pre><code>library(RPostgreSQL)
  pg &lt;- dbDriver( 'PostgreSQL' )
  con = dbConnect( pg, host=&lt;MyHost&gt;, dbname='warehouse', 
                   user='warehouse', password=&lt;MyPass&gt;, port=5432 )
  </code></pre>
  
  <p>The error I received in R was:</p>
  
  <pre><code>RS-DBI driver: (could not connect warehouse@&lt;MyHost&gt;.com:5432 on 
  dbname "warehouse": FATAL:  no pg_hba.conf entry for host "75.166.243.177", 
  user "warehouse", database "warehouse", SSL off
  </code></pre>
  
  <p>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: <a href="https://stackoverflow.com/questions/30989264/connect-to-postgres-via-ssl-using-r">Connect to Postgres via SSL using R</a></p>
  
  <pre><code>library(RPostgreSQL)
  pg &lt;- dbDriver( 'PostgreSQL' )
  con = dbConnect( pg, host=&lt;MyHost&gt;, dbname='dbname=warehouse sslmode=require', 
                   user='warehouse', password=&lt;MyPass&gt;, port=5432 )
  </code></pre>
  
  <p>The error I received then was:</p>
  
  <pre><code>RS-DBI driver: (could not connect warehouse@&lt;MyHost&gt;:5432 on dbname 
  "warehouse": sslmode value "require" invalid when SSL support is not 
  compiled in
  </code></pre>
  
  <p>I found this post (<a href="https://stackoverflow.com/questions/36186935/postgres-sslmode-value-require-invalid-when-ssl-support-is-not-compiled-using">Postgres SSLMode Value &quot;require&quot; Invalid When SSL Support Is Not Compiled Using Foreign Data Wrapper</a>) that suggested my build of PostgreSQL may not have had the <code>--with-openssl</code> 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.</p>
  
  <p>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 <code>RPostgreSQL</code> package or (b) I have a confounding install of PostgreSQL somewhere else on my computer that was <em>not</em> built with the <code>--with-openssl</code> flag included. I have tried <code>locate postgresql</code> 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.</p>
  
  <p>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 (<a href="https://github.com/rstats-db/RPostgres" rel="noreferrer">https://github.com/rstats-db/RPostgres</a>), 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 <code>R CMD INSTALL</code>, as the resulting error message suggested (I may have missed something here though...). It sounds like some users have had better luck with <code>RPostgres</code> than with <code>RPostgreSQL</code>, but again, I wasn't able to install this package.</p>
  
  <p>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.</p>