c77_secure_db

Enterprise-grade PostgreSQL extension for secure database operations with tamper detection and transaction control.

PostgreSQL 14+ MIT License Version 2.0

🔒 Security-First Database Protection

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();

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', '[email protected]'
    )
));

✨ 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

🛡️ 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

🏢 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

🔧 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);
    }
};

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!

View Source Code →