Destination Improvement: Allow Redshift Destination to operate without CREATE permissions on database
The Redshift Destination currently requires two permissions on whichever database you connect it to: GRANT TEMPORARY ON DATABASE <dbname> TO USER fivetran; and GRANT CREATE ON DATABASE <dbname> TO USER fivetran;.
It explicitly checks that it has both of these permissions from time to time.
If you create such a user, let it pass the connection tests, and then run REVOKE CREATE ON DATABASE <dbname> FROM USER fivetran; then you will have gotten a (temporarily) valid destination configuration which lacks the CREATE permission.
Then, you can create a Fivetran Logs schema with CREATE SCHEMA fivetran_log AUTHORIZATION fivetran;, at which point you can still set up the Fivetran Logs connector and have it successfully go through its initial sync and several incremental syncs. Everything works as expected.
After a day or two, however, Fivetran will re-run the connection tests, and will invalidate the Destination configuration, halting all incremental syncs.
Given that those incremental syncs go from a functioning state to a non-functioning state on quite a bit of a delay, and both the initial sync and the following incremental syncs all work, it's clear that the CREATE permission is not strictly needed.
Can you please add some way to disable this check for the CREATE permission? This would be useful for organizations with stricter data management policies, where it's expected that all new schemas are created by the database administrators.
-
Official comment
Hi John,
Apologies for the delayed response here, I'm Kevin from the product team here at Fivetran!
The CREATE permission is utilized whenever a new connector is set up, so removing this requirement would mean adding a manual step to each new connector setup process, rather than having a fully automated process (which this permission provides for us).
However, I definitely hear you that more security-conscious customers might not allow this internally. I would be curious to hear if other permissions in our documented requirements would be a blocker for you? Additionally, if this permission were optional, would it be acceptable to manually run additional steps to set up each connector afterwards?
Please sign in to leave a comment.
Comments
1 comment