I have been trying to access AWS redshift using psql client. With Psql client i am able to access redshift using db credentials.

redshift_query_output=$(PGPASSWORD=${pass} psql --no-align --quiet --field-separator=' ' -h ${host} -U ${user} -d $database_name -p $port_number  <<< $run_redshift_query |  sed -e '1d' |  head -n -1)

The above query works. But I want to know if there is anyway to access using IAM role or User. I am not able to find any proper resource on that.

I tried using IAM role credentials, but that's not working directly.

CodePudding user response：

From Using IAM authentication to generate database user credentials - Amazon Redshift:

You can generate temporary database credentials based on permissions granted through an AWS Identity and Access Management (IAM) permissions policy to manage the access that your users have to your Amazon Redshift database.

Commonly, Amazon Redshift database users log in to the database by providing a database user name and password. However, you don't have to maintain user names and passwords in your Amazon Redshift database. As an alternative, you can configure your system to permit users to create user credentials and log in to the database based on their IAM credentials.

The easiest way is to use the AWS CLI:

From Generating IAM database credentials using the Amazon Redshift CLI or API - Amazon Redshift:

To programmatically generate temporary database user credentials, Amazon Redshift provides the get-cluster-credentials command for the AWS Command Line Interface (AWS CLI) and the GetClusterCredentials API operation. Or you can configure your SQL client with Amazon Redshift JDBC or ODBC drivers that manage the process of calling the GetClusterCredentials operation, retrieving the database user credentials, and establishing a connection between your SQL client and your Amazon Redshift database.

aws redshift get-cluster-credentials --cluster-identifier examplecluster --db-user temp_creds_user -–auto-create --db-name exampledb -–db-groups example_group --duration-seconds 3600

You can then use the returned credentials with the psql command.