For last decade data breaches and data losses of Social Security numbers, credit card numbers, or one of a slew of other pieces of private information, have landed businesses on the front page of major newspapers, and in some instances with legal liability.
I knew when I had an opportunity to work an application which handled sensitive information, that it had to be done with the protection of this information as the number one priority.
I began familiarizing myself with the application by starting with the PostgreSQL database back-end. The pgcrypto module was present, but none of it’s functions were currently in use.
For the remainder of the article, we will take a look at how to use some of the pgcrypto PGP encryption functions to encrypt sensitive data.
First we will need to create our table. For the purposes of our example, the table will contain only one column.
CREATE TABLE messages ( message bytea );
Next we need to create our PGP keys. This process is outlined in the PostgreSQL pgcrypto documentation here.
Once we’ve created our PGP keys we can test the encryption functionality using the below Python code. Here we are using the public key to encrypt the word “testing” and inserting the result into our table.
#!/usr/bin/env python
import psycopg2
conn = psycopg2.connect("dbname='crypto_test' user='tgc' host='localhost'")
curs = conn.cursor()
f = open("public.key", "r")
key = f.read()
curs.execute("INSERT INTO messages (message) VALUES (pgp_pub_encrypt('testing', dearmor('%s')))" % (key))
conn.commit()
Did it work? Let’s see what was actually inserted into our table.
crypto_test=# select * from messages;
message
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
\301\301N\003~_\240V\321\032$\006\020\010\000\271H9\327\224\0336\245:\242\202\352B\232\314\261\2263\032#Z\343\344\365;m\333\337\3226`\352\344\017H\304\373|\213\022\246\343$\351Z\307\366`<\266\206<\330\332\024\203xX\270S\261\317\\\317\336bz\241\307\337\333`\203\020\311J?Y\013\331\021b{\220[S\011\032eu\374s\265l\336JiD\217\364P\275\272\004i\220\214\205\3111\206<\361\025%\235+U\253\202\340\250V\247\361\310\334f\307\322x=>\315x\263;\307w\335\351~--\363\350\361]5)\203\327"\254vk4o\211\244s^r\301\310\374=\312L\347\233\263\232\256~\337\033tM>\012\001C\200\345\303r\326\354{\005\3149\314\211\246\210\273\002~Z\317\270p\237\376\307\025\377r\370\221G\324\306\303\355'\301\020\357/\2049\342\222}\327K\321\347\322+\343Y\220C\027vn\027\356V-\203\355\311\033\355A)\277\266\224\256\022\007\377_\031:\015\345-\264Rj~n\207\240/\370D\3143z\305k\245/f'\004\230r[\207\321H\240a^\247\015\037\351\276!~\354\031\314~)\224\364r\354\036\337\376\006\024K\300ih\335\377\011\256C\262\253\016\210ZN\272\231{\332\304\303\332\003;\221\022\004\275\335\330$\027v\355\004,f\2713\266\352\311\027\326\316\3742LMM\353\037\222C\264\240\275>,\210o\326\212F_@G\306\276\274\332\247\256\336\027U\377\224\313\221>b\355\003K\300\231c<H\236;[\027!\263\311\260V\230\200Jp\333\023\027\004Y\273\321\306N\024%\376\010\317\351\016\332&\030\213\225\017#\006\243nb\332r\363~\013\336\207\337\005\024\322\351$KG\334\376\260\366\025]K\031\037w\016O\2358\277\032?s%\300R\263\031R\273S:\376&\377\326\365\240_\252+;\002+\243\034\257\364\377\322U\320F\005\260\230OS\373~\3228\001*\304\265\204\273\335\236\022\305\312\233\350\011\231\211\250\2103\273\305\214\1770g\305k\254\245!X\262\216\000\020K%~OsJ\365m"\330*\246KX"K4\350\013\214#
(1 row)
Well, that’s certainly a far cry from “testing”.
Now for the code that decrypts our message.
#!/usr/bin/env python
import psycopg2
conn = psycopg2.connect("dbname='crypto_test' user='tgc' host='localhost'")
curs = conn.cursor()
f = open("secret.key", "r")
key = f.read()
curs.execute("SELECT pgp_pub_decrypt(message, dearmor('%s')) from messages" % (key))
rows = curs.fetchall()
for row in rows:
print row[0]
Let’s try running our decryption script
$ python ./test-decrypt.py testing
Note: depending on the type of data you are encrypting, the functions pgp_pub_encrypt_bytea and pgp_pub_decrypt_bytea are also available.