![Page 1: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/1.jpg)
© 2013 VMware Inc. All rights reserved
A Comparison of PostgreSQL Encryption
Options
Syed Faisal Akber, Staff Technical Support Engineer
Dong Ye, Staff Engineer
![Page 2: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/2.jpg)
2
Agenda
Why encryption?
Some Postgres encryption options
Performance results
Real-world use cases
Conclusions
![Page 3: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/3.jpg)
3
Why Encrypt Data?
Protect sensitive information
Prevent identity theft
Satisfy paranoia
Comply with laws and standards (SOX, HIPPA, PCI, …)
![Page 4: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/4.jpg)
4
Typical Architecture
![Page 5: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/5.jpg)
5
Postgres Encryption Options
Where?
• Encrypting Specific Columns
• Encrypting Data Partitions
• Encrypting Data Across Network
Who?
• Database Server/Client Communication over SSL
• Complete Application Encryption
![Page 6: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/6.jpg)
6
Encrypting Specific Columns
Why?
• Offload
• Centralize
Use the pgcrypto module
Require application change
![Page 7: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/7.jpg)
7
Encrypting Specific Columns: Diagram
Specific columns are protected
A B C
1 1200 F7956d6e
2 -45 249e401
![Page 8: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/8.jpg)
8
Encrypting Specific Columns: pgcrypto
Provide a number of functions
• General hashing functions
• Password hashing functions
• PGP functions
• RAW encryption/decryption functions
![Page 9: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/9.jpg)
9
Using pgcrypto
Build Server and Extension, Use Extension
Augment DML in application
./configure --with-openssl
make
make install
cd contrib/pgcrypto
make
make install
INSERT Example INSERT INTO z (a, b, c) VALUES (3, 34500, encrypt('Test'::bytea,
'key'::bytea, 'aes'));
SELECT Example SELECT a, b, convert_from(decrypt(c, 'key'::bytea, 'aes'),
current_setting('server_encoding'))::int AS c FROM z WHERE a = 1;
pgbench=# CREATE EXTENSION pgcrypto;
![Page 10: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/10.jpg)
10
Encrypting Data Partition: Diagram
![Page 11: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/11.jpg)
11
Encrypting Data Partition (Filesystem)
Prepare an encrypted filesystem with dm-crypt
Run initdb on the encrypted filesystem
Start Postgres server
dd if=/dev/zero of=/data/crypt count=8 bs=1G
chmod 600 /data/crypt
losetup /dev/loop0 /data/crypt
cryptsetup -y create secretfs /dev/loop0
cryptsetup status secretfs
mke2fs -j -O dir_index /dev/mapper/secretfs
tune2fs -l /dev/mapper/secretfs
mkdir /mnt/secretfs
mount /dev/mapper/secretfs /mnt/secretfs/
![Page 12: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/12.jpg)
12
Encrypting Data Across Network
Two main methods
Postgres built-in SSL
SSH tunnel
![Page 13: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/13.jpg)
13
Encrypting Data Across Network
![Page 14: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/14.jpg)
14
Encrypting Data Across Network: SSL
Facility exists in Postgres
Configure server
Configure SSL flag in client
May need to open ports in firewall/router
Cisco PAT configuration in Cisco IOS
ip nat inside source static tcp 10.4.3.2 5432 interface Serial0 5432
![Page 15: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/15.jpg)
15
Server Configuration
Create SSL Keys and Sign Certificate
openssl req -new -text -out server.req
openssl rsa -in privkey.pem -out server.key
rm privkey.pem
openssl req -x509 -in server.req -text -key server.key -out
server.crt
chmod 600 server.key
Build Server
./configure --with-openssl
make
make install
![Page 16: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/16.jpg)
16
Server Configuration (cont.)
Update pg_hba.conf
Update postgresql.conf
• Ensure listen_addresses is set correctly
• Add ssl = on
• Check SSL certificate files location
Restart Postgres server
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
hostssl all all 0.0.0.0/0 md5
![Page 17: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/17.jpg)
17
Client Configuration
Connect using sslmode option with one of four values:
• disable
• allow
• prefer
• require
PHP Connection Example
$link = pg_connect("host=10.4.3.2 port=5432 dbname=pgbench
user=pgbench password=pgbench sslmode=require");
![Page 18: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/18.jpg)
18
Encrypting Data Across Network: SSH Tunnel
No modifications to Postgres configuration
Use of existing SSH gateway
PHP Connection Example
$link = pg_connect("host=127.0.0.1 port=2000 dbname=pgbench
user=pgbench password=pgbench");
ssh -f -N -L 127.0.0.1:2000:10.4.3.2:5432 [email protected]
![Page 19: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/19.jpg)
19
Complete Application Encryption
Application encrypts and writes data into database
Application reads and decrypts data from database
Requires no involvement of database and network
• Listed here for completeness
• No tests done
![Page 20: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/20.jpg)
20
Complete Client Encryption
![Page 21: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/21.jpg)
21
Test Bed
4x Intel Xeon E5-5640 (32 cores in total), EMC VNX5500 SAN
Hypervisor: VMware ESXi 5.1 Express Patch 2
Virtual machine: 32 vCPUs, 12GB vRAM
Guest operating system: SuSE Linux Enterprise Server 11 SP1
Postgres 9.3.0:
• shared_buffers= 8GB, checkpoint_segments=100
• Separate partitions for PGDATA and XLOG
Benchmark:
• pgbench -i -s 100; pgbench -c 32 -j 32 -M prepared -T 300
![Page 22: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/22.jpg)
22
Encrypting Columns (pgcrypto) Tests
Test bed
• pgbench connects over LAN
• Workload: pgbench from postgresql.git versus pgbench modified
• pgbench modified: encrypt/decrypt abalance column in pgbench_accounts
Results
UPDATE pgbench_accounts SET abalance = encrypt( decrypt(abalance) + :delta)
WHERE tid = :tid;
SELECT convert_from(decrypt(abalance, 'key'::bytea, 'aes'),
current_setting('server_encoding')) FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_accounts SET abalance = encrypt(0::text::bytea, 'key'::bytea,
'aes');
Baseline pgcrypto
pgbench tps 3483 3311
![Page 23: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/23.jpg)
23
Encrypting Data Partition Tests
Test bed
• pgbench connects over Unix domain sockets
Results
Baseline Encrypting DATA & XLOG
pgbench tps 13814 5414
![Page 24: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/24.jpg)
24
Encrypting Data over Network Tests
Test bed
• pgbench connects over LAN and WAN (coast-to-coast)
Results
pgbench tps Baseline SSL SSH tunnel
LAN 3250 3132 1510
WAN 42.11 42.01 34.68
![Page 25: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/25.jpg)
25
Real-World Use Cases
E-commerce website
Patient information application
![Page 26: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/26.jpg)
26
E-Commerce Website
Case
• Web server is hosted on public cloud
• Database server is hosted internally
Options to encrypt data on the wire
• SSL
• pgcrypto for specific columns (e.g., credit card)
![Page 27: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/27.jpg)
27
Patient Information Application
Case
• Internal application
• Information remains in-house (within clinic or hospital)
Options to encrypt data on disk
• Data partition
• Specific columns
![Page 28: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/28.jpg)
28
Conclusions
Why Encrypt Data?
Encryption Options
• pg_crypto and Column based Encryption
• SSL/SSH Tunnel
• Filesystem Encryption
Performance results
Real-world Examples
![Page 29: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/29.jpg)
29
Questions?
![Page 30: PostgresOpen 2013 A Comparison of PostgreSQL Encryption Options](https://reader033.vdocuments.us/reader033/viewer/2022060107/554a5d74b4c905522f8b5188/html5/thumbnails/30.jpg)
30
References
http://www.postgresql.org/docs/current/static/encryption-options.html
http://www.postgresql.org/docs/current/static/pgbench.html
http://www.postgresql.org/docs/current/static/ssl-tcp.html
http://www.postgresql.org/docs/current/static/ssh-tunnels.html
http://www.postgresql.org/docs/current/static/libpq-connect.html
http://www.postgresql.org/docs/current/static/pgcrypto.html
http://www.postgresql.org/docs/current/static/libpq-ssl.html
http://www.revsys.com/writings/quicktips/ssh-tunnel.html
http://cubist.cs.washington.edu/doc/ExamplePHPwPostgreSQL.shtml
http://php.net/manual/en/ref.pgsql.php
http://www.php.net/manual/en/function.pg-connect.php
http://wiki.centos.org/HowTos/EncryptedFilesystem
http://www.faqs.org/docs/Linux-HOWTO/Loopback-Encrypted-Filesystem-HOWTO.html