User Roles & Permissions

Introduction

Databases support fine-grained roles, users, GRANTs and REVOKEs. Use them to enforce least privilege at the DB level.

Understanding the topic

Core concepts to understand:

• CREATE ROLE app_user LOGIN PASSWORD '...';
• GRANT SELECT, INSERT ON orders TO app_user;
• Group roles for permission sets.
• Postgres: Row-Level Security (RLS) filters rows per user.
• Never use the superuser/root for app connections.

Syntax reference

Visual workflow / architecture:

roles
 ├── superuser (DBA only)
 ├── app_writer ──── INSERT/UPDATE on app tables
 ├── app_reader ──── SELECT on app tables
 └── analytics ───── SELECT on warehouse

App service ──▶ app_writer  (least privilege)

Real-world use

Multi-tenant SaaS uses RLS so each tenant sees only its rows. Banks use roles to separate teller, audit and admin access.

Best practices

• One role per service; never share creds.
• Use group roles for tidy management.
• RLS for multi-tenant isolation.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Roles vs users.
• Q2. RLS — what and why.
• Q3. Least privilege example.
• Q4. GRANT vs REVOKE.
• Q5. Multi-tenant patterns.