Federate Single Sign-On Access to Amazon Athena Query Editor with OneLogin

By Rimon Mazzawi, Sr. Solutions Architect – AWS
By Attila Papp, Solutions Architect – Adesso

Adesso

The Amazon Athena web-based query editor, which is available on the AWS Management Console, enables data consumers to author and run SQL queries on data sources registered with the AWS Glue Data Catalog and other data sources such as Amazon Simple Storage Service (Amazon S3).

In this post, we describe the setup to provide federated access with OneLogin as the identity provider (IdP) to securely access, author, and run queries in the Amazon Athena web-based editor via the AWS console, without the need for users to install a JDBC driver or run a SQL client on their machines.

OneLogin is a cloud-based identity and access management (IAM) solution used to securely manage and control user access to various applications and systems within an organization. It helps enhance security by ensuring that only authorized users can access sensitive applications and data and streamlines user authentication.

Together, Amazon Web Services (AWS) and Adesso are sharing this step-by-step guidance to implement a single sign-on (SSO) access federation to the Athena query editor with OneLogin. Adesso is an AWS Partner and end-to-end provider that helps unlock your full business potential using AWS technologies—from consultation and transformation to managed services.

Solution Overview

The solution described in this post uses OneLogin as the IdP and illustrates how to set up your OneLogin application and AWS Identity and Access Management (IAM) permissions.

The Amazon Athena web editor integration with your IdP automatically redirects the user’s browser to the web user interface (UI) with the appropriate permissions to Athena and AWS Glue. This enables users to easily access the Athena web editor via the AWS console.

We will demonstrate how to provide access to users while following the principle of least privilege, limiting the user’s permission to only access the Athena web editor. This is often required for users that connect from outside of the organization; for example, external users and partners where the organization requires the access to be limited to the query editor only and prevents users from accessing other AWS services from the console.

Figure 1 – Architecture diagram.

Walkthrough

The steps outlined in this post are as follows:

Set up the OneLogin app.
Set up the IAM role that establishes a trust relationship between the OneLogin IdP and AWS, and allows access to Amazon Athena.
Complete OneLogin advanced configuration:
- Finalize the OneLogin configuration.
- Define a default relay state to direct users to the Athena web editor after a successful SAML authentication.
- Configure the necessary SAML attributes Role and RoleSessionName.
Sign in to OneLogin account and access the application assigned to you. The application directs you to the Athena web editor using federated login.
Access and query AWS Glue Data Catalog tables via the Athena web editor.

Prerequisites

For this walkthrough, you should have the following prerequisites:

AWS account.
Configure the Amazon Athena settings for query results location (Amazon S3 bucket) and workgroups.
Access to OneLogin application administration page.
OneLogin SAML application.

Set Up OneLogin

First, you need to set up the OneLogin application:

Log in to your OneLogin application administration page: https://<prefix>.com/admin2/apps, where <prefix> is specific to your account and was created at account setup.
Open the Admin console and navigate to the Applications page; click on Add App as shown below.
Search for SAML Custom and choose the SAML Custom Connection (advanced) option.

Figure 2 – OneLogin find applications.

Insert a display name and optionally upload an Icon, and then click Save in the top right.
Click on the configuration page in the navigation pane to set following attributes:
- RelayState – https://eu-central-1.console.thinkwithwp.com/athena/home
  - Note that the AWS region is part of the URL, so choose the region of your choice.
- Audience (EntityID) – urn:amazon:webservices
- Recipient – https://signin.thinkwithwp.com/saml
- ACS (consumer) URL validator – ^https:\/\/signin\.aws\.amazon\.com\/saml$
- ACS (consumer) URL – https://signin.thinkwithwp.com/saml
- Scroll down
- SAML initiater – OneLogin
- SAML nameID format – Persistent
- SAML issuer type – Specific
- SAML signature element – Assertion
- Click on Save on the top right side

Figure 3 – OneLogin add application details.

Navigate to the Parameters page on the left pane and click on the “+” button to add the following parameters for the Role and RoleSessionName:
- Field name – https://thinkwithwp.com/SAML/Attributes/Role
  - Choose the No Default value and tick the Include in SAML assertion box.
- Field name – https://thinkwithwp.com/SAML/Attributes/RoleSessionName
  - Set the default value to Email and tick the Include in SAML assertion box.

Figure 4 – OneLogin parameters configuration.

In the top right corner, click on SAML Metadata under More Actions and download the SAML configuration file. This will later be uploaded in AWS IAM as the identity provider’s configuration.
Click on Save to complete the setup.

Set up the IAM Identity Provider and IAM Role

Next, set up an IAM identity provider to establish a trust relationship between the OneLogin IdP and AWS as follows:

In the IAM console, choose Identity providers under Access management in the navigation pane.
Click on Add provider to add a new IdP.
For Provider type, select SAML.
For Provider name, enter a name such as OneLogin.
Select Choose file and upload the SAML metadata file you downloaded from previous step.
Click on Add provider.

Figure 5 – Add an identity provider.

Now, create the IAM role for SAML 2.0 federation as follows:

In the IAM console, choose Roles in the navigation pane.
Choose Create role.
For Trusted entity type, select SAML 2.0 federation.
For SAML 2.0-based provider, choose the IdP you created in the previous step.
Select Allow programmatic and AWS Management Console access.
Click Next and then select Create Policy to create a custom policy.

In the following custom policy, you will provide your region, account number, and the Amazon Athena workgroup parameters to grant access to Athena to get the appropriate permissions. Name the policy “saml_policy” for convenience. This can be customized based on your security requirements to provide the necessary access to the users applying principle of least privilege.

In this example IAM policy, users are granted access to Amazon Athena, AWS Glue, and Amazon Simple Storage Service (Amazon S3) for a specific S3 bucket and a specific Athena workgroup (prerequisite) in your region and AWS account.

adfdafadfd
   "Version":"2012-10-17",
   "Statement":[
      {
         "Action":[
            "glue:GetTable",
            "glue:GetTables",
            "glue:SearchTables",
            "glue:GetDatabase",
            "glue:GetDatabases",
            "glue:CreateDatabase",
            "glue:CreateTable",
            "glue:UpdateTable",
            "glue:DeleteTable",
            "glue:GetPartition",
            "glue:GetPartitions",
            "glue:GetUserDefinedFunction",
            "glue:GetUserDefinedFunctions"
         ],
         "Resource":[
            "arn:aws:glue:{Region}:{Account}:catalog",
            "arn:aws:glue:{Region}:{Account}:database/{DatabaseName}",
            "arn:aws:glue:{Region}:{Account}:table/{DatabaseName}/{TableName}"
         ],
         "Effect":"Allow"
      },
      {
         "Action":[
            "s3:GetBucketLocation",
            "s3:GetObject",
            "s3:ListBucket",
            "s3:ListBucketMultipartUploads",
            "s3:AbortMultipartUpload",
            "s3:PutObject",
            "s3:ListMultipartUploadParts"
         ],
         "Resource":[
            "arn:aws:s3:::<athena query result bucket name>",
            "arn:aws:s3:::<athena query result bucket name>/*"
         ],
         "Effect":"Allow"
      },
      {
         "Action":"athena:*",
         "Resource":"arn:aws:athena:<region>:<account>:workgroup/<workgroup name>",
         "Effect":"Allow"
      }
   ]
}

Attach the policy you created in the previous step to the SAML 2.0 federation role and click Next.
Provide a Role Name such as “SAMLFederationRole” and then click Create role to complete the IAM role creation step.

Configure OneLogin User with IAM Role and SAML Provider

In this step, we go back to the OneLogin admin page to set up the user details and allow federated access to the Athena web editor:

Navigate to the Users page on the left pane to onboard a selected user.
Click on the user and update the following fields.
Set the attribute: https://thinkwithwp.com/SAML/Attributes/Role to <role arn>,<saml provider arn>
- For example: arn:aws:iam::012345678910:role/my-sso-prefix/my-role,arn:aws:iam::012345678910:saml-provider/my-saml-provider-name
- This is the role to assume and the SAML provider Amazon Resource Names (ARNs), divided by a comma.

Figure 6 – Edit SAML custom connector.

Log in to Athena Using OneLogin and Run Queries

Navigate to OneLogin and click on the tile of your SAML application.
In the top right corner, under Workgroup paste in the name of the workgroup you want to use and click Enter. Note that this has to be done only the first time the user logs in.
Acknowledge the workgroup change, and click Confirm.

Figure 7 – Confirm workgroup change.

Please note that the solution could be enhanced to control which IAM role within AWS a user is given based on their OneLogin roles.

OneLogin app rules could be applied to the application config to populate the value of the https://thinkwithwp.com/SAML/Attributes/Role claim in the SAML assertion, with the appropriate values corresponding to each IAM role on the AWS side when the user holds a particular OneLogin role. This way provides more control over who can access AWS and what level of access they get within AWS all based on OneLogin roles.

Summary

This post demonstrated how to federate single sign-on access to Amazon Athena using OneLogin as an identity provider.

We illustrated how to set up the OneLogin application and configure SAML federation with IAM IdP and IAM role in your AWS account. We then described how to log in to Amazon Athena using the federated access on the web console and validated the configuration by switching the workgroup and running sample queries on an AWS Glue Data Catalog table.

This setup eliminates the need for users to install a JDBC driver or run a SQL client on their local machines, and provides an alternative and secure access directly to the Athena web editor to author and run SQL queries on their data.

.

.

Adesso – AWS Partner Spotlight

Adesso is an AWS Partner and end-to-end provider that helps unlock your full business potential using AWS technologies—from consultation and transformation to managed services.

Contact Adesso | Partner Overview

AWS Partner Network (APN) Blog