c77_secure_db provides database-level security that cannot be bypassed by application bugs or SQL injection attacks. All data modifications go through secure, audited operations with cryptographic tamper detection.

Why c77_secure_db?

🛡️ Unbypassable Security: Token-based authorization prevents all unauthorized access
🔍 Tamper Detection: SHA-256 content hashing detects any unauthorized data changes
📊 Complete Audit Trail: Every operation logged with user context and performance metrics
🔗 RBAC Integration: Seamless integration with c77_rbac extension for advanced permissions
⚡ Production Ready: Optimized for high-performance enterprise workloads
🏗️ Framework Agnostic: Works with Laravel, Django, Node.js, and any PostgreSQL client

🚀 Quick Start

Installation

# Copy files to PostgreSQL extension directory
sudo cp c77_secure_db.control $(pg_config --sharedir)/extension/
sudo cp c77_secure_db--1.0.sql $(pg_config --sharedir)/extension/

-- Install extension (requires superuser)
sudo -u postgres psql
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION c77_secure_db;

-- Verify installation (CRITICAL - must pass!)
SELECT c77_secure_db_run_all_tests();

-- Set up application user
CREATE USER myapp_user WITH PASSWORD 'secure_password';
GRANT c77_secure_db_user TO myapp_user;

Basic Usage

-- Create secure schema
CREATE SCHEMA myapp;
SELECT c77_secure_db_manage_secure_schemas('add', 'myapp');

-- Create secure table
CREATE TABLE myapp.users (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    -- Required security columns
    content_hash TEXT,
    hash_version INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

-- Secure operations (direct SQL is automatically blocked)
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'
    )
));

✨ Key Features

Token-Based Security

5-second expiring tokens prevent replay attacks
Single-use authorization - tokens cannot be reused
Session-specific - tied to database connection
Automatic cleanup - no token buildup

Content Hash Verification

SHA-256 cryptographic hashing for tamper detection
Configurable exclusions - exclude frequently changing columns
Automatic calculation - hashes computed transparently
Bulk verification - check entire tables for integrity

Comprehensive Audit Logging

Every operation logged with complete context
Performance metrics - execution time tracking
User attribution - who did what, when
Error tracking - detailed failure analysis

RBAC Integration

Optional c77_rbac integration - works standalone or with advanced permissions
Feature-based security - granular permission control
Scope-based access - department, region, or custom scopes
Graceful degradation - works without RBAC if not needed

🔧 Advanced Features

Bulk Operations

-- Verify multiple records at once
SELECT c77_secure_db_check_freshness_bulk(
    'myapp', 'users',
    '[{"id":1,"name":"John"},{"id":2,"name":"Jane"}]'::jsonb
);

Hash Verification

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

-- Fix any hash mismatches
SELECT c77_secure_db_verify_content_hashes('myapp', 'users', true);

RBAC-Protected Operations

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

-- Use operation with permission checking
SELECT c77_secure_db_operation(
    jsonb_build_object(...),
    true,                    -- check RBAC
    'secure_db_insert'       -- required permission
);

🏗️ Framework Integration

Laravel

// Service class integration
class SecureDbService {
    public function insert(string $table, array $data): array {
        $result = DB::selectOne('SELECT c77_secure_db_operation(?) as result', [
            json_encode([
                'schema_name' => 'myapp',
                'table_name' => $table,
                'operation' => 'insert',
                'data' => $data
            ])
        ]);
        
        return json_decode($result->result, true);
    }
}

Node.js

// Express integration
const secureDb = {
    async insert(table, data) {
        const result = await pool.query(
            'SELECT c77_secure_db_operation($1) as result',
            [JSON.stringify({
                schema_name: 'myapp',
                table_name: table,
                operation: 'insert', 
                data: data
            })]
        );
        
        return JSON.parse(result.rows[0].result);
    }
};

Django

# Django service integration
class SecureDbService:
    def insert(self, table, data):
        with connection.cursor() as cursor:
            cursor.execute(
                "SELECT c77_secure_db_operation(%s) as result",
                [json.dumps({
                    'schema_name': 'myapp',
                    'table_name': table,
                    'operation': 'insert',
                    'data': data
                })]
            )
            result = cursor.fetchone()[0]
            return json.loads(result)

📊 Monitoring & Maintenance

Health Monitoring

-- System health check
SELECT c77_secure_db_health_check();

-- Performance monitoring
SELECT 
    operation_type,
    avg(execution_time_ms) as avg_time,
    count(*) as operation_count
FROM c77_secure_db_operation_audit 
WHERE created_at > now() - interval '1 hour'
GROUP BY operation_type;

Maintenance

-- Daily cleanup
SELECT c77_secure_db_cleanup_expired_tokens();

-- Weekly integrity check
SELECT c77_secure_db_verify_content_hashes('myapp', 'users');

🛡️ Security Architecture

Multi-Layer Protection

Trigger Layer: Prevents all direct SQL modifications
Token Layer: Authorizes legitimate operations with expiring tokens
Hash Layer: Detects unauthorized data tampering
Audit Layer: Logs all operations for compliance
RBAC Layer: Optional permission-based access control

Threat Mitigation

✅ SQL Injection: Cannot bypass trigger protection
✅ Data Tampering: Detected by content hash verification
✅ Unauthorized Access: Blocked by token validation
✅ Replay Attacks: Prevented by single-use tokens
✅ Session Hijacking: Mitigated by session-specific tokens
✅ Application Bugs: Cannot bypass database-level security

🔄 Migration from v1.x

⚠️ BREAKING CHANGES: Version 2.0 is a complete security rewrite.

The vulnerable session variable approach has been completely removed:

-- ❌ v1.x had this vulnerability (NEVER use this approach)
SET "myapp.allow_direct_modification" TO 'true';  -- Could bypass security!

-- ✅ v2.0 uses secure token-based authorization (unbypassable)
-- All security is handled internally by the extension

Migration Steps:

Backup your data
Drop old extension: DROP EXTENSION c77_secure_db CASCADE;
Install v2.0: CREATE EXTENSION c77_secure_db;
Re-register secure schemas
Run security tests: SELECT c77_secure_db_run_all_tests();

📚 Documentation

USAGE.md - Comprehensive usage guide with examples
INSTALL.md - Detailed installation instructions
CHANGELOG.md - Version history and changes
SECURITY.md - Security policies and reporting

⚡ Performance

Designed for production workloads:

Optimized hash calculations - Efficient SHA-256 implementation
Indexed operations - Fast token lookups and audit queries
Bulk processing - Handle thousands of records efficiently
Minimal overhead - < 10ms typical operation time
Scalable architecture - Tested with millions of records

🧪 Testing

Built-in comprehensive test suite:

-- Run all tests (must pass before production use)
SELECT c77_secure_db_run_all_tests();

-- Security-specific tests
SELECT c77_secure_db_test_security();

-- RBAC integration tests  
SELECT c77_secure_db_test_rbac_integration();

🏢 Production Use Cases

Healthcare

HIPAA compliance with audit trails
Patient data integrity verification
Role-based access by department

Financial Services

Transaction integrity protection
Regulatory audit requirements
Multi-level approval workflows

E-commerce

Customer data protection
Order processing security
Payment data integrity

Government

Classification-based access control
Data integrity verification
Complete audit trails

🤝 Contributing

Contributions are welcome! Please:

Fork the repository
Create a feature branch
Add tests for new functionality
Ensure all tests pass
Submit a pull request

Development Setup

# Clone repository
git clone https://github.com/yourusername/c77_secure_db.git
cd c77_secure_db

# Install in development PostgreSQL
make install

# Run tests
make test

📋 Requirements

PostgreSQL: 14 or later
Extensions: pgcrypto (required), c77_rbac (optional)
Installation: Superuser privileges required for installation
Usage: Regular database users (with granted roles)
Platform: Linux, macOS, Windows (with PostgreSQL)

📝 License

MIT License - see LICENSE file for details.

🆘 Support

Documentation: Check USAGE.md for comprehensive guides
Issues: Report bugs via GitHub Issues
Security: See SECURITY.md for vulnerability reporting
Discussions: Use GitHub Discussions for questions

c77_rbac - Role-Based Access Control extension
PostgreSQL Extensions - Part of the c77_ extension family

⭐ Why Choose c77_secure_db?

"Traditional application-level security can be bypassed by bugs, SQL injection, or direct database access. c77_secure_db provides unbypassable database-level protection with cryptographic integrity verification."

Before c77_secure_db

-- ❌ Vulnerable to bypasses
INSERT INTO users (name) VALUES ('Hacker');  -- Could work!

After c77_secure_db

-- ❌ Automatically blocked
INSERT INTO users (name) VALUES ('Hacker');
-- ERROR: Direct modifications not allowed

-- ✅ Must use secure API
SELECT c77_secure_db_operation(...);  -- Audited, authorized, verified

Get started today and secure your PostgreSQL database with enterprise-grade protection!

CREATE EXTENSION c77_secure_db;
SELECT c77_secure_db_run_all_tests();  -- Must pass!

README.md

c77_secure_db

🔒 Security-First Database Protection