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:
Before you begin, verify that you have the following:
Note: AmazonRedshiftFederatedAuthorization is a managed policy that provides the necessary permissions for running queries with Amazon Redshift federated authorization.
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
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
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.
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:
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.
In this walkthrough, you create two Amazon Redshift IAM Identity Center (IDC) connections:
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.
To create the producer IDC connection:
Figure 3: Data sharing producer IDC connection
To create the consumer IDC connection:
Figure 4: Data sharing consumer IDC connection
To register the edw-ns namespace with federated permissions:
Figure 5: 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.
For cpd-sales-wg and cpd-marketing-wg serverless workgroups, gather the following information from your registered IAM Identity Center connection:
Run the following SQL command as a database administrator to enable the integration:
To modify an existing identity provider, use the ALTER IDENTITY PROVIDER command:
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.
Connect to the EDW data warehouse as an IDC Admin user and run the following SQL commands.
Create the product table:
Insert sample product data:
Create the customer table:
Insert sample customer data:
Create the masking policy for customer date of birth:
Create RLS policies for product launch status:
Create the database roles:
Attach the masking policy to both roles:
Attach the RLS policies and enable row-level security:
Grant SELECT permissions to both roles:
To connect as a Sales Analyst:
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
Query the customer table with dynamic data masking applied:
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
Query the product table with row-level security enabled:
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
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.
To connect as a Marketing Analyst:
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
Query the customer table with dynamic data masking applied:
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
Query the product table with row-level security enabled:
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
For more information about implementing federated permissions in your environment, see the following resources:
AWS Documentation
AWS Blogs
AWS Demo
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.
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 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 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 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 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 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.
