Amazon Redshift Roles (RBAC)

4 minute read
Advanced
1

Simplified Migration of Groups to Roles

Intro

Amazon Redshift introduced Role Based Access Control (RBAC) on April 7, 2022 to help simplify the management of security privileges. Prior to RBAC, Redshift relied on Groups to organize privileges to collections of users. While Groups are still present in Redshift, a Group only contains Users while Roles have users or even other Roles granted to it. This allows you to nest privileges for easier management.

Groups vs Roles

In this example, there are two development groups named "dev_app1" and "dev_app2". Both groups use tables in the "core" and "shared" schemas but the application specific schemas (app1 and app2) are not shared between groups. Redshift Groups

This results in duplicate grants for the same objects to two different groups.

CREATE GROUP dev_app1;
--Grants to core and shared schemas are executed for each Group
GRANT ALL ON ALL TABLES IN SCHEMA core TO GROUP dev_app1;
GRANT USAGE ON SCHEMA core TO GROUP dev_app1;
GRANT ALL ON ALL TABLES IN SCHEMA shared TO GROUP dev_app1;
GRANT USAGE ON SCHEMA shared TO GROUP dev_app1;

GRANT ALL ON ALL TABLES IN SCHEMA app1 TO GROUP dev_app1;
GRANT USAGE ON SCHEMA app1 TO GROUP dev_app1;
ALTER GROUP dev_app1 ADD USER ramu;

CREATE GROUP dev_app2;
--Grants to core and shared schemas are executed for each Group
GRANT ALL ON ALL TABLES IN SCHEMA core TO GROUP dev_app2;
GRANT USAGE ON SCHEMA core TO GROUP dev_app2;
GRANT ALL ON ALL TABLES IN SCHEMA shared TO GROUP dev_app2;
GRANT USAGE ON SCHEMA shared TO GROUP dev_app2;

GRANT ALL ON ALL TABLES IN SCHEMA app2 TO GROUP dev_app2;
GRANT USAGE ON SCHEMA app2 TO GROUP dev_app2;
ALTER GROUP dev_app1 ADD USER susan;

With RBAC, you can create a "developers" Role and grant access to the "core" and "shared" schemas. Next, you can grant the "app1" schema to the "dev_app1" Role and also grant the "developers" Role to the "dev_app1" role.

Redshift Roles

This structure makes it easier to manage the access privileges as you can use a hierarchy of Roles to manage database privileges. It greatly reduces the amount of grants that are needed.

CREATE ROLE developers;
--Grants to core and shared schemas are executed just once to the developers Role
GRANT ALL ON ALL TABLES IN SCHEMA core TO ROLE developers;
GRANT USAGE ON SCHEMA core TO ROLE developers;
GRANT ALL ON ALL TABLES IN SCHEMA shared TO ROLE developers;
GRANT USAGE ON SCHEMA shared TO ROLE developers;

CREATE ROLE dev_app1;
GRANT ALL ON ALL TABLES IN SCHEMA app1 TO ROLE dev_app1;
GRANT USAGE ON SCHEMA app1 TO ROLE dev_app1;

CREATE ROLE dev_app2;
GRANT ALL ON ALL TABLES IN SCHEMA app2 TO ROLE dev_app2;
GRANT USAGE ON SCHEMA app2 TO ROLE dev_app2;

GRANT ROLE developers TO ROLE dev_app1;
GRANT ROLE developers TO ROLE dev_app2;
GRANT ROLE dev_app1 TO ramu;
GRANT ROLE dev_app2 TO susan;

Migration

Moving from Groups to Roles can be achieved with a handy stored procedure found in this Github project.

The stored procedure pr_migrate_groups_to_roles.sql creates Roles and the grants to objects based on your current Groups. Simply create the stored procedure in your database and then execute it.

There is one parameter for the stored procedure named "dryrun". Pass in "true" if you wish to simply test the procedure to see what Roles and grants would be executed in your database. Pass in "false" to proceed with the creation of Roles and Grants.

Note: Output from the stored procedure is through RAISE INFO messages.

Reports

There are additional query scripts in this repo which can display the current grants to different types of objects for Groups and Roles. For example, you can use query_table_group_grants.sql to show the current grants to Groups on tables and you can then run query_table_role_grants.sql to display the current grants to Roles on tables.

Summary

Role Based Access Control (RBAC) provides simplified management of database privileges. And migrating from Groups to Roles has been simplified with this Github repo.

profile picture
AWS
EXPERT
published a month ago152 views