Support for AWS SSE-S3 and SSE-KMS Encryption for EXPORT to S3

Detail name	Value
Changelog Number	7393
Type	Improvement
Status	Resolved
Fix Versions	Exasol 6.1.2
Resolution Date	2019-02-08

The EXPORT statement now has support for writing SSE-S3 and SSE-KMS encrypted files to AWS S3.

SSE-C is currently not supported.

For details of the various encryption options, please refer to the following. https://docs.aws.amazon.com/AmazonS3/latest/dev/serv-side-encryption.html

In order to specify the required and/or optional information for an encryption option, additional parameters were added which must be specified in the IDENTIFIED BY field of the EXPORT statement.

Syntax:

EXPORT test_table INTO CSV AT 'https://testbucket.s3.amazonaws.com'
USER '<key-ID>' IDENTIFIED BY '<secret-key>;sse_type=<aws:kms|AES256>;kms_key_id=<aws-kms-key-id>;enc_context=<encryption-context>'
FILE 'testpath/test.csv';

IDENTIFIED BY parameters:

Name	Required	Description
secret-key	no for temporary EC2 role credentials, yes for password authentication	secret access key
sse_type	no, yes for SSE	'aws:kms' for SSE-KMS, 'AES256' for SSE-S3
kms_key_id	no, yes for 'aws:kms'	KMS Key ID (ARN) to use for encryption
enc_context	no	JSON-formatted string key-value pairs

For IMPORT, no changes are required because AWS S3 automatically decrypts the encrypted files before sending the data. However, if the above parameters are given in the IMPORT statement, they will simply be ignored. It will not result in an error.

Examples:
SSE-S3 encryption, user/password authentication:

EXPORT test_table INTO CSV AT 'https://testbucket.s3.amazonaws.com'
USER '<key-ID>' IDENTIFIED BY '<secret-key>;sse_type=AES256'
FILE 'testpath/test.csv';

SSE-KMS encryption with encryption context, temporary credentials for EC2 role authentication:

EXPORT test_table INTO CSV AT 'https://testbucket.s3.amazonaws.com'
USER '' IDENTIFIED BY ';sse_type=aws:kms;kms_key_id=arn:aws:kms:eu-west-1:<XXXXX>:key/<YYYYY>;enc_context={"foo":"bar", "baz":"qux"}'
FILE 'testpath/test.csv';