c77_rbac_laravel/USEAGE.md

# c77_rbac_laravel Usage Guide

This guide provides detailed instructions for using the `c77_rbac_laravel` PostgreSQL extension, which integrates the `c77_rbac` Role-Based Access Control system with Laravel applications. The guide assumes you have already installed both the `c77_rbac` and `c77_rbac_laravel` extensions.

## Table of Contents

1. [Extension Functions](#extension-functions)
2. [Laravel Integration Setup](#laravel-integration-setup)
3. [Common Usage Patterns](#common-usage-patterns)
4. [Advanced Usage](#advanced-usage)
5. [Best Practices](#best-practices)
6. [Troubleshooting](#troubleshooting)

## Extension Functions

The `c77_rbac_laravel` extension provides two key functions that bridge Laravel's authentication system with PostgreSQL's RBAC implementation:

### c77_rbac_laravel_auth_id()

Retrieves the current Laravel user ID from the session variable.

```sql
SELECT public.c77_rbac_laravel_auth_id();
```

- **Returns**: TEXT value of the current user's ID, or NULL if not set
- **Use Case**: Used within RLS policies to identify the current user
- **Example**:
  ```sql
  CREATE POLICY user_data_policy ON user_data 
  FOR ALL TO PUBLIC 
  USING (user_id::text = public.c77_rbac_laravel_auth_id());
  ```

### c77_rbac_laravel_assign_user()

Assigns a Laravel user to a role with optional scope.

```sql
SELECT public.c77_rbac_laravel_assign_user(
    p_laravel_id BIGINT,    -- Laravel user ID (integer)
    p_role_name TEXT,       -- Role name to assign
    p_scope_type TEXT,      -- Optional scope type
    p_scope_id TEXT         -- Optional scope ID
);
```

- **Parameters**:
    - `p_laravel_id`: Laravel's integer user ID (will be converted to TEXT)
    - `p_role_name`: Name of the role to assign
    - `p_scope_type`: Scope category (can be NULL)
    - `p_scope_id`: Scope value (can be NULL)
- **Example**:
  ```sql
  -- Assign user ID 1 to the 'editor' role for the 'marketing' department
  SELECT public.c77_rbac_laravel_assign_user(1, 'editor', 'department', 'marketing');
  
  -- Assign user ID 2 to the global 'admin' role
  SELECT public.c77_rbac_laravel_assign_user(2, 'admin', NULL, NULL);
  ```

## Laravel Integration Setup

### Setting Up the Middleware

Create a middleware to set the `c77_rbac.external_id` session variable for each request:

```php
<?php
// app/Http/Middleware/SetRbacExternalId.php

namespace App\Http\Middleware;

use Closure;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

class SetRbacExternalId
{
    public function handle($request, Closure $next)
    {
        if (Auth::check()) {
            DB::statement('SET "c77_rbac.external_id" TO ?', [Auth::id()]);
        } else {
            // Optional: Reset the external_id if no user is logged in
            DB::statement('RESET "c77_rbac.external_id"');
        }
        
        return $next($request);
    }
}
```

Register this middleware in your `app/Http/Kernel.php`:

```php
protected $middlewareGroups = [
    'web' => [
        // Other middleware...
        \App\Http\Middleware\SetRbacExternalId::class,
    ],
];
```

### Database Migrations

Create migrations to set up your RBAC rules:

```php
<?php
// database/migrations/2025_04_12_000001_setup_rbac_roles.php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

class SetupRbacRoles extends Migration
{
    public function up()
    {
        // Create roles
        $roles = ['admin', 'editor', 'viewer'];
        foreach ($roles as $role) {
            DB::statement("
                INSERT INTO public.c77_rbac_roles (name) 
                VALUES (?) ON CONFLICT (name) DO NOTHING
            ", [$role]);
        }
        
        // Define features
        $features = [
            'view_dashboard', 'edit_content', 'publish_content', 
            'manage_users', 'view_reports'
        ];
        
        foreach ($features as $feature) {
            DB::statement("
                INSERT INTO public.c77_rbac_features (name) 
                VALUES (?) ON CONFLICT (name) DO NOTHING
            ", [$feature]);
        }
        
        // Grant features to roles
        $roleFeatures = [
            'admin' => ['view_dashboard', 'edit_content', 'publish_content', 'manage_users', 'view_reports'],
            'editor' => ['view_dashboard', 'edit_content', 'publish_content', 'view_reports'],
            'viewer' => ['view_dashboard', 'view_reports']
        ];
        
        foreach ($roleFeatures as $role => $features) {
            foreach ($features as $feature) {
                DB::statement("
                    SELECT public.c77_rbac_grant_feature(?, ?)
                ", [$role, $feature]);
            }
        }
    }

    public function down()
    {
        // Clean up if needed
        DB::statement("TRUNCATE public.c77_rbac_role_features CASCADE");
    }
}
```

```php
<?php
// database/migrations/2025_04_12_000002_assign_initial_users.php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
use App\Models\User;

class AssignInitialUsers extends Migration
{
    public function up()
    {
        // Find admin user(s)
        $adminUsers = User::where('is_admin', true)->get();
        foreach ($adminUsers as $admin) {
            DB::statement("
                SELECT public.c77_rbac_laravel_assign_user(?, ?, ?, ?)
            ", [$admin->id, 'admin', NULL, NULL]);
        }
        
        // Assign department editors
        $editors = User::where('role', 'editor')->get();
        foreach ($editors as $editor) {
            DB::statement("
                SELECT public.c77_rbac_laravel_assign_user(?, ?, ?, ?)
            ", [$editor->id, 'editor', 'department', $editor->department]);
        }
        
        // Assign viewers
        $viewers = User::where('role', 'viewer')->get();
        foreach ($viewers as $viewer) {
            DB::statement("
                SELECT public.c77_rbac_laravel_assign_user(?, ?, ?, ?)
            ", [$viewer->id, 'viewer', 'department', $viewer->department]);
        }
    }

    public function down()
    {
        // Clean up if needed
        DB::statement("TRUNCATE public.c77_rbac_subject_roles CASCADE");
    }
}
```

### Applying RLS to Tables

Create migrations to enable RLS on your tables:

```php
<?php
// database/migrations/2025_04_12_000003_enable_rls_policies.php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class EnableRlsPolicies extends Migration
{
    public function up()
    {
        // Enable RLS on content table
        DB::statement("ALTER TABLE content ENABLE ROW LEVEL SECURITY");
        
        // Create policy for content access
        DB::statement("
            CREATE POLICY content_access_policy ON content
            FOR ALL
            TO PUBLIC
            USING (
                public.c77_rbac_can_access(
                    'edit_content', 
                    public.c77_rbac_laravel_auth_id(), 
                    'department', 
                    department
                )
            )
        ");
        
        // Enable RLS on users table
        DB::statement("ALTER TABLE users ENABLE ROW LEVEL SECURITY");
        
        // Create policy for user management
        DB::statement("
            CREATE POLICY user_management_policy ON users
            FOR ALL
            TO PUBLIC
            USING (
                public.c77_rbac_can_access(
                    'manage_users', 
                    public.c77_rbac_laravel_auth_id(), 
                    NULL,
                    NULL
                )
            )
        ");
    }

    public function down()
    {
        // Drop policies
        DB::statement("DROP POLICY IF EXISTS content_access_policy ON content");
        DB::statement("DROP POLICY IF EXISTS user_management_policy ON users");
        
        // Disable RLS
        DB::statement("ALTER TABLE content DISABLE ROW LEVEL SECURITY");
        DB::statement("ALTER TABLE users DISABLE ROW LEVEL SECURITY");
    }
}
```

## Common Usage Patterns

### User Registration

When registering new users, assign them appropriate roles:

```php
public function register(Request $request)
{
    // Validate request and create user
    $user = User::create([
        'name' => $request->name,
        'email' => $request->email,
        'password' => Hash::make($request->password),
        'department' => $request->department,
    ]);
    
    // Assign default role with department scope
    DB::statement("
        SELECT public.c77_rbac_laravel_assign_user(?, ?, ?, ?)
    ", [$user->id, 'viewer', 'department', $user->department]);
    
    // Continue with registration process...
}
```

### Role Management

Create a controller for managing user roles:

```php
public function assignRole(User $user, Request $request)
{
    $request->validate([
        'role' => 'required|string|exists:c77_rbac_roles,name',
        'scope_type' => 'nullable|string',
        'scope_id' => 'nullable|string',
    ]);
    
    DB::statement("
        SELECT public.c77_rbac_laravel_assign_user(?, ?, ?, ?)
    ", [
        $user->id,
        $request->role,
        $request->scope_type,
        $request->scope_id
    ]);
    
    return back()->with('success', 'Role assigned successfully');
}
```

### Checking Permissions in Controllers

Check if a user has a specific permission:

```php
public function edit(Content $content)
{
    $canEdit = DB::selectOne("
        SELECT public.c77_rbac_can_access(?, ?, ?, ?) AS result
    ", ['edit_content', Auth::id(), 'department', $content->department])->result;
    
    if (!$canEdit) {
        abort(403, 'Unauthorized action.');
    }
    
    return view('content.edit', compact('content'));
}
```

### Blade Directives

Create custom Blade directives for permission checks:

```php
// In AppServiceProvider.php
public function boot()
{
    Blade::directive('can', function ($expression) {
        list($feature, $scopeType, $scopeId) = explode(',', $expression);
        
        return "<?php if(DB::selectOne(\"
            SELECT public.c77_rbac_can_access(?, ?, ?, ?) AS result
        \", [{$feature}, auth()->id(), {$scopeType}, {$scopeId}])->result): ?>";
    });
    
    Blade::directive('endcan', function () {
        return "<?php endif; ?>";
    });
}
```

Usage in Blade:

```blade
@can('edit_content', 'department', $content->department)
    <a href="{{ route('content.edit', $content) }}" class="btn btn-primary">Edit</a>
@endcan
```

## Advanced Usage

### Batch User Assignments

For batch operations, use a transaction to improve performance:

```php
DB::transaction(function () use ($users, $role, $scopeType, $scopeId) {
    foreach ($users as $user) {
        DB::statement("
            SELECT public.c77_rbac_laravel_assign_user(?, ?, ?, ?)
        ", [$user->id, $role, $scopeType, $scopeId]);
    }
});
```

### Dynamic Scope Resolution

Create a helper for dynamic scope resolution:

```php
// In a helper file or service
function resolveScope($model)
{
    switch (get_class($model)) {
        case 'App\Models\Department':
            return ['department', $model->id];
        case 'App\Models\Project':
            return ['project', $model->id];
        case 'App\Models\Team':
            return ['team', $model->id];
        default:
            return [null, null];
    }
}

// Usage
list($scopeType, $scopeId) = resolveScope($department);
DB::statement("
    SELECT public.c77_rbac_laravel_assign_user(?, ?, ?, ?)
", [$user->id, 'manager', $scopeType, $scopeId]);
```

### Command-Line Role Management

Create an Artisan command for role management:

```php
<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Models\User;
use Illuminate\Support\Facades\DB;

class AssignUserRole extends Command
{
    protected $signature = 'rbac:assign-role {user} {role} {--scope-type=} {--scope-id=}';
    protected $description = 'Assign a role to a user with optional scope';

    public function handle()
    {
        $user = User::findOrFail($this->argument('user'));
        $role = $this->argument('role');
        $scopeType = $this->option('scope-type');
        $scopeId = $this->option('scope-id');
        
        DB::statement("
            SELECT public.c77_rbac_laravel_assign_user(?, ?, ?, ?)
        ", [$user->id, $role, $scopeType, $scopeId]);
        
        $this->info("Role '$role' assigned to user #{$user->id}");
        
        return 0;
    }
}
```

Usage:

```bash
php artisan rbac:assign-role 1 editor --scope-type=department --scope-id=marketing
```

## Best Practices

### Connection Management

1. **Connection Pooling**: If using connection pooling (like PgBouncer), always reset the `c77_rbac.external_id` variable at the end of each request:

```php
// In a middleware or terminating callback
public function terminate($request, $response)
{
    DB::statement('RESET "c77_rbac.external_id"');
}
```

2. **Long-running Jobs**: For queued jobs, set the external ID at the beginning of the job:

```php
public function handle()
{
    // Set the RBAC external ID for this job
    DB::statement('SET "c77_rbac.external_id" TO ?', [$this->user_id]);
    
    // Job logic...
    
    // Reset when done
    DB::statement('RESET "c77_rbac.external_id"');
}
```

### Security

1. **Middleware Priority**: Ensure your RBAC middleware runs early in the request cycle to protect all database queries.

2. **Authorization Wrapper**: Create a service for all authorization checks:

```php
// app/Services/RbacService.php
class RbacService
{
    public function can($feature, $scopeType = null, $scopeId = null)
    {
        $userId = auth()->id();
        if (!$userId) return false;
        
        return DB::selectOne("
            SELECT public.c77_rbac_can_access(?, ?, ?, ?) AS result
        ", [$feature, $userId, $scopeType, $scopeId])->result;
    }
    
    public function assignRole($userId, $role, $scopeType = null, $scopeId = null)
    {
        DB::statement("
            SELECT public.c77_rbac_laravel_assign_user(?, ?, ?, ?)
        ", [$userId, $role, $scopeType, $scopeId]);
    }
}
```

3. **Policy Integration**: Integrate with Laravel's policy system:

```php
// app/Policies/ContentPolicy.php
class ContentPolicy
{
    protected $rbac;
    
    public function __construct(RbacService $rbac)
    {
        $this->rbac = $rbac;
    }
    
    public function update(User $user, Content $content)
    {
        return $this->rbac->can('edit_content', 'department', $content->department);
    }
}
```

### Performance

1. **Eager Loading**: When loading related models that will be filtered by RLS, use eager loading with care:

```php
// This will load ALL departments first, then filter
$user->load('departments');

// Better approach for large datasets
$departments = Department::all(); // Already filtered by RLS
```

2. **Cache Role Assignments**: Cache role assignments for frequently accessed permissions:

```php
public function can($feature, $scopeType = null, $scopeId = null)
{
    $cacheKey = "rbac:{$feature}:{$scopeType}:{$scopeId}:" . auth()->id();
    
    return Cache::remember($cacheKey, now()->addMinutes(10), function () use ($feature, $scopeType, $scopeId) {
        return DB::selectOne("
            SELECT public.c77_rbac_can_access(?, ?, ?, ?) AS result
        ", [$feature, auth()->id(), $scopeType, $scopeId])->result;
    });
}
```

## Troubleshooting

### Common Issues

1. **No Data Returned**

   **Symptom**: Queries return empty results when you expect data.

   **Possible Causes**:
    - External ID not set
    - User doesn't have required permissions
    - RLS policy incorrectly configured

   **Solution**:
   ```php
   // Check if external ID is set
   $externalId = DB::selectOne("SELECT current_setting('c77_rbac.external_id', true) AS id")->id;
   
   // Check direct permissions
   $hasPermission = DB::selectOne("
       SELECT public.c77_rbac_can_access(?, ?, ?, ?) AS result
   ", ['required_feature', auth()->id(), 'scope_type', 'scope_id'])->result;
   
   // Verify RLS policy
   $policyExists = DB::selectOne("
       SELECT EXISTS (
           SELECT 1 FROM pg_policies 
           WHERE tablename = 'your_table' AND policyname = 'your_policy'
       ) AS exists
   ")->exists;
   ```

2. **Permission Denied Errors**

   **Symptom**: PostgreSQL returns permission denied errors.

   **Possible Causes**:
    - Database user doesn't have execute permission on functions
    - RLS preventing access to tables

   **Solution**:
   ```sql
   -- Grant execute permission
   GRANT EXECUTE ON FUNCTION public.c77_rbac_laravel_auth_id() TO app_user;
   GRANT EXECUTE ON FUNCTION public.c77_rbac_laravel_assign_user(BIGINT, TEXT, TEXT, TEXT) TO app_user;
   
   -- Check for explicit denials in RLS
   SELECT tablename, policyname, cmd, qual 
   FROM pg_policies 
   WHERE tablename = 'your_table';
   ```

3. **Middleware Not Working**

   **Symptom**: RBAC checks fail despite user being logged in.

   **Possible Causes**:
    - Middleware not registered or running
    - Auth system not initialized when middleware runs

   **Solution**:
    - Check middleware registration in Kernel.php
    - Add debug code to verify middleware execution:
   ```php
   // In your middleware
   public function handle($request, Closure $next)
   {
       \Log::debug('RBAC Middleware - User: ' . (Auth::check() ? Auth::id() : 'Guest'));
       
       if (Auth::check()) {
           DB::statement('SET "c77_rbac.external_id" TO ?', [Auth::id()]);
           \Log::debug('Set c77_rbac.external_id to ' . Auth::id());
       }
       
       return $next($request);
   }
   ```

4. **Role Assignment Issues**

   **Symptom**: User assignments aren't taking effect.

   **Possible Causes**:
    - Transaction issues
    - Scopes not matching exactly

   **Solution**:
    - Ensure transactions are committed
    - Check for exact scope matches:
   ```sql
   SELECT * FROM c77_rbac_subject_roles 
   WHERE subject_id = (
       SELECT subject_id FROM c77_rbac_subjects 
       WHERE external_id = '1'
   );
   ```

For persistent issues, you can enable PostgreSQL query logging to see exactly what's happening:

```php
// In a debug route or controller
DB::listen(function ($query) {
    \Log::debug($query->sql, [
        'bindings' => $query->bindings,
        'time' => $query->time
    ]);
});
```