Decrypt Amazon Redshift CSV dump

Question

I would like to decrypt a CSV dump of an Amazon Redshift table locally. I m using the unload command and client side encryption since the data contains sensitive information.

The command i am using is like this:

unload ('select * from testtable.test')
to 's3://unload'
credentials 'aws_access_key_id=<AWS_KEY_ID>;aws_secret_access_key=<AWS_SECRET_KEY_ID>;master_symmetric_key=<MASTER_KEY>'
delimiter as ','
addquotes
escape
encrypted
ALLOWOVERWRITE

to generate a master_key i used the follwing command:

openssl enc -aes-256-cbc -pass pass:<mypass> -P -nosalt -base64

This outputs:

key=....
iv =....

I used the key as the `master_symmetric_key.

I copy the s3 data locally and try to decrypt like this:

openssl enc -aes-256-cbc -d -nosalt -base64 -in 0000_part_00 -out temps.csv

But get:

bad decrypt 6038:error:0606506D:digital envelope routines:EVP_DecryptFinal_ex: wrong
final block length:/SourceCache/OpenSSL098/OpenSSL098-52.20.2/src/crypto/evp/evp_enc.c:323

How do I decrypt an Amazon Redshift CSV dump?

Answer 1

The Key is stored as metadata and is available in x-amz-meta-x-amz-key, and The IV is stored as metadata and is available in x-amz-meta-x-amz-iv. From the Redshift documentation:

... UNLOAD then stores the encrypted data files in Amazon S3 and stores the encrypted envelope key and IV as object metadata with each file. The encrypted envelope key is stored as object metadata x-amz-meta-x-amz-key and the IV is stored as object metadata x-amz-meta-x-amz-iv.

When you get the S3 object you will also get these meta-data fields. Here are some example of S3 GET-Object example: http://docs.aws.amazon.com/AmazonS3/latest/API/RESTObjectGET.html

Answer 2

So Redshift uses the envelope encryption the same way as the AWS SDK uses envelope encryption to store files on S3. So in order to decrypt the file you should:

1. Get the encrypted data key and the iv from the S3 object metadata (x-amz-meta-x-amz-key and x-amz-meta-x-amz-iv respectively)
2. Decrypt that x-amz-meta-x-amz-key value using your symmetric key using AES256 ECB mode
3. Then decrypt '0000_part_00' using AES256 CBC mode with iv set to iv from step1 and the key set to the result of step 2.
4. Remove padding.

If you find these steps cumbersome what you can do is add MANIFEST to your UNLOAD this will add a manifest files. Then you can use redshift-manifest-tools which allows to retrieve files mentioned in a manifest file and perform the decryption for you.

A command like:

redshift-manifest-tools --action retrieve-files --manifest-s3url 's3://<your-bucket>/path/to.manifest' --dest /tmp/ --symmetric-key '<base64-master-key>'

Would download the files, decrypt them and store the plaintext versions in /tmp/.