Failed setting up RedShift destination CopyDestination test failed

Connector Questions & Issues
1
  • Is this your first time deploying Airbyte?: Yes
  • OS Version / Instance: Amazon Linux 5.10
  • Memory / Disk: 4GB / 30GB
  • Deployment: AWS EC2
  • Airbyte Version: 0.36.6-alpha
  • Source name/version: None
  • Destination name/version: RedShift 0.3.32
  • Step: During creating destination
  • Description: Remove this with the description of your problem.

Airbyte failed to setup RedShift destination with CopyDestination test failed.
I granted Airbyte RedShift user all privileges in its schema and create global temporary tables.
I tried the user account with RedShift query editor v2 and it can perform as expected.
The S3 access key has R/W permissions to the staging bucket.
Is there other permissions I have to grant to the account to load data?

2022-05-09 04:20:45 INFO i.a.w.p.a.DefaultAirbyteStreamFactory(lambda$create$0):61 - 2022-05-09 04:20:45 ERROR i.a.i.d.j.c.CopyDestination(check):64 - Exception attempting to connect to the warehouse: 
2022-05-09 04:20:45 INFO i.a.w.p.a.DefaultAirbyteStreamFactory(lambda$create$0):61 - java.sql.SQLException: [Amazon](500310) Invalid operation: permission denied for database test;
2022-05-09 04:20:45 INFO i.a.w.p.a.DefaultAirbyteStreamFactory(lambda$create$0):61 - 	at com.amazon.redshift.client.messages.inbound.ErrorResponse.toErrorException(Unknown Source) ~[redshift-jdbc42-no-awssdk-1.2.51.1078.jar:RedshiftJDBC_1.2.51.1078]
2022-05-09 04:20:45 INFO i.a.w.p.a.DefaultAirbyteStreamFactory(lambda$create$0):61 - 	at com.amazon.redshift.client.PGMessagingContext.handleErrorResponse(Unknown Source) ~[redshift-jdbc42-no-awssdk-1.2.51.1078.jar:RedshiftJDBC_1.2.51.1078]
2022-05-09 04:20:45 INFO i.a.w.p.a.DefaultAirbyteStreamFactory(lambda$create$0):61 - 	at com.amazon.redshift.client.PGMessagingContext.handleMessage(Unknown Source) ~[redshift-jdbc42-no-awssdk-1.2.51.1078.jar:RedshiftJDBC_1.2.51.1078]
2022-05-09 04:20:45 INFO i.a.w.p.a.DefaultAirbyteStreamFactory(lambda$create$0):61 - 	at com.amazon.jdbc.communications.InboundMessagesPipeline.getNextMessageOfClass(Unknown Source) ~[redshift-jdbc42-no-awssdk-1.2.51.1078.jar:RedshiftJDBC_1.2.51.1078]
2022-05-09 04:20:45 INFO i.a.w.p.a.DefaultAirbyteStreamFactory(lambda$create$0):61 - 	at com.amazon.redshift.client.PGMessagingContext.doMoveToNextClass(Unknown Source) ~[redshift-jdbc42-no-awssdk-1.2.51.1078.jar:RedshiftJDBC_1.2.51.1078]
2022-05-09 04:20:45 INFO i.a.w.p.a.DefaultAirbyteStreamFactory(lambda$create$0):61 - 	at com.amazon.redshift.client.PGMessagingContext.getErrorResponse(Unknown Source) ~[redshift-jdbc42-no-awssdk-1.2.51.1078.jar:RedshiftJDBC_1.2.51.1078]
2022-05-09 04:20:45 INFO i.a.w.p.a.DefaultAirbyteStreamFactory(lambda$create$0):61 - 	at com.amazon.redshift.client.PGClient.handleErrorsScenario3(Unknown Source) ~[redshift-jdbc42-no-awssdk-1.2.51.1078.jar:RedshiftJDBC_1.2.51.1078]
2022-05-09 04:20:45 INFO i.a.w.p.a.DefaultAirbyteStreamFactory(lambda$create$0):61 - 	at com.amazon.redshift.client.PGClient.handleErrors(Unknown Source) ~[redshift-jdbc42-no-awssdk-1.2.51.1078.jar:RedshiftJDBC_1.2.51.1078]
2022-05-09 04:20:45 INFO i.a.w.p.a.DefaultAirbyteStreamFactory(lambda$create$0):61 - 	at com.amazon.redshift.dataengine.PGQueryExecutor$CallableExecuteTask.call(Unknown Source) ~[redshift-jdbc42-no-awssdk-1.2.51.1078.jar:RedshiftJDBC_1.2.51.1078]
2022-05-09 04:20:45 INFO i.a.w.p.a.DefaultAirbyteStreamFactory(lambda$create$0):61 - 	at com.amazon.redshift.dataengine.PGQueryExecutor$CallableExecuteTask.call(Unknown Source) ~[redshift-jdbc42-no-awssdk-1.2.51.1078.jar:RedshiftJDBC_1.2.51.1078]
2022-05-09 04:20:45 INFO i.a.w.p.a.DefaultAirbyteStreamFactory(lambda$create$0):61 - 	at java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[?:?]
2022-05-09 04:20:45 INFO i.a.w.p.a.DefaultAirbyteStreamFactory(lambda$create$0):61 - 	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) ~[?:?]
2022-05-09 04:20:45 INFO i.a.w.p.a.DefaultAirbyteStreamFactory(lambda$create$0):61 - 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) ~[?:?]
2

Hey are you using the API to copy the destination? If so can you confirm if the other destination is created in similar way is working ?

3

I’m not sure what API to copy the destination is. I used WebUI to create this RedShift destination.
The RedShift cluster has S3 read-only permission to all buckets,
and the Airbyte access key has read-write permission to its staging bucket.

Is there someway to pinpoint where the test code is in GitHub? So that I can grant its required permissions.

4

Sorry my bad. So you created a new Redshift cluster by cloning? and that is not working as a destination after adding in Airbyte ?

5

No, the cluster was created long before I starting using Airbyte.
I’m trying to target it as destination for the first time in Airbyte.

6

The solution is granting an Airbyte user in RedShift to CREATE at database scope.
CREATE scope at the schema level is not sufficient.

GRANT CREATE ON DATABASE my_database TO airbyte_user;
1 Like
7

Hi there from the Community Assistance team.
We’re letting you know about an issue we discovered with the back-end process we use to handle topics and responses on the forum. If you experienced a situation where you posted the last message in a topic that did not receive any further replies, please open a new topic to continue the discussion. In addition, if you’re having a problem and find a closed topic on the subject, go ahead and open a new topic on it and we’ll follow up with you. We apologize for the inconvenience, and appreciate your willingness to work with us to provide a supportive community.