Failed setting up RedShift destination CopyDestination test failed

  • 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) ~[?:?]

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 ?

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.

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

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.

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;

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.