AWS Redshift | How to setup a redshift cluster with external connectivity | AWS VPC

[@BiInsightsInc]

Link to AWS Playlist: kzbin.info/www/bejne/mXeyY2CdqJppmpo

[@mohanjangid6]

amazing brother. having a tough time moving from azure to aws. you made it easy.

[@aniketkapdule]

I have been looking for this for so many days, Thank you for making this video. 1 sub from me!

[@mikemikey255]

excellent walkthru! Thank you

[@JohnNapoleon-Kuofie]

This video helped me so much thank you!

[@prabhathkota107]

Very nice explanation

[@tranngoccat7552]

Bro, you're truly my life saver! Thank you so muchhhhhh

[@BiInsightsInc]

Glad to hear that!

[@kofio7581]

Thanks again, great video! I will give this a shot today!

[@deekay9213]

1. in the diagram you show the Redshift cluster to be in 'Private Subnet' - but while creating the cluster you do not associate it with a private subnet but instead with the default subnet which generally is public subnet. 2. If the cluster is not in private subnet, will you still need to create a vpc endpoint to access it from DBeaver?

[@BiInsightsInc]

Hi Deepak, yes you'd still need an endpoint to access your cluster from a client like DBeaver and from other services such as AWS Glue. You're right I did go with the default VPC that comes with a public subnet. Here is a link if you want to create a subnet. docs.aws.amazon.com/vpc/latest/userguide/working-with-subnets.html#create-subnets

[@mickaelphilippon7925]

So if you Redshift cluster is in private subnet it's impossible to allow external connection from AWS Glue or Looker through vpc endpoint. If i enable public access on my RedShift Cluster build in private subnet, i've a public ip but on a private subnet without IGW. The only that i've found it's to build a NLB on public subnet in front of the cluster

[@xiaocuizhang6879]

Thanks for the sharing, and may I ask how to choose the services when creating the VPC endpoint?

[@BiInsightsInc]

Here is an AWS article on this subject on Accessing an AWS service using an interface VPC endpoint. docs.aws.amazon.com/vpc/latest/privatelink/create-interface-endpoint.html

[@DeepakPunia-mc2dn]

You attached policy "AmazoneRedshiftServiceLinkedRolePolicy" to redshift role but I don't see any aws managed policy with such name. If it is user managed policy, could you please share link of video, where you created it.

[@BiInsightsInc]

That is a default policy that is managed by by AWS. I have created a role and attached that policy. You can search for it under policies. If you can't locate it then here is the JSON script that defines this policy: { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "ec2:DescribeVpcs", "ec2:DescribeSubnets", "ec2:DescribeNetworkInterfaces", "ec2:DescribeAddresses", "ec2:AssociateAddress", "ec2:DisassociateAddress", "ec2:CreateNetworkInterface", "ec2:DeleteNetworkInterface", "ec2:ModifyNetworkInterfaceAttribute", "ec2:CreateVpcEndpoint", "ec2:DeleteVpcEndpoints", "ec2:DescribeVpcEndpoints", "ec2:ModifyVpcEndpoint" ], "Resource": "*" }, { "Effect": "Allow", "Action": [ "ec2:AllocateAddress" ], "Resource": [ "arn:aws:ec2:*:*:elastic-ip/*" ], "Condition": { "StringEquals": { "aws:RequestTag/Redshift": "true" } } }, { "Effect": "Allow", "Action": [ "ec2:ReleaseAddress" ], "Resource": [ "arn:aws:ec2:*:*:elastic-ip/*" ], "Condition": { "StringEquals": { "aws:ResourceTag/Redshift": "true" } } }, { "Sid": "EnableCreationAndManagementOfRedshiftCloudwatchLogGroups", "Effect": "Allow", "Action": [ "logs:CreateLogGroup", "logs:PutRetentionPolicy" ], "Resource": [ "arn:aws:logs:*:*:log-group:/aws/redshift/*" ] }, { "Sid": "EnableCreationAndManagementOfRedshiftCloudwatchLogStreams", "Effect": "Allow", "Action": [ "logs:CreateLogStream", "logs:PutLogEvents", "logs:DescribeLogStreams", "logs:GetLogEvents" ], "Resource": [ "arn:aws:logs:*:*:log-group:/aws/redshift/*:log-stream:*" ] }, { "Effect": "Allow", "Action": [ "ec2:AuthorizeSecurityGroupEgress", "ec2:AuthorizeSecurityGroupIngress", "ec2:UpdateSecurityGroupRuleDescriptionsEgress", "ec2:ReplaceRouteTableAssociation", "ec2:CreateRouteTable", "ec2:AttachInternetGateway", "ec2:UpdateSecurityGroupRuleDescriptionsIngress", "ec2:AssociateRouteTable", "ec2:RevokeSecurityGroupIngress", "ec2:CreateRoute", "ec2:CreateSecurityGroup", "ec2:RevokeSecurityGroupEgress", "ec2:ModifyVpcAttribute", "ec2:CreateSubnet" ], "Resource": [ "arn:aws:ec2:*:*:subnet/*", "arn:aws:ec2:*:*:route-table/*", "arn:aws:ec2:*:*:security-group/*", "arn:aws:ec2:*:*:vpc/*", "arn:aws:ec2:*:*:internet-gateway/*" ], "Condition": { "StringEquals": { "aws:ResourceTag/Purpose": "RedshiftMigrateToVpc" } } }, { "Effect": "Allow", "Action": [ "ec2:CreateSecurityGroup", "ec2:CreateInternetGateway", "ec2:CreateVpc", "ec2:CreateRouteTable", "ec2:CreateSubnet" ], "Resource": [ "arn:aws:ec2:*:*:subnet/*", "arn:aws:ec2:*:*:route-table/*", "arn:aws:ec2:*:*:security-group/*", "arn:aws:ec2:*:*:vpc/*", "arn:aws:ec2:*:*:internet-gateway/*" ], "Condition": { "StringEquals": { "aws:RequestTag/Purpose": "RedshiftMigrateToVpc" } } }, { "Effect": "Allow", "Action": "ec2:CreateTags", "Resource": [ "arn:aws:ec2:*:*:subnet/*", "arn:aws:ec2:*:*:route-table/*", "arn:aws:ec2:*:*:security-group/*", "arn:aws:ec2:*:*:vpc/*", "arn:aws:ec2:*:*:internet-gateway/*", "arn:aws:ec2:*:*:elastic-ip/*" ], "Condition": { "StringEquals": { "ec2:CreateAction": [ "CreateVpc", "CreateSecurityGroup", "CreateSubnet", "CreateInternetGateway", "CreateRouteTable", "AllocateAddress" ] } } }, { "Effect": "Allow", "Action": [ "ec2:DescribeVpcAttribute", "ec2:DescribeSecurityGroups", "ec2:DescribeInternetGateways", "ec2:DescribeSecurityGroupRules", "ec2:DescribeAvailabilityZones", "ec2:DescribeNetworkAcls", "ec2:DescribeRouteTables" ], "Resource": "*" }, { "Effect": "Allow", "Action": [ "cloudwatch:PutMetricData" ], "Resource": "*", "Condition": { "StringEquals": { "cloudwatch:namespace": [ "AWS/Redshift-Serverless", "AWS/Redshift" ] } } } ] }

[@awengirr]

Thank you for the video. I followed everything but I am getting connection timeout error in Dbeaver. What can be the issue?

[@BiInsightsInc]

There can be a number of reasons for this. Some of the common reasons for a timeout error are; either your are inputting a wrong host or port, or port is blocked by firewall or not available publicly (VPC). Check your security group's inbound rules to make sure the port is open and your IP is added, like in the video. Also, check if your Redshift Cluster has the publicly accessible option checked.

[@mwanthidaniel1254]

Hello Sir, how do you configure vpc such that you can access redshift from DBeaver? I have a correct IAM role, but... default VPC seems to fail

[@BiInsightsInc]

I have covered the VPC, Security Group and Endpoint setup that allows us to connect to redshift from DBeaver. In addition, we go over the settings of Redshift Cluster needed for this setup. What is the error are you facing? And what resources are working with? If you need details or working with custom VPC then I suggest read AWS docs on it. docs.aws.amazon.com/redshift/latest/mgmt/managing-cluster-cross-vpc.html

[@Its-Viru]

So, if we disable publicly accessible, how can RedShift be accessed from DBeaver ( w/o jump/bastion)

[@BiInsightsInc]

One way would be to set up a SSH Tunnel to Redshift. Then you can establish a connection using the tunnel with DBeaver.

[@vaibhaavs2775]

Hello Sir , When i go into edit routes it shows a different Vpc id and im unable to change that. Therefore i deleted and created my endpoint. It's still targeting a random vpc which dosent exist

[@BiInsightsInc]

Hi Vaibhaas, when you are creating a new endpoint make sure the service type is gateway. In addition, under the VPC section you can select one of the existing VPC in your environment from the dropdown.

[@orchidmhs297]

Hello sir We are facing a problem retrieving data from Redshift to Salesforce CRM analytics due to firewall issue. How to resolve it

[@BiInsightsInc]

You can add an exception to the firewall.

[@saimanideepakkammili]

Failed to test connection Redshift connection2 due to FAILED status. i am getting this error. could you please help me? i am trying to test the connection from glue connection to redshit

[@BiInsightsInc]

There can be many reasons why the connection is failing. Here is a link to AWS docs on how to troubleshoot AWS Glue test connection fail? repost.aws/knowledge-center/glue-test-connection-failed