How to truncate a Redshift-serverless table by Temporary credentials?

Question

Hi, I connect to the Redshift-serverless with the latest boto3 with temporary credentials. I saw the page below and only set WorkgroupName, Database and Sql, without DbUser. https://dev.classmethod.jp/articles/quicksight-folder-group/

In this situation, the truncate operation failed with a error; "Error": "ERROR: must be owner of relation <table name>".

I think temporary user doesn't have the grant to do. I see the temporary user-ish in the redshift by 'select * from pg_user;'.

IAMR:****** usesuper=false

When I used a redshift cluster, I set DbUser=admin who has a grant (usesuper=true) to be able to truncate the table. How should I do to truncate a table in Redshift serverless?

Thanks,

Accepted Answer

Hi Michihiro Ota,

Yes currently and unfortunately we cannot specify the DbUser or the DbGroup in the Redshift Serverless GetCredentials. For the moment, there isn't any database access auto grant mechanism for the IAMR prefix users. At present, I see the following two possible methods for serverless:

*  If you are using JDBC connection with IAM and you want to specify a custom DbUser or DbGroup, then the following connection string can be used *jdbc:redshift:iam://redshift-serverless-:aws-region/*.  You can find more information on the following link https://docs.aws.amazon.com/redshift/latest/mgmt/serverless-migration.html (the **Connecting using IAM with dbUser and dbGroup** section)
* The other way is to create a role in Redshift, give it the required permissions and then grant it to the IAMR:****** using the normal syntax *grant role  to 'IAMR:******'; If you want the IAMR user to be a constant value then you can add the RedshiftDbUser tag in the Tags tab of your IAM role and give a value.

Thanks,

How to truncate a Redshift-serverless table by Temporary credentials?

Relevant content