Cannot Connect to Remote Db Using Ssh Tunnel and Activerecord

Ruby on Rails - Connect back to app database after SSH Tunnel query

Do not use ActiveRecord::Base.establish_connection, call establish_connection on Item class instead:

class Item < ActiveRecord::Base
  establish_connection { # your connection options here }
end

Or, if you have no other choice, then store local database connection options somewhere before calling ActiveRecord::Base.establish_connection and then connect local database manually using stored configuration:

@local_db = Rails.configuration.database_configuration[Rails.env]
@remote_db = {
  adapter: 'mysql2',
  host: '127.0.0.1',
  username: username,
  password: password,
  database: remote_db_name,
  port: @port
}
ActiveRecord::Base.establish_connection(@remote_db)

# when you need your local database again:
ActiveRecord::Base.establish_connection(@local_db)

rails Rake and mysql ssh port forwarding

The issue is very likely the same as here:

Cannot connect to remote db using ssh tunnel and activerecord

Don't use threads, you need to fork the importer off in another process for it to work, otherwise you will lock up with the ssh event loop.

Can't connect Ruby on Rails to remote mysql database

I'm guessing that, per my original comment, your remote DB is refusing connections from anything but localhost. Based on that assumption, here are two solutions:

1. Use a ssh tunnel. There are tons of howto's for connecting over ssh tunnel, e.g., http://www.howtogeek.com/howto/ubuntu/access-your-mysql-server-remotely-over-ssh/.

2. Make sure your remote DB is accepting remote connections. Check out the accepted answer on the following stackoverflow thread Can't connect to MySQL server error 111

How do I configure Rails for password-less access to remote database

First, you need to establish an SSH tunnel the MySQL server. On the client machine, run:

ssh -fNg -L 3307:127.0.0.1:3306 guardian@salt.woofwoof.com

That will establish an SSH tunnel to the salt.woofwoof.com server. Any connections to localhost port 3307 will get sent through the tunnel to the remote host on port 3306.

Then just configure your database.yml like you would for a local connection, but specify the forwarded port 3307:

canine:
  adapater: mysql2
  database: canine
  username: bowser
  password: *secret*
  port: 3307

You may also want to add the ssh tunnel setup to /etc/inittab so that the tunnel is establish after boot. See http://chxo.com/be2/20040511_5667.html for one example of how to do that.

Can ActiveRecord connect to PostgreSQL remotely and protect the DB password?

Regardless of whether Postgres allows this functionality, you can enable a secure connection to a remote database by using SSH tunneling. Here's the gratuitous Stack Overflow paste-in from the Web docs:

First make sure that an SSH server is
running properly on the same machine
as the PostgreSQL server and that you
can log in using ssh as some user.
Then you can establish a secure tunnel
with a command like this from the
client machine:

ssh -L 3333:foo.com:5432 joe@foo.com
The first number in the -L argument,
3333, is the port number of your end
of the tunnel; it can be chosen
freely. The second number, 5432, is
the remote end of the tunnel: the port
number your server is using. The name
or IP address between the port numbers
is the host with the database server
you are going to connect to. In order
to connect to the database server
using this tunnel, you connect to port
3333 on the local machine:

psql -h localhost -p 3333 postgres To
the database server it will then look
as though you are really user
joe@foo.com and it will use whatever
authentication procedure was
configured for connections from this
user and host. Note that the server
will not think the connection is
SSL-encrypted, since in fact it is not
encrypted between the SSH server and
the PostgreSQL server. This should not
pose any extra security risk as long
as they are on the same machine.

In case you want more, you can find it online by searching for "SSL tunnel" or "postgres SSL tunnel". Here's the Postgres site where I got the above:

http://www.postgresql.org/docs/current/static/ssh-tunnels.html

To summarize for Rails, you would then do the following:

1) In a terminal window, run the first ssh command above to establish the tunnel.

2) Set your database props like so:

development:
  adapter: postgresql
  database: journalapp_development
  username: xxx
  password: yyy
  host: localhost
  port: 3333

---

Related Topics