Use IAM tags to enable fine-grained federated authentication to Redshift Serverless

Introduction

In this post, we will demonstrate following steps to enable federated authentication in Redshift Serverless establishing fine-grained access control by passing the database roles through IAM tags:

1. Configure role-based access within Redshift Serverless.
2. Configure an IAM role; adding the IAM tag RedshiftDbRoles with the value defined in step-1.
3. Switch Role to authenticate as IAM Role created in step-2.
4. Connect to Redshift Query Editor V2 using Federated user option.
5. Validate authentication and query your Amazon Redshift database.
6. Establish fine-grained authentication while invoking Individual Data API calls

Configure Role based access in Redshift

1. Login as a super user into your Redshift Serverless workgroup.
2. Create schemas and tables in two domains, sales and finance:

--Create schema

create schema sales_schema;
create schema finance_schema;

--Create tables

CREATE TABLE IF NOT EXISTS finance_schema.revenue
(
account INTEGER   ENCODE az64
,customer VARCHAR(20)   ENCODE lzo
,salesamt NUMERIC(18,0)   ENCODE az64
)
DISTSTYLE AUTO
;
insert into finance_schema.revenue values (10001, 'ABC Company', 12000);
insert into finance_schema.revenue values (10002, 'Tech Logistics', 175400);
insert into finance_schema.revenue values (10003, 'XYZ Industry', 24355);
insert into finance_schema.revenue values (10004, 'The tax experts', 186577);

CREATE TABLE IF NOT EXISTS sales_schema.store_sales
(
ID INTEGER   ENCODE az64,
Product varchar(20),
Sales_Amount INTEGER   ENCODE az64
)
DISTSTYLE AUTO
;
Insert into sales_schema.store_sales values (1,'product1',1000);
Insert into sales_schema.store_sales values (2,'product2',2000);
Insert into sales_schema.store_sales values (3,'product3',3000);
Insert into sales_schema.store_sales values (4,'product4',4000);

2. Create following DB roles where the sales and finance database roles will be used to grant privileges to their respective schemas and the analyst database role would have access to both schemas:

--Create roles
create role sales;
create role finance;
create role analyst;

3. Grant schema and table level access to roles as shown below:

• Grant select access on all tables of sales_schema to sales database role
• Grant select access on all tables of finance_schema to finance database role
• Grant the analyst database role access to both by nesting the sales and finance database roles

--Grant access to roles
grant usage on schema sales_schema to role sales;
grant select on all tables in schema sales_schema to role sales;
grant usage on schema finance_schema to role finance;
grant select on all tables in schema finance_schema to role finance;
grant role sales to role analyst;
grant role finance to role analyst;

Create IAM Roles with Principal tags

1. On IAM console choose Roles under Access management.
2. Create a Role with name *SalesRole *and Trusted entity type as AWS account. Then attach following policies to it:

• AmazonRedshiftFullAccess
• AmazonRedshiftQueryEditorV2ReadWriteSharing

3. Choose the Tags tab.
4. Choose the Manage tags.
5. Choose Add tag and enter the Key as RedshiftDbRoles and Value as sales which is the Redshift DB role.
6. Choose Save changes.
7. Repeat steps 1 to 6 to create :

• IAM Role FinanceRole with Key as RedshiftDbRoles and Value as finance which is the Redshift DB role.
• IAM Role AnalystRole with Key as RedshiftDbRoles and Value as analyst which is the Redshift DB role.

Connect to Query Editor V2

1. Switch Role to SalesRole as shown below by passing account number and role name. Please follow AWS documentation on how to use switch role.

2. On Redshift console choose your Serverless workgroup and click on Query data to navigate to Query Editor V2.
3. Create connection for your workgroup and choose Federated user.

4. Check current user using below query.

select current_user  

You are logged in as IAMR:SalesRole.

5. Verify your access on sales_schema:

6. Repeat step 1 to 4 for FinanceRole. Verify your access on finance_schema:

7. Repeat step 1 to 4 for AnalystRole. Verify your access on sales_schema and finance_schema:

Authentication using Redshift Data API for Serverless

You can enable federated authentication in Redshift Serverless while invoking Data API call using CLI command or from other AWS services establishing role based access control by passing the database roles through IAM principal tags. In below example, we are using Lambda to access finance_schema in Redshift Serverless workgroup using temporary credentials by adding principal tag to the IAM Service Role in action.

1. Modify Lambda Service Role to add following principal tag :

• Key as RedshiftDbRoles and Value as finance which is the Redshift DB role.

2. Use batch_execute_statement to run one or more SQL statements by passing workgroup name, database name and query statement to access finance_Schema as shown below:

query = ["CREATE TABLE public.revenue_abc AS SELECT * FROM finance_schema.revenue where account=10001;"]
boto3.client('redshift-data').batch_execute_statement(Database="dev", Sqls=<query>, WorkgroupName="test-workgroup")

Please follow documentation to configure Redshift Data API to access Amazon Redshift data with web services–based applications, including AWS Lambda, Amazon SageMaker notebooks, and AWS Cloud9.