The Dev Guide has a section on IAM Policies for Amazon Redshift Spectrum which can help you step through the various roles, as well as cross-account permissions, and a specific call out for chaining roles, if required.
Without looking at what you've done already, it is difficult to troubleshoot via re:Post... if you have an account team, I'd recommend chatting with the Solution Architect as a potential next step as they may be able to troubleshoot in conjunction with you.
Redshift serverless: error while trying to create an external tableasked 5 months ago
Is it possible to have a Redshift Spectrum schema access multiple accounts data ?asked 9 months ago
Redshift Spectrum giving IERR_READ_ERROR (Access Denied) errorasked 3 years ago
Redshift Data Share External Schemaasked 2 months ago
Is there a way to create a Redshift Table from a Glue table's schema?Accepted Answerasked 7 months ago
Can you use a Redshift Spectrum Table in a CTE?asked 10 months ago
Redshift is translating 'dbo' schema name in to 'public' schema name.asked 8 days ago
RedShift Serverless / Spectrum / Glue access issueAccepted Answerasked 9 months ago
Redshift Spectrum Access to Lake FormationAccepted Answerasked 3 years ago
Redshift external schemaasked 6 months ago