# c77_secure_db v2.0 - Installation & Quick Start Guide

## Overview

The c77_secure_db extension provides enterprise-grade database security with:
- **Token-based authorization** (no more session variable bypasses!)
- **Content hashing** for tamper detection
- **Optional c77_rbac integration** for advanced permissions
- **Comprehensive audit logging**
- **Automatic trigger management**

## Prerequisites

- PostgreSQL 14 or later
- pgcrypto extension
- Superuser access for installation
- Optional: c77_rbac extension for advanced permissions

## Installation

### 1. Copy Extension Files

```bash
# Copy control file
sudo cp c77_secure_db.control $(pg_config --sharedir)/extension/

# Copy SQL file  
sudo cp c77_secure_db--1.0.sql $(pg_config --sharedir)/extension/
```

### 2. Install Dependencies

```sql
-- Connect as superuser
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Optional: Install c77_rbac for advanced permissions
-- CREATE EXTENSION IF NOT EXISTS c77_rbac;
```

### 3. Install the Extension (Superuser Required)

```sql
-- Connect as PostgreSQL superuser
sudo -u postgres psql

-- Install the extension
CREATE EXTENSION c77_secure_db;

-- Verify correct version
SELECT extname, extversion FROM pg_extension WHERE extname = 'c77_secure_db';
-- Should show: c77_secure_db | 1.0

-- Verify installation
SELECT c77_secure_db_health_check();
```

### 4. Run Security Tests (Superuser)

```sql
-- CRITICAL: Run tests to verify security works
SELECT c77_secure_db_run_all_tests();

-- This should return: "overall_status": "ALL_TESTS_PASSED"
-- If not, DO NOT use in production!
```

## Quick Start

### 1. Set Up Your Application User (Superuser)

```sql
-- Create your application user and grant secure access
CREATE USER myapp_user WITH PASSWORD 'secure_password';
GRANT c77_secure_db_user TO myapp_user;

-- Now connect as your application user for regular operations
\c your_database myapp_user
```

### 2. Create a Secure Schema

```sql
-- Create your application schema
CREATE SCHEMA myapp;

-- Register it as secure (this auto-applies triggers)
SELECT c77_secure_db_manage_secure_schemas('add', 'myapp');
```

### 3. Create Secure Tables

```sql
-- Create tables with required security columns
CREATE TABLE myapp.users (
                             id BIGSERIAL PRIMARY KEY,
                             name TEXT NOT NULL,
                             email TEXT UNIQUE NOT NULL,
    -- Security columns (required for tamper detection)
                             content_hash TEXT,
                             hash_version INTEGER DEFAULT 1,
                             created_at TIMESTAMPTZ DEFAULT NOW(),
                             updated_at TIMESTAMPTZ DEFAULT NOW(),
                             deleted_at TIMESTAMPTZ -- Optional: for soft deletes
);

-- Triggers are automatically applied to new tables in secure schemas!
```

### 4. Perform Secure Operations

```sql
-- INSERT: Use the secure operation function
SELECT c77_secure_db_operation(jsonb_build_object(
        'schema_name', 'myapp',
        'table_name', 'users',
        'operation', 'insert',
        'data', jsonb_build_object(
                'name', 'John Doe',
                'email', 'john@example.com'
                )
                               ));

-- UPDATE: Include the primary key in data
SELECT c77_secure_db_operation(jsonb_build_object(
        'schema_name', 'myapp',
        'table_name', 'users',
        'operation', 'update',
        'data', jsonb_build_object(
                'id', 1,
                'name', 'John Smith',
                'email', 'john.smith@example.com'
                )
                               ));

-- SOFT DELETE: Mark as deleted (preserves data)
SELECT c77_secure_db_operation(jsonb_build_object(
        'schema_name', 'myapp',
        'table_name', 'users',
        'operation', 'soft_delete',
        'data', jsonb_build_object('id', 1)
                               ));
```

### 5. Verify Data Integrity

```sql
-- Check if a record has been tampered with
SELECT c77_secure_db_check_freshness(
               'myapp',
               'users',
               jsonb_build_object(
                       'id', 1,
                       'name', 'John Smith',
                       'email', 'john.smith@example.com'
               )
       );

-- Verify all records in a table
SELECT c77_secure_db_verify_content_hashes('myapp', 'users');
```

## RBAC Integration (Optional)

If you have c77_rbac installed, you can add permission-based security:

```sql
-- Set up RBAC permissions
SELECT c77_rbac_grant_feature('user_manager', 'secure_db_insert');
SELECT c77_rbac_grant_feature('user_manager', 'secure_db_update');
SELECT c77_rbac_assign_subject('123', 'user_manager', 'department', 'engineering');

-- Set user context in your application
SET "c77_rbac.external_id" TO '123';

-- Use secure operations with RBAC checking
SELECT c77_secure_db_operation(
               jsonb_build_object(
                       'schema_name', 'myapp',
                       'table_name', 'users',
                       'operation', 'insert',
                       'data', jsonb_build_object('name', 'Jane Doe', 'email', 'jane@example.com')
               ),
               true, -- check_rbac = true
               'secure_db_insert' -- required_feature
       );
```

## Laravel Integration

In your Laravel application:

```php
// In your middleware or service provider
DB::statement('SET "c77_rbac.external_id" TO ?', [auth()->id()]);

// Use the secure operation
$result = DB::selectOne('
    SELECT c77_secure_db_operation(?) as result
', [json_encode([
    'schema_name' => 'myapp',
    'table_name' => 'users', 
    'operation' => 'insert',
    'data' => [
        'name' => $request->name,
        'email' => $request->email
    ]
])]);

$response = json_decode($result->result, true);

if (!$response['success']) {
    throw new Exception($response['error']);
}
```

## Security Best Practices

### 1. Never Use Direct SQL
```sql
-- ❌ NEVER DO THIS (will be blocked)
INSERT INTO myapp.users (name, email) VALUES ('John', 'john@example.com');

-- ✅ ALWAYS DO THIS
SELECT c77_secure_db_operation(...);
```

### 2. Regular Integrity Checks
```sql
-- Run this regularly to detect tampering
SELECT c77_secure_db_verify_content_hashes('myapp', 'users');
```

### 3. Monitor Audit Logs
```sql
-- Check recent operations
SELECT * FROM c77_secure_db_operation_audit
WHERE created_at > now() - interval '1 hour'
ORDER BY created_at DESC;

-- Check for errors
SELECT * FROM c77_secure_db_operation_audit
WHERE success = false AND created_at > now() - interval '24 hours';
```

### 4. Regular Maintenance
```sql
-- Clean up expired tokens (run daily)
SELECT c77_secure_db_cleanup_expired_tokens();

-- System health check
SELECT c77_secure_db_health_check();
```

## Troubleshooting

### Common Issues

**Issue**: "Direct modifications are not allowed"
- **Cause**: Trying to use direct SQL instead of secure operations
- **Solution**: Use `c77_secure_db_operation()` function

**Issue**: "RBAC enabled but no user context set"
- **Cause**: RBAC checking enabled but `c77_rbac.external_id` not set
- **Solution**: Set the session variable: `SET "c77_rbac.external_id" TO 'user_id'`

**Issue**: "Insufficient permissions"
- **Cause**: User doesn't have required RBAC feature
- **Solution**: Grant the feature: `SELECT c77_rbac_grant_feature('role', 'feature')`

### Debug Commands

```sql
-- Check if schema is registered as secure
SELECT * FROM c77_secure_db_secure_schemas;

-- Check recent operations
SELECT * FROM c77_secure_db_operation_audit ORDER BY created_at DESC LIMIT 10;

-- Test RBAC integration
SELECT c77_secure_db_test_rbac_integration();

-- Get operation template for your table
SELECT c77_secure_db_get_operation_template('myapp', 'users', 'insert');
```

## Advanced Features

### Custom Hash Exclusions

You can exclude specific columns from hash calculation by adding a comment to the `content_hash` column:

```sql
-- Exclude 'last_login' from hash calculation
COMMENT ON COLUMN myapp.users.content_hash IS
    '{"exclude_hash_columns": ["last_login", "login_count"]}';
```

### Bulk Operations

For processing multiple records efficiently:

```sql
-- Bulk freshness check
SELECT c77_secure_db_check_freshness_bulk(
               'myapp',
               'users',
               '[
                   {"id": 1, "name": "John", "email": "john@example.com"},
                   {"id": 2, "name": "Jane", "email": "jane@example.com"}
               ]'::jsonb
       );
```

### Operation Templates

Generate SQL templates for your tables:

```sql
-- Get template for insert operation
SELECT c77_secure_db_get_operation_template('myapp', 'users', 'insert');

-- Get template for update operation  
SELECT c77_secure_db_get_operation_template('myapp', 'users', 'update');
```

## Migration from v1.0

If you're upgrading from the old vulnerable version:

```sql
-- 1. Drop the old extension (backup your data first!)
DROP EXTENSION c77_secure_db CASCADE;

-- 2. Install the new version
CREATE EXTENSION c77_secure_db;

-- 3. Re-register your secure schemas
SELECT c77_secure_db_manage_secure_schemas('add', 'your_schema');

-- 4. Run security tests to verify everything works
SELECT c77_secure_db_run_all_tests();
```

## Performance Tuning

### Indexes for Large Tables

```sql
-- Add indexes for better performance on large audit tables
CREATE INDEX CONCURRENTLY idx_audit_user_time
    ON c77_secure_db_operation_audit(user_name, created_at);

CREATE INDEX CONCURRENTLY idx_audit_schema_table_time
    ON c77_secure_db_operation_audit(schema_name, table_name, created_at);
```

### Token Cleanup

Set up automatic token cleanup:

```sql
-- Add to your daily maintenance script
SELECT c77_secure_db_cleanup_expired_tokens();

-- Or use pg_cron if available
SELECT cron.schedule('cleanup-secure-db-tokens', '0 2 * * *', 'SELECT c77_secure_db_cleanup_expired_tokens();');
```

## Monitoring and Alerting

### Key Metrics to Monitor

```sql
-- Error rate (should be very low)
SELECT
    count(*) FILTER (WHERE success = false)::numeric / count(*) * 100 as error_rate_percent
FROM c77_secure_db_operation_audit
WHERE created_at > now() - interval '24 hours';

-- Average execution time
SELECT avg(execution_time_ms) as avg_execution_ms
FROM c77_secure_db_operation_audit
WHERE created_at > now() - interval '1 hour' AND execution_time_ms IS NOT NULL;

-- Active tokens (should be very low, usually 0)
SELECT count(*) as active_tokens
FROM c77_secure_db_auth_tokens
WHERE expires_at > now();
```

### Alert Conditions

Set up alerts for:
- Error rate > 5%
- Average execution time > 1000ms
- More than 100 active tokens
- Any failed operations with "CRITICAL" in the error message

## File Structure

Your extension should have these files:

```
c77_secure_db.control          # Extension control file
c77_secure_db--1.0.sql        # Main extension SQL
```

## Security Architecture

### Token-Based Security
- 5-second expiring tokens
- Single-use only
- Session-specific
- Cannot be bypassed

### Content Hashing
- SHA-256 cryptographic hashes
- Configurable excluded columns
- Automatic hash calculation and verification
- Tamper detection

### Audit Trail
- Every operation logged
- User context tracking
- Performance metrics
- Error details

### RBAC Integration
- Optional but recommended
- Feature-based permissions
- Scope-based access control
- Graceful degradation when not available

## Support and Contributing

For issues or questions:
1. Check the troubleshooting section
2. Run the test suite: `SELECT c77_secure_db_run_all_tests()`
3. Check audit logs for error details
4. Review PostgreSQL logs

Remember: **Security is paramount**. If tests fail, do not use in production until issues are resolved.

---

**Note**: This is a complete rewrite of the extension with security-first design. The old session variable approach has been completely removed and replaced with secure token-based authorization.