Setting Up Role-Based Access Control (RBAC) in Amazon Redshift

Overview

Amazon Redshift added true role-based access control (RBAC) in late 2022, bringing it closer to enterprise data-warehouse peers such as Snowflake and BigQuery. Instead of granting privileges directly to users or groups, you now create roles—logical containers for privileges—and then grant those roles to users. This greatly simplifies security administration, auditing, and DevOps automation.

Why Redshift Needed RBAC

Prior to RBAC, administrators relied on database groups or object-level grants to control access. As the number of users and objects grew, so did the surface area for mistakes:

• Privilege sprawl—users accumulated hundreds of grants over time.
• Operational toil—every new table required a new grant statement.
• Weak auditing—no quick way to see which business function needed a permission.

RBAC solves these by making the role, rather than the person, the unit of authorization. You can map roles to business functions (e.g., finance_analyst) or environments (e.g., prod_readonly) and manage them centrally.

Core RBAC Concepts

1. Roles

Roles are database objects that own no data themselves but can hold privileges on other objects—schemas, tables, stored procedures, data-shares, or future objects.

2. Admin Option

A role granted WITH ADMIN OPTION on another role can further delegate that role, enabling hierarchy without superuser involvement.

3. Default Roles

Redshift supplies built-in roles such as rs_admin, rs_reader, and rs_monitor. These cover common operational patterns and reduce the need for custom grants.

4. Trusted Language

Only roles that have the CREATE MODEL or CREATE EXTERNAL FUNCTION privileges can execute untrusted code. RBAC therefore improves security for ML and UDF workloads.

Step-by-Step Setup Guide

Step 1 – Enable Role Management (Optional)

RBAC is enabled by default on new clusters. For older clusters running revocable grants, set the parameter:

ALTER SYSTEM SET enable_session_authorization = true;

Step 2 – Create Roles

-- Analysts need read-only access
CREATE ROLE analyst_role;

-- Data engineers need DML and DDL on staging schemas
CREATE ROLE data_eng_role;

Step 3 – Grant Object Privileges to Roles

-- Give analysts SELECT on all current tables
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO ROLE analyst_role;

-- Give analysts future SELECT as new tables arrive
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
GRANT SELECT ON TABLES TO ROLE analyst_role;

-- Data engineers can INSERT/UPDATE and CREATE tables in staging
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA staging TO ROLE data_eng_role;
GRANT USAGE, CREATE ON SCHEMA staging TO ROLE data_eng_role;

Step 4 – Assign Roles to Users (or Groups)

-- Map individual users
GRANT ROLE analyst_role TO USER alice;
GRANT ROLE data_eng_role TO USER bob;

-- Or grant to existing groups
GRANT ROLE analyst_role TO GROUP financial_analysts WITH ADMIN OPTION;

Step 5 – Activate a Role at Runtime

Users can switch roles during a session:

SET ROLE analyst_role; -- Activate
RESET ROLE; -- Revert to default

Practical Example: End-to-End

Suppose a startup has two departments—Finance and Marketing—each with analysts and engineers. The security policy says:

• Finance analysts: read-only access to finance.*
• Marketing analysts: read-only access to marketing.*
• All engineers: full DML in staging and ability to create tables

Implementation:

-- Create roles
CREATE ROLE finance_read;
CREATE ROLE marketing_read;
CREATE ROLE eng_write;

-- Grant privileges to roles
GRANT SELECT ON ALL TABLES IN SCHEMA finance TO ROLE finance_read;
GRANT SELECT ON ALL TABLES IN SCHEMA marketing TO ROLE marketing_read;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA staging TO ROLE eng_write;
GRANT CREATE ON SCHEMA staging TO ROLE eng_write;

-- Default privileges for future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA finance GRANT SELECT ON TABLES TO ROLE finance_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA marketing GRANT SELECT ON TABLES TO ROLE marketing_read;

-- Map roles to users
GRANT ROLE finance_read TO USER anna;
GRANT ROLE marketing_read TO USER mark;
GRANT ROLE eng_write TO USER dave WITH ADMIN OPTION; -- Dave can delegate


Now auditing becomes trivial: anna has exactly one role governing her access, and Dave can onboard new engineers without superuser help.

Galaxy & RBAC

Galaxy, the modern SQL editor, respects Redshift RBAC automatically. When you connect your cluster, Galaxy runs under the credentials you provide. If you’re SET ROLE analyst_role, Galaxy’s autocomplete, AI copilot, and metadata explorer show only the objects you’re entitled to see. This lets dev teams share a single Galaxy Workspace while remaining compliant with principle of least privilege.

You can even store common SET ROLE commands in Galaxy Collections so that queries execute with the correct context every time—no more accidental writes to production.

Best Practices

Design for Least Privilege

Create narrow roles and combine them rather than building one mega-role. For instance, read_customer and read_finance can be granted together to power-users.

Use Role Hierarchies

Grant senior roles to junior ones with WITH ADMIN OPTION to model organizational ladders while minimizing grants.

Automate with IaC

Store your CREATE ROLE and GRANT statements in Terraform or AWS CDK so every environment is reproducible.

Review Regularly

Query SVV_ROLE_GRANTS and SVV_ROLE_PRIVS to detect unused roles or privilege drift.

Common Misconceptions

• “RBAC replaces groups.” Groups still exist and can hold roles, letting you phase into RBAC gradually.
• “Superusers are required for every grant.” Not when you design roles with WITH ADMIN OPTION—delegation is encouraged.
• “Roles slow query performance.” RBAC is an authorization layer evaluated at plan time; it has negligible impact on execution.

Troubleshooting & Monitoring

Diagnose Privilege Errors

If a user gets permission denied, run:

SELECT *
FROM SVV_RESTRICTED_GRANT_ERRORS; -- Superusers only


Audit Active Role

SELECT current_role, session_user;

View Role Grants

SELECT * FROM SVV_ROLE_GRANTS WHERE grantee = 'alice';

Conclusion

By adopting role-based access control, Redshift shops can eliminate grant sprawl, satisfy auditors, and automate security. Coupled with Galaxy’s role-aware SQL editor, developers gain a frictionless yet secure workflow.