unload_table fails with KMS encypted s3 bucket, option KMS_KEY_ID misses

[Jacco]

Describe the bug

When the S3 bucket requires encryption

The error received is:

S3ServiceException:User: arn:aws:sts:::assumed-role/redshift-s3-role/RedshiftIamRoleSession is not authorized to perform: s3:PutObject on resource: "arn:aws:s3:::bucketname..."

Steps to reproduce

Create a bucket and a KMS key
Create bucket policy that does not allow PutObject unless the are encrypted

        "Sid": "DenyUnEncryptedObjectUploads",
        "Effect": "Deny",
        "Principal": {
            "AWS": "*"
        },
        "Action": "s3:PutObject",
        "Resource": "arn:aws:s3:::bucketname/*",
        "Condition": {
            "Null": {
                "s3:x-amz-server-side-encryption": "true"
            }
        }

Try use the unload_table macro

Expected results

It should succeed as long as you provide KMS_KEY_ID but that missed

Actual results

The error mentioned above occurs

System information

• postgres
• redshift
• bigquery
• snowflake
• other (specify: ____________)

I can create a PR for this. Let my know :-)

I was able to work around this creating my own wrapper:

{% macro my_unload_table(
                schema,
                table,
                s3_path,
                iam_role=None,
                aws_key=None,
                aws_secret=None,
                aws_region=None,
                aws_token=None,
                manifest=False,
                header=False,
                format=None,
                delimiter=",",
                null_as="",
                max_file_size='6 GB',
                escape=True,
                compression=None,
                add_quotes=False,
                encrypted=False,
                overwrite=False,
                cleanpath=False,
                parallel=False,
                partition_by=None,
                extension=None,
                key_id=None
                ) %}

{{ redshift.unload_table(
    schema,
    table,
    s3_path,
    iam_role=iam_role,
    aws_key=aws_key,
    aws_secret=aws_secret,
    aws_region=aws_region,
    aws_token=aws_token,
    manifest=manifest,
    header=header,
    format=format,
    delimiter=delimiter,
    null_as=null_as,
    max_file_size=max_file_size,
    escape=escape,
    compression=compression,
    add_quotes=add_quotes,
    encrypted=encrypted,
    overwrite=overwrite,
    cleanpath=cleanpath,
    parallel=parallel,
    partition_by=partition_by,
    extension=extension
) }}

  {% if key_id %}
  KMS_KEY_ID '{{ key_id }}'
  {% endif %}
{% endmacro %}