447 lines
12 KiB
Markdown
447 lines
12 KiB
Markdown
# 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. |