AWS Big Data Blog

Scale fine-grained permissions across warehouses with Amazon Redshift and AWS IAM Identity Center

Amazon Redshift is a fully managed, petabyte-scale cloud-based data warehouse that you can use to scale analytics workloads effortlessly. As organizations expand their analytics capabilities across multiple business units, they need streamlined approaches for defining and managing fine-grained permissions for each warehouse. Many organizations use external identity providers (IdPs) like Microsoft Entra ID, Okta, or Ping to manage workforce identities centrally and need streamlined data warehouse integration with consistent access controls. We address these challenges by introducing Amazon Redshift federated permissions with AWS IAM Identity Center integration so that you can define security policies once and automatically enforce them across the warehouses in your account.

Amazon Redshift federated permissions are now supported with IAM Identity Center across multiple AWS Regions, where you can use identities from supported identity provider (IdP) such as Microsoft Entra ID, Okta, Ping Identity, or OneLogin across supported AWS Regions with IAM Identity Center. This enables you to align with business requirements including resiliency and proximity to users. You can now extend IAM Identity Center from your primary AWS Region to additional Regions of your choice based on your data residency requirements. In that region, you can get horizontal multi-warehouse scalability by adding new warehouses using Amazon Redshift federated permissions across multiple warehouses. With Redshift federated permissions, you define data permissions once from any Redshift warehouse in that region and automatically enforce them across all warehouses in the account in that region.

This post provides a comprehensive technical walkthrough for implementing Amazon Redshift federated permissions with AWS IAM Identity Center to help achieve scalable data governance across multiple data warehouses. It demonstrates a practical architecture where an Enterprise Data Warehouse (EDW) serves as the producer data warehouse with centralized policy definitions, helping automatically enforce security policies to consuming Sales and Marketing data warehouses without manual reconfiguration. You will learn how to do the following:

  • Configure IAM Identity Center connections for both data sharing producers and consumers
  • Register Amazon Redshift serverless namespaces with AWS Glue Data Catalog
  • Set up trusted identity propagation (TIP)
  • Create and attach Dynamic data masking policies to help protect personally identifiable information (PII) like customer dates of birth
  • Implement row-level security policies to control data visibility based on user roles
  • Map IdP groups to Amazon Redshift database roles for seamless access management

Prerequisites

Before you begin, verify that you have the following:

  • An AWS account with admin role privileges
  • Assign data lake admin permissions to above admin role. For instructions, see Create a data lake administrator
  • Enable IAM Identity Center integration using the Lake Formation
  • Review the blog post to understand the setup process of AWS IAM Identity Center integration with Amazon Redshift Query Editor v2
  • IAM Identity Center enabled in your AWS account, with users and groups created as listed under Solution overview section of User access (figure 2)
  • As an Amazon Redshift superuser, grant CONNECT, CREATE TABLE, INSERT, SELECT, and sys:secadmin permissions to AWSIDC:awssso-admin database role
  • An IAM role for IAM Identity Center access:
    • Step 1:Create an IAM policy for Amazon Redshift access. To integrate Amazon Redshift with IAM Identity Center, create an IAM policy (for example, aws-idc-policy) in the account where your Amazon Redshift data warehouse exists: 
      {
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "VisualEditor0",
      "Effect": "Allow",
      "Action": [
        "redshift:DescribeQev2IdcApplications",
        "redshift-serverless:ListNamespaces",
        "redshift-serverless:ListWorkgroups",
        "redshift-serverless:GetWorkgroup"
      ],
      "Resource": [
        "arn:aws:redshift-serverless:<AWS Region>:<AWS Account ID>:workgroup/*",
        "arn:aws:redshift-serverless:<AWS Region>:<AWS Account ID>:namespace/*"
      ]
    },
    {
      "Sid": "VisualEditor1",
      "Effect": "Allow",
      "Action": [
        "sso:DescribeApplication",
        "sso:DescribeInstance"
      ],
      "Resource": [
        "arn:aws:sso:::instance/<IAM Identity Center Instance ID>",
        "arn:aws:sso::<AWS Account ID>:application/<IAM Identity Center Instance ID>/*"
      ]
    }
  ]
}
    • Step 2: Create the IAM role. Create an IAM role (Amazon Redshift – Customizable) in the account where your Amazon Redshift data warehouse exists (for example, IAMIDCRedshiftRole).
    • Step 3: Attach IAM policies to the role. Attach the following two IAM policies to the previously mentioned role:
    • Step 4: Update the trust relationships. Update the trust relationships for this role with the following: 
      {
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "redshift.amazonaws.com"
      },
      "Action": [
        "sts:AssumeRole",
        "sts:SetContext"
      ]
    }
  ]
}

      Note: AmazonRedshiftFederatedAuthorization is a managed policy that provides the necessary permissions for running queries with Amazon Redshift federated authorization.

  • Attach above IAMIDCRedshiftRole IAM role to all Redshift serverless endpoints

Solution overview

The following architecture diagram demonstrates federated permissions in a multi-warehouse environment, enabling scalable data governance across Amazon Redshift warehouses by automatically enforcing security policies.

Figure 1 : Sample architecture diagram

Figure 1: Sample architecture diagram

User access

Users can access data warehouses through Amazon Redshift Query Editor v2, third-party SQL editors (such as DBeaver and SQL Workbench), or custom client applications. The access methods help provide consistent security enforcement.

Figure 2: Solution overview flow

Figure 2: Solution overview flow

AWS IAM Identity Center integration

IAM Identity Center provides centralized authentication with single sign-on capabilities and automatically assigns role-based permissions based on organizational roles. This identity federation links corporate identities directly to AWS resources, making sure that authentication occurs at the identity layer before warehouse access.

Multi-warehouse architecture

This architecture uses three distinct data warehouses that serve different business functions while sharing centralized security policies.

Enterprise Data Warehouse (EDW)

The EDW serves as the central repository for enterprise data. In this architecture, customer and product data are stored in the Customer Profile Database (CPD), where administrators define two critical security policies:

  • Dynamic data masking (DDM) – Masks sensitive customer Date of Birth (DOB) fields for both Sales Analyst and Marketing Analyst roles, helping protect personally identifiable information (PII) while allowing analytical work
  • Row-level security (RLS) – Controls product visibility based on user roles. Sales Analysts view only launched products, while Marketing Analysts view both launched and planned products

The EDW registers with the AWS Glue Data Catalog, creating a unified metadata repository that makes data discoverable across the warehouses in the account. This registration establishes the foundation for federated permissions, enabling automatic policy propagation.

Sales data warehouse

When Sales Analysts query customer and product tables, the system automatically enforces policies defined in the EDW through federated permissions. The registered namespace from the EDW automatically mounts as an external database, alleviating the need to recreate or reattach policies. Customer DOB fields appear masked, and only launched products are visible without additional configurations.

Marketing data warehouse

The Marketing Data Warehouse automatically inherits and enforces EDW security policies. Customer DOB fields remain masked to help protect PII, but with RLS policies, Marketing Analysts can view both launched and planned products. This provides the broader visibility needed for marketing planning. This differentiated access control is automatically enforced based on user roles.

Walkthrough

In this walkthrough, you create two Amazon Redshift IAM Identity Center (IDC) connections:

  1. Data sharing producer identity center connection – Assigned to the edw-wg Amazon Redshift serverless workgroup
  2. Data sharing consumer identity center connection – Assigned to the cpd-sales-wg and cpd-marketing-wg Amazon Redshift serverless workgroups

Set up IDC connections for Amazon Redshift federated permissions

In this section, you configure the IAM Identity Center connections that enable federated authentication across your warehouses. You will create separate connections for the producer (policy-defining) warehouse and consumer warehouses.

Configure Amazon Redshift data sharing producer IDC connection

To create the producer IDC connection:

  1. Open the Amazon Redshift Serverless console.
  2. Choose IAM Identity Center connections by expanding the hamburger menu.
  3. Choose Create application.
  4. Verify that you see “Amazon Redshift connected to IAM Identity Center”, and then choose Next.
  5. Configure the connection properties:
    • For IAM Identity Center display name, enter a name.
    • For Managed application name, enter rs-multicluster-producer.
    • For Identity provider namespace, choose AWSIDC.
    • For IAM role for IAM Identity Center access, choose the TIP IAM role that you created.
    • For Query editor v2 application, choose Enable the query editor v2 application.
    • For IAM Identity Center application type, choose Configure Amazon Redshift federated permissions using AWS IAM Identity Center (Recommended).
    • Choose Next.
  6. For Configure client connections that use third-party IdPs, choose No.
  7. Choose Next.
  8. Verify that the configuration details match your inputs and then choose Create Application.
Figure 3: Data sharing producer IDC connection

Figure 3: Data sharing producer IDC connection

Configure data sharing consumer IDC connection

To create the consumer IDC connection:

  1. Open the Amazon Redshift Serverless console.
  2. Choose IAM Identity Center connections by expanding the hamburger menu.
  3. Choose Create application.
  4. Verify that you see “Amazon Redshift connected to IAM Identity Center”, and then choose Next.
  5. Configure the connection properties:
    • For IAM Identity Center display name, enter a name.
    • For Managed application name, enter rs-multicluster-consumer.
    • For Identity provider namespace, choose AWSIDC.
    • For IAM role for IAM Identity Center access, choose the TIP IAM role that you created.
    • For Query editor v2 application, you will see the notification “You already have a query editor v2 application.”
    • For IAM Identity Center application type, deselect Configure Amazon Redshift federated permissions using AWS IAM Identity Center (Recommended).
    • For Trusted identity propagation, choose AWS Lake Formation access grants and Amazon Redshift Connect.
    • Choose Next.
  6. For Configure client connections that use third-party IdPs, choose No.
  7. Choose Next.
  8. Verify that the configuration details match your inputs, and then choose Create Application.
  9. Add your required users or groups to the IDC application for Amazon Redshift data sharing consumers.
Figure 4: Data sharing consumer IDC connection

Figure 4: Data sharing consumer IDC connection

Configure Amazon Redshift data sharing producer IDC connection for Amazon Redshift serverless namespace

To register the edw-ns namespace with federated permissions:

  1. Open the Amazon Redshift Serverless Namespace console.
  2. Choose your Amazon Redshift Serverless namespace.
  3. Choose Actions, and then select Register with AWS Glue Data Catalog.
  4. Choose Register with Amazon Redshift federated permissions.
  5. Choose Amazon Redshift federated permissions using AWS IAM Identity Center.
  6. Choose Register.
Figure 5: Amazon Redshift data warehouse registration with Glue Data Catalog

Figure 5: Amazon Redshift data warehouse registration with Glue Data Catalog

Figure 6: Amazon Redshift data warehouse registration with Glue Data Catalog

Figure 6: Amazon Redshift data warehouse registration with Glue Data Catalog

Note: IAM Identity Center managed application ARN Data sharing producer IDC connection created would be used.

Configure Amazon Redshift data sharing consumer IDC connection for existing serverless namespace

For cpd-sales-wg and cpd-marketing-wg serverless workgroups, gather the following information from your registered IAM Identity Center connection:

  • IAM Identity Center display name
  • Identity provider namespace
  • IAM Identity Center managed application ARN
  • IAM role for IAM Identity Center access

Run the following SQL command as a database administrator to enable the integration:

CREATE IDENTITY PROVIDER "<IAM Identity Center display name>" TYPE AWSIDC
NAMESPACE '<Identity provider namespace>'
APPLICATION_ARN '<IAM Identity Center managed application ARN>'
IAM_ROLE '<IAM role for IAM Identity Center access>';

To modify an existing identity provider, use the ALTER IDENTITY PROVIDER command:

ALTER IDENTITY PROVIDER "<IAM Identity Center display name>"
NAMESPACE '<Identity provider namespace>';
ALTER IDENTITY PROVIDER "<IAM Identity Center display name>"
IAM_ROLE default | '<IAM role for IAM Identity Center access>';

Data preparation and access setup from producer

In this section, you create the customer and product tables, load sample data, create DDM and RLS policies, attach the policies to database roles and grant SELECT permissions to the roles.

Prepare data on EDW

Connect to the EDW data warehouse as an IDC Admin user and run the following SQL commands.

Create the product table:

CREATE TABLE product (
  product_id VARCHAR(16) NOT NULL,
  product_desc VARCHAR(200),
  current_price NUMERIC(7,2),
  wholesale_cost NUMERIC(7,2),
  category_desc VARCHAR(50),
  launch_status VARCHAR(50)
);

Insert sample product data:

INSERT INTO product 
VALUES 
  ('AAAAAAAAAFNPEAAA','At least concerned authors adopt just brown, federal',7.12,4.12,'Jewelry','launched'),
  ('AAAAAAAAOAAGDAAA','Complex services may not find totally changing accountants. Tiny, available ministers could not know always systems. Hot, male speakers discer',8.08,5.49,'Shoes','planned'),
  ('AAAAAAAAMJJMCAAA','Rows could prevent political, old duties. Just international stairs would regret police. Conditions discard always interesting, warm years. Present jobs shall take nearby relatively dreadful',8.18,5.31,'Jewelry','launched'),
  ('AAAAAAAAKLBLBAAA','Suddenly external sentences believe then by the assets. Simultaneously young feet could not probe separately shortly new men. Forms work again individuals. Images',17.96,7.9,'Shoes','launched'),
  ('AAAAAAAAMBKMCAAA','Clubs see finally materials. Significant objectives sell fairly left, civil power',3.18,3.84,'Books','launched'),
  ('AAAAAAAACPCAAAAA','Perhaps past preferences tell rather to a accounts. Very common feet can command never available final years; minutes expect recent, due employers. Altogether english shoes',9.84,0.19,'Electronics','planned'),
  ('AAAAAAAAFOIABAAA','More responsible characters go left factors. Championships shall stand twice new, important shows. Books could receive too able, national pounds. Central',3.55,2.2,'Books','launched'),
  ('AAAAAAAAKGBIAAAA','High, political changes shall not',9.55,5.25,'Electronics','launched');

Create the customer table:

CREATE TABLE customer (
  customer_id VARCHAR(16),
  first_name VARCHAR(20),
  last_name VARCHAR(30),
  date_of_birth VARCHAR(32),
  birth_country VARCHAR(20),
  email_address VARCHAR(50)
);

Insert sample customer data:

INSERT INTO customer
VALUES
  ('AAAAAAAALAMKHGBA','Regina','Coleman','1926-12-17','GAMBIA','Regina.Coleman@JFFRohn.edu'),
  ('AAAAAAAAMCMKHGBA','John','Bell','1980-01-07','PAPUA NEW GUINEA','John.Bell@uAR3ReP6yi9eDyq.edu'),
  ('AAAAAAAANNMKHGBA','Jacqueline','Pierre','1951-12-18','SAMOA','Jacqueline.Pierre@UQcHfFDEVdj.com'),
  ('AAAAAAAANFNKHGBA','Frank','Mackay','1992-03-19','HONG KONG','Frank.Mackay@MzAI.edu'),
  ('AAAAAAAAOGNKHGBA','Anthony','Miller','1948-02-26','ALGERIA','Anthony.Miller@pF.edu'),
  ('AAAAAAAACPOKHGBA','Bradley','Sawyer','1956-12-25','ZAMBIA','Bradley.Sawyer@kAXu5U1MrRRkAqP.edu'),
  ('AAAAAAAAOIPKHGBA','Robert','Carter','1951-01-01','UNITED STATES','Robert.Carter@Z.org'),
  ('AAAAAAAALJPKHGBA','Ola','High','1980-11-19','SUDAN','Ola.High@N.org');

Create DDM and RLS policies

Create the masking policy for customer date of birth:

CREATE MASKING POLICY mask_cust_dob  
WITH (date_of_birth VARCHAR(32))  
USING (sha2(date_of_birth, 256)::TEXT);

Create RLS policies for product launch status:

CREATE RLS POLICY product_launch_status  
WITH (launch_status VARCHAR(50))   
USING (launch_status = 'launched');
  
CREATE RLS POLICY product_launch_status_all
WITH (launch_status VARCHAR(50))   
USING (launch_status IN ('launched','planned'));

Create Amazon Redshift DB roles for Sales and Marketing groups

Create the database roles:

CREATE ROLE "AWSIDC:awssso-sales";
CREATE ROLE "AWSIDC:awssso-marketing";

Attach masking policies

Attach the masking policy to both roles:

ATTACH MASKING POLICY mask_cust_dob  
ON dev.public.customer (date_of_birth)  
TO ROLE "AWSIDC:awssso-marketing";
ATTACH MASKING POLICY mask_cust_dob  
ON dev.public.customer (date_of_birth)  
TO ROLE "AWSIDC:awssso-sales";

Attach RLS policies and enable RLS on product table

Attach the RLS policies and enable row-level security:

ATTACH RLS POLICY product_launch_status  
ON dev.public.product  
TO ROLE "AWSIDC:awssso-sales"; 
ATTACH RLS POLICY product_launch_status_all  
ON dev.public.product  
TO ROLE "AWSIDC:awssso-marketing";
ALTER TABLE dev.public.product ROW LEVEL SECURITY ON;

Grant access to tables to roles

Grant SELECT permissions to both roles:

GRANT SELECT ON dev.public.customer TO ROLE "AWSIDC:awssso-sales";
GRANT SELECT ON dev.public.customer TO ROLE "AWSIDC:awssso-marketing";
GRANT SELECT ON dev.public.product TO ROLE "AWSIDC:awssso-sales"; 
GRANT SELECT ON dev.public.product TO ROLE "AWSIDC:awssso-marketing";

Connect to SALES data warehouse using IAM Identity Center

To connect as a Sales Analyst:

  1. Connect to cpd-sales-wg using the IAM Identity Center connection type as user sales-analyst, and then choose Continue.
  2. Choose sales-analyst, and then choose Next.
  3. Enter your password, and then choose Sign in.
  4. Enter your MFA code, and then choose Sign in.

You are now connected to Amazon Redshift Query Editor V2 with a successful connection to cpd-sales-wg as sales-analyst.

Figure 7: Connect to Sales data warehouse as IDC user

Figure 7: Connect to Sales data warehouse as IDC user

Query shared data as Sales Analyst

Query the customer table with dynamic data masking applied:

SELECT * FROM "dev@edw-ns"."public"."customer";

You can successfully access the customer table, but the sensitive information in the date_of_birth column is encrypted.

Figure 8: Result set of customer table

Figure 8: Result set of customer table

Query the product table with row-level security enabled:

SELECT * FROM "dev@edw-ns"."public"."product";

You can successfully access the product table, but only view data for products with a launch_status value of launched.

Figure 9: Result set of product table

Figure 9: Result set of product table

Note: To connect to the data sharing producer onboarded to Amazon Redshift federated permissions as an IDC user, a superuser is required to provide a CONNECT privilege to the IDC user trying to connect. For more information about how to grant the CONNECT privileges to the user, see Connect privileges in the Amazon Redshift Database Developer Guide.

Connect to Marketing data warehouse using IAM Identity Center

To connect as a Marketing Analyst:

  1. Connect to cpd-marketing-wg using the IAM Identity Center connection type as user marketing-analyst, and then choose Continue.
  2. Choose marketing-analyst, and then choose Next.
  3. Enter your password, and then choose Sign in.
  4. Enter your MFA code, and then choose Sign in.

You are now connected to Amazon Redshift Query Editor V2 with a successful connection to cpd-marketing-wg as marketing-analyst.

Figure 10: Connect to Marketing data warehouse as IDC user

Figure 10: Connect to Marketing data warehouse as IDC user

Query shared data as Marketing Analyst

Query the customer table with dynamic data masking applied:

SELECT * FROM "dev@edw-ns"."public"."customer";

You can successfully access the customer table, but the sensitive information in the date_of_birth column is encrypted.

Figure 11: Result set of customer table

Figure 11: Result set of customer table

Query the product table with row-level security enabled:

SELECT * FROM "dev@edw-ns"."public"."product";

You can successfully access the product table and view data for products with launch_status values of both launched and planned.

Figure 12: Result set of product table

Figure 12: Result set of product table

Additional resources

For more information about implementing federated permissions in your environment, see the following resources:

AWS Documentation

AWS Blogs

AWS Demo

Key benefits

  • Reduced administrative overhead – Centralized policy management removes manual replication
  • Consistent security enforcement – Policies apply uniformly across the warehouses and access methods
  • Seamless identity integration – Single sign-on with existing identity providers through trusted identity propagation and role-based access control

Conclusion

This post showed you how Amazon Redshift federated permissions with AWS IAM Identity Center integration helps streamline multi-warehouse data governance by centralizing security policy management. You define dynamic data masking and row-level security policies once in a central Enterprise Data Warehouse, and they automatically enforce across the connected data warehouses in the same account and Region.

About the authors

Raghu Kuppala

Raghu Kuppala

Raghu is an Analytics Specialist Solutions Architect experienced working in the databases, data warehousing, and analytics space. Outside of work, he enjoys trying different cuisines and spending time with his family and friends.

Satesh Sonti

Satesh Sonti

Satesh is a Principal Specialist Solutions Architect based out of Atlanta, specializing in building enterprise data platforms, data warehousing, and analytics solutions. He has over 20 years of experience in building data assets and leading complex data platform programs for banking and insurance clients across the globe.

Sandeep Adwankar

Sandeep Adwankar

Sandeep is a Senior Product Manager with Amazon SageMaker Lakehouse. Based in the California Bay Area, he works with customers around the globe to translate business and technical requirements into products that help customers improve how they manage, secure, and access data.

Sumukh Bapat

Sumukh Bapat

Sumukh is a Software Engineer at AWS. He works on improving customer experience for Amazon Redshift by solving complex problems in authentication, connectivity, and security. His work focuses on identity management, secure access, and distributed database systems.

Praveen Kumar Ramakrishnan

Praveen Kumar Ramakrishnan

Praveen is a Senior Software Engineer at AWS. He has nearly 20 years of experience spanning various domains including filesystems, storage virtualization and network security. At AWS, he focuses on enhancing the Redshift data security.

Ashish Ghodke

Ashish Ghodke

Ashish is a Software Engineer at Amazon Web Services, where he works on identity and access management systems for large-scale cloud services like Amazon Redshift. His work focuses on building secure authentication and single sign-on solutions for distributed systems. He is passionate about distributed systems, cloud security, and building reliable infrastructure at scale.