How to RBAC in PostgreSQL

How do I set up RBAC in Amazon Redshift?

RBAC in Amazon Redshift lets you create roles, grant them object or role privileges, and assign those roles to users for granular, auditable access control.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why pick RBAC over individual GRANTs?

RBAC centralizes permissions in reusable roles. You grant privileges once to a role and attach that role to as many users or groups as needed. This avoids repetitive GRANT statements, keeps audits simple, and enables least-privilege by mixing read-only, writer, or admin roles instead of giving blanket rights.

What is the core syntax to manage roles?

Amazon Redshift supports CREATE ROLE, DROP ROLE, GRANT <role|privilege> TO <role|user>, and REVOKE. You can also set default object privileges with ALTER DEFAULT PRIVILEGES. The next section lists every option.

How do I create a read-only role for ecommerce tables?

First, create the role, then grant object privileges, and finally attach the role to analysts. Example below shows the full workflow.

How can I grant multiple roles to a single user?

Redshift supports role chaining. Use GRANT role_a, role_b TO analyst1. The user inherits the union of all granted privileges.

How do I audit current role assignments?

Query SVV_ROLE_GRANTS and PG_USER views. These catalog tables list which users own which roles and which roles own other roles.

What are best practices for secure RBAC?

Create small, task-oriented roles (reader, writer, loader). Grant roles instead of direct privileges. Use default privileges so new tables inherit correct permissions. Review SVV_ROLE_GRANTS monthly.

Which mistakes should I avoid?

Common pitfalls include granting privileges directly to users and forgetting default privileges. More details appear in the Mistakes section below.

Common mistake: Missing default privileges?

If you grant SELECT only on existing tables, newly created tables remain inaccessible. Fix by running ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO ecommerce_read.

Common mistake: Over-granting admin rights?

Using ALL PRIVILEGES on the database level gives users DDL power they may not need. Grant table-level rights instead and reserve ALL for trusted ops roles.

FAQ

Can roles own other roles in Redshift?

Yes. Use GRANT role_child TO role_parent to build role hierarchies. A user assigned role_parent inherits child privileges.

Does RBAC affect performance?

No. Role lookups happen at login and are cached for the session. They do not add noticeable query overhead.

How do I drop a role that still has grants?

First revoke the role from users and other roles, then revoke object privileges, and finally call DROP ROLE role_name.

Why How to RBAC in PostgreSQL is important

How to RBAC in PostgreSQL Example Usage


-- 1) Create a writer role for order processing
CREATE ROLE order_writer;

-- 2) Allow INSERT and UPDATE on Orders & OrderItems
GRANT INSERT, UPDATE ON TABLE Orders, OrderItems TO ROLE order_writer;

-- 3) Analysts need read only; engineers need writer + reader
GRANT ROLE ecommerce_read TO USER analyst1;
GRANT ROLE ecommerce_read, order_writer TO USER engineer1;

How to RBAC in PostgreSQL Syntax


-- Create a role
CREATE ROLE ecommerce_read;

-- Grant object privileges
GRANT SELECT ON TABLE Customers, Orders, Products, OrderItems TO ROLE ecommerce_read;

-- Set default privileges so future tables stay readable
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO ROLE ecommerce_read;

-- Grant the role to a user
GRANT ROLE ecommerce_read TO USER analyst1;

-- View current grants
SELECT * FROM SVV_ROLE_GRANTS WHERE grantee = 'analyst1';

Common Mistakes

Granting privileges directly to every user. This bloats catalog tables and complicates audits. Instead, create roles and grant them to users.
Forgetting ALTER DEFAULT PRIVILEGES. Without it, new tables are unreadable to read-only roles. Always pair object GRANTs with matching default privileges.

Frequently Asked Questions (FAQs)

Can I migrate existing GRANTs to roles?

Yes. Script a query against the catalog to capture current object privileges, create matching roles, grant privileges to those roles, then assign roles to users and revoke direct GRANTs.