title: kinessis is not able to send data to redshift: Redshift.ConnectionFailed
body: <p>I am newbie in aws. there is a week I try to stream data from Nifi to Redshift as test to learn how it work. what I do is that generating a simple record "aa,12,2020" with Nifi send it to kinesis firehose and kinesis send it to s3 bucket and then from there to a table in redshiftcluster.
   I have this error all the time </p>
  
  <p>Redshift.ConnectionFailed</p>
  
  <p>I had try it without nifi with test data from kinesis but the result is the same. 
  what I had done til now to resolve the problem is what is writen in this link:
  <a href="https://stackoverflow.com/questions/34207317/aws-kinesis-firehose-not-inserting-data-in-redshift">AWS Kinesis Firehose not inserting data in Redshift</a>
  it is a little more detailed in compare with aws help. 
  in this list all things seems to be ok, until </p>
  
  <blockquote>
    <p>At this point, you should be able to see the connection attempts in Redshift logs : 
    `select * from stl_connection_log where remotehost like '52%' order by recordtime desc;</p>
  </blockquote>
  
  <p>there is any record in my redshift DB.</p>
  
  <p>how much I red, all saying that it is about security and rolls. I think I miss something and I can not find it.
  I had 3 times delete all process and make it again, but not helped.
  here is my  settings for testing with kinesistestdata:
  <strong>Kinesisfirehose:</strong></p>
  
  <ul>
  <li>IAM role: firehose_delivery_role</li>
  <li>policies: oneClick_firehose_delivery_role_1586881780618, AmazonRedshiftFullAccess , AmazonS3FullAccess  </li>
  <li>Monitoring result:<a href="https://i.stack.imgur.com/MH0Z7.png" rel="nofollow noreferrer">Monitoring shows Redshift success is zero</a></li>
  </ul>
  
  <p>S3 Bucket:
  data file content in bucket:</p>
  
  <pre><code>{"ticker_symbol":"AAPL","sector":"TECHNOLOGY","change":0.26,"price":90.78}{"ticker_symbol":"WMT","sector":"RETAIL","change":1.84,"price":95.82}{"ticker_symbol":"CVB","sector":"TECHNOLOGY","change":-0.07,"price":57.51}{"ticker_symbol":"DFT","sector":"RETAIL","change":-0.06,"price":131.62}
  </code></pre>
  
  <p>Object URL-xml file in bucket:</p>
  
  <pre><code>&lt;Error&gt;
  &lt;Code&gt;AccessDenied&lt;/Code&gt;
  &lt;Message&gt;Access Denied&lt;/Message&gt;
  &lt;RequestId&gt;11111&lt;/RequestId&gt;
  &lt;HostId&gt;
  1111111111111    &lt;/HostId&gt;
  &lt;/Error&gt;
  </code></pre>
  
  <p><strong>Redshift Cluster:</strong></p>
  
  <ul>
  <li>attached IAM Role
  AWSServiceRoleForRedshift 
  firehose_delivery_role (same role in kinesis)</li>
  <li>Network and Security:
  Virtual private cloud (VPC) info</li>
  <li>both CIDR and inbound rules:
  172.31.0.0/16
  13.58.135.96/27</li>
  <li>Availability Zone : us-east-2a</li>
  <li>VPC security group:default</li>
  <li>Publicly accessible: yes</li>
  </ul>
  
  <p>I have read also the Jason pollicies file like: 
  <a href="https://docs.aws.amazon.com/firehose/latest/dev/controlling-access.html#using-iam-s3" rel="nofollow noreferrer">https://docs.aws.amazon.com/firehose/latest/dev/controlling-access.html#using-iam-s3</a>
  but it is not clear how should I do it. on the other side the policy are added via GUI, so it should work. </p>
  
  <p>would you please help me how should I go step forward?
  Thanks alot!</p>
  
  <p>--------some days later-------------</p>
  
  <p>after finding error of copy command I tried to find problem with help of this page:
  <a href="https://aws.amazon.com/premiumsupport/knowledge-center/s3-troubleshoot-403/" rel="nofollow noreferrer">s3 access denied 403</a>
  I could not find a problem specifically but there is no error in kinesis:redshift log and no error in Redshift database with these 3 query:</p>
  
  <pre><code>select * from STL_LOAD_ERRORS;
  select * from  STL_FILE_SCAN;
  select * from STL_S3CLIENT_ERROR order by recordtime desc;
  </code></pre>
  
  <p>but still no data is coping in redshift.(in kinesis monitoring tab the "DeliveryToRedshift Success " is still on zero)
  I will appreciate if anyone has an idea.</p>