Encrypted PostgreSQL
PGCon 2009 Ottawa, Canada
Magnus Hagander
Redpill Linpro AB
Consulting Development IT Operations Training Support Products
Decide w at !our t reat is
Everything comes at a cost
Per"or#ance or #aintaina$ilit!
Encryption for the sake of encryption? Compliance/regulations?
Consulting Development IT Operations Training Support Products
%ncr!ption at di""erent la!ers
Application
Application data encryption
Database
Pgcrypto encryption functions
Storage
Full harddrive/filesystem encryption
Consulting Development IT Operations Training Support Products
%ncr!ption at di""erent la!ers
Application
Application data encryption SSL or VPN
Database
Pgcrypto encryption functions
Storage
Full harddrive/filesystem encryption
Consulting Development IT Operations Training Support Products
Application data encr!ption
Independent of the database Implemented in the application layer
&o, we won't tal( a$out t e #!riad o" options ere
Consulting Development IT Operations Training Support Products
Harddri)e*"iles!ste# encr!ption
Independent of the database Filesystem och block device level Needs to keep fsync behaviour! Keeps all database functionality here to store the key?
Consulting Development IT Operations Training Support Products
Pgcr!pto
Encryption as database functions Client independent !on"t forget to encrypt the connection!
Consulting Development IT Operations Training Support Products
Pgcr!pto + c allenges
Encryption is easy
Relati)el! spea(ing As long as !ou don't in)ent !our own,
Key management is not
Consulting Development IT Operations Training Support Products
Pgcr!pto - o)er)iew
#a$ encryption %&% compatible encryption 'ashing
Consulting Development IT Operations Training Support Products
pgcr!pto. raw encr!ption
SELECT encrypt(data, key, type) SELECT decrypt(data, key, type) SELECT encrypt_iv(data, key, iv, type)
(ype) bf*cbc+ aes*cbc+ ,,, -ecb supported+ but,,. /perates on bytea+ returns bytea gen0random0bytes-. can be used to create key
Consulting Development IT Operations Training Support Products
pgcr!pto. PGP encr!ption
pgp_sym_encrypt(data, password[, opt]) pgp_sym_decrypt(data, password[, opt])
/perates on te1t in plainte1t+ bytea in cipherte1t
ar#or/0, dear#or/0
(akes gpg style options like ciper-algo=aes256
Consulting Development IT Operations Training Support Products
pgcr!pto. PGP encr!ption
pgp_sym_encrypt(data, password[, opt]) pgp_sym_decrypt(data, password[, opt])
%ublic key encryption also supported+ but no key generation ill detect $rong key/corrupt data
Consulting Development IT Operations Training Support Products
pgcr!pto. Has ing
SELECT digest(txt, type)
Returns $!tea, use encode/0 to get e1 Md2, s a3, s a4#ore5
SELECT encode( digest(' o cats!', 's"a#$%'), '&ase%'')
Consulting Development IT Operations Training Support Products
pgcr!pto. Has ing
SELECT crypt('secret', gen_sa t('&('))
6tores salt as part o" as Autodetects algorit #
#d2, $", etc SELECT "as")crypt('secret', "as")
Consulting Development IT Operations Training Support Products
7e! #anage#ent
here to store the key 'o$ to protect the key 'o$ to access the key 'o$ to do key recovery
Consulting Development IT Operations Training Support Products
6earc ing encr!pted data
2orry+ can"t really be done by inde1 3atch encrypted data for ra$ encrypted wit out padding
But t is decreases securit! And does 8is e9ual: #atc ing onl!
Inde1 on e1pression
But w ! did !ou encr!pt in t e "irst place;
Consulting Development IT Operations Training Support Products
66L
Consulting Development IT Operations Training Support Products
66L secured connections
Encryption 3an*in*the*middle protection 4uthentication
Consulting Development IT Operations Training Support Products
66L secured connections
Enabled on the server -ssl5yes. /ptionally re6uired through pg0hba /ptionally re6uired in libp6
Consulting Development IT Operations Training Support Products
66L secured connections
Need to protect data in !ot directions For e1ample username/pass$ord 3ust "now before connection is started
<n(nown e9uals unprotected
Consulting Development IT Operations Training Support Products
66L encr!ption
227 alwa#s re6uires a server certificate Can be self*signed !oes not need to be kno$n by client
Consulting Development IT Operations Training Support Products
Certi"icate c ains
Issuer
oot certificate
Issuer
Intermediate certificate
Issuer
Server certificate
Consulting Development IT Operations Training Support Products
Certi"icate c ains
Self!signed certificate
Issuer
oot certificate
Issuer
Intermediate certificate
Issuer
Server certificate
Consulting Development IT Operations Training Support Products
66L secured connections
"lient
Consulting Development IT Operations Training Support Products
Server
= reats andled $! 66L. %a)esdropping
S#L#"$ % F &' secret(stuff
"lient
Consulting Development IT Operations Training Support Products
Server
%a)esdropping
%revented by encrypting all data Key negotiation is automatic 2erver certificate used but not verified
Consulting Development IT Operations Training Support Products
= reats andled $! 66L. Man in t e #iddle
Valid SSL session Valid SSL session
Fa)e server
"lient
Consulting Development IT Operations Training Support Products
Server
66L ser)er )eri"ication
/n top of encryption 8alidate that the server is $ho it claims to be C4 issues certificate+ can be self* signed C4 certificate kno$n by client
Consulting Development IT Operations Training Support Products
= reats andled $! 66L. Man in t e #iddle
Valid SSL session
Fa)e server
"lient
Consulting Development IT Operations Training Support Products
Server
66L client aut entication
/n top of encryption Normally on top of server verificateion+ but not necessary C4 issued certificate on client 3atch C$ on certificate to user id %rotect client certificate!
Consulting Development IT Operations Training Support Products
66L in li$p9
Controlled by sslmode parameter /r environment P%SS&'OD( For security+ must be set on client
Re#e#$er, unknown = unsecure
Consulting Development IT Operations Training Support Products
6u##ar! o" li$p9 66L #odes
Client Mode
disable allo* prefer re+uire verify!ca verify!full
Protect against Eavesdrop MITM
no no no yes yes yes no no no no yes yes
Compatible with server set to... SSL required SSL disabled
FAIL *or)s *or)s *or)s *or)s *or)s *or)s *or)s *or)s FAIL FAIL FAIL
Performance overhead
no If necessary If possible yes yes yes
Consulting Development IT Operations Training Support Products
6u##ar! o" li$p9 66L #odes
Client Mode
disable allo* prefer re+uire verify!ca verify!full
Protect against Eavesdrop MITM
no no no yes yes yes no no no no yes yes
Compatible with server set to... SSL required SSL disabled
FAIL *or)s *or)s *or)s *or)s *or)s *or)s *or)s *or)s FAIL FAIL FAIL
Performance overhead
no If necessary If possible yes yes yes
Consulting Development IT Operations Training Support Products
6u##ar! o" li$p9 66L #odes
Client Mode
disable allo* prefer re+uire verify!ca verify!full
Protect against Eavesdrop MITM
no no no yes yes yes no no no no yes yes
Compatible with server set to... SSL required SSL disabled
FAIL *or)s *or)s *or)s *or)s *or)s *or)s *or)s *or)s FAIL FAIL FAIL
Performance overhead
no If necessary If possible yes yes yes
Consulting Development IT Operations Training Support Products
6u##ar! o" li$p9 66L #odes
Client Mode
disable allo* prefer re+uire verify!ca verify!full
Protect against Eavesdrop MITM
no no no yes yes yes no no no no yes yes
Compatible with server set to... SSL required SSL disabled
FAIL *or)s *or)s *or)s *or)s *or)s *or)s *or)s *or)s FAIL FAIL FAIL
Performance overhead
no If necessary If possible yes yes yes
Consulting Development IT Operations Training Support Products
6u##ar!
/nly encrypt $hat you really need /nly encrypted w ere you really need Key management is ard 3any use*cases are very narro$
Consulting Development IT Operations Training Support Products
Encrypted PostgreSQL Questions? magnus@hagander.net http://blog.hagander.net
Consulting Development IT Operations Training Support Products
