Connecting to a Redshift instance using R without using oVPN

I am relatively new to using both R and Redshift together. However, I was recently trying to find a way in which I can establish a connection to a particular Redshift instance using R. Consequently, I have been successful in using the redshiftTools package to achieve the above by using the code below:

conn_test <- dbConnect(RPostgres::Postgres(),host = "xxxxxxxxxxx.us-east-1.redshift.amazonaws.com",
port = 5439,user = "user_name",password = "password",dbname = "dbname",sslmode = "require")

Additionally, I have been able to carry out transactions on the required tables from R itself without having to open the SQLWorkbench itself using the dbGetQuery command in R.

Now, the challenge that I am facing currently is to establish a connection to the instance without needing to rely on an OpenVPN(oVPN) connection in case I am working remotely(i.e. out-of-office). Currently, if I am trying to run the above code without connecting to oVPN, I am getting the following error:

Error: could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "xxxxxxxxx.us-east-1.redshift.amazonaws.com" (10.5.236.205) and accepting TCP/IP connections on port 5439?

I tried to rectify this problem by trying to access and alter the pg_hba.conf file using the documentation here. However, couldn't make much headway.

Let me know if I am going about it incorrectly and any help forthcoming would be appreciated.

Thank You.

Regards,

Anubhav

You need to open up the Security Group to allow access from the non-VPN IP address(es). https://docs.aws.amazon.com/redshift/latest/mgmt/connecting-refusal-failure-issues.html