Encrypted Drupal Database Connections with Amazon RDS
Malicious users can intercept or monitor plaintext data transmitting across unencrypted networks, jeopardising the confidentiality of sensitive data in Drupal applications. This tutorial will show you how to mitigate this type of attack by encrypting your database queries in transit.
by
Nick Santamaria
/ 8 August 2018
With attackers and data breaches becoming more sophisticated every day, it is imperative that we take as many steps as practical to protect sensitive data in our Drupal apps. PreviousNext use Amazon RDS for our MariaDB and MySQL database instances. RDS supports SSL encryption for data in transit, and it is extremely simple to configure your Drupal app to connect in this manner.
1. RDS PEM Bundle
The first step is ensuring your Drupal application has access to the RDS public certificate chain to initiate the handshake. How you achieve this will depend on your particular deployment methodology - we have opted to bake these certificates into our standard container images. Below are the lines we've added to our PHP Dockerfile.
# Add Amazon RDS TLS public certificate.ADD https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem /etc/ssl/certs/rds-combined-ca-bundle.pemRUN chmod 755 /etc/ssl/certs/rds-combined-ca-bundle.pem
If you use a configuration management tool like ansible or puppet, the same principal applies - download that .pem file to a known location on the app server.
If you have limited control of your hosting environment, you can also commit this file to your codebase and have it deployed alongside your application.
2. Drupal Database Configuration
Next you need to configure Drupal to use this certificate chain if it is available. The PDO extension makes light work of this. This snippet is compatible with Drupal 7 and 8.
$rds_cert_path = "/etc/ssl/certs/rds-combined-ca-bundle.pem";if (is_readable($rds_cert_path)) { $databases['default']['default']['pdo'][PDO::MYSQL_ATTR_SSL_CA] = $rds_cert_path;}
3. Confirmation
The hard work is done, you'll now want to confirm that the connections are actually encrypted.
Use drush to smoke check the PDO options are being picked up correctly. Running drush sql-connect should give you a new flag: --ssl-ca.
$ drush sql-connectmysql ... --ssl-ca=/etc/ssl/certs/rds-combined-ca-bundle.pem
If that looks OK, you can take it a step further and sniff the TCP connection between Drupal and the RDS server.
This requires root access to your server, and the tcpflow package installed - this tool will stream the data being transmitted over port 3306. You are wanting to see illegible garbled data - definitely not content that looks like a SQL queries or responses!
Run this command, and click around your site while logged in (to ensure minimal cache hits).
$ tcpflow -i any -C -g port 3306
This is the type of output which indicates the connection is encrypted.
tcpflow: listening on anyx1c"|{mOXU{7-rd 0EW$Q{C3uQ1g3a]9o1K*z:yPTqxqSvcCH#Zq2Hf8Fy>5iWlyz$A>jtfV9pdazdP7tpQ=i\R[dRa+Rk4)P5mR_h9S;lO&/=lnC<u ykixmyb>F4P&!Y5_*f^1bvy)Nmga4jQ3"W0[I=[3=3\NLB0|8TGo0>I%^Q^~jLL*HhsM5%7dXh6w`;B;;|kHTt[_'CDm:PJbs$`/fTv'M .p2<kte lt3 ddujr v>JP' Ok&erwW")wLLi1%l5#lDV85nj>R~7Nj%*\I!zFt?w$u >;5~#)/tJbzwS~3$0u'/hK /99.X?F{2DNrpdHw{Yf!fLv`KTWiWFagS.@XEw?AsmczC2*`-/R rA-0(}DXDKC9KVnRro}m#IP*2]ftyPU3A#.?~+MDE}|l~uPi5E&hzfgp02!lXnPJLfMyFOIrcq36s90Nz3RX~n?'}ZX'Kl[k<tk xqj>{#fBa4B\D-H`;c/~O,{DWrltYDbucB&H\hVaZIDYTP|JpTw0 |(ElJo{vC@#5#TnA4d@#{f)ux(EES'Ur]N!P[cp`8+Z-$vh%Hnk=K^%-[KQF'2NzTfjSgxG'/p HYMxgfOGx1"'SEQ1yY&)DC*|z{')=u`TS0u0{xp-(zi6zp3uZ'~E*ncrGPD,oW\m`2^ Hn0`h{G=zohi6H[d>^BJ~ W"c+JxhIu[{d&s*LFh/?&r8>$x{CG4(72pwr*MRVQf.g"dZU\9f$h*5%nV9[:60:23K Q`8:Cysg%8q?iX_`Q"'Oj:OS^aTO.OO&O|c`p*%1TeV}"X*rHl=m!cD2D^)Xp$hj-N^pMb7x[Jck"P$Mp41NNv`5x4!k1Z/Y|ZH,k)W*Y(>f6sZRpYm8Ph42K)}.%g%M]`1R^'<luu5l7i ie4tf>qh/$3|]]y"zEh0xG(A]-I`MJGU7rKO~oi+K:4M(nyOXnvaWP4xV?d4Y^$8)2WOK,2s]gyny:-)@D*F%}ICTTu>ofc)P[DQ>Qn3=<al_q8 nxa>0^fuefIm1]-YHq5rx|W(S<egz dmke rtc s>3:~2&*6!O|DAZWB:#n9<fz vej>|09`I`A3bq@\E\$=/L5VHm)<pi- vp wtoldfb pobw3>[#|tI"lkuK.u|!2MT/@u7u(S{"H.H'Fh/4kF_2{)Jc9NQ%jA_rI1lH;k'$n~M_%t%y)t!C_4FO?idwMB]t^M::S!a=*JeeX1</pi-></fz></egz></al_q8></luu5l7i></tk></kte></u>
Resources:
Tagged