treii28 has asked for the wisdom of the Perl Monks concerning the following question:

I'm aware there are openSSH modules for perl and I have used many of the DBI/DBD modules. Has anyone ever implemented a layer of the DBI set that would use an ssh tunnel? I know some of the other tools I have use something like this (dreamweaver, navicat) where they ssh to the remote before issuing commands the sql server. I was just curious. I haven't looked deep enough into any of the DBI packages yet to see if they are actually opening their own sockets to talk to the server(s) or if they were issuing shell commands.
  • Comment on Any implementations of DBI through SSH?

Replies are listed 'Best First'.
Re: Any implementations of DBI through SSH?
by ctilmes (Vicar) on May 16, 2008 at 19:30 UTC
    Why do you need it in DBI/DBD? Just set up the ssh tunnel to the DB host/port on the far end and point your local database connect string to "localhost" and whatever port. Stock DBI/DBD will work fine.
[reply]
Re: Any implementations of DBI through SSH?
by dragonchild (Archbishop) on May 16, 2008 at 19:14 UTC
    That's a client library question. DBD::mysql implements the mysql_ssl flag which enables the MySQL client library SSL encryption for the socket. Other DBDs may implement similar things for their libraries.
    My criteria for good software:
    1. Does it work?
    2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
[reply]
Re: Any implementations of DBI through SSH?
by ikegami (Patriarch) on May 16, 2008 at 19:35 UTC

    Like it's been mentioned, this wouldn't be a DBI feature, but a database (DBD) feature, since some databases (like DBD::SQLite) don't even use sockets.

    If you want a generic solution or a solution for databases don't have DBI or native support for encryption, you can create a secure (ssh) tunnel and give DBI the address of the tunnel instead of the address of the database server.

    (Oops, Beaten to the punch!)

[reply]
Re: Any implementations of DBI through SSH?
by psini (Deacon) on May 16, 2008 at 19:00 UTC

    Not sure for I've never tried but perhaps DBD::Gofer could be what you are looking for

    Rule One: Do not act incautiously when confronting a little bald wrinkly smiling man.

[reply]
Re: Any implementations of DBI through SSH?
by hesco (Deacon) on May 17, 2008 at 16:27 UTC
    Postgres also permits ssl encrypted connections. One of my typical connection modules include this function:

    sub connect {
  my $self = shift;
  my $db = shift;

  my $db_driver = $db->{'db_driver'};
  my $host_name = $db->{'db_host'};
  my $db_name = $db->{'db_name'};
  my $USER = $db->{'db_user'};
  my $PASSWORD = $db->{'db_pw'};

  my $dsn = "DBI:$db_driver:host=$host_name;database=$db_name";
  if($db->{'db_ssl'}){
    $dsn .= ';sslmode=require';
  }

  # print $dsn,"\n";

   return (DBI->connect($dsn,$USER,$PASSWORD,
      {PrintError => 0, RaiseError => 1}));
}

    [download]
    while my /etc/postgres/8.1/main/pg_hba.conf, includes:

    # local      DATABASE  USER  METHOD  [OPTION]
# host       DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTION]
# hostssl    DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTION]
# hostnossl  DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTION]

hostssl db_name db_user   192.168.105.45/32       md5

    [download]
    -- Hugh

    if( $lal && $lol ) { $life++; }
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom