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


If a connect using query Editor V2 with IAM role, i am able to query catalog. But if i am trying to connect with database user, i am not able to do it. Also how to i connect with a python client.

asked a month ago173 views
2 Answers

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:

profile pictureAWS
answered 23 days ago

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?

answered 19 days ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions