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 wish to handle customers and teams centrally, usually 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 primarily based on the precise workforce person and their group memberships within the company listing.

With multi-Area assist for AWS IAM Id Middle, 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 operating providers akin to Amazon Redshift or Amazon Athena in these Areas can combine with IAM Id Middle domestically, to resolve the identical centrally managed customers and teams.

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

In Half 1 of this sequence, 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 Id Middle 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 submit, we lengthen that resolution throughout AWS Areas. We introduce a fictional firm, AnyCompany World, for instance how organizations with international operations can use AWS IAM Id Middle Multi-Area to arrange constant, identity-based entry to Amazon Redshift and Amazon S3 Tables throughout Areas.

Particularly, we exhibit:

• How IAM Id Middle Multi-Area replicates id 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 primarily based on group membership.
• How S3 Entry Grants controls UNLOAD/COPY operations to Amazon S3 primarily based on the identical id.

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

Fictional situation: AnyCompany World

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

• Alice — IT administrator (manages IAM Id Middle 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 World has two AWS accounts:

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

The identical IAM Id Middle 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 Id Middle 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 Id Middle, 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 Id Middle.

This resolution demonstrates two complementary knowledge entry patterns, each managed by the tip person id:

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 Id Middle 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 Id Middle group membership and grants entry to the cataloged knowledge.
• For Sample B (UNLOAD/COPY): Amazon Redshift requests non permanent credentials from S3 Entry Grants in us-west-2. S3 Entry Grants evaluates the request, matches Ethan’s id and group membership, and vends scoped non permanent credentials for the licensed S3 location.
• Ethan runs SELECT to question knowledge via Lake Formation, and UNLOAD to jot down 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 Id Middle Multi-Area, configuring Amazon S3 Tables with Lake Formation within the secondary Area, testing each entry patterns, and verifying the consequence with AWS CloudTrail. Begin with the conditions, then full every step so as.

Stipulations

It’s best to have the next conditions already arrange:

• AWS Organizations enabled with a minimum of two AWS accounts – Centralized Account(Area 1) and Member Account(Region2)
• IAM Id Middle enabled within the administration account (Account A, us-east-1) with a delegated administration account
• Company IdP built-in with IAM Id Middle (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 resolution from Half 1 replicated in us-west-2 (Account B), together with:
  • Amazon Redshift cluster (in us-west-2) with IAM Id Middle integration enabled (utilizing the replicated Id Middle endpoint in us-west-2).
  • S3 Entry Grants occasion configured with IAM Id Middle 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 utility below Trusted id propagation
  • Cross-account useful resource sharing by way of AWS RAM (if Amazon Redshift and S3 Entry Grants are in numerous accounts)
  • Lake Formation enabled on the Amazon Redshift managed utility below Trusted id propagation
  • Lake Formation and Glue permissions added to the IAM function used within the Amazon Redshift managed utility (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 Knowledge Lake Admin in Lake Formation

Notice: Creating and utilizing AWS assets on this tutorial incurs fees, 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 submit to keep away from ongoing fees.

Step 1: Arrange IAM Id Middle Multi-Area

Alice performs this step within the administration account (Account A, us-east-1). IAM Id Middle makes use of encryption at relaxation for id knowledge. To allow multi-Area, you should 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 duplicate keys.
4. Choose US West (Oregon) us-west-2.
5. Select Replicate key.

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

Determine 2: Duplicate key configured for the extra Area.

Add us-west-2 to IAM Id Middle

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

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

For detailed directions, see Add the Area in IAM Id Middle.

Determine 3: IAM Id Middle settings displaying the multi-Area duplicate key added for us-west-2.

Replace your IdP configuration for the extra Area

You’ve efficiently replicated your Id Middle occasion to the Oregon (us-west-2) Area. Your workforce identities are actually accessible in that extra Area and might use the brand new AWS entry portal endpoint.

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

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

Determine 4: IAM Id Middle settings displaying the View ACS URLs possibility.

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

Determine 5: IAM Id Middle settings displaying the ACS URLs for each Areas.

Use the next directions so as to add the ACS URL for the extra Area in your Id Middle utility in Okta:

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

Determine 6: Okta utility for IAM Id Middle 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 Id Middle console.
2. Within the navigation pane, below 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 title (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 Id Middle group (for instance, awssso-sales) for Account B (us-west-2).

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

On this step, the info 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 Knowledge 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 Id Middle group (awssso-sales) in order that solely licensed customers can question knowledge via Trusted Id Propagation.

Step 2.1: Take away default Lake Formation permissions

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

The order issues. If you happen to 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. If you happen to register the useful resource first, that you must 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 → Knowledge Catalog settings.
3. Uncheck each choices:
   • Use solely IAM entry management for brand spanking new databases
   • Use solely IAM entry management for brand spanking new tables in new databases
4. Select Save.

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

Non-obligatory: Confirm Lake Formation default permissions via the AWS CLI

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

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

Add AWSServiceRoleForRedshift as a read-only admin

If you happen to plan to question S3 Tables from Amazon Redshift Question Editor V2, you should 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 Knowledge lake directors, select Add. Select Learn solely administrator.
• From the menu, select AWSServiceRoleForRedshift.
• Select Verify.

Essential: 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 Knowledge 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 non permanent, 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 Id Propagation requires sts:SetContext within the belief coverage, which isn’t accessible on the service-linked function. With no customized function with this permission, Lake Formation can’t propagate the person’s IAM Id Middle id 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": "Enable",
                "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 info entry function. This step lets Lake Formation handle entry to S3 Tables via the Knowledge Catalog and creates the s3tablescatalog federated catalog robotically.

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 possibility.

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

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

Different: 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 

Essential: 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.

Non-obligatory: Confirm the registration

aws lakeformation list-resources --region 

Verify the S3 Tables entry reveals WithFederation: true and the right 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 subsequent web page, enter the bucket title as .
4. Hold the opposite choices as default and select Create desk bucket.
5. After you create it, the AWS Administration Console redirects you to the checklist 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 Knowledge Catalog). Enter the namespace (database) title as and select Create namespace.

You can too carry out these steps utilizing the AWS Command Line Interface (AWS CLI). Discuss with Making a desk bucket utilizing the AWS CLI for equal instructions.

Step 2.5: Grant admin function entry

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

From the console

• Open the Lake Formation console in your Area.
• Select Knowledge permissions → Grant.
• Underneath Principals, choose IAM customers and roles and select your Admin function.
• Underneath LF-Tags or catalog assets, choose Named Knowledge Catalog assets.
• 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 should be a Knowledge 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 Knowledge 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 Id Middle group

Give your IAM Id Middle group entry to question tables. This step permits Trusted Id Propagation (TIP) for this group. When customers within the group entry knowledge via TIP-integrated providers like Amazon Redshift, Lake Formation evaluates their IAM Id Middle 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 Knowledge permissions → Grant.
3. Underneath Principals, choose IAM Id Middle and select your IAM Id Middle group (for instance, awssso-sales).
4. Underneath LF-Tags or catalog assets, choose Named Knowledge Catalog assets.
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 Knowledge permissions → Grant.
2. Underneath Principals, choose IAM Id Middle and select your IAM Id Middle group (for instance, awssso-sales).
3. Underneath LF-Tags or catalog assets, choose Named Knowledge Catalog assets.
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 can too configure column-level or row-level permissions for fine-grained entry management. When granting on a particular desk, extra choices for Column permissions and Knowledge filters change into accessible.

Step 2.8: Non-obligatory: Confirm the Lake Formation permissions

Verify database-level permissions

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

Verify table-level permissions

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

It’s best to see:

• Your Admin function with ALL permissions on the database degree.
• Your IAM Id Middle group with DESCRIBE permissions on the database degree.
• Your IAM Id Middle 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 assets to IAM Id Middle 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, 'Cellphone', 800),
  (3, 'Pill', 450),
  (4, 'Monitor', 350),
  (5, 'Keyboard', 120);

Grant permissions to the IAM Id Middle 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 Id Middle console and replica the AWS entry portal URL (for instance, https://d-1234560789.awsapps.com/begin) from the dashboard.

• Sign 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 Id Middle.
• As a result of your IdP credentials are already cached, the browser reuses them robotically. You’re now linked to Amazon Redshift.

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

Question the shopper profile knowledge via s3tablescatalog. Lake Formation enforces entry primarily based on Ethan’s IAM Id Middle 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 jot down 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 primarily based on Ethan’s IAM Id Middle id and group membership, propagated throughout Areas utilizing IAM Id Middle Multi-Area assist.

Confirm the info 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 information 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 reveals which you could be a part of S3 Tables knowledge with Amazon Redshift utilizing the identical IAM Id Middle id.

Confirm entry management

To substantiate that S3 Entry Grants is implementing 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 primarily based on the person’s id and group membership.

Step 4: Confirm with AWS CloudTrail

You’ll be able to 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 varieties present Ethan’s IAM Id Middle person id, confirming trusted id propagation works end-to-end for each entry patterns.

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

Key issues

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

• IAM Id Middle Multi-Area requires a customer-managed multi-Area AWS KMS key replicated to every extra Area earlier than you may add the Area to Id Middle.
• S3 Entry Grants cases are regional. You want a separate occasion in every Area the place your customers entry knowledge. A bucket should be in the identical Area because the Entry Grants occasion that manages it.
• IAM Id Middle Multi-Area offers the identical person and group identities throughout Areas, so you need to use the identical group IDs in grants throughout Areas.
• It’s essential to register Lake Formation knowledge areas 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 Id Middle 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 Id Middle id.
• The Amazon Redshift managed utility IAM function should embody 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 Id Middle utility 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 assets.
• AWSServiceRoleForRedshift should 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 assets in Lake Formation, use the complete path format: :s3tablescatalog/. Utilizing the account ID alone returns empty outcomes.

Clear up

To keep away from ongoing fees, clear up the assets created on this submit:

• 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 Id Middle integration.
• Delete the Amazon Redshift cluster in us-west-2 in the event you created one for testing.
• Take away us-west-2 from IAM Id Middle Multi-Area (if not wanted).
• Schedule deletion of the AWS KMS duplicate key in us-west-2 (minimal 7-day ready interval).

Conclusion

On this submit, we prolonged the Amazon Redshift and S3 Entry Grants integration to a multi-Area setup utilizing IAM Id Middle 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 Id Middle id for entry management. We additionally confirmed how one can arrange a customer-managed multi-Area AWS KMS key, allow IAM Id Middle in a further Area, configure Amazon S3 Tables with Lake Formation for identity-based entry management utilizing Trusted Id Propagation, and replicate the entire S3 Entry Grants setup in a distinct Area and account.

With this strategy, AnyCompany World’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, discuss with the next assets:

In regards to the authors