Data encryption in postgreSQL

Introduction

Recently there has been news about hacker claim that they are stealing data from website or an organization affecting sensitive data of million customers made public and sold for a certain price. however organization have the responsibility to protect their customers data.

At this point, we want to protect customer data from database layer, even if it was steal by a group of hacker or someone can gain access to database. but the hacker cannot read at some private data in some column because its encrypted by private/public key. so, whoever steal your data. their cannot read or retrieve it unless they have the key.

PGP Encryption

postgreSQL offers encryption at several levels, and provides flexibility in protecting data from disclosure due to database server theft, unscrupulous administrators, and insecure networks. with postgreSQL encryption we will encrypt some customer data that are private with 2 way encryption decryption with PGP encryption.

Create PGP public/private Key

Before we can use PGP encryption, we need to make keys. If you are on some Linux OS you probably have the command line tool called gpg.
if you are on mac os x. install gpg with brew.

brew install gpg

If you are on windows, you need to download them from somewhere like this page GNU Pg binaries

next, follow the step to generate gpg keys for public/private key to decrypt/encrypt data from query.

gpg --gen-key

and follow the directions

gpg --list-secret-keys

This will provide you a list of keys one being the one you generated. It will look something like:

sec 1024R/123ABCD 2020–05–15
uid      My key (super data encrypt)
ssb 1024R/999DEFG 2020–05–15

Where the 1024R is the bit strength I chose and 123ABCD is the private key and 999DEFG is the public key.

gpg -a --export 999DEFG > public.key

Replacing the 999DEFG with your public key code. This is the key you will need to encrypt data.

gpg -a --export-secret-keys 123ABCD > secret.key

again Replacing the 123ABCD with your private key code. This is the key you will need to decrypt data.

note the -a switch is short for armour. Each key by default is a binary ugly looking thing that is hard to handle. The -a converts it to a palpable like piece of text easy for cut and paste. When using it, you will need the PostgreSQL `pgcrypto dearmor` function to feed it into the relevant encrypt/decrypt functions.

PostgreSQL with PGP Encryption

Now that we have our public and private keys, we are ready to encrypt the data. we’ll demonstrate using the following functions:

• pgp_pub_encrypt — this is the function we will use to encrypt our data using our public key.
• pgp_pub_decrypt — this is the function we will use to decrypt our data using our private key.
• dearmor — as we stated earlier we used the -a argument in gpg to produce an easy to cut and paste text version of our keys. The keys are binary in nature, so we need dearmor to convert them back to their native format suitable for passing to the encrypt/decrypt functions
• pgp_key_id — over time you may use several keys to encrypt your data in the database so some data may be accessible by one key and some by another. You may even use different keys for different set of users so they can’t view each others personal data. This function tells you the key that was used to encrypt a given piece of data so that you can pull from your chest of collected keys, the right key to decrypt a certain piece of data.

Now, lets create query for insert and select data

first, create extension:

CREATE EXTENSION pgcrypto;

then, lets create a sample customer table :

in this exercise we want to protect customer email & credit card number.

before we create insert query, copy your public key to clipboard. and paste it in the public key block of this query.

and now lets insert customer data with encrypted only for email data & credit card number

now if we select our data

select * from customers;

we will see that email , & cc column are filled with encrypted data, and it will be hard to read or print it.

To decrypt the data we pull from our chest of private keys matching the public key we used to encrypt with.

before we create retrieve data/select query, copy your private key to clipboard. and paste it in the private key block of this query.

then we will get result :

id | username | email | cc
— — — — — — — — — — — — — — — 
1 | steven | steven@gmail.com | 4114423232323332

Conclusion

It is important to protect your customer private data. so whoever steal your data or anonymous breacher access your data or gain your data. they cannot easily read or print sensitive data that you already encrypted. in this case we use postgreSQL encryption with PGP encryption to encrypt/decrypt data. and make sure you saved the public / private key in the safe place.