Federated user not working for Redshift Serverless

When connecting to Amazon Redshift Serverless using a federated user through query editor v2, there are a few potential issues that could cause the error you're experiencing.

The error "Databases couldn't be listed. FATAL: Failed query when cluster is auto paused" typically occurs when your Amazon Redshift Serverless workgroup is in an idle state (paused). When using an AWS IAM Identity Center identity to connect, the workgroup may remain paused during the connection attempt.

To resolve this issue:

1. You need to log in with another authentication method first to resume the Serverless workgroup.
2. Once the workgroup is active, you can then connect using your federated identity.

For federated users to connect properly, you also need to ensure:

1. The user is registered with IAM or AWS IAM Identity Center, as only these users can query objects in Amazon Redshift federated permissions catalog.

2. The federated user has been explicitly granted CONNECT privileges to access the workgroup. When registering an existing Amazon Redshift Serverless Namespace with federated permissions, all AWS IAM Identity Center federated users must be granted these privileges.

3. Your IAM role or user has the correct principal tags configured:

• RedshiftDbUser: This required tag defines the database user used by query editor v2
• RedshiftDbGroups: This optional tag defines database groups to join when connecting

If you're not familiar with database credentials or AWS Secrets Manager, you might want to consider using temporary credentials or IAM Identity Center for authentication instead of the federated user method until you become more familiar with the system.
Sources
Troubleshooting connections from Amazon Redshift query editor v2 - Amazon Redshift
Considerations when using Amazon Redshift federated permissions - Amazon Redshift
Connecting to an Amazon Redshift database - Amazon Redshift