Encrypted Drupal Database Connections with Amazon RDS

by nick.santamaria /

Share this post on social media

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.

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.pem
RUN 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-connect

mysql ... --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 port3306

This is the type of output which indicates the connection is encrypted.

tcpflow: listening on any

x1c
"|{mOXU{7-rd 0E
W$Q{C3uQ1g3&#a]9o1K*z:yPTqxqSvcCH#Zq2Hf8Fy>5iWlyz$A>jtfV9pdazdP7
tpQ=
i\R[dRa+Rk4)P5mR_h9S;lO&/=lnC<U)U87'^CE?W7_Q2sI~9Rx@l{4d'Qj2{10
YKIXMyb#i(',,j4-\1I%%N>F4P&!Y5_*f^1bvy)Nmga4jQ3"W0[I=[3=3\NLB0|8TGo0>I%^Q^~jL
L*HhsM5%7dXh6w`;B;;|kHTt[_'CDm:PJbs$`/fTv'M .p2<KTE
lt3'[*z]n6)O*Eiq9w20Rq453*mm=<gwJ_'tn]#p`SQ]5hGDLnn?YQ
DDujr!e7D#d^w@xMlF.e+v3Hy(('7O\2.6{0+
V{+m'[cq|6t!Zhv,_/:EJbBF9D8Qz+2t=E(6}jR}qDezq'~)ikO$Y:F:G,UjC[{qF;/srT?7mm=#DDUNa"%|"uDHZlfp!p@lpyIjv5foTVAJe<szV*B^g/Ij;-f~r~X~t-]}Yvr9zpO0Yf2mOoZ-{muU1w6R.'u=zCfT,S|Cp4.<vRN_gqc[vER?NLN_XGgve-O}3.q'b*]V@(|Sm15c&=k6Ty$Ak_ZaA.`vE=]V($Bm;_Z)sp..~&!9}uH+K>JP' Ok&erw
W")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^'Wobyg.oz#kh35'@NlJ{r'qlQ;YE>{#fBa4B\D-H`;c/~O,{DWrltYDbu
cB&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(>f6sZRpYm
8Ph42K)}.%g%M]`1R^'<luU5l7i;1|D2U\
#\?M"33F6{sN?tb|&E.08, &To*H4ovTXH;IWt<zwQ(Z4kyuLr6tKkdEw3Q,Pq!'_%~MyYL~R^(=(CH;F%CKf8q,eNObGX2Oue2#b]4<
;
IE4tf&*`)n<Z9sJTvUMhChiD/0byETR57r$".ul;qd*M+,<?&xq&H)yE$2?+unw;FsF3AE->qh/$3|]]y"zEh0xG(A]-I`MJGU7rKO~oi+K:4M(nyOXnvaWP4xV?d4Y^$8)2WOK,2s]gyny:-)@D*F%}ICT
Tu>ofc)P[DQ>Qn3=<al_q8%&C\"\_{GN%iPB;@NYyr)<!oYMOgy'PM$oLr}<#0(g]B.(1LQv)fg\.]0)9$7I nXa[e[w8oRDI1:B6 
\Vbf2bCOKZ%b^/zkk=pu(9xkg|/MnsRc9<p@[A!.t|?|tRr (>0^fuefIm1]-YHq5rx|W(S<egZ']=h%*Qq+gR</+0^_2q5GWGam7N).'mA4*`NhwI}noV{V<ZAbgW*c\jFiVyZ0A28TB*&GffP[zb-G,\rirs2
dmkE^hB:(R;<U8 rTc[~s/w7:%QC%TQR'f,:*|o@=!qKgql7D!v
 S+.Y7cg^m!g9G*KFgI)>3:~2&*6!O|DAZWB:#n9<fz/N }(e9m8]!QOHbkd48W%h#!r)uw7{O)2cG`~Vr&AA*Z=Zo<PP
Vej+^)(9MA;De2oMiG^a`tnoCH9l#tLMXGb%EjEkkjQb/=YblLSd}~;S*>|09`I`A3bq@\E\$=/L5VHm)<pI-%(:UYeZ~/1#A.`1m]lH^oVkPsx$ASIla3=E|j{H"{Z!|$[h~W/v!]Iy:I6H%nI\26E=p.ay?JbYd`q\q( VP+mFoJ#$Dt$u
wToLdFb~gay'8uBYRKsiSL?~5LD#MS$Y&Lf[,#jj/*W (E9tT&lhTywDv
$Fc:/+]i<YK:d07.~<P;5yE.45e=UH9mu9w_6de2
poBW3|gJI}2?|&9A/kDCo:X^w<{faH_>[#|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=*Jee<3sgX@)L;zAuTN2}v#K4AX.(`<J0#G=$FNRof2|O*`0Ef]z\g5n"LH.Z_n3LqDsoa}D&#=XyDp.o\Z2j@2$jKs=Rn
%uZ!bR=vz);i)\2h,GD.qO,84M]augk28?(9hDEiw0"EYi[|TA7Ps/o|}V=F{
Ky`i_&|H0<y]~=XJH%f_s2~u |y\o 35c#ufmrd7'GQ/ P"9 w,Q>X1<{#

Resources:

Tagged

MySQL, TLS