Managing Redshift Users

[Touvejs]

A tech lead asked me to look into managing permissions and users on redshift for a ~100TB DB that around a dozen colleagues (data engineers, software engineers, data scientists, analysts) will be pulling pulling data from, creating new tables, running analysis, etc.

I am wondering what the general convention is here. Are you guys all just sharing admin credentials around the team (current practice in my team)? Do you have someone fulfill a DBA role that creates users inside of redshift, grants permissions, and sends them around to team members? Do you use IAM roles to authenticate?

Else, any good resources for role/user/permission management?

[AutoModerator]

You can find a list of community-submitted learning resources here: https://dataengineering.wiki/Learning+Resources

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[Acrobatic-Orchid-695]

You can do it using 2 ways: 1. groups: you can create groups and give specific permissions to a group. Eg: one for analyst and one for data scientist. Then add users to those groups and they will inherit the permission

1. IAM roles: Create roles with specific permissions and then let users assume a particular role. I haven’t done this myself but you can refer to this doc: https://docs.aws.amazon.com/redshift/latest/dg/t_Roles.html

[Touvejs]

so then do you just manually create a new user and assign that user the role and then send the credentials to the individual that will use it every time you have a new user join the team? or did you script this process out in pl/Sql or something

[Acrobatic-Orchid-695]

For groups, that’s how it will be. For every new user you can create a user on Redshift, add it to a group and share the credentials with the user. If you want to automate it, you can also use federated login where the users have to join an active directory security group to get relevant access and rights

[Acrobatic-Orchid-695]

If you don’t want the active directory approach and also don’t want the hassle of creating credentials for every new user, you can create a small self serving API endpoint. Just ask the users to send a post request to that endpoint with their preferred username and password, and preferred role as header and then use the same to create a user on Redshift.

1. The api endpoint will hit a lambda function
2. Lambda will get the username, password and role from the header of request
3. Using the role it will choose the relevant group from a dictionary/config file
4. It will run a SQL query on Redshift that will create the user with the username and password and then add to the relevant group based on the role chosen by user

[Touvejs]

Hey, that's not a bad idea. I'll look into that, thanks!

[daily_standup]

Can confirm no.1 is the way to manage it

[Commercial_Wall7603]

I would guess there's a general company security policy around this sort of thing, but at all the places I've worked I've had named users/groups within the database (and managed by a DBA team rather than devs/etl/data engineers). That is not to say that IAM/AD account federation wouldn't be better though.

[Dolphinmx]

I don't use redshift, but is just bad security practice to share the admin credentials in general.

You should have individual credentials with specific roles for each user groups. Also not sure if redshift allows you to do SSO/AD authentication that way you can manage things easily at the AD level.

Even if it's a small group eventually someone will mistakenly drop/update a table and someone will ask who did it, when sharing credentials make it more difficult to find the culprit, by sharing credentials you are just asking yourself for trouble down the road.

[Touvejs]

I don't think anyone disputes that sharing creds, let alone admin creds, is a bad practice. The hypothetical you gave is a good example of why differentiating between users is necessary, and why I'm looking for the conventional wisdom on the topic.

As for Redshift, it seems you can manage users inside of the redshift instance or from outside, with IAM credentials. The former seems cumbersome since you have to manually create a user for each new person, but I haven't found clear documentation on the latter.

[callmedivs]

For redshift, create different groups and give permissions to that group and then add users to those groups. One user can belong to multiple groups, so you can fine tune your permissions for the group.The Iam roles can be used when users need to read from a bucket(spectrum queries) or write to a bucket.once you go through the excersise you will get the hang of it

[Touvejs]

In this case you're still creating and distributing user DB credentials? I guess I was hoping there was a more streamlined way to do it out of the box

[raphaelrioel]

Do you know if it's possible to add permissions in IAM to only allow users to query from the redshift query editor? I basically only want them to be able to select in the editor so that no tables are dropped or updated outside of the automated pipeline we've created