Simplify enterprise knowledge entry utilizing the Amazon Redshift integration with Amazon S3 Entry Grants

Scaling knowledge entry securely whereas sustaining operational effectivity is a crucial problem for organizations. Entry rights are sometimes fragmented throughout varied AWS companies, as totally different enterprise items personal and handle totally different knowledge shops, corresponding to Amazon Easy Storage Service (Amazon S3) and Amazon Redshift. As knowledge grows, modeling entry in AWS Identification and Entry Administration (IAM) insurance policies turns into difficult for knowledge homeowners, as they attempt to handle entry for various teams and customers throughout accounts within the group. Managing these distributed entry rights requires substantial overhead, as a result of safety groups and knowledge homeowners should collaborate to replace and monitor permissions to ensure knowledge is barely accessible to approved customers.

Recognizing this problem, the Amazon S3 Entry Grants integration with Amazon Redshift permits centralized person authentication by AWS IAM Identification Heart, offering unified identification throughout the group. S3 Entry Grants permits particular IAM Identification Heart customers or teams to entry registered Amazon S3 areas by a grant. Making a grant with a gaggle as grantee lets the group members entry solely the S3 bucket, prefix, or object throughout the grant’s scope. Which means that entry will be managed by merely making a grant for a gaggle and including or eradicating the person from the group, lowering administrative overhead.

On this submit, we present grant Amazon S3 permissions to IAM Identification Heart customers and teams utilizing S3 Entry Grants. We additionally check the combination utilizing an IAM Identification Heart federated person to unload knowledge from Amazon Redshift to Amazon S3 and cargo knowledge from Amazon S3 to Amazon Redshift.

Resolution overview

This submit covers a use case the place a big group manages hundreds of company customers throughout a number of enterprise items by their identification supplier (IdP). These customers usually work together with huge quantities of knowledge saved throughout quite a few S3 buckets, regularly performing extract, rework, and cargo (ETL) operations by Amazon Redshift. Their purpose is to have a less complicated ETL course of of knowledge loading and unloading operations in Amazon Redshift with out managing a number of IAM roles and insurance policies for Amazon S3 entry. Additionally, they need a centralized entry administration resolution that seamlessly integrates their company identities from present IdP with AWS companies.

For this resolution, AWS Organizations is enabled and IAM Identification Heart is configured within the delegated administration account. The group has two member accounts: Member Account 1 runs analytical workloads on Amazon Redshift, with all of the companies enabled with trusted identification propagation, and Member Account 2 manages knowledge saved in Amazon S3; right here you’ll arrange S3 Entry Grants. Amazon Redshift will load the user-specific knowledge from Amazon S3 saved in Member Account 2 utilizing entry management based mostly on IAM Identification Heart customers and teams. This improves the person expertise sustaining a single authentication mechanism inside a corporation, retaining entry management, and useful resource separation utilizing AWS accounts as a boundary per enterprise items.

The next diagram illustrates the answer structure.

Determine 1: Structure displaying the answer

To run this resolution in a single account, configure Amazon Redshift and S3 Entry Grants with account situations of IAM Identification Heart. Evaluate When to make use of account situations for extra data.

The answer workflow consists of the next steps:

1. The person configures and connects with their respective shoppers (corresponding to Amazon Redshift Question Editor v2 or a SQL consumer) to entry Amazon Redshift utilizing IAM Identification Heart.
2. A brand new browser home windows opens and is redirected to the login web page of the IdP.
3. The person logs in with their IdP person title and password.
4. After the login is profitable, the person is redirected to the consumer utility, such because the Amazon Redshift Question Editor.
5. When the person tries to entry knowledge in Amazon S3 utilizing the LOAD or UNLOAD SQL command, Amazon Redshift in Member Account 1 will request credentials from the S3 Entry Grants occasion from Member Account 2, the place the Amazon S3 knowledge is saved. This request will comprise the person context.
6. S3 Entry Grants will then consider the request towards the grants it has, matching the identification specified within the grant with the one obtained within the request. If there’s a match, the requestor will obtain non permanent entry to the Amazon S3 areas specified within the grant’s scope.

To implement the answer, we stroll you thru the next steps:

1. Allow S3 Entry Grants in your Amazon Redshift managed utility.
2. Replace IAM function permissions used within the utility.
3. Create a bucket for S3 Entry Grants.
4. Create an IAM coverage and function for S3 Entry Grants.
5. Arrange S3 Entry Grants.
6. Enable cross-account entry of sources.
7. Create Redshift tables.
8. Unload and cargo knowledge in Amazon Redshift.

Stipulations

You need to have the next stipulations already arrange:

Allow S3 Entry Grants from the Amazon Redshift managed utility

After you may have created your Redshift utility in IAM Identification Heart, it’s essential carry out the next steps to allow S3 Entry Grants within the account the place Amazon Redshift exists. For this submit, we use Member Account 1:

1. Log in to the AWS Administration Console as admin.
2. On the Amazon Redshift console, select IAM Identification Heart connection within the navigation pane.
3. Choose the managed Redshift utility and select Edit.
4. Select Amazon S3 entry grants in Trusted identification propagation.
5. Select Save modifications.

The next screenshot exhibits the up to date configuration.

Determine 2: Redshift managed utility

Replace the IAM function permission connected to the Amazon Redshift managed utility

The Amazon Redshift managed utility has an IAM function connected (within the previous screenshot, you possibly can see the function referred to as IAMIDCRedshiftRole below IAM function for IAM Identification Heart entry. We now want to change the coverage on this function and add permissions to permit interplay with Amazon S3. Edit the function and add s3:GetAccessGrantsInstanceForPrefix and s3:GetDataAccess as proven within the following coverage:

{     "Model": "2012-10-17",     "Assertion": [         {             "Sid": "AllowGetRedsfhitInformation",             "Effect": "Allow",             "Action": [                 "redshift-serverless:ListNamespaces",                 "redshift-serverless:ListWorkgroups",                 "redshift:DescribeQev2IdcApplications",                 "redshift-serverless:GetWorkgroup"             ],             "Useful resource": "*"         },         {             "Sid": "AllowDescribeIdentityCenter",             "Impact": "Enable",             "Motion": [                 "sso:DescribeApplication",                 "sso:DescribeInstance"             ],             "Useful resource": [                 "arn:aws:sso:::instance/",                 "arn:aws:sso:::application//*"             ]         },         {             "Sid": "RetrieveAGinstanceforParticularPrefix",             "Impact": "Enable",             "Motion":                        "s3:GetAccessGrantsInstanceForPrefix",             "Useful resource": "*"         },         {             "Sid": "CrossAccountAccessGrantsPolicy",             "Impact": "Enable",             "Motion": [                 "s3:GetDataAccess"             ],             "Useful resource": "arn:aws:s3:::access-grants/default"         }     ] }

Change along with your IAM Identification Heart occasion ID and with the account ID the place IAM Identification Heart is ready up. You additionally want to exchange the useful resource in CrossAccountAccessGrantscasePolicy along with your S3 Entry Grants occasion data.

Create an S3 bucket for S3 Entry Grants

On this step, you create a S3 bucket that you just wish to grant entry to or use an present bucket. For this submit, we create a bucket referred to as amzn-s3-demo-bucket. You may select one other applicable title. For extra data, see Making a basic function bucket.

The bucket have to be situated in the identical AWS Area as your S3 Entry Grants occasion and IAM Identification Heart.

Subsequent, create two folders within the newly created S3 bucket. Should you’re utilizing an present S3 bucket, determine two folders to make use of for this walkthrough. For this weblog submit, we create two folders: awssso-sales and awssso-finance, below a bucket named amzn-s3-demo-bucket. The aim of making two folders is in order that customers from totally different teams have entry solely to their respective folder.

Create an IAM coverage and function for S3 Entry Grants

Full the next steps to create an IAM coverage to scope the permissions for a particular entry grant:

1. Create an IAM coverage with the next permissions. For extra data on creating IAM coverage, see Create IAM insurance policies. To get further data on the next particular coverage, check with Register a location.

   {     "Model": "2012-10-17",     "Assertion": [         {             "Sid": "ObjectLevelReadPermissions",             "Effect": "Allow",             "Action": [                 "s3:GetObject",                 "s3:GetObjectVersion",                 "s3:GetObjectAcl",                 "s3:GetObjectVersionAcl",                 "s3:ListMultipartUploadParts"             ],             "Useful resource": "arn:aws:s3:::/*",             "Situation": {                 "StringEquals": {                     "aws:ResourceAccount": ""                 },                 "ArnEquals": {                     "s3:AccessGrantsInstanceArn": [                         "arn:aws:s3:::access-grants/default"                     ]                 }             }         },         {             "Sid": "ObjectLevelWritePermissions",             "Impact": "Enable",             "Motion": [                 "s3:PutObject",                 "s3:PutObjectAcl",                 "s3:PutObjectVersionAcl",                 "s3:DeleteObject",                 "s3:DeleteObjectVersion",                 "s3:AbortMultipartUpload"             ],             "Useful resource": "arn:aws:s3:::/*",             "Situation": {                 "StringEquals": {                     "aws:ResourceAccount": ""                 },                 "ArnEquals": {                     "s3:AccessGrantsInstanceArn": "arn:aws:s3:::access-grants/default"                 }             }         },         {             "Sid": "BucketLevelReadPermissions",             "Impact": "Enable",             "Motion": [                 "s3:ListBucket"             ],             "Useful resource": "arn:aws:s3:::",             "Situation": {                 "StringEquals": {                     "aws:ResourceAccount": ""                 },                 "ArnEquals": {                     "s3:AccessGrantsInstanceArn": "arn:aws:s3:::access-grants/default"                 }             }         }     ] }

2. Create an IAM function that has permission to entry your S3 knowledge within the Area. For extra data, see IAM function creation. On this instance, we create an IAM function referred to as iamidcs3accessgrant. You should connect the previous coverage to the IAM function.
3. Use the next belief coverage for the IAM function:

   {     "Model": "2012-10-17",     "Assertion": [         {             "Sid": "ForAccessGrants",             "Effect": "Allow",             "Principal": {                 "Service": "access-grants.s3.amazonaws.com"             },             "Action": [                 "sts:AssumeRole",                 "sts:SetContext",                 "sts:SetSourceIdentity"             ],             "Situation": {         "StringEquals": {           "aws:SourceAccount":"",           "aws:SourceArn":"arn:aws:s3:::access-grants/default"         }       }         }     ] }

Arrange S3 Entry Grants

The S3 Entry Grants occasion serves because the container on your S3 Entry Grants sources, which embrace registered areas and grants. You may create just one S3 Entry Grants occasion per Area per account. You may affiliate this S3 Entry Grants occasion to your company listing along with your IAM Identification Heart occasion. After you’ve completed so, you possibly can create grants on your company customers and teams. S3 Entry Grants requires registering a location to map an S3 bucket or prefix to an IAM function, enabling safe entry by offering non permanent credentials to grantees for that particular location.

Full the next steps to arrange S3 Entry Grants:

1. On the Amazon S3 console, select your most well-liked Area.
2. Within the navigation pane, select Entry Grants.
3. Select Create S3 Entry Grants occasion.
4. Choose Add IAM Identification Heart occasion in and enter the IAM Identification Heart occasion Amazon Useful resource Identify (ARN). For this submit, we use the delegated administration account IAM Identification Heart ARN.
5. Select Subsequent.
   

   Determine 3: S3 Entry Grants occasion

6. After you create an Amazon S3 Entry Grants occasion in a Area in your account, you register an Amazon S3 location in that occasion. For Location scope, select Browse S3 or enter the S3 URI path to the placement that you just wish to register. After you enter a URI, you possibly can select View to browse the placement. On this instance, we offer the scope as s3://amzn-s3-demo-bucket.
7. For IAM function, choose Select from present IAM roles and select the IAM function you beforehand created (iamidcs3accessgrant).
8. Select Subsequent.

This can register a location in your S3 Entry Grants occasion.

Determine 4: S3 Entry Grants occasion location scope

9. You’ll now create a grant.
   1. Should you chosen the default Amazon S3 location, use the Subprefix field to slender the scope of the entry grant. For extra data, see Working with grants in S3 Entry Grants.
   2. Should you’re granting entry solely to an object, choose Grant scope is an object. In our instance, we register the placement as s3://amzn-s3-demo-bucket after which for the subprefix, we specify the folder title adopted by an asterisk (awssso-sales/*).
10. Underneath Permissions and entry, choose the Permission degree, both Learn, Write, or each. On this instance, we choose each as a result of we are going to first unload from Amazon S3 to Amazon Redshift after which copy from the identical bucket to Amazon Redshift.
11. For Grantee sort, select Listing identification from IAM Identification Heart.
12. For Listing identification sort, you possibly can select both Person or Group. On this instance, we select Group.
13. For IAM Identification Heart group ID, enter the group ID from IAM Identification Heart the place person and group data belongs.

To get this worth, open the IAM Identification Heart console and select Teams within the navigation pane, then select one of many teams you wish to present entry and replica the worth below Group ID. Within the following instance, we acquire the group ID data from the delegated administration account.

Determine 5: IAM Identification Heart group data

15. Select Subsequent.
    

    Determine 6: S3 Entry Grants occasion permissions and entry

16. Select End.
    

    Determine 7: S3 Entry Grants occasion evaluation data web page

You may view the main points of the entry grant on the Amazon S3 console, as proven within the following screenshot. For extra data, see View a grant.

Determine 8: S3 Entry Grants grants

Equally, you may get the main points of a location that’s registered in your S3 Entry Grants occasion. For extra data, see View the main points of a registered location.

Determine 9: S3 Entry Grants areas

Enable cross-account entry of sources and create preliminary tables

Now we wish to share sources to make our cross-account situation work. This step is barely wanted in case your Amazon Redshift and Amazon S3 sources are in several accounts. This must be completed within the account the place Amazon S3 is ready up. Full the next steps:

1. On the AWS RAM console, within the navigation pane, select Useful resource shares.
2. Select Create useful resource share.
3. For Identify, enter a descriptive title for the useful resource share (for instance, s3accessgrant).
4. For Assets – non-compulsory, select S3 Entry Grants. The S3 Entry Grants occasion you created will probably be proven; choose the default S3 Entry Grant occasion ARN.
5. Select Subsequent.
6. Underneath Managed permission for s3:AccessGrants, you possibly can select to affiliate a managed permission created by AWS with the useful resource sort, select an present buyer managed permission, or create your personal buyer managed permission for supported useful resource sorts. On this submit, we select the prevailing permission named AWSRAMPermissionAccessGrantsData.
7. Select Subsequent.
8. For Grant entry to principals, select Enable sharing solely inside your group and enter the account ID the place the Redshift occasion exists.
9. Select Add.
10. Select Subsequent.
11. Select Create useful resource share.

The next screenshot exhibits the brand new useful resource share particulars.

Determine 10: AWS RAM – create useful resource share wizard

Create tables in Amazon Redshift

As an Amazon Redshift admin person, it’s essential first create the tables you’ll use to unload knowledge. Within the following code, we create a brand new store_sales_s3access desk:

CREATE TABLE IF NOT EXISTS  sales_schema.store_sales_s3access (  ID INTEGER ENCODE az64,  Product varchar(20),  Sales_Amount INTEGER ENCODE az64  )  DISTSTYLE AUTO ;

Additionally be sure the next permissions are utilized on the respective IAM Identification Heart group; this group is represented in Amazon Redshift as a Redshift function. For this submit, we grant permissions to the awssso-sales group:

grant utilization on schema sales_schema to function "awsidc:awssso-sales"; grant choose,insert  for tables in schema sales_schema to function "awsidc:awssso-sales";

As an Amazon Redshift admin person, you may have created a Redshift desk and assigned related permissions to the Redshift database function awsidc:awssso-sales. Now when an authenticated person that belongs to the group awssso-sales runs a question in Amazon Redshift to entry Amazon S3 (corresponding to a COPY, UNLOAD, or Amazon Redshift Spectrum operation), Amazon Redshift retrieves non permanent Amazon S3 entry credentials scoped to that IAM Identification Heart person from S3 Entry Grants. Amazon Redshift then makes use of the retrieved non permanent credentials to entry the approved Amazon S3 areas for that question.

Unload and cargo knowledge in Amazon Redshift

On this step, we log in to the Amazon Redshift Question Editor utilizing IAM Identification Heart authentication and run an UNLOAD command to unload knowledge from the desk created earlier into the S3 bucket. After that, we run the COPY command to repeat data from Amazon S3 into the identical desk in the identical listing we unloaded the info from.

Full the next steps to entry the Amazon Redshift Question Editor with an IAM Identification Heart person:

1. On the Amazon Redshift console, open the Amazon Redshift Question Editor.
2. Select (right-click) your Redshift occasion and select Create connection.
3. Select IAM Identification Heart as your authentication technique.
4. A pop-up will seem. As a result of your IdP credentials are already cached, it makes use of the identical credentials and connects to the Amazon Redshift Question Editor utilizing IAM Identification Heart authentication.

Now you’re able to run the SQL queries in Amazon Redshift.

Unload knowledge

As a federated person, you’ll first run an unload command from the desk store_sales within the bucket s3://amzn-s3-demo-bucket/awssso-sales/.

On this submit, we run an UNLOAD command as a federated IAM Identification Heart person (Ethan), the place we will probably be unloading the info from a Redshift desk. Change the S3 bucket title with the one you created.

UNLOAD ('SELECT * FROM "dev"."sales_schema"."store_sales"') TO 's3://amzn-s3-demo-bucket/awssso-sales/';

The previous command doesn’t embrace an IAM function ARN. This simplified syntax not solely makes your code extra readable, but additionally reduces the potential for configuration errors. The underlying permissions are dealt with robotically by S3 Entry Grants and trusted identification propagation, sustaining sturdy safety whereas simplifying permissions administration.

Load knowledge

Now we reveal a typical knowledge workflow utilizing the identical federated IAM Identification Heart person (Ethan), the place we will probably be operating the COPY command accessing the identical Amazon S3 location the place we beforehand unloaded our knowledge. Use to following command to load knowledge right into a separate desk referred to as store_sales_s3access:

copy dev.sales_schema.store_sales_s3access  from 's3://amzn-s3-demo-bucket/awssso-sales/' delimiter '|'

If person Ethan tries to unload "sales_schema"."store_sales" in sales_schema to a special folder within the S3 bucket (awssso-finance), they get a permission denied error. It’s because entry is managed by S3 Entry Grants, and this person doesn’t have a grant to the awssso-finance folder. Use the next command to check the entry denied use case:

UNLOAD ('SELECT * FROM "dev"."sales_schema"."store_sales"') TO 's3://amzn-s3-demo-bucket/awssso-finance/';

Determine 11: QEv2 question outcome error

IAM Identification Heart associated operations are robotically captured and logged in AWS CloudTrail, providing enhanced visibility and complete audit capabilities. To view detailed error data on the CloudTrail console, select Occasion historical past within the navigation pane, then specify s3.amazonaws.com because the occasion supply and open GetDataAccess.

The next screenshot exhibits the snippet from the CloudTrail logs displaying that person entry is denied.

Determine 12: Amazon CloudTrail

Clear up

Full the next steps to scrub up your sources:

1. Delete the IdP purposes that you just created to combine with IAM Identification Heart.
2. Delete the IAM Identification Heart configuration.
3. Delete the Redshift utility and the Amazon Redshift provisioned cluster or serverless occasion that you just created for testing.
4. Delete the IAM function and IAM insurance policies that you just created on this submit.
5. Delete the permission set from IAM Identification Heart that you just created for the Amazon Redshift Question Editor within the administration account.
6. Delete the S3 bucket and related S3 Entry Grants occasion.

Conclusion

On this submit, we explored combine Amazon Redshift with S3 Entry Grants utilizing IAM Identification Heart. We established cross-account entry to allow centralized person authentication by IAM Identification Heart within the delegated administrator account, whereas protecting Amazon Redshift and Amazon S3 remoted by enterprise unit in separate member accounts. We additionally confirmed simplified variations of operating COPY and UNLOAD instructions as a federated IAM Identification Heart person with out utilizing an IAM function ARN. This setup creates a strong and safe analytics setting that streamlines knowledge entry for enterprise customers.

For added steerage and detailed documentation, check with the next key sources:

Concerning the Authors

Maneesh Sharma is a Senior Database Engineer at AWS with greater than a decade of expertise designing and implementing large-scale knowledge warehouse and analytics options. He collaborates with varied Amazon Redshift Companions and clients to drive higher integration.

Laura is an Identification Options Architect at AWS, the place she thrives on serving to clients overcome safety and identification challenges. In her free time, she enjoys wreck diving and touring around the globe.

Praveen Kumar Ramakrishnan is a Senior Software program Engineer at AWS. He has practically 20 years of expertise spanning varied domains together with filesystems, storage virtualization and community safety. At AWS, he focuses on enhancing the Redshift knowledge safety.

Yanzhu Ji is a Product Supervisor within the Amazon Redshift crew. She has expertise in product imaginative and prescient and technique in industry-leading knowledge merchandise and platforms. She has excellent talent in constructing substantial software program merchandise utilizing net improvement, system design, database, and distributed programming methods. In her private life, Yanzhu likes portray, pictures, and enjoying tennis.