Hello. I would like to connect to my MySQL server through a SSH tunnel with encryption/compression. There is a very neat SSH implementation for Python called Paramiko (http://www.lag.net/paramiko/). What is the best way to do this? I'm not that good in Python yet. What I now have:
You could just use the SSH command-line client. If you want your remote server to look like a local server, do this:
ssh -z -L 3306:localhost:3306 remote
The -L option says to forward connections made to the local port 3306 to localhost:3306 on the remote side (localhost == remote's localhost). -z enables compression. You can also set these as defaults in your configuration.
This assumes UNIX or Linux SSH. If you're using Windows, it sucks to be you, but you can also get Cygwin (which provides a UNIX-like run-time environment on Windows) and use the SSH that comes with it (openssh). If you hunt around, you may be able to find a stand-alone version of the Cygwin SSH.
Another possibility is to use SSL instead. This requires some server AND client configuration to work. You need SSL certificates for the server and client; I believe they can be self-signed ones, which you can generate with OpenSSL. The client cert needs to be included in a GRANT. Getting MySQL to work with SSL is almost a HOW-TO by itself. Search the on-line MySQL docs for SSL so you can get an idea of what's involved.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks for your answers, maybe I was not clear enough.
I am looking into building an POS system for my company (Apple Reseller). It should be as open as possible, and run on macs, and be fast and easy to use as well as eye-pleasing. For the client-side, I want to use Python and PyObjC because I like Python, and I love cocoa widgets. For the backend, I'd like to use any SQL database (for this moment MySQL). Instead of tunneling networks (backend will be colocated) I'd like each client to make an SSH connection to the backend. This way the data is secure AND compressed. SSL is not compressed, and is quite a hassle to set up with certificates (IMHO).
It's true that SSL is not compressed; however, you can enable compression in the MySQL protocol by passing compress=1 to connect(). Generally I would not recommend compression unless you are connecting over a WAN connection, which is what you seem to have based on your comment above.
The previous ssh options I listed should work fine on Mac OS X, since it uses OpenSSH. However, you might want to think about whether or not to give each client it's own credentials. If you use shared credentials, compromising one client requires you to change the credentials for all clients. If you give each clients it's own credentials, then you can disable individual clients centrally. In ssh, this means either a) creating a user account for each client and/or b) generating SSH keys for each client and updating authorized_keys on the server.
SSL is somewhat of a pain to set up correctly, but it might actually be easier than SSH for many clients. For example, I assume you would like your clients to be as identical as possible.
With SSL, you need to at least have a server certifcate, signed by some certificate authority. With OpenSSL, you can be your own CA. This page describes how to generate the necessary certificates:
You can use client certificates to authenticate users, but it is not required; a client certificate is only needed if you have REQUIRE X509 (or one of the variants, such as REQUIRE ISSUER) in the GRANT. REQUIRE SSL does not, by itself, require a client certificate.
It appears that a minimal SSL setup where you don't have to pay for anything requires:
1) Create a certificate authority.
2) Create a server certificate.
3) Sign the server certificate with the CA.
4) Install the CA public certificate on the clients.
6) Create GRANTs for the clients with REQUIRE SSL.
6) Configure the clients so they know where to find the CA certificate. This is can be done in a MySQL configuration file which is read with the read_default_file option to connect(), or use the ssl=dict(ssl_ca=path_to_ca_file) option to connection; using read_default_file is probably better. Just passing ssl={} to connect() should be enough to enable SSL, assuming you are doing the rest with configuration files.
I don't think there's anyway to make the MySQL client library use paramiko to do it's tunneling unless you run paramiko as a separate process, in which case, you are probably better off just using OpenSSH.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Not necessarily: PostgreSQL 8.0 supports SSL, and apparently so do the 7.x versions. It's not clear, though, if psycopg supports SSL, but there is more than one PostgreSQL module out there.
As near as I can tell, Firebird does not support SSL, but I am far from an authority on Firebird.
SQLObject doesn't seem to have direct support for SSL in it's connection string format. The docs are wrong about transaction support in MySQL: InnoDb is not the only storage engine that support transactions. BDB does, and so does the new NDB (clustering).
Hello. I would like to connect to my MySQL server through a SSH tunnel with encryption/compression. There is a very neat SSH implementation for Python called Paramiko (http://www.lag.net/paramiko/). What is the best way to do this? I'm not that good in Python yet. What I now have:
MySQL<--->SSH<--->Python:Socket<--->Pyhton:Paramiko<--->Python-MySQL
It does work, but I have the feeling there must be a more efficint way to do this.
You could just use the SSH command-line client. If you want your remote server to look like a local server, do this:
ssh -z -L 3306:localhost:3306 remote
The -L option says to forward connections made to the local port 3306 to localhost:3306 on the remote side (localhost == remote's localhost). -z enables compression. You can also set these as defaults in your configuration.
This assumes UNIX or Linux SSH. If you're using Windows, it sucks to be you, but you can also get Cygwin (which provides a UNIX-like run-time environment on Windows) and use the SSH that comes with it (openssh). If you hunt around, you may be able to find a stand-alone version of the Cygwin SSH.
http://cygwin.com/
Another possibility is to use SSL instead. This requires some server AND client configuration to work. You need SSL certificates for the server and client; I believe they can be self-signed ones, which you can generate with OpenSSL. The client cert needs to be included in a GRANT. Getting MySQL to work with SSL is almost a HOW-TO by itself. Search the on-line MySQL docs for SSL so you can get an idea of what's involved.
Thanks for your answers, maybe I was not clear enough.
I am looking into building an POS system for my company (Apple Reseller). It should be as open as possible, and run on macs, and be fast and easy to use as well as eye-pleasing. For the client-side, I want to use Python and PyObjC because I like Python, and I love cocoa widgets. For the backend, I'd like to use any SQL database (for this moment MySQL). Instead of tunneling networks (backend will be colocated) I'd like each client to make an SSH connection to the backend. This way the data is secure AND compressed. SSL is not compressed, and is quite a hassle to set up with certificates (IMHO).
Check my sketch at http://koen.nu/Diko/Schets.pdf to make it even more clear.
It's true that SSL is not compressed; however, you can enable compression in the MySQL protocol by passing compress=1 to connect(). Generally I would not recommend compression unless you are connecting over a WAN connection, which is what you seem to have based on your comment above.
The previous ssh options I listed should work fine on Mac OS X, since it uses OpenSSH. However, you might want to think about whether or not to give each client it's own credentials. If you use shared credentials, compromising one client requires you to change the credentials for all clients. If you give each clients it's own credentials, then you can disable individual clients centrally. In ssh, this means either a) creating a user account for each client and/or b) generating SSH keys for each client and updating authorized_keys on the server.
SSL is somewhat of a pain to set up correctly, but it might actually be easier than SSH for many clients. For example, I assume you would like your clients to be as identical as possible.
With SSL, you need to at least have a server certifcate, signed by some certificate authority. With OpenSSL, you can be your own CA. This page describes how to generate the necessary certificates:
http://dev.mysql.com/doc/mysql/en/secure-create-certs.html
You can use client certificates to authenticate users, but it is not required; a client certificate is only needed if you have REQUIRE X509 (or one of the variants, such as REQUIRE ISSUER) in the GRANT. REQUIRE SSL does not, by itself, require a client certificate.
It appears that a minimal SSL setup where you don't have to pay for anything requires:
1) Create a certificate authority.
2) Create a server certificate.
3) Sign the server certificate with the CA.
4) Install the CA public certificate on the clients.
6) Create GRANTs for the clients with REQUIRE SSL.
6) Configure the clients so they know where to find the CA certificate. This is can be done in a MySQL configuration file which is read with the read_default_file option to connect(), or use the ssl=dict(ssl_ca=path_to_ca_file) option to connection; using read_default_file is probably better. Just passing ssl={} to connect() should be enough to enable SSL, assuming you are doing the rest with configuration files.
I don't think there's anyway to make the MySQL client library use paramiko to do it's tunneling unless you run paramiko as a separate process, in which case, you are probably better off just using OpenSSH.
Man. I owe you. Thanks for you great answers.
I might be looking into SSL after all.
The only cons I can think of now is that this way I am more obligated to use the MySQL server. But that's fine with me.
Not necessarily: PostgreSQL 8.0 supports SSL, and apparently so do the 7.x versions. It's not clear, though, if psycopg supports SSL, but there is more than one PostgreSQL module out there.
As near as I can tell, Firebird does not support SSL, but I am far from an authority on Firebird.
SQLObject doesn't seem to have direct support for SSL in it's connection string format. The docs are wrong about transaction support in MySQL: InnoDb is not the only storage engine that support transactions. BDB does, and so does the new NDB (clustering).
http://sqlobject.org/docs/SQLObject.html#mysql
Tip jar: https://sourceforge.net/project/project_donations.php?group_id=22307