glue job for redshift connection: "Unable to find suitable security group"

Question

I'm trying to set up a AWS Glue job and make a connection to Redshift.

I'm getting error when I set the connection type to Redshift:

"Unable to find a suitable security group. Change connection type to JDBC and retry adding your connection."

Following what was said here in these forums, I added permissions to my IAM account for role AWSGlueServiceRoleDefault:

I then set up the job with the matching IAM role AWSGlueServiceRoleDefault:

I need to store the Glue data in Redshift DB, so I chose JDBC then added a connection:

As soon as I choose Redshift, it complains that it cannot find a suitable security group. Why is this?

Answer 1

I found the same issue when trying to connect Glue with Amazon RDS (MySQL) and solved it following the AWS Glue guidelines -> Setting Up a VPC to Connect to JDBC Data Stores.

In a nutshell you should check that the security group associated to your RedShift cluster allows self-referencing traffic.

1. Go to RedShift console and choose Clusters
2. Look at the Cluster Properties section for the ID of the security group associated to the cluster (e.g. sg-957be3ef).
3. Click at the security group name to jump to the EC2 console -> Security groups section. Choose the group and modify the Inbound and Outbound rules adding self-referencing rule to allow AWS Glue components to communicate.
   • Inbound rules: chose Type=All TCP, leave the default values and type the security group in the Source field (i.e. sg-957be3ef for this example).
   • Outbound rules: same as Inbound rules.

Hope that works!

Answer 2

You have to create a:

1)AWSGlueServiceRole role and attach s3FullAccess,GlueServiceRole and RedshiftFullAccess policy.

2)Check you have vpc Endpoint, If not create a VPC endpoint and make sure subnets are added to route table. 3)Create self referencing security group.

Now connect using JDBC connection,

jdbc:type://xxxx:port/databasename

type is redshift/postgresql/etc... xxxx: server name where the database hosted.