rbac-authorization-patterns

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

RBAC Authorization Patterns for laneweaverTMS

laneweaverTMS的RBAC授权模式

Expert guidance for implementing Role-Based Access Control (RBAC) and multi-tenant authorization in a Go/Echo backend with Supabase/PostgreSQL.

为基于Go/Echo后端、Supabase/PostgreSQL的系统实现基于角色的访问控制（RBAC）和多租户授权提供专业指导。

When to Use This Skill

何时使用该方案

Use when:

Defining user roles and permissions for freight brokerage operations
Implementing Echo middleware for role/permission checks
Setting up multi-tenant isolation with account-based access
Designing JWT claims structure for authorization
Writing RLS policies for tenant-isolated data access
Choosing appropriate HTTP status codes for authorization failures

适用于以下场景：

为货运代理业务定义用户角色和权限
实现用于角色/权限校验的Echo授权中间件
基于账户访问设置多租户隔离
设计用于授权的JWT声明结构
编写用于租户隔离数据访问的RLS策略
为授权失败选择合适的HTTP状态码

Freight Brokerage Role Definitions

货运代理角色定义

Standard Roles

标准角色

Role	Description	Typical Access
`admin`	Full system access	All resources, user management, system config
`dispatcher`	Load management, carrier selection	Loads, carriers, tracking, dispatch operations
`sales`	Account management, quotes	Customers, quotes, lanes, tenders
`finance`	Invoicing, payments, reports	Invoices, carrier bills, payments, financial reports
`driver`	Limited mobile access	Assigned loads only, status updates, document upload
`readonly`	View-only access	Read all operational data, no modifications

角色	描述	典型权限
`admin`	系统完全访问权限	所有资源、用户管理、系统配置
`dispatcher`	运单管理、承运人选择	运单、承运人、跟踪、调度操作
`sales`	账户管理、报价	客户、报价、线路、投标
`finance`	发票、付款、报表	发票、承运人账单、付款、财务报表
`driver`	有限的移动端访问权限	仅查看分配给自己的运单、状态更新、文档上传
`readonly`	只读访问权限	查看所有运营数据，无修改权限

Permission Model

权限模型

Permissions follow a

resource:action

pattern:

loads:read, loads:create, loads:update, loads:delete
carriers:read, carriers:create, carriers:update
customers:read, customers:create, customers:update
invoices:read, invoices:create, invoices:approve
reports:financial, reports:operational
users:manage

权限遵循

资源:操作

的模式：

loads:read, loads:create, loads:update, loads:delete
carriers:read, carriers:create, carriers:update
customers:read, customers:create, customers:update
invoices:read, invoices:create, invoices:approve
reports:financial, reports:operational
users:manage

Database Schema Patterns

数据库模式设计

Core RBAC Tables

核心RBAC表

sql

-- Roles table
CREATE TABLE public.roles (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    is_system_role BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);

-- Permissions table
CREATE TABLE public.permissions (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    resource TEXT NOT NULL,
    action TEXT NOT NULL,
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    UNIQUE(resource, action)
);

-- Role-Permission junction
CREATE TABLE public.role_permissions (
    role_id UUID NOT NULL REFERENCES public.roles(id) ON DELETE CASCADE,
    permission_id UUID NOT NULL REFERENCES public.permissions(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    PRIMARY KEY (role_id, permission_id)
);

-- User-Role junction (within account/tenant context)
CREATE TABLE public.user_roles (
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    role_id UUID NOT NULL REFERENCES public.roles(id) ON DELETE CASCADE,
    account_id UUID NOT NULL REFERENCES public.accounts(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    created_by UUID REFERENCES auth.users(id),
    PRIMARY KEY (user_id, role_id, account_id)
);

-- Account-User junction for multi-tenant
CREATE TABLE public.account_users (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    account_id UUID NOT NULL REFERENCES public.accounts(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    is_primary BOOLEAN DEFAULT false,
    invited_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    accepted_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    UNIQUE(account_id, user_id)
);

-- Indexes for RLS policy performance
CREATE INDEX idx_user_roles_user_id ON public.user_roles(user_id);
CREATE INDEX idx_user_roles_account_id ON public.user_roles(account_id);
CREATE INDEX idx_account_users_user_id ON public.account_users(user_id);
CREATE INDEX idx_account_users_account_id ON public.account_users(account_id);

sql

-- Roles table
CREATE TABLE public.roles (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    is_system_role BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);

-- Permissions table
CREATE TABLE public.permissions (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    resource TEXT NOT NULL,
    action TEXT NOT NULL,
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    UNIQUE(resource, action)
);

-- Role-Permission junction
CREATE TABLE public.role_permissions (
    role_id UUID NOT NULL REFERENCES public.roles(id) ON DELETE CASCADE,
    permission_id UUID NOT NULL REFERENCES public.permissions(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    PRIMARY KEY (role_id, permission_id)
);

-- User-Role junction (within account/tenant context)
CREATE TABLE public.user_roles (
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    role_id UUID NOT NULL REFERENCES public.roles(id) ON DELETE CASCADE,
    account_id UUID NOT NULL REFERENCES public.accounts(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    created_by UUID REFERENCES auth.users(id),
    PRIMARY KEY (user_id, role_id, account_id)
);

-- Account-User junction for multi-tenant
CREATE TABLE public.account_users (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    account_id UUID NOT NULL REFERENCES public.accounts(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    is_primary BOOLEAN DEFAULT false,
    invited_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    accepted_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    UNIQUE(account_id, user_id)
);

-- Indexes for RLS policy performance
CREATE INDEX idx_user_roles_user_id ON public.user_roles(user_id);
CREATE INDEX idx_user_roles_account_id ON public.user_roles(account_id);
CREATE INDEX idx_account_users_user_id ON public.account_users(user_id);
CREATE INDEX idx_account_users_account_id ON public.account_users(account_id);

Seed Default Roles

初始化默认角色

sql

INSERT INTO public.roles (name, description, is_system_role) VALUES
    ('admin', 'Full system access', true),
    ('dispatcher', 'Load management and carrier selection', true),
    ('sales', 'Account management and quotes', true),
    ('finance', 'Invoicing, payments, and reports', true),
    ('driver', 'Limited mobile access for assigned loads', true),
    ('readonly', 'View-only access to operational data', true);

sql

INSERT INTO public.roles (name, description, is_system_role) VALUES
    ('admin', 'Full system access', true),
    ('dispatcher', 'Load management and carrier selection', true),
    ('sales', 'Account management and quotes', true),
    ('finance', 'Invoicing, payments, and reports', true),
    ('driver', 'Limited mobile access for assigned loads', true),
    ('readonly', 'View-only access to operational data', true);

Echo Authorization Middleware

Echo授权中间件

Context Keys

上下文键

package middleware

type contextKey string

const (
    ContextKeyUserID    contextKey = "user_id"
    ContextKeyAccountID contextKey = "account_id"
    ContextKeyRoles     contextKey = "roles"
    ContextKeyUser      contextKey = "user"
)

package middleware

type contextKey string

const (
    ContextKeyUserID    contextKey = "user_id"
    ContextKeyAccountID contextKey = "account_id"
    ContextKeyRoles     contextKey = "roles"
    ContextKeyUser      contextKey = "user"
)

JWT Claims Structure

JWT声明结构

package auth

import "github.com/golang-jwt/jwt/v5"

type Claims struct {
    jwt.RegisteredClaims
    UserID      string   `json:"user_id"`
    Email       string   `json:"email"`
    AccountID   string   `json:"account_id"`
    Roles       []string `json:"roles"`
    Permissions []string `json:"permissions,omitempty"` // Optional: can derive from roles
}

package auth

import "github.com/golang-jwt/jwt/v5"

type Claims struct {
    jwt.RegisteredClaims
    UserID      string   `json:"user_id"`
    Email       string   `json:"email"`
    AccountID   string   `json:"account_id"`
    Roles       []string `json:"roles"`
    Permissions []string `json:"permissions,omitempty"` // Optional: can derive from roles
}

Authentication Middleware

认证中间件

Validates JWT and extracts claims into context:

package middleware

import (
    "net/http"
    "strings"

    "github.com/labstack/echo/v4"
)

func JWTAuth(jwtSecret []byte) echo.MiddlewareFunc {
    return func(next echo.HandlerFunc) echo.HandlerFunc {
        return func(c echo.Context) error {
            authHeader := c.Request().Header.Get("Authorization")
            if authHeader == "" {
                return echo.NewHTTPError(http.StatusUnauthorized, "missing authorization header")
            }

            tokenString := strings.TrimPrefix(authHeader, "Bearer ")
            if tokenString == authHeader {
                return echo.NewHTTPError(http.StatusUnauthorized, "invalid authorization format")
            }

            claims, err := ValidateToken(tokenString, jwtSecret)
            if err != nil {
                return echo.NewHTTPError(http.StatusUnauthorized, "invalid or expired token")
            }

            // Store in context for downstream handlers
            c.Set(string(ContextKeyUserID), claims.UserID)
            c.Set(string(ContextKeyAccountID), claims.AccountID)
            c.Set(string(ContextKeyRoles), claims.Roles)
            c.Set(string(ContextKeyUser), claims)

            return next(c)
        }
    }
}

验证JWT并将声明提取到上下文中：

package middleware

import (
    "net/http"
    "strings"

    "github.com/labstack/echo/v4"
)

func JWTAuth(jwtSecret []byte) echo.MiddlewareFunc {
    return func(next echo.HandlerFunc) echo.HandlerFunc {
        return func(c echo.Context) error {
            authHeader := c.Request().Header.Get("Authorization")
            if authHeader == "" {
                return echo.NewHTTPError(http.StatusUnauthorized, "missing authorization header")
            }

            tokenString := strings.TrimPrefix(authHeader, "Bearer ")
            if tokenString == authHeader {
                return echo.NewHTTPError(http.StatusUnauthorized, "invalid authorization format")
            }

            claims, err := ValidateToken(tokenString, jwtSecret)
            if err != nil {
                return echo.NewHTTPError(http.StatusUnauthorized, "invalid or expired token")
            }

            // Store in context for downstream handlers
            c.Set(string(ContextKeyUserID), claims.UserID)
            c.Set(string(ContextKeyAccountID), claims.AccountID)
            c.Set(string(ContextKeyRoles), claims.Roles)
            c.Set(string(ContextKeyUser), claims)

            return next(c)
        }
    }
}

Tenant Context Middleware

租户上下文中间件

Ensures valid tenant context after authentication:

func TenantContext() echo.MiddlewareFunc {
    return func(next echo.HandlerFunc) echo.HandlerFunc {
        return func(c echo.Context) error {
            accountID := c.Get(string(ContextKeyAccountID))
            if accountID == nil || accountID.(string) == "" {
                return echo.NewHTTPError(http.StatusForbidden, "no tenant context")
            }

            // Optionally validate account exists and is active
            // This can be cached for performance

            return next(c)
        }
    }
}

认证后确保有效的租户上下文：

func TenantContext() echo.MiddlewareFunc {
    return func(next echo.HandlerFunc) echo.HandlerFunc {
        return func(c echo.Context) error {
            accountID := c.Get(string(ContextKeyAccountID))
            if accountID == nil || accountID.(string) == "" {
                return echo.NewHTTPError(http.StatusForbidden, "no tenant context")
            }

            // Optionally validate account exists and is active
            // This can be cached for performance

            return next(c)
        }
    }
}

Role-Based Authorization Middleware

基于角色的授权中间件

// RequireRole checks if user has any of the specified roles
func RequireRole(roles ...string) echo.MiddlewareFunc {
    return func(next echo.HandlerFunc) echo.HandlerFunc {
        return func(c echo.Context) error {
            userRoles, ok := c.Get(string(ContextKeyRoles)).([]string)
            if !ok || len(userRoles) == 0 {
                return echo.NewHTTPError(http.StatusForbidden, "no roles assigned")
            }

            for _, required := range roles {
                for _, userRole := range userRoles {
                    if userRole == required {
                        return next(c)
                    }
                }
            }

            return echo.NewHTTPError(http.StatusForbidden, "insufficient role permissions")
        }
    }
}

// RequireAnyRole is an alias for RequireRole (OR logic)
var RequireAnyRole = RequireRole

// RequireAllRoles checks if user has ALL specified roles
func RequireAllRoles(roles ...string) echo.MiddlewareFunc {
    return func(next echo.HandlerFunc) echo.HandlerFunc {
        return func(c echo.Context) error {
            userRoles, ok := c.Get(string(ContextKeyRoles)).([]string)
            if !ok {
                return echo.NewHTTPError(http.StatusForbidden, "no roles assigned")
            }

            userRoleSet := make(map[string]bool)
            for _, r := range userRoles {
                userRoleSet[r] = true
            }

            for _, required := range roles {
                if !userRoleSet[required] {
                    return echo.NewHTTPError(http.StatusForbidden, "missing required role")
                }
            }

            return next(c)
        }
    }
}

// RequireRole checks if user has any of the specified roles
func RequireRole(roles ...string) echo.MiddlewareFunc {
    return func(next echo.HandlerFunc) echo.HandlerFunc {
        return func(c echo.Context) error {
            userRoles, ok := c.Get(string(ContextKeyRoles)).([]string)
            if !ok || len(userRoles) == 0 {
                return echo.NewHTTPError(http.StatusForbidden, "no roles assigned")
            }

            for _, required := range roles {
                for _, userRole := range userRoles {
                    if userRole == required {
                        return next(c)
                    }
                }
            }

            return echo.NewHTTPError(http.StatusForbidden, "insufficient role permissions")
        }
    }
}

// RequireAnyRole is an alias for RequireRole (OR logic)
var RequireAnyRole = RequireRole

// RequireAllRoles checks if user has ALL specified roles
func RequireAllRoles(roles ...string) echo.MiddlewareFunc {
    return func(next echo.HandlerFunc) echo.HandlerFunc {
        return func(c echo.Context) error {
            userRoles, ok := c.Get(string(ContextKeyRoles)).([]string)
            if !ok {
                return echo.NewHTTPError(http.StatusForbidden, "no roles assigned")
            }

            userRoleSet := make(map[string]bool)
            for _, r := range userRoles {
                userRoleSet[r] = true
            }

            for _, required := range roles {
                if !userRoleSet[required] {
                    return echo.NewHTTPError(http.StatusForbidden, "missing required role")
                }
            }

            return next(c)
        }
    }
}

Permission-Based Authorization Middleware

基于权限的授权中间件

// RequirePermission checks for specific resource:action permission
func RequirePermission(resource, action string) echo.MiddlewareFunc {
    return func(next echo.HandlerFunc) echo.HandlerFunc {
        return func(c echo.Context) error {
            claims, ok := c.Get(string(ContextKeyUser)).(*Claims)
            if !ok {
                return echo.NewHTTPError(http.StatusForbidden, "invalid user context")
            }

            // Check explicit permissions if available
            requiredPerm := resource + ":" + action
            for _, perm := range claims.Permissions {
                if perm == requiredPerm || perm == resource+":*" || perm == "*:*" {
                    return next(c)
                }
            }

            // Fallback: derive from roles (requires DB lookup or cached mapping)
            if hasPermissionViaRole(claims.Roles, resource, action) {
                return next(c)
            }

            return echo.NewHTTPError(http.StatusForbidden, "permission denied")
        }
    }
}

// hasPermissionViaRole checks role-permission mapping
// In production, use cached lookup or include permissions in JWT
func hasPermissionViaRole(roles []string, resource, action string) bool {
    // Admin role has all permissions
    for _, role := range roles {
        if role == "admin" {
            return true
        }
    }

    // Role-permission mapping (simplified; use DB in production)
    rolePerms := map[string][]string{
        "dispatcher": {"loads:*", "carriers:read", "tracking:*"},
        "sales":      {"customers:*", "quotes:*", "lanes:*", "tenders:*"},
        "finance":    {"invoices:*", "payments:*", "reports:financial"},
        "driver":     {"loads:read", "loads:update_status", "documents:upload"},
        "readonly":   {"loads:read", "carriers:read", "customers:read"},
    }

    requiredPerm := resource + ":" + action
    for _, role := range roles {
        for _, perm := range rolePerms[role] {
            if matchPermission(perm, requiredPerm) {
                return true
            }
        }
    }
    return false
}

func matchPermission(pattern, required string) bool {
    if pattern == required {
        return true
    }
    // Handle wildcard: "loads:*" matches "loads:read"
    if strings.HasSuffix(pattern, ":*") {
        prefix := strings.TrimSuffix(pattern, "*")
        return strings.HasPrefix(required, prefix)
    }
    return false
}

// RequirePermission checks for specific resource:action permission
func RequirePermission(resource, action string) echo.MiddlewareFunc {
    return func(next echo.HandlerFunc) echo.HandlerFunc {
        return func(c echo.Context) error {
            claims, ok := c.Get(string(ContextKeyUser)).(*Claims)
            if !ok {
                return echo.NewHTTPError(http.StatusForbidden, "invalid user context")
            }

            // Check explicit permissions if available
            requiredPerm := resource + ":" + action
            for _, perm := range claims.Permissions {
                if perm == requiredPerm || perm == resource+":*" || perm == "*:*" {
                    return next(c)
                }
            }

            // Fallback: derive from roles (requires DB lookup or cached mapping)
            if hasPermissionViaRole(claims.Roles, resource, action) {
                return next(c)
            }

            return echo.NewHTTPError(http.StatusForbidden, "permission denied")
        }
    }
}

// hasPermissionViaRole checks role-permission mapping
// In production, use cached lookup or include permissions in JWT
func hasPermissionViaRole(roles []string, resource, action string) bool {
    // Admin role has all permissions
    for _, role := range roles {
        if role == "admin" {
            return true
        }
    }

    // Role-permission mapping (simplified; use DB in production)
    rolePerms := map[string][]string{
        "dispatcher": {"loads:*", "carriers:read", "tracking:*"},
        "sales":      {"customers:*", "quotes:*", "lanes:*", "tenders:*"},
        "finance":    {"invoices:*", "payments:*", "reports:financial"},
        "driver":     {"loads:read", "loads:update_status", "documents:upload"},
        "readonly":   {"loads:read", "carriers:read", "customers:read"},
    }

    requiredPerm := resource + ":" + action
    for _, role := range roles {
        for _, perm := range rolePerms[role] {
            if matchPermission(perm, requiredPerm) {
                return true
            }
        }
    }
    return false
}

func matchPermission(pattern, required string) bool {
    if pattern == required {
        return true
    }
    // Handle wildcard: "loads:*" matches "loads:read"
    if strings.HasSuffix(pattern, ":*") {
        prefix := strings.TrimSuffix(pattern, "*")
        return strings.HasPrefix(required, prefix)
    }
    return false
}

Middleware Chain Example

中间件链示例

Apply middleware in order: Auth -> Tenant -> Role -> Permission:

func SetupRoutes(e *echo.Echo, cfg *config.Config) {
    // Public routes (no auth required)
    e.GET("/health", handlers.HealthCheck)

    // API routes with auth
    api := e.Group("/api/v1")
    api.Use(middleware.JWTAuth(cfg.JWTSecret))
    api.Use(middleware.TenantContext())

    // Load routes - dispatchers and admins
    loads := api.Group("/loads")
    loads.Use(middleware.RequireRole("admin", "dispatcher", "sales", "readonly"))
    loads.GET("", handlers.ListLoads)
    loads.GET("/:id", handlers.GetLoad)

    // Modify operations require specific roles
    loads.POST("", handlers.CreateLoad, middleware.RequireRole("admin", "dispatcher", "sales"))
    loads.PUT("/:id", handlers.UpdateLoad, middleware.RequireRole("admin", "dispatcher"))

    // Finance routes
    finance := api.Group("/finance")
    finance.Use(middleware.RequireRole("admin", "finance"))
    finance.GET("/invoices", handlers.ListInvoices)
    finance.POST("/invoices", handlers.CreateInvoice)

    // Admin-only routes
    admin := api.Group("/admin")
    admin.Use(middleware.RequireRole("admin"))
    admin.GET("/users", handlers.ListUsers)
    admin.POST("/users", handlers.CreateUser)
}

按顺序应用中间件：认证 -> 租户 -> 角色 -> 权限：

func SetupRoutes(e *echo.Echo, cfg *config.Config) {
    // Public routes (no auth required)
    e.GET("/health", handlers.HealthCheck)

    // API routes with auth
    api := e.Group("/api/v1")
    api.Use(middleware.JWTAuth(cfg.JWTSecret))
    api.Use(middleware.TenantContext())

    // Load routes - dispatchers and admins
    loads := api.Group("/loads")
    loads.Use(middleware.RequireRole("admin", "dispatcher", "sales", "readonly"))
    loads.GET("", handlers.ListLoads)
    loads.GET("/:id", handlers.GetLoad)

    // Modify operations require specific roles
    loads.POST("", handlers.CreateLoad, middleware.RequireRole("admin", "dispatcher", "sales"))
    loads.PUT("/:id", handlers.UpdateLoad, middleware.RequireRole("admin", "dispatcher"))

    // Finance routes
    finance := api.Group("/finance")
    finance.Use(middleware.RequireRole("admin", "finance"))
    finance.GET("/invoices", handlers.ListInvoices)
    finance.POST("/invoices", handlers.CreateInvoice)

    // Admin-only routes
    admin := api.Group("/admin")
    admin.Use(middleware.RequireRole("admin"))
    admin.GET("/users", handlers.ListUsers)
    admin.POST("/users", handlers.CreateUser)
}

Multi-Tenant RLS Policies

多租户RLS策略

Enable RLS on Tables

为表启用RLS

sql

ALTER TABLE public.loads ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.carriers ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.invoices ENABLE ROW LEVEL SECURITY;

sql

ALTER TABLE public.loads ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.carriers ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.invoices ENABLE ROW LEVEL SECURITY;

Account-Based Tenant Isolation

基于账户的租户隔离

sql

-- Users see only their account's loads
CREATE POLICY "Users see only their account loads"
    ON public.loads
    FOR SELECT
    TO authenticated
    USING (
        account_id IN (
            SELECT account_id
            FROM public.account_users
            WHERE user_id = (SELECT auth.uid())
        )
    );

-- Users can create loads for their account
CREATE POLICY "Users create loads for their account"
    ON public.loads
    FOR INSERT
    TO authenticated
    WITH CHECK (
        account_id IN (
            SELECT account_id
            FROM public.account_users
            WHERE user_id = (SELECT auth.uid())
        )
    );

-- Users can update their account's loads
CREATE POLICY "Users update their account loads"
    ON public.loads
    FOR UPDATE
    TO authenticated
    USING (
        account_id IN (
            SELECT account_id
            FROM public.account_users
            WHERE user_id = (SELECT auth.uid())
        )
    )
    WITH CHECK (
        account_id IN (
            SELECT account_id
            FROM public.account_users
            WHERE user_id = (SELECT auth.uid())
        )
    );

sql

-- Users see only their account's loads
CREATE POLICY "Users see only their account loads"
    ON public.loads
    FOR SELECT
    TO authenticated
    USING (
        account_id IN (
            SELECT account_id
            FROM public.account_users
            WHERE user_id = (SELECT auth.uid())
        )
    );

-- Users can create loads for their account
CREATE POLICY "Users create loads for their account"
    ON public.loads
    FOR INSERT
    TO authenticated
    WITH CHECK (
        account_id IN (
            SELECT account_id
            FROM public.account_users
            WHERE user_id = (SELECT auth.uid())
        )
    );

-- Users can update their account's loads
CREATE POLICY "Users update their account loads"
    ON public.loads
    FOR UPDATE
    TO authenticated
    USING (
        account_id IN (
            SELECT account_id
            FROM public.account_users
            WHERE user_id = (SELECT auth.uid())
        )
    )
    WITH CHECK (
        account_id IN (
            SELECT account_id
            FROM public.account_users
            WHERE user_id = (SELECT auth.uid())
        )
    );

Role-Based RLS Policies

基于角色的RLS策略

Combine tenant isolation with role restrictions:

sql

-- Helper function to check user roles within account
CREATE OR REPLACE FUNCTION public.user_has_role(required_roles TEXT[])
RETURNS BOOLEAN
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
    SELECT EXISTS (
        SELECT 1
        FROM public.user_roles ur
        JOIN public.roles r ON ur.role_id = r.id
        WHERE ur.user_id = (SELECT auth.uid())
        AND r.name = ANY(required_roles)
    );
$$;

-- Only finance and admin can view invoices
CREATE POLICY "Finance users view invoices"
    ON public.customer_invoices
    FOR SELECT
    TO authenticated
    USING (
        account_id IN (
            SELECT account_id
            FROM public.account_users
            WHERE user_id = (SELECT auth.uid())
        )
        AND public.user_has_role(ARRAY['admin', 'finance', 'readonly'])
    );

-- Only finance and admin can create invoices
CREATE POLICY "Finance users create invoices"
    ON public.customer_invoices
    FOR INSERT
    TO authenticated
    WITH CHECK (
        account_id IN (
            SELECT account_id
            FROM public.account_users
            WHERE user_id = (SELECT auth.uid())
        )
        AND public.user_has_role(ARRAY['admin', 'finance'])
    );

将租户隔离与角色限制结合：

sql

-- Helper function to check user roles within account
CREATE OR REPLACE FUNCTION public.user_has_role(required_roles TEXT[])
RETURNS BOOLEAN
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
    SELECT EXISTS (
        SELECT 1
        FROM public.user_roles ur
        JOIN public.roles r ON ur.role_id = r.id
        WHERE ur.user_id = (SELECT auth.uid())
        AND r.name = ANY(required_roles)
    );
$$;

-- Only finance and admin can view invoices
CREATE POLICY "Finance users view invoices"
    ON public.customer_invoices
    FOR SELECT
    TO authenticated
    USING (
        account_id IN (
            SELECT account_id
            FROM public.account_users
            WHERE user_id = (SELECT auth.uid())
        )
        AND public.user_has_role(ARRAY['admin', 'finance', 'readonly'])
    );

-- Only finance and admin can create invoices
CREATE POLICY "Finance users create invoices"
    ON public.customer_invoices
    FOR INSERT
    TO authenticated
    WITH CHECK (
        account_id IN (
            SELECT account_id
            FROM public.account_users
            WHERE user_id = (SELECT auth.uid())
        )
        AND public.user_has_role(ARRAY['admin', 'finance'])
    );

Driver-Specific Policies

驾驶员专属策略

Drivers see only their assigned loads:

sql

-- Drivers see only loads assigned to them
CREATE POLICY "Drivers see assigned loads"
    ON public.loads
    FOR SELECT
    TO authenticated
    USING (
        -- Driver is assigned to this load
        driver_user_id = (SELECT auth.uid())
        OR
        -- Or user has broader access via role
        (
            account_id IN (
                SELECT account_id
                FROM public.account_users
                WHERE user_id = (SELECT auth.uid())
            )
            AND public.user_has_role(ARRAY['admin', 'dispatcher', 'sales', 'readonly'])
        )
    );

驾驶员仅能查看分配给自己的运单：

sql

-- Drivers see only loads assigned to them
CREATE POLICY "Drivers see assigned loads"
    ON public.loads
    FOR SELECT
    TO authenticated
    USING (
        -- Driver is assigned to this load
        driver_user_id = (SELECT auth.uid())
        OR
        -- Or user has broader access via role
        (
            account_id IN (
                SELECT account_id
                FROM public.account_users
                WHERE user_id = (SELECT auth.uid())
            )
            AND public.user_has_role(ARRAY['admin', 'dispatcher', 'sales', 'readonly'])
        )
    );

Authorization Decision Patterns

授权决策模式

HTTP Status Code Guidelines

HTTP状态码指南

Scenario	Status Code	When to Use
Missing or invalid token	`401 Unauthorized`	Token absent, expired, or malformed
Valid token, insufficient permissions	`403 Forbidden`	User authenticated but lacks required role/permission
Resource not found (or hidden)	`404 Not Found`	Resource doesn't exist OR hiding existence is security concern

场景	状态码	适用情况
令牌缺失或无效	`401 Unauthorized`	令牌不存在、过期或格式错误
令牌有效但权限不足	`403 Forbidden`	用户已认证但缺少所需角色/权限
资源不存在（或隐藏）	`404 Not Found`	资源不存在，或出于安全考虑隐藏其存在

Security-Aware 404 Pattern

安全友好的404模式

Use 404 instead of 403 when revealing resource existence is a security concern:

func GetLoad(c echo.Context) error {
    loadID := c.Param("id")
    accountID := c.Get(string(middleware.ContextKeyAccountID)).(string)

    load, err := repo.GetLoad(c.Request().Context(), loadID)
    if err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            // Resource doesn't exist
            return echo.NewHTTPError(http.StatusNotFound, "load not found")
        }
        return echo.NewHTTPError(http.StatusInternalServerError, "failed to fetch load")
    }

    // Check tenant ownership - return 404 to hide existence
    if load.AccountID != accountID {
        return echo.NewHTTPError(http.StatusNotFound, "load not found")
    }

    return c.JSON(http.StatusOK, load)
}

当暴露资源存在性会带来安全风险时，使用404而非403：

func GetLoad(c echo.Context) error {
    loadID := c.Param("id")
    accountID := c.Get(string(middleware.ContextKeyAccountID)).(string)

    load, err := repo.GetLoad(c.Request().Context(), loadID)
    if err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            // Resource doesn't exist
            return echo.NewHTTPError(http.StatusNotFound, "load not found")
        }
        return echo.NewHTTPError(http.StatusInternalServerError, "failed to fetch load")
    }

    // Check tenant ownership - return 404 to hide existence
    if load.AccountID != accountID {
        return echo.NewHTTPError(http.StatusNotFound, "load not found")
    }

    return c.JSON(http.StatusOK, load)
}

Error Response Structure

错误响应结构

type ErrorResponse struct {
    Error   string `json:"error"`
    Code    string `json:"code,omitempty"`
    Details string `json:"details,omitempty"`
}

// Authorization error examples
// 401: {"error": "missing authorization header", "code": "AUTH_REQUIRED"}
// 401: {"error": "invalid or expired token", "code": "TOKEN_INVALID"}
// 403: {"error": "insufficient role permissions", "code": "ROLE_REQUIRED"}
// 403: {"error": "permission denied", "code": "PERMISSION_DENIED"}

type ErrorResponse struct {
    Error   string `json:"error"`
    Code    string `json:"code,omitempty"`
    Details string `json:"details,omitempty"`
}

// Authorization error examples
// 401: {"error": "missing authorization header", "code": "AUTH_REQUIRED"}
// 401: {"error": "invalid or expired token", "code": "TOKEN_INVALID"}
// 403: {"error": "insufficient role permissions", "code": "ROLE_REQUIRED"}
// 403: {"error": "permission denied", "code": "PERMISSION_DENIED"}

JWT Claims Best Practices

JWT声明最佳实践

Minimal Claims for Performance

性能优先的极简声明

Include only essential claims; derive others from database:

type MinimalClaims struct {
    jwt.RegisteredClaims
    UserID    string `json:"sub"`       // Use standard 'sub' claim
    AccountID string `json:"account_id"`
    Roles     []string `json:"roles"`    // Include for middleware checks
}

仅包含必要声明，其他信息从数据库获取：

type MinimalClaims struct {
    jwt.RegisteredClaims
    UserID    string `json:"sub"`       // 使用标准'sub'声明
    AccountID string `json:"account_id"`
    Roles     []string `json:"roles"`    // 用于中间件校验
}

Full Claims with Permissions

包含权限的完整声明

For reduced database lookups, include permissions:

type FullClaims struct {
    jwt.RegisteredClaims
    UserID      string   `json:"sub"`
    Email       string   `json:"email"`
    AccountID   string   `json:"account_id"`
    AccountName string   `json:"account_name"`
    Roles       []string `json:"roles"`
    Permissions []string `json:"permissions"` // Flattened from roles
}

为减少数据库查询，可在声明中包含权限：

type FullClaims struct {
    jwt.RegisteredClaims
    UserID      string   `json:"sub"`
    Email       string   `json:"email"`
    AccountID   string   `json:"account_id"`
    AccountName string   `json:"account_name"`
    Roles       []string `json:"roles"`
    Permissions []string `json:"permissions"` // 从角色中提取的扁平化权限
}

Token Generation

令牌生成

func GenerateToken(user *User, account *Account, roles []string, permissions []string) (string, error) {
    now := time.Now()
    claims := &FullClaims{
        RegisteredClaims: jwt.RegisteredClaims{
            Subject:   user.ID,
            IssuedAt:  jwt.NewNumericDate(now),
            ExpiresAt: jwt.NewNumericDate(now.Add(24 * time.Hour)),
            Issuer:    "laneweavertms",
        },
        UserID:      user.ID,
        Email:       user.Email,
        AccountID:   account.ID,
        AccountName: account.Name,
        Roles:       roles,
        Permissions: permissions,
    }

    token := jwt.NewWithClaims(jwt.SigningMethodHS256, claims)
    return token.SignedString(jwtSecret)
}

func GenerateToken(user *User, account *Account, roles []string, permissions []string) (string, error) {
    now := time.Now()
    claims := &FullClaims{
        RegisteredClaims: jwt.RegisteredClaims{
            Subject:   user.ID,
            IssuedAt:  jwt.NewNumericDate(now),
            ExpiresAt: jwt.NewNumericDate(now.Add(24 * time.Hour)),
            Issuer:    "laneweavertms",
        },
        UserID:      user.ID,
        Email:       user.Email,
        AccountID:   account.ID,
        AccountName: account.Name,
        Roles:       roles,
        Permissions: permissions,
    }

    token := jwt.NewWithClaims(jwt.SigningMethodHS256, claims)
    return token.SignedString(jwtSecret)
}

Authorization Checklist

授权检查清单

RBAC Schema:
[ ] roles, permissions, role_permissions tables created
[ ] user_roles table includes account_id for multi-tenant
[ ] account_users table for tenant membership
[ ] Indexes on user_id, account_id columns for RLS performance

Middleware Chain:
[ ] JWT validation middleware extracts claims to context
[ ] Tenant context middleware validates account_id
[ ] Role middleware checks user roles array
[ ] Permission middleware checks specific resource:action

RLS Policies:
[ ] RLS enabled on all tenant-owned tables
[ ] SELECT policies use account_id IN (SELECT from account_users)
[ ] INSERT policies use WITH CHECK for account_id
[ ] UPDATE policies use both USING and WITH CHECK
[ ] auth.uid() wrapped in SELECT for query plan caching
[ ] Indexes exist on columns used in RLS conditions

JWT Claims:
[ ] Token includes user_id, account_id, roles
[ ] Token expiration set appropriately (e.g., 24 hours)
[ ] Refresh token mechanism for long-lived sessions

Error Handling:
[ ] 401 for missing/invalid authentication
[ ] 403 for valid auth but insufficient permissions
[ ] 404 when hiding resource existence is security concern
[ ] Error responses don't leak sensitive information

RBAC Schema:
[ ] 已创建roles、permissions、role_permissions表
[ ] user_roles表包含用于多租户的account_id字段
[ ] 已创建account_users表用于租户成员管理
[ ] 为RLS性能优化，已在user_id、account_id列创建索引

Middleware Chain:
[ ] JWT验证中间件已将声明提取到上下文
[ ] 租户上下文中间件已验证account_id
[ ] 角色中间件已检查用户角色数组
[ ] 权限中间件已检查特定的resource:action

RLS Policies:
[ ] 所有租户拥有的表已启用RLS
[ ] SELECT策略使用account_id IN (SELECT from account_users)
[ ] INSERT策略使用WITH CHECK校验account_id
[ ] UPDATE策略同时使用USING和WITH CHECK
[ ] auth.uid()包裹在SELECT中以优化查询计划缓存
[ ] RLS条件中使用的列已创建索引

JWT Claims:
[ ] 令牌包含user_id、account_id、roles
[ ] 已设置合适的令牌过期时间（如24小时）
[ ] 已实现长会话的刷新令牌机制

Error Handling:
[ ] 针对缺失/无效认证返回401
[ ] 针对认证有效但权限不足返回403
[ ] 当隐藏资源存在性是安全需求时返回404
[ ] 错误响应不泄露敏感信息