Multi-Area identity-based entry to Amazon Redshift and S3 Tables

Organizations with strains of enterprise working throughout a number of AWS Areas more and more run analytics workloads on globally distributed knowledge. These organizations need to handle customers and teams centrally, sometimes within the AWS Organizations administration account and in a single Area, whereas nonetheless letting every line of enterprise entry knowledge from the Area the place its workloads run. Organizations ought to govern entry based mostly on the precise workforce person and their group memberships within the company listing.

With multi-Area help for AWS IAM Identification Heart, organizations can federate workforce identities right into a single group occasion of their main Area. After you replicate this occasion to extra Areas, member accounts working providers corresponding to Amazon Redshift or Amazon Athena in these Areas can combine with IAM Identification Heart domestically, to resolve the identical centrally managed customers and teams.

This answer makes use of Trusted Identification Propagation (TIP), a functionality that passes a person’s Identification Heart identification and group memberships via a sequence of AWS providers. With TIP, when a person authenticates via Identification Heart, that identification context flows to downstream providers like AWS Lake Formation and Amazon S3 Entry Grants. With this method, you get constant, identity-based entry management with out extra AWS Identification and Entry Administration (IAM) function configurations.

In Half 1 of this collection, we confirmed how one can simplify enterprise knowledge entry utilizing the Amazon Redshift integration with Amazon S3 Entry Grants. We demonstrated how one can grant Amazon Easy Storage Service (Amazon S3) permissions to AWS IAM Identification Heart customers and teams utilizing S3 Entry Grants, and examined the mixing utilizing a federated person to unload and cargo knowledge between Amazon Redshift and Amazon S3 inside a single AWS Area.

On this put up, we lengthen that answer throughout AWS Areas. We introduce a fictional firm, AnyCompany International, as an instance how organizations with world operations can use AWS IAM Identification Heart Multi-Area to arrange constant, identity-based entry to Amazon Redshift and Amazon S3 Tables throughout Areas.

Particularly, we exhibit:

• How IAM Identification Heart Multi-Area replicates identification knowledge in order that the identical customers and teams can be found in every enabled Area.
• How AWS Lake Formation grants fine-grained table-level and column-level entry to S3 Tables based mostly on group membership.
• How S3 Entry Grants controls UNLOAD/COPY operations to Amazon S3 based mostly on the identical identification.

We additionally present how one can join along with your most popular SQL consumer.

Fictional situation: AnyCompany International

AnyCompany International is a retail analytics firm with a centralized IT staff and distributed analytics groups. They use the next personas:

• Alice — IT administrator (manages IAM Identification Heart and AWS accounts).
• Bob — platform engineer (units up knowledge infrastructure in us-west-2).
• Ethan — knowledge analyst (member of the awssso-sales group, queries knowledge).

AnyCompany International has two AWS accounts:

• Account A (us-east-1) — administration account with IAM Identification Heart.
• Account B (us-west-2) — analytics account with Amazon Redshift, Amazon S3, and the AWS Glue Information Catalog.

The identical IAM Identification Heart person (Ethan) authenticates as soon as and accesses knowledge in Account B (us-west-2) utilizing the identical credentials and group memberships — you don’t want extra person provisioning as a result of IAM Identification Heart replicates identities to the secondary Area.

Answer overview

The next diagram illustrates the multi-account, multi-Area structure. Account A (us-east-1) hosts IAM Identification Heart, which replicates identities to us-west-2 the place Account B runs the analytics workloads.

Determine 1: Multi-account, multi-Area structure with S3 Entry Grants, AWS Lake Formation, and IAM Identification Heart.

This answer demonstrates two complementary knowledge entry patterns, each managed by the top person identification:

The answer workflow consists of the next steps:

• Ethan connects from Amazon Redshift Question Editor v2 in us-west-2 and authenticates by way of the IAM Identification Heart endpoint (replicated to us-west-2) utilizing his company IdP credentials.
• For Sample A (SELECT): Amazon Redshift queries the Amazon S3 Tables catalog (s3tablescatalog). Lake Formation evaluates Ethan’s IAM Identification Heart group membership and grants entry to the cataloged knowledge.
• For Sample B (UNLOAD/COPY): Amazon Redshift requests momentary credentials from S3 Entry Grants in us-west-2. S3 Entry Grants evaluates the request, matches Ethan’s identification and group membership, and vends scoped momentary credentials for the licensed S3 location.
• Ethan runs SELECT to question knowledge via Lake Formation, and UNLOAD to put in writing knowledge to Amazon S3 via S3 Entry Grants. You don’t want an IAM function ARN within the instructions.

Walkthrough

The next sections stroll you thru enabling IAM Identification Heart Multi-Area, configuring Amazon S3 Tables with Lake Formation within the secondary Area, testing each entry patterns, and verifying the end result with AWS CloudTrail. Begin with the stipulations, then full every step so as.

Conditions

You need to have the next stipulations already arrange:

• AWS Organizations enabled with no less than two AWS accounts – Centralized Account(Area 1) and Member Account(Region2)
• IAM Identification Heart enabled within the administration account (Account A, us-east-1) with a delegated administration account
• Company IdP built-in with IAM Identification Heart (customers and teams synced, for instance, awssso-sales and awssso-finance teams).
• Useful resource sharing enabled in your group with AWS Useful resource Entry Supervisor (AWS RAM)
• Full answer from Half 1 replicated in us-west-2 (Account B), together with:
  • Amazon Redshift cluster (in us-west-2) with IAM Identification Heart integration enabled (utilizing the replicated Identification Heart endpoint in us-west-2).
  • S3 Entry Grants occasion configured with IAM Identification Heart affiliation
  • Amazon S3 bucket (for instance, amzn-s3-demo-bucket-west) with folders for every group (for instance, awssso-sales/, awssso-finance/).
  • IAM function for S3 Entry Grants (for instance, iamidcs3accessgrant) with belief coverage and permissions coverage.
  • S3 Entry Grants location registered and grant created for the awssso-sales group.
  • S3 Entry Grants enabled on the Amazon Redshift managed software underneath Trusted identification propagation
  • Cross-account useful resource sharing by way of AWS RAM (if Amazon Redshift and S3 Entry Grants are in several accounts)
  • Lake Formation enabled on the Amazon Redshift managed software underneath Trusted identification propagation
  • Lake Formation and Glue permissions added to the IAM function used within the Amazon Redshift managed software (for instance, IAMIDCRedshiftRole). For the required permissions, see Querying knowledge via AWS Lake Formation.
• An AWS account with an IAM function that has administrative entry (e.g., Admin function) configured as a Information Lake Admin in Lake Formation

Notice: Creating and utilizing AWS sources on this tutorial incurs prices, together with AWS Key Administration Service (AWS KMS) keys, S3 desk buckets, Amazon Redshift clusters, and Amazon S3 storage. See the cleanup part on the finish of this put up to keep away from ongoing prices.

Step 1: Arrange IAM Identification Heart Multi-Area

Alice performs this step within the administration account (Account A, us-east-1). IAM Identification Heart makes use of encryption at relaxation for identification knowledge. To allow multi-Area, you need to first create a multi-Area customer-managed AWS Key Administration Service (AWS KMS) key and replicate it to the extra Area.

Create a multi-Area AWS KMS key

1. On the AWS KMS console in us-east-1, select Create key.
2. For Key kind, choose Symmetric.
3. For Key utilization, choose Encrypt and decrypt.
4. Underneath Superior choices, choose Multi-Area key.
5. Present an alias (for instance, idc-multi-region-key).
6. Apply the AWS KMS key coverage as documented in Baseline KMS key coverage.

Replicate the important thing to us-west-2

1. On the AWS KMS console in us-east-1, choose the important thing you created.
2. Select the Regionality tab.
3. Select Create new reproduction keys.
4. Choose US West (Oregon) us-west-2.
5. Select Replicate key.

For detailed directions, see Creating multi-Area reproduction keys.

Determine 2: Duplicate key configured for the extra Area.

Add us-west-2 to IAM Identification Heart

1. On the IAM Identification Heart console in us-east-1, within the navigation pane, select Settings.
2. Select Add Area.
3. From the Area listing, choose US West (Oregon) us-west-2. The listing exhibits Areas the place you replicated the customer-managed AWS KMS key.
4. Select Add Area.

A blue banner signifies that Identification Heart is replicating your workforce identities, configuration, and metadata to the brand new Area. After the preliminary replication, the Replication Standing column adjustments to Replicated. Your Identification Heart endpoints in us-west-2 at the moment are energetic.

For detailed directions, see Add the Area in IAM Identification Heart.

Determine 3: IAM Identification Heart settings exhibiting the multi-Area reproduction key added for us-west-2.

Replace your IdP configuration for the extra Area

You’ve efficiently replicated your Identification Heart occasion to the Oregon (us-west-2) Area. Your workforce identities at the moment are accessible in that extra Area and may use the brand new AWS entry portal endpoint.

To ensure AWS managed software (service provider-initiated) authentication redirect person to respective software, add the ACS URL for the extra Area in order that the app comprises each Regional ACS URLs.

Within the following part highlighted in crimson, you may view all ACS URL info:

Determine 4: IAM Identification Heart settings exhibiting the View ACS URLs choice.

Copy the respective ACS URL as proven within the following determine:

Determine 5: IAM Identification Heart settings exhibiting the ACS URLs for each Areas.

Use the next directions so as to add the ACS URL for the extra Area in your Identification Heart software in Okta:

1. Log in to the Okta portal as an Admin.
2. Broaden the Purposes drop-down within the left pane, then select Purposes
3. Select your Identification Heart Software
4. Choose the Signal-on tab and select Edit within the Settings home windows.
5. Within the AWS SSO ACS URL1 field underneath Superior Signal-on Settings – add the extra ACS URL
6. Select Save.

Determine 6: Okta software for IAM Identification Heart Signal-on tab so as to add ACS URLs.

Create a permission set for the secondary Area

Create a permission set within the administration account to grant federated customers console entry to Amazon Redshift Question Editor V2 within the secondary Area (us-west-2). For extra details about permission units, see Permission units.

1. Within the administration account, open the IAM Identification Heart console.
2. Within the navigation pane, underneath Multi-Account permissions, select Permission units → Create permission set.
3. Select Customized permission set, then select Subsequent.
4. Underneath AWS managed insurance policies, choose AmazonRedshiftQueryEditorV2ReadSharing.
5. Underneath Inline coverage, add the next coverage:

   {
     "Model": "2012-10-17",
     "Assertion": [
       {
         "Effect": "Allow",
         "Action": [
           "redshift:DescribeQev2IdcApplications",
           "redshift-serverless:ListNamespaces",
           "redshift-serverless:ListWorkgroups",
           "redshift-serverless:GetWorkgroup"
         ],
         "Useful resource": "*"
       }
     ]
   }

6. Select Subsequent. Enter a permission set identify (for instance, Redshift-QEV2-West).
7. Underneath Relay state, set the default to the Question Editor V2 URL for the secondary Area: https://us-west-2.console.aws.amazon.com/sqlworkbench/dwelling.
8. Select Subsequent, then Create.

After creation, assign this permission set to the related IAM Identification Heart group (for instance, awssso-sales) for Account B (us-west-2).

Step 2: Arrange Amazon S3 Tables integration with AWS Glue Information Catalog and Lake Formation in Account B (us-west-2)

On this step, the information lake administrator (Bob) units up Amazon S3 Tables with Lake Formation for fine-grained entry management. He completes the next duties:

1. Create an S3 tables bucket.
2. Allow S3 Tables integration with AWS Glue Information Catalog and Lake Formation.
3. Register the desk bucket with Lake Formation (removes default IAM-based entry).
4. Grant Lake Formation permissions to an IAM Identification Heart group (awssso-sales) in order that solely licensed customers can question knowledge via Trusted Identification Propagation.

Step 2.1: Take away default Lake Formation permissions

Earlier than creating S3 Tables sources, disable the default IAMAllowedPrincipals grants that Lake Formation applies to new databases and tables. By default, Lake Formation grants IAMAllowedPrincipals entry to new sources, which implies that customary IAM insurance policies (relatively than Lake Formation permissions) management entry. For identity-based entry via Trusted Identification Propagation, you want Lake Formation to be the only arbiter of entry.

The order issues. For those who take away these defaults earlier than registering the S3 Tables useful resource, Lake Formation won’t apply IAMAllowedPrincipals to your S3 Tables catalog or its kids. For those who register the useful resource first, you should manually revoke the IAMAllowedPrincipals grants from every useful resource.

From the console

1. Open the Lake Formation console in your goal Area (for instance, us-west-2).
2. Within the left navigation, select Administration → Information Catalog settings.
3. Uncheck each choices:
   • Use solely IAM entry management for brand new databases
   • Use solely IAM entry management for brand new tables in new databases
4. Select Save.

Determine 7: Lake Formation Information Catalog settings with default IAM entry management disabled.

Optionally available: Confirm Lake Formation default permissions via the AWS CLI

aws lakeformation get-data-lake-settings --region 

Affirm each CreateDatabaseDefaultPermissions and CreateTableDefaultPermissions are empty arrays ([]).

Add AWSServiceRoleForRedshift as a read-only admin

For those who plan to question S3 Tables from Amazon Redshift Question Editor V2, you need to add the Amazon Redshift service-linked function as a Learn-Solely Admin in Lake Formation. Full the next steps:

• Within the Lake Formation console, go to Administration → Administrative roles and duties.
• Underneath Information lake directors, select Add. Select Learn solely administrator.
• From the menu, select AWSServiceRoleForRedshift.
• Select Affirm.

Necessary: With out this, Amazon Redshift Question Editor V2 doesn’t show exterior databases from s3tablescatalog. The Amazon Redshift service-linked function wants read-only admin entry to browse the Information Catalog on behalf of customers.

Step 2.2: Create the Lake Formation knowledge entry function for S3 Tables

Create an IAM function that Lake Formation assumes to generate momentary, scoped credentials on behalf of customers requesting entry to S3 Tables knowledge. Lake Formation makes use of this function (as an alternative of its service-linked function) as a result of Trusted Identification Propagation requires sts:SetContext within the belief coverage, which isn’t accessible on the service-linked function. And not using a customized function with this permission, Lake Formation can’t propagate the person’s IAM Identification Heart identification when accessing S3 Tables.

Create the function with the belief coverage

aws iam create-role 
    --role-name LFAccessRole-S3Tables 
    --assume-role-policy-document '{
        "Model": "2012-10-17",
        "Assertion": [{
            "Effect": "Allow",
            "Principal": {
                "Service": "lakeformation.amazonaws.com"
            },
            "Action": [
                "sts:AssumeRole",
                "sts:SetSourceIdentity",
                "sts:SetContext"
            ]
        }]
    }'

Connect the S3 Tables permissions coverage

aws iam put-role-policy 
    --role-name LFAccessRole-S3Tables 
    --policy-name S3TablesDataAccess 
    --policy-document '{
        "Model": "2012-10-17",
        "Assertion": [
            {
                "Sid": "LakeFormationPermissionsForS3ListTableBucket",
                "Effect": "Allow",
                "Action": ["s3tables:ListTableBuckets"],
                "Useful resource": ["*"]
            },
            {
                "Sid": "LakeFormationDataAccessPermissionsForS3TableBucket",
                "Impact": "Permit",
                "Motion": [
                    "s3tables:CreateTableBucket",
                    "s3tables:GetTableBucket",
                    "s3tables:CreateNamespace",
                    "s3tables:GetNamespace",
                    "s3tables:ListNamespaces",
                    "s3tables:DeleteNamespace",
                    "s3tables:DeleteTableBucket",
                    "s3tables:CreateTable",
                    "s3tables:DeleteTable",
                    "s3tables:GetTable",
                    "s3tables:ListTables",
                    "s3tables:RenameTable",
                    "s3tables:UpdateTableMetadataLocation",
                    "s3tables:GetTableMetadataLocation",
                    "s3tables:GetTableData",
                    "s3tables:PutTableData"
                ],
                "Useful resource": ["arn:aws:s3tables:::bucket/*"]
            }
        ]
    }'

Step 2.3: Register S3 Tables with Lake Formation

Register the S3 Tables useful resource with Lake Formation utilizing the information entry function. This step lets Lake Formation handle entry to S3 Tables via the Information Catalog and creates the s3tablescatalog federated catalog routinely.

Open the Lake Formation console and full the next steps:

1. Select Catalogs within the navigation pane and select Allow S3 Desk integration.

Determine 8: Lake Formation Catalogs web page with the Allow S3 Desk integration choice.

2. Choose the IAM function and choose Permit exterior engines to entry knowledge in Amazon S3 places with full desk entry. Select Allow.

Determine 9: Allow S3 Desk integration dialog with the IAM function and external-engine entry configured.

Various: Register via the AWS CLI

aws lakeformation register-resource 
    --resource-arn "arn:aws:s3tables:::bucket/*" 
    --role-arn "arn:aws:iam:::function/LFAccessRole-S3Tables" 
    --with-federation 
    --region 

Necessary: Confirm that the --role-arn matches the precise ARN of the function created in Step 2.2 (together with the trail). A mismatch (e.g., function/service-role/LFAccessRole-S3Tables vs function/LFAccessRole-S3Tables) will trigger credential merchandising failures later.

Optionally available: Confirm the registration

aws lakeformation list-resources --region 

Affirm the S3 Tables entry exhibits WithFederation: true and the proper function ARN.

Step 2.4: Create the S3 desk bucket and namespace

Create an S3 desk bucket and a namespace. Full the next steps on the Amazon S3 console:

1. Within the navigation pane, select Desk buckets.
2. Select Create desk bucket.
3. On the following web page, enter the bucket identify as .
4. Maintain the opposite choices as default and select Create desk bucket.
5. After you create it, the AWS Administration Console redirects you to the listing of desk buckets. Select the desk bucket .
6. Select Create desk with Athena.
7. Create a namespace in S3 Tables (equal to a database in AWS Glue Information Catalog). Enter the namespace (database) identify as and select Create namespace.

You may as well carry out these steps utilizing the AWS Command Line Interface (AWS CLI). Seek advice from Making a desk bucket utilizing the AWS CLI for equal instructions.

Step 2.5: Grant admin function entry

After you take away default permissions, you should give your Admin function specific Lake Formation permissions to create tables. As a result of your Admin function is a Information Lake Admin, you may already see s3tablescatalog within the Amazon Athena console, however creating tables requires an specific grant.

From the console

• Open the Lake Formation console in your Area.
• Select Information permissions → Grant.
• Underneath Principals, choose IAM customers and roles and select your Admin function.
• Underneath LF-Tags or catalog sources, choose Named Information Catalog sources.
• For Catalogs, select :s3tablescatalog/.
• For Databases, choose your database (for instance, customer_ns_db).
• Choose Tremendous for Database permissions and Grantable permissions.
• Select Grant.

After this grant, you may create and insert knowledge into tables from the Athena console.

Notice: Your Admin function have to be a Information Lake Admin (configured in Step 2.1) to browse s3tablescatalog in Athena. You want the specific database grant for write operations (CREATE TABLE, INSERT).

Step 2.6: Create a desk from the Athena console

1. Open the Amazon Athena console in your Area.
2. Within the Information supply menu, choose AwsDataCatalog.
3. For Catalog, select s3tablescatalog/.
4. For Database, select your namespace.
5. Run a CREATE TABLE assertion. For instance:

CREATE TABLE . (
    customer_id int,
    first_name string,
    last_name string,
    area string,
    membership_tier string
)
TBLPROPERTIES ('table_type' = 'ICEBERG');

INSERT INTO . VALUES
  (1, 'Joyce', 'Deaton', 'West', 'Gold'),
  (2, 'Daniel', 'Dow', 'East', 'Silver'),
  (3, 'Marie', 'Lange', 'West', 'Gold'),
  (4, 'Wesley', 'Harris', 'East', 'Bronze'),
  (5, 'Jerry', 'Tracy', 'West', 'Silver');

Step 2.7: Grant permissions to the IAM Identification Heart group

Give your IAM Identification Heart group entry to question tables. This step permits Trusted Identification Propagation (TIP) for this group. When customers within the group entry knowledge via TIP-integrated providers like Amazon Redshift, Lake Formation evaluates their IAM Identification Heart group membership and enforces table-level and column-level permissions accordingly.

From the console

Grant DESCRIBE on the database:

1. Open the Lake Formation console in your Area.
2. Select Information permissions → Grant.
3. Underneath Principals, choose IAM Identification Heart and select your IAM Identification Heart group (for instance, awssso-sales).
4. Underneath LF-Tags or catalog sources, choose Named Information Catalog sources.
5. For Catalogs, select :s3tablescatalog/.
6. For Databases, choose your database (for instance, customer_ns_db).
7. For Database permissions, choose Describe.
8. Select Grant.

Grant SELECT and DESCRIBE on tables:

1. Select Information permissions → Grant.
2. Underneath Principals, choose IAM Identification Heart and select your IAM Identification Heart group (for instance, awssso-sales).
3. Underneath LF-Tags or catalog sources, choose Named Information Catalog sources.
4. For Catalogs, select :s3tablescatalog/.
5. For Databases, choose your database (for instance, customer_ns_db).
6. For Tables, choose All tables (or a particular desk).
7. For Desk permissions, choose Choose and Describe.
8. Select Grant.

Tip: You may as well configure column-level or row-level permissions for fine-grained entry management. When granting on a particular desk, extra choices for Column permissions and Information filters grow to be accessible.

Step 2.8: Optionally available: Confirm the Lake Formation permissions

Affirm database-level permissions

aws lakeformation list-permissions 
    --resource '{"Database": {"CatalogId": ":s3tablescatalog/", "Identify": ""}}' 
    --region 

Affirm table-level permissions

aws lakeformation list-permissions 
    --resource '{"Desk": {"CatalogId": ":s3tablescatalog/", "DatabaseName": "", "TableWildcard": {}}}' 
    --region 

You need to see:

• Your Admin function with ALL permissions on the database degree.
• Your IAM Identification Heart group with DESCRIBE permissions on the database degree.
• Your IAM Identification Heart group with DESCRIBE on ALL_TABLES and SELECT on ALL_TABLES (with ColumnWildcard) on the desk degree.
• No IAM_ALLOWED_PRINCIPALS entries.

Step 2.9: Create Amazon Redshift tables and grant permissions

Connect with the Amazon Redshift cluster in us-west-2 as an admin person and create Redshift native tables. Grant permissions on these native sources to IAM Identification Heart teams.

Create a schema and desk

CREATE SCHEMA IF NOT EXISTS sales_schema;

CREATE TABLE IF NOT EXISTS
sales_schema.store_sales (
  customer_id INTEGER ENCODE az64,
  product VARCHAR(50),
  sales_amount INTEGER ENCODE az64
)
DISTSTYLE AUTO;

-- Insert pattern knowledge
INSERT INTO sales_schema.store_sales VALUES
  (1, 'Laptop computer', 1200),
  (2, 'Telephone', 800),
  (3, 'Pill', 450),
  (4, 'Monitor', 350),
  (5, 'Keyboard', 120);

Grant permissions to the IAM Identification Heart group

GRANT USAGE ON SCHEMA sales_schema TO ROLE "awsidc:awssso-sales";
GRANT SELECT, INSERT FOR TABLES IN SCHEMA sales_schema TO ROLE "awsidc:awssso-sales";

-- Grant entry to the S3 Tables exterior database in Redshift (for Lake Formation queries on buyer profiles)
GRANT USAGE ON DATABASE "customers3tables@s3tablescatalog" TO ROLE "awsidc:awssso-sales";

Step 3: Take a look at the answer

Within the administration account, navigate to the IAM Identification Heart console and replica the AWS entry portal URL (for instance, https://d-1234560789.awsapps.com/begin) from the dashboard.

• Log off from the administration account and paste the AWS entry portal URL in a brand new browser window.
• A pop-up redirects you to your IdP login web page. Enter Ethan’s IdP credentials.
• After profitable authentication, you’re logged into the AWS console as a federated person. Choose the QEV2 permission set for the secondary Area (us-west-2).
• In Question Editor V2, open the context (right-click) menu in your Amazon Redshift occasion, select Create connection, and for Authentication, choose IAM Identification Heart.
• As a result of your IdP credentials are already cached, the browser reuses them routinely. You’re now related to Amazon Redshift.

Sample A: Question the S3 desk catalog utilizing Lake Formation permissions

Question the client profile knowledge via s3tablescatalog. Lake Formation enforces entry based mostly on Ethan’s IAM Identification Heart group membership:

SELECT *
FROM "customers3tables@s3tablescatalog"."customer_ns_db"."customer_profiles";

Determine 10: Question outcomes from s3tablescatalog returned via Lake Formation in Amazon Redshift Question Editor V2.

This question reads buyer profile knowledge from Amazon S3 via Amazon Redshift Spectrum, with Lake Formation controlling who can entry which tables and columns.

Sample B: Unload knowledge to Amazon S3 utilizing S3 Entry Grants

Run the UNLOAD command to put in writing knowledge from Amazon Redshift to the S3 bucket:

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

You don’t want an IAM function ARN within the command. S3 Entry Grants handles authorization based mostly on Ethan’s IAM Identification Heart identification and group membership, propagated throughout Areas utilizing IAM Identification Heart Multi-Area help.

Confirm the information in Amazon S3

On the Amazon S3 console, navigate to s3://west-idc-amzn-s3-demo-bucket/awssso-sales/ and confirm that the unloaded knowledge recordsdata are current.

Be part of Lake Formation knowledge with domestically loaded Amazon Redshift knowledge

Mix buyer profile knowledge (queried by way of Lake Formation) with gross sales knowledge (loaded by way of S3 Entry Grants) utilizing the shared customer_id column:

SELECT c.first_name, c.last_name, c.membership_tier,
  s.product, s.sales_amount
FROM "customers3tables@s3tablescatalog"."customer_ns_db"."customer_profiles" c
JOIN  dev.sales_schema.store_sales s ON c.customer_id = s.customer_id
ORDER BY s.sales_amount DESC;

Determine 11: Joined outcomes from S3 Tables and Amazon Redshift native knowledge, ordered by gross sales quantity.

This exhibits that you would be able to be part of S3 Tables knowledge with Amazon Redshift utilizing the identical IAM Identification Heart identification.

Confirm entry management

To verify that S3 Entry Grants is imposing entry, strive accessing a folder Ethan doesn’t have a grant for:

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

This could return an entry denied error, confirming that S3 Entry Grants is controlling entry based mostly on the person’s identification and group membership.

Step 4: Confirm with AWS CloudTrail

You possibly can confirm that Amazon Redshift used each S3 Entry Grants and Lake Formation for authorization by checking AWS CloudTrail:

• On the CloudTrail console, select Occasion historical past.
• Filter by Occasion supply: s3.amazonaws.com. Search for GetDataAccess occasions (S3 Entry Grants).
• Filter by Occasion supply: lakeformation.amazonaws.com. Search for GetDataAccess occasions (Lake Formation).

Each occasion sorts present Ethan’s IAM Identification Heart person identification, confirming trusted identification propagation works end-to-end for each entry patterns.

The next desk lists associated weblog posts and integration guides overlaying extra identity-based entry patterns with Amazon Redshift. Though many of those have been written for single-Area deployments, you may lengthen them to multi-Area environments by first enabling IAM Identification Heart Multi-Area as described in Step 1 of this put up. Use the desk to search out the information that matches your identification supplier and tooling:

Key concerns

When implementing this multi-Area structure, maintain the next operational and configuration concerns in thoughts. These mirror frequent challenges and design choices encountered throughout deployment:

• IAM Identification Heart Multi-Area requires a customer-managed multi-Area AWS KMS key replicated to every extra Area earlier than you may add the Area to Identification Heart.
• S3 Entry Grants cases are regional. You want a separate occasion in every Area the place your customers entry knowledge. A bucket have to be in the identical Area because the Entry Grants occasion that manages it.
• IAM Identification Heart Multi-Area supplies the identical person and group identities throughout Areas, so you need to use the identical group IDs in grants throughout Areas.
• You have to register Lake Formation knowledge places with a customer-managed function that features sts:SetContext in its belief coverage. For S3 Tables, use aws lakeformation register-resource with the --with-federation flag and the useful resource ARN format arn:aws:s3tables:::bucket/*. Utilizing the service-linked function causes the error: Can't vend credentials from service-linked function to Identification Heart principal.
• SELECT and UNLOAD use totally different permission fashions. Lake Formation controls query-time entry to cataloged knowledge (SELECT via Spectrum). S3 Entry Grants controls direct Amazon S3 entry (COPY/UNLOAD). Each use the identical IAM Identification Heart identification.
• The Amazon Redshift managed software IAM function should embrace sts:SetContext in its belief coverage and have each Lake Formation/Glue and S3 Entry Grants permissions.
• Cross-account setup requires AWS RAM useful resource sharing for S3 Entry Grants and correct IAM Identification Heart software configuration within the analytics account.
• Scoped vs object-level permissions in Amazon Redshift. When granting permissions with GRANT ... FOR TABLES IN SCHEMA, use REVOKE ... FOR TABLES IN SCHEMA to take away them. The REVOKE ... ON ALL TABLES IN SCHEMA syntax solely removes object-level permissions, not scoped permissions.
• The Lake Formation knowledge entry function for S3 Tables requires sts:SetContext in its belief coverage (for TIP) and s3tables:* permissions on the desk bucket sources.
• AWSServiceRoleForRedshift have to be a Learn-Solely Admin in Lake Formation for Amazon Redshift Question Editor V2 to show exterior databases from s3tablescatalog.
• Federated catalog CatalogId format. When utilizing CLI instructions for S3 Tables sources in Lake Formation, use the total path format: :s3tablescatalog/. Utilizing the account ID alone returns empty outcomes.

Clear up

To keep away from ongoing prices, clear up the sources created on this put up:

• Delete the S3 desk bucket (delete tables → namespaces → bucket utilizing aws s3tables CLI instructions).
• Deregister the S3 Tables useful resource from Lake Formation (aws lakeformation deregister-resource --resource-arn "arn:aws:s3tables:::bucket/*").
• Delete s3tablescatalog from Glue (aws glue delete-catalog --catalog-id "s3tablescatalog").
• Delete the LFAccessRole-S3Tables IAM function and related insurance policies.
• Delete the S3 Entry Grants occasion and grants in us-west-2.
• Delete the S3 bucket used for UNLOAD/COPY in us-west-2.
• Delete the iamidcs3accessgrant IAM function and related insurance policies.
• Deregister the S3 knowledge location from Lake Formation.
• Delete the Lake Formation IAM Identification Heart integration.
• Delete the Amazon Redshift cluster in us-west-2 in case you created one for testing.
• Take away us-west-2 from IAM Identification Heart Multi-Area (if now not wanted).
• Schedule deletion of the AWS KMS reproduction key in us-west-2 (minimal 7-day ready interval).

Conclusion

On this put up, we prolonged the Amazon Redshift and S3 Entry Grants integration to a multi-Area setup utilizing IAM Identification Heart Multi-Area replication. We demonstrated two complementary knowledge entry patterns: SELECT via Lake Formation for fine-grained entry management on S3 Tables knowledge, and UNLOAD/COPY via S3 Entry Grants for direct Amazon S3 entry. Each patterns use the identical IAM Identification Heart identification for entry management. We additionally confirmed how one can arrange a customer-managed multi-Area AWS KMS key, allow IAM Identification Heart in a further Area, configure Amazon S3 Tables with Lake Formation for identity-based entry management utilizing Trusted Identification Propagation, and replicate the entire S3 Entry Grants setup in a distinct Area and account.

With this method, AnyCompany International’s analysts authenticate as soon as and entry knowledge in any enabled Area whereas Lake Formation and S3 Entry Grants implement per-user, per-group entry insurance policies.

For added steerage, consult with the next sources:

Concerning the authors