How to query awsdatacatalog mounted on redshift cluster with python client ??

Make sure the IAM role and Redshift user have access to the database. You can provide access to the 'awsdatacatalog' database to the Redshift user, using the GRANT command.

Important note - for auto-mounted data catalogs:

• Must use authentication method Temporary credentials using your IAM identity with the Redshift provisioned cluster.
• Must use the authentication method federated user to connect with a Redshift Serverless workgroup.

Check the following Blog - Simplify external object access in Amazon Redshift using automatic mounting of the AWS Glue Data Catalog

Regarding the Python connector, you can refer to the following documentation:

• Configuring the Amazon Redshift Python connector
• Examples of using the Amazon Redshift Python connector

Thank you for this answer and/or hints

could you please check on the following understanding: with your statement and the references I would conclude that it is NOT possible to create a database role (and users) which can be used by an external sql client (e.g. rsql) to access awsdatacatalog. In order to do so, I will have to create an IAM user assuming a role which is allowed to access the glue databases, redshift and IAM GetClusterCredentials (or WithIAM). With the GetClusterCredentials credentials I can logon redshift (cluster or serverless) to read external databases like awsdatacatalog. If this requests also contains group definition of a database role to grant access to non-external databases (actual redshift tables) the session will also be able to access those. The GRANT ASSUMEROLE ON the (very same) IAM role from above, does not do the same or anything TO the dedicated ROLE or user of the database.

Besides the bummer, that I have to go over AWS/IAM when connecting to redshift database for reading awsdatacatalog, I couldnt find documentation or guides on how to properly define a minimal permission setup for that use-case. The blog article states a full-access policy to various services and just mentions that shouldnt be for production. So, I ask myself what are the essential IAM permissions (glue, redshift) required to just grant read access to awsdatacatalog over a raw database connection (e.g. rsql). Do you have any hints?