I am trying to follow the guide in : https://aws.amazon.com/blogs/big-data/cross-account-streaming-ingestion-for-amazon-redshift/
in order to set up real-time data ingestion from a kinesis stream (in one AWS account) to amazon redshift (in another account). Here are the queries that I run (replacing my AWS account IDs with placeholders). The first query runs fine (creating the initial schema and doing the role chaining) but then I get the below error when I run the seconds query after about ~100 seconds or so.
CREATE EXTERNAL SCHEMA connect_stream
FROM KINESIS
IAM_ROLE 'arn:aws:iam::<account 2>:role/RedshiftConnectRole,
arn:aws:iam::<account 1>:role/KinesisConnectRole';
CREATE MATERIALIZED VIEW ctr_processor_vw AS
SELECT approximatearrivaltimestamp,
partitionkey,
shardid,
sequencenumber,
json_parse(from_varbyte(data, 'utf-8')) as payload
FROM connect_stream."ctr-processor";
ERROR: ----------------------------------------------- error: User arn:aws:redshift:us-east-1:<account 2>:dbuser:spectra-lakehouse/awsuser is not authorized to assume IAM Role arn:aws:iam::<account 2>:role/RedshiftConnectRole,\narn:aws:iam::<account 1>:role/KinesisConnectRole. code: 8001 context: IAM Role=arn:aws:iam::<account 2>:role/RedshiftConnectRole,\narn:aws:iam::<account 1>:role/KinesisConnectRole query: 0 location: xen_aws_credentials_mgr.cpp:506 process: padbmain [pid=25744] ----------------------------------------------- [ErrorId: 1-63ed507f-352533ea31e31bff7d62b5a3]