MySql encryption using DAL

[appj...@gmail.com]

Hello,

I have a Web2py app I'm moving from a dedicated server to the cloud but I'm leaving my MySQL database on the original server. 

I am connecting to mysql via the DAL:

db = DAL('mysql://username:pass...@db.example.com/', pool_size=8, migrate = migrate_dev, lazy_tables=True, fake_migrate=True)

I can't find any examples though on how to make this connection secure as it goes across the public internet.  Can someone point me at the code need on both the Web2py app and MySql server ends to make that happen?

Thank you so much,

-Mike

[Massimo Di Pierro]

DAL does not provide https encryption. I do not if the adapters support it either.

The solution is to create a SSH tunnel between the server and the database server and the use a URI string connecting to localhost.

[appj...@gmail.com]

Thank you Massimo!

There is a great detailed deployment recipe for SSH tunneling to your database server from a Heroku app instance (dyno). It works as of this post.10/8/17

https://stackoverflow.com/questions/21575582/ssh-tunneling-from-heroku/46629121#46629121

There are 2 issues/questions with this though:

1) So now that I can tunnel in, I have a performance question:  Since the mysql database server will be making all of its connections to localhost is that a single connection rather than multiple?  Will I lose database read concurrency?  If so, will either that or the SSH tunnels be a bottleneck and severely degrade my database performance?

2) SSH tends to be flaky and drop connections leaving a broken Web2py app instance.  Any suggestions on best practices for handling that case?

It's too bad DAL doesn't support secure connections.  Encrypted database connections are pretty standard nowadays and I see there are python mysql connectors that do. Has anyone successfully swapped out the one that ships with one of those?

Cloud deployment is new to me so I really appreciate the help, and I love using Web2py so thanks for making it and the ongoing support! 

[Massimo Di Pierro]

On Saturday, 7 October 2017 01:55:53 UTC-5, appj...@gmail.com wrote:

Thank you Massimo!

There is a great detailed deployment recipe for SSH tunneling to your database server from a Heroku app instance (dyno). It works as of this post.10/8/17

https://stackoverflow.com/questions/21575582/ssh-tunneling-from-heroku/46629121#46629121

There are 2 issues/questions with this though:

1) So now that I can tunnel in, I have a performance question:  Since the mysql database server will be making all of its connections to localhost is that a single connection rather than multiple?  Will I lose database read concurrency?  If so, will either that or the SSH tunnels be a bottleneck and severely degrade my database performance?

web2py has connection pooling. Each of connection from the pool will go through the tunnel, concurrently up to the max number in the pool.

2) SSH tends to be flaky and drop connections leaving a broken Web2py app instance.  Any suggestions on best practices for handling that case?

No. Sorry. If others have suggestions I would like to hear them.

 

It's too bad DAL doesn't support secure connections.  Encrypted database connections are pretty standard nowadays and I see there are python mysql connectors that do. Has anyone successfully swapped out the one that ships with one of those?

It would be easy to add. we will work in it.

[appj...@gmail.com]

Thank you!  What is the process for this? Should I open an issue in github? 

[Chinh Dang]

Depending on the type of cloud service you may be able to setup VPN over internet and connect your DB via the VPN tunnel.

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[appj...@gmail.com]

I'm using Heroku. Would the VPN option be more stable/reliable/performant than an SSH tunnel?

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.

[appj...@gmail.com]

I am using "autossh" which is a monitor wrapper around ssh that restarts the ssh connection if it gets dropped.  It appears to be working well.

[appj...@gmail.com]

Hi, I wanted to check back to see if there was any update on this.  

With more and more database solutions moving to hosted DB services (so no SSH tunnels), Web2Py apps do not have the ability to connect to these securely.   

We are stuck hosting a dedicated machine to server our MySQL and run an SSH server.  We'd like to switch to Amazon Aurora for example.

On Wednesday, October 11, 2017 at 11:52:02 AM UTC-5, Massimo Di Pierro wrote:

[appj...@gmail.com]

I was curious if the ability to connect securely to a database service (without an SSH tunnel) was added to 2.17.1?  Thanks.

[Massimo Di Pierro]

For postgresql it has been there for long time:

    postgres://{username}:{password}@{domain}:5432/{dbname}?sslmode=require

For MySQL it can also be done with current PyDAL. My understanding is that MySQL requires certificates so you

1) you need to install certificates

2) on server side in my my.ini:

require_secure_transport=true
tls_version=TLSv1,TLSv1.1,TLSv1.2
ssl-ca=install_path/ca-cert.pem
ssl-cert=install_path/cert.pem
ssl-key=install_path/key.pem

3) on web2py side:

ssl = { 'cert': 'install_path/cert.pem',
        'key': 'install_path/key.pem',
        'ca': 'install_path/ca-cert.pem'} 

DAL('mysql://....', driver_args = {'ssl': ssl})

Mind I did not try this. All I am telling you is how to use driver_args to pass ssl info to the mysqldb.connect( ...., ssl = ...) function.

[appj...@gmail.com]

Thanks Massimo.

These are the steps and syntax to connect to Amazon's RDS using SSL.

Step1:

Download Amazons's CA certificate from here:

 https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem

and save it into web2py/applications/myapp/private/ssl/rds-combined-ca-bundle.pem

Step2:

Modify your model (db.py) as follows, using your own username, password, endpoint, and DB

driver_args = { 'ssl':{ 'ca': 'applications/ads/private/ssl/rds-combined-ca-bundle.pem'} }

db_auth = 'mysql://web2py_db_user:mypas...@XXXXX-cluster-1.cluster-XXXXXus-east-1.rds.amazonaws.com:3306/my_db'  

db = DAL( db_auth, 

                  driver_args = driver_args )

Step 3 (optional):

To force SSL for this user (web2py_db_user), connect to the RDS server using MySQL Workbench (or any other DB client) and execute the following SQL. 

For MySQL 5.6 GRANT USAGE ON *.* TO 'web2py_db_user'@'%' REQUIRE SSL;

For MySQL 5.7

ALTER USER 'web2py_db_user'@'%' REQUIRE SSL;

[Massimo Di Pierro]

Very useful. this should go in the book.

https://github.com/web2py/web2py-book

Would you be able to submit a PR? else I can do it.

On Friday, 30 November 2018 15:26:17 UTC-8, appj...@gmail.com wrote:

Thanks Massimo.

These are the steps and syntax to connect to Amazon's RDS using SSL.

Step1:

Download Amazons's CA certificate from here:

 https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem

and save it into web2py/applications/myapp/private/ssl/rds-combined-ca-bundle.pem

Step2:

Modify your model (db.py) as follows, using your own username, password, endpoint, and DB

driver_args = { 'ssl':{ 'ca': 'applications/ads/private/ssl/rds-combined-ca-bundle.pem'} }

db_auth = 'mysql://web2py_db_user:mypassword@XXXXX-cluster-1.cluster-XXXXXus-east-1.rds.amazonaws.com:3306/my_db'