c77_rbac_laravel/README.md

# c77_rbac_laravel

A PostgreSQL extension that provides Laravel integration for the `c77_rbac` Role-Based Access Control system. This extension simplifies the use of `c77_rbac` in Laravel applications by providing Laravel-specific functions and utilities.

## Prerequisites

- PostgreSQL 13 or later (tested on 17)
- `c77_rbac` extension must be installed
- Laravel 9.0 or later

## Installation

### Step 1: Install the Extension

Copy the extension files to your PostgreSQL extension directory:

```bash
sudo cp c77_rbac_laravel--1.0.sql c77_rbac_laravel.control /usr/share/postgresql/17/extension/
```

### Step 2: Create/Connect to Your Database

Ensure you have a database with the `c77_rbac` extension already installed:

```sql
-- Connect to your database
\c your_database_name

-- Verify c77_rbac is installed
SELECT * FROM pg_extension WHERE extname = 'c77_rbac';
```

### Step 3: Enable the Laravel Extension

```sql
CREATE EXTENSION c77_rbac_laravel;
```

## Features

The extension provides two key functions:

1. **c77_rbac_laravel_auth_id()**
    - Retrieves the current Laravel user ID from the session variable
    - Used in RLS policies to identify the current user
    - Returns NULL if the session variable is not set

2. **c77_rbac_laravel_assign_user()**
    - Assigns a Laravel user to a role with optional scope
    - Automatically converts Laravel's integer IDs to the text format used by `c77_rbac`

## Integration with Laravel

### Middleware Setup

Create a middleware to set the current user's ID as the `c77_rbac.external_id` session variable:

```php
<?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()]);
        }
        return $next($request);
    }
}
```

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

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

### Setting Up RBAC in Migrations

```php
<?php

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

class SetupRbacRoles extends Migration
{
    public function up()
    {
        // Assign an admin user
        DB::statement("SELECT public.c77_rbac_laravel_assign_user(?, ?, ?, ?)", [
            1, // User ID
            'admin',
            NULL,
            NULL
        ]);
        
        // Grant features to the admin role
        DB::statement("SELECT public.c77_rbac_grant_feature(?, ?)", [
            'admin', 'view_dashboard'
        ]);
        
        // Assign a regular user to a scoped role
        DB::statement("SELECT public.c77_rbac_laravel_assign_user(?, ?, ?, ?)", [
            2, // User ID
            'editor',
            'department',
            'marketing'
        ]);
        
        // Grant features to the editor role
        DB::statement("SELECT public.c77_rbac_grant_feature(?, ?)", [
            'editor', 'edit_content'
        ]);
    }

    public function down()
    {
        // Cleanup code if needed
    }
}
```

### Creating RLS Policies

Apply RLS policies that use the Laravel user ID:

```php
DB::statement("
    CREATE POLICY department_access_policy ON documents
    FOR ALL
    TO PUBLIC
    USING (public.c77_rbac_can_access('edit_content', public.c77_rbac_laravel_auth_id(), 'department', department))
");
```

## Usage Examples

### Checking Access in Queries

Once the middleware is active, your Laravel queries will automatically respect RLS policies:

```php
// This query will only return rows the user has access to based on RLS
$documents = DB::table('documents')->get();
```

### Manual Access Checks

You can also perform manual access checks in your code:

```php
$userId = Auth::id();
$canAccess = DB::selectOne("
    SELECT public.c77_rbac_can_access(?, ?, ?, ?) AS result
", ['edit_content', $userId, 'department', 'marketing'])->result;

if ($canAccess) {
    // Allow the action
} else {
    // Deny the action
}
```

## Connection Pooling Considerations

If you're using connection pooling (e.g., PgBouncer), be aware that session variables like `c77_rbac.external_id` persist for the duration of the database connection. For session pooling to work correctly:

1. Ensure your pooling is configured in "Session" mode, not "Transaction" mode
2. Set the external ID at the beginning of each request via middleware
3. Consider using a cleanup middleware for long-lived connections

## Troubleshooting

### No Rows Returned

If your queries return no rows when you expect data:

1. Verify the middleware is setting the external ID:
   ```php
   $externalId = DB::selectOne("SELECT current_setting('c77_rbac.external_id', true) AS id")->id;
   ```

2. Check if your user has the required role and feature:
   ```php
   $hasAccess = DB::selectOne("
       SELECT public.c77_rbac_can_access(?, ?, ?, ?) AS result
   ", ['required_feature', Auth::id(), 'scope_type', 'scope_id'])->result;
   ```

### Database Errors

For "undefined_object" errors related to `c77_rbac.external_id`, ensure your middleware is running and setting the variable correctly.

## License

MIT License. See `LICENSE` file for details.

## Related Projects

- [c77_rbac](https://github.com/yourusername/c77_rbac) - The core RBAC extension for PostgreSQL

---

For more detailed information on the underlying RBAC system, please refer to the documentation for the `c77_rbac` extension.