I can't connect to remote db with RPostgreSQL

[Diego Forteza]

Hi All,

I'm trying to use, RPostgreSQL to connect from my pc to a remote server with postgresql install. I've already install RPostrgreSQL package in my pc without trouble, but I'm not being able to make it work and all the examples I've seen in the web are with connections to local databases.

This is what I'm doing

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL");
con <- dbConnect(drv,
                 user="myUser",
                 password="myPassword",
                 dbname="myDB",
                 host="myHost",
                 port="myPort");

Error en postgresqlNewConnection(drv, ...) :
  RS-DBI driver: (could not connect myUser@myHost on dbname "myDB"

If I access to the remote server by ssh I can get into the psql by the next command

psql -h localhost -p myPort -U myUser myDB

Can anyone help me? Do you have this issue before?

Thanks,

Cheers,

Diego

[Neil Tiffin]

Most likely there is a firewall not that is not allowing access to the PostgreSQL data base port on the remote host.

Try creating an SSH tunnel for the PostgreSQL port, for example something like:

ssh -L 63333:localhost:myPort myUser@myHost

Then connect in a different local terminal session from R using 'localhost' with port 63333. Of course 63333 can be any unused port.

Neil

--
You received this message because you are subscribed to the Google Groups "RPostgreSQL Development and Discussion List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rpostgresql-d...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Ian Gow]

Diego:

I use SSH tunneling for this.

ssh -L 5433:localhost:5432 myUser@myHost

and then

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL");
con <- dbConnect(drv,
                 user="myUser",
                 password="myPassword",
                 dbname="myDB",

                 host="localhost",
                 port=5433);

This assumes that myPort below is 5432.

-Ian

[Paul Gilbert]

Possibly this is a firewall problem, or possibly the PostgreSQL server
is just not configured to accept remote connections from your machine.
When you ssh to the machine and then run psql you are essentially just
running locally on the server. If you can run R on the server in the ssh
session then you should be able to access the database on the local
port, just like you are doing with psql. When you create an ssh tunnel
you are just allowing the R session on your machine to bypass the
PostgreSQL configuration and pretend it is a localhost connection, for
which the server is listening.

If the server is configured to accept connections from your machine you
should be able to run psql locally, rather than on an ssh connection,
with something like

psql -h myHost -p myPort -U myUser myDB

If you try that I expect you will get the same error you get from R.

There are several possible problems in the server configuration, which
is usually done in a file like /etc/postgresql/x.x/main/postgresql.conf
although the file is set in an initialization file and could be
different. To listen on TCP/IP it needs something like
listen_addresses = '*'
and then there are other settings for hosts/users and authentication
methods for users, etc.

If you cannot get the database administrator to adjust the settings for
you, then you will probably need to bypass those settings by using the
ssh tunnel.

Paul

On 02/20/2014 07:28 PM, Neil Tiffin wrote:
> Most likely there is a firewall not that is not allowing access to the
> PostgreSQL data base port on the remote host.
>
> Try creating an SSH tunnel for the PostgreSQL port, for example
> something like:
>
> ssh -L 63333:localhost:myPort myUser@myHost
>
> Then connect in a different local terminal session from R using
> 'localhost' with port 63333. Of course 63333 can be any unused port.
>
> Neil
>
> On Feb 20, 2014, at 4:06 PM, Diego Forteza <diegof...@gmail.com

>> <mailto:rpostgresql-d...@googlegroups.com>.