search

transferwise / pipelinewise-target-redshift

Singer.io Target for Amazon Redshift - PipelineWise compatible
https://transferwise.github.io/pipelinewise/
Other
12 stars 65 forks source link

Support redshift permissions for users/groups #15

Closed Limess closed 5 years ago

Limess commented 5 years ago

Current Behaviour

Currently this target will attempt to create privilidges for roles given in the singer catalog target_schema_select_permissions property, if is set: https://github.com/transferwise/pipelinewise-target-redshift/blob/6e3c5b895745ec4de63fdc918aee90a587974893/target_redshift/db_sync.py#L263-L276

This is then set as either:

GRANT USAGE ON SCHEMA {} TO ROLE {}
GRANT SELECT ON ALL TABLES IN SCHEMA {} TO ROLE {}

https://github.com/transferwise/pipelinewise-target-redshift/blob/6e3c5b895745ec4de63fdc918aee90a587974893/target_redshift/db_sync.py#L477-L480

This seems to be a hangup from the equivalent postgres target, as Redshift doe s not support roles, only users and groups.

Expected behaviour

target_schema_select_permissions is a json map which supports users and groups keys, which are each lists. These are then mapped to 

GRANT SELECT ON ALL TABLES IN SCHEMA {} TO USER {}
GRANT SELECT ON ALL TABLES IN SCHEMA {} TO GROUP {}

or the equivalent for USAGE permissions.

koszti commented 5 years ago

@Limess thanks for reporting this issue. It has been fixed by https://github.com/transferwise/pipelinewise-target-redshift/commit/c58f4ee59db459e8b2aca71b49f1729ed8d505ad . Now we can define grants as you recommended:

{
    "users": ["user_1","user_2"],
    "groups": ["group_1", "group_2"]
}

This will be released to pypi with some other work in progress items very soon.

koszti commented 5 years ago

same feature got merged into PipelineWise that allows to do FasySync to redshift https://github.com/transferwise/pipelinewise/pull/209

Closing this issue.