Disable override client-side settings on Athena workgroups

[SoumayaMauthoorMOJ]

Note

Using the proposed RFC template

Need (Context / Problem)

Athena Workgroups

Athena uses workgroups to separate workloads, control team access, enforce configuration, and track query metrics and control costs. Primary is the default workgroup. We have created custom workgroups for dbt and Airflow to make it easier to track costs and debug issues.

primary and the dbt athena workgroups have the override client side settings turned off. This means that Athena uses the client's settings for all queries that run in the workgroup, including the settings for expected bucket owner, encryption, control of objects written to the query results bucket and query results location.

Query Result Location

Athena automatically stores query results to a query result location that you can specify in Amazon S3.

We customise the Athena query output location to s3://mojap-athena-query-dump/{principal-id} in pydbtools. For example:

import pydbtools as pd

df = pd.read_sql_query(sql="select 1 as ID", ctas_approach=False)

print(df.query_metadata['ResultConfiguration']['OutputLocation'])
print(df)

gets written to s3://mojap-athena-query-dump/AROAYUIXP4BWSZH6CWPHR:airflow_dev_example_role when run from airflow using the airflow_dev_example_role role (see use_kubernetes_athena.py) and the default primary workgroup.

All alpha users automatically get access to their respective mojap-athena-query-dump folder in iam-builder.

This is inconsistent with the custom Airflow Athena workgroups.

Airflow Athena Workgroups

The custom Athena workgroups for Airflow currently overrides client side settings. The query output location is set to s3://mojap-athena-query-dump/ and we have manually added s3://mojap-athena-query-dump/ to all airflow roles which use a custom workgroup. This is quite permissive but since it's the airflow role we were less worried about granting it access to the entire bucket.

We tested disabling the override (see PR), so that we could carry on writing to s3://mojap-athena-query-dump/{principal-id}.

However Trivy, an open source security scanner, raised this error:

HIGH: The workgroup configuration is not enforced.
════════════════════════════════════════
Clients can ignore encryption requirements without enforced configuration. Athena workgroup configuration should be enforced to prevent client side changes to disable encryption settings.

However I think it's impossible to disable encryption settings.

Disabling encryption settings

According to https://docs.aws.amazon.com/athena/latest/ug/workgroups-settings-override.html If Override client-side settings is not selected, workgroup settings are not enforced at the client level, including encryption.

This means that when I run this query, I should be saving the result without encryption:

import pydbtools as pd

df = pd.read_sql_query(sql="select 1 as ID", ctas_approach=False, encryption=None)

print(df.query_metadata['ResultConfiguration']['OutputLocation'])
print(df)

However the result is still saved with the default SSE-S3 S3 managed keys

I have tested to make sure that the encryption parameter is working as expected by setting encryption type to SSE_KMS:

import pydbtools as pd

df = pd.read_sql_query(sql="select 1 as ID", 
                       ctas_approach=False,
                       encryption='SSE_KMS',
                       kms_key='arn:aws:kms:eu-west-1:593291632749:key/{}'
                       )

print(df.query_metadata['ResultConfiguration']['OutputLocation'])
print(df)

My suspicion is that since Amazon S3 now applies server-side encryption with SSE-S3 as the base level of encryption, it's now impossible to disable encryption settings. I have raised an AWS support ticket to confirm this.

Approach

• Use a consistent settings for all our Athena workgroups
• Disable the override client-side settings on all Athena workgroups, allowing pydbtools to handle the query result location
• Ignore the resultant Trivy error

Evaluation

Pros

• This will not impact users / airflow workflows currently using the primary workgroup
• Query results will be saved to s3://mojap-athena-query-dump/{principal-id} which makes it easier to find and assign permissions

Cons

• This setting is there for a reason, it could be bad practice to disable it and there may be other consequences I haven't thought off.

Alternatives

1. Enable override client-side settings

Enable the override client-side settings on all Athena workgroups, including primary. This means that query results will have to be saved to a single location per workgroup, for example s3://mojap-athena-query-dump/{workgroup}

Pros

• Potentially safer - needs confirmation

Cons

• This will require changing pydbtools and iam-builder so that queries are written to the new location and permissions are updated.
• It will be difficult for users using the Primary workgroup to locate their query results, should they ever need to
• DEAL-BRAKER - Users are likely to be able to access other users' query results because it's all saved to the same location

2. Stay AS-IS

Use the As-Is solution so Enable the override client-side settings on airflow Athena workgroups only, and save query results to s3://mojap-athena-query-dump/

Pros

• Less complicated and nothing's gone wrong yet

Cons

• The airflow role has access to the entire s3://mojap-athena-query-dump/ bucket
• Airflow is a more regulated environment so it makes less sense to enable override client-settings for Airflow and disable it for one-off queries.

[jhpyke]

Wording of the docs on disabling Override client-side settings:
Athena uses the client's settings for all queries that run in the workgroup, including the settings for query results location, expected bucket owner, encryption, and control of objects written to the query results bucket. We want to give users control over results location, but would ideally like to retain control of bucket owner, encryption, and control of objects in bucket with bucket owner. We believe, that with our current bucket configuration that we're able to achieve this, but this bears testing.

[SoumayaMauthoorMOJ]

I was thinking about what @gwionap said yesterday, specifically that if we go with the alternative and enable override client settings on primary it will mean that users will be able to access each other query results because data will be saved to the same location.

I remembered that I did some testing with awswrangler which saves results to the default s3://aws-athena-query-results-593291632749-eu-west-1 location:

import awswrangler as wr

df = wr.athena.read_sql_query(sql="select 1 as ID", 
                       database='default',
                       ctas_approach=False,
                       )

print(df.query_metadata['ResultConfiguration']['OutputLocation'])
print(df)

I got an alpha user to try to access the file and he couldn't access it, even though he has get/put access to aws-athena-query-results-*:

       {
            "Action": [
                "s3:GetObject",
                "s3:PutObject"
            ],
            "Effect": "Allow",
            "Resource": [
                "arn:aws:s3:::aws-athena-query-results-*"
            ],
            "Sid": "AllowGetPutObject"
        },

so maybe Athena is able to somehow ensure only the right people can access query results? It would require further testing