Database Proxy Enforcement

Database Proxy Enforcement enables fine-grained authorization at the data layer, perfect for legacy applications that can’t be modified or scenarios requiring data-centric security controls.

Architecture Overview

graph TB
    APP[Legacy Application] --> PROXY[Authonomy DB Proxy]
    PROXY --> DB[Database]
    
    PROXY <--> FGA[FGA Policy Engine]
    PROXY <--> IDX[Query Analyzer]
    
    FGA --> AUDIT[Audit Logs]
    PROXY --> CACHE[Query Cache]

The database proxy intercepts all SQL queries, evaluates them against FGA policies, and automatically modifies queries to enforce authorization before forwarding to the actual database.

Core Capabilities

🔍 Automatic Query Rewriting

Transform queries to enforce row-level security:

Original Query:

SELECT customer_id, name, revenue 
FROM customers 
WHERE status = 'active';

Rewritten Query (for sales rep user):

SELECT customer_id, name, revenue 
FROM customers 
WHERE status = 'active' 
  AND region = 'west_coast'  -- Added: user's assigned region
  AND customer_id IN (       -- Added: user's assigned customers
    SELECT customer_id FROM user_assignments 
    WHERE user_id = 'alice@company.com'
  );

🛡️ Row-Level Security Implementation

Enforce fine-grained data access without application changes:

# FGA policy for customer data access
policies:
  customer_data_access:
    resource: "table:customers"
    conditions:
      # Sales reps see assigned customers only
      - role: "sales_rep"
        filter: "customer_id IN (user.assigned_customers)"
        
      # Managers see their region only  
      - role: "regional_manager"
        filter: "region = user.region"
        
      # Admins see everything
      - role: "admin"
        filter: "1=1"  # No restrictions

📊 Column-Level Filtering

Hide sensitive columns based on user permissions:

# Column access policies
column_policies:
  customers_table:
    - column: "credit_score"
      condition: "user.role in ['finance_admin', 'credit_analyst']"
      
    - column: "ssn"  
      condition: "user.role == 'compliance_officer'"
      mask_when_denied: "***-**-****"
      
    - column: "revenue"
      condition: "user.department in ['sales', 'finance']"
      default_value: "null"

Database Support

🐘 PostgreSQL Integration

Leverages native RLS capabilities with FGA enhancement:

-- Authonomy automatically creates and manages RLS policies
CREATE POLICY authonomy_customer_access ON customers
FOR ALL TO application_user
USING (authonomy_check_access('customer_data_access', 
                              current_user_context(), 
                              row_to_json(customers)));

🐬 MySQL Integration

Uses views and triggers for access control:

-- Automatically generated secure views
CREATE VIEW customers_secure AS
SELECT customer_id, name, 
       CASE WHEN authonomy_column_allowed('revenue') 
            THEN revenue 
            ELSE NULL END as revenue
FROM customers_raw
WHERE authonomy_row_allowed('customer_data_access', customer_id);

🔷 SQL Server Integration

Leverages Row-Level Security and Dynamic Data Masking:

-- Automatically managed security predicates
CREATE SECURITY POLICY AuthonomyCustomerPolicy
ADD FILTER PREDICATE dbo.authonomy_filter_fn(customer_id, region) ON dbo.customers,
ADD BLOCK PREDICATE dbo.authonomy_block_fn(customer_id) ON dbo.customers;

Advanced Features

🔄 Change Data Capture (CDC)

Sync authorization changes in real-time:

# CDC configuration for authorization data
cdc_sources:
  - table: "user_roles"
    trigger_events: ["INSERT", "UPDATE", "DELETE"]
    policy_refresh: "immediate"
    
  - table: "customer_assignments"  
    trigger_events: ["INSERT", "DELETE"]
    policy_refresh: "batch_5min"
    
  - table: "department_hierarchy"
    trigger_events: ["UPDATE"] 
    policy_refresh: "immediate"
    full_policy_rebuild: true

🎯 Dynamic Policy Parameters

Use runtime context in database policies:

# Context-aware database policies
dynamic_policies:
  time_based_access:
    resource: "table:sensitive_reports"
    conditions:
      - "current_time BETWEEN '09:00' AND '17:00'"
      - "current_date NOT IN (SELECT holiday_date FROM holidays)"
      
  geographic_restrictions:
    resource: "table:customer_data"  
    conditions:
      - "user.login_country = data.customer_country"
      - "data.restriction_level <= user.clearance_level"

🔍 Query Performance Optimization

Intelligent query optimization for authorization:

-- Original inefficient query with authorization
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.customer_id  
WHERE c.customer_id IN (user_assigned_customers())
  AND o.date >= '2024-01-01';

-- Authonomy optimization: push filters down
SELECT * FROM orders o
WHERE o.customer_id IN (user_assigned_customers())  -- Filter early
  AND o.date >= '2024-01-01'                        -- Date filter first
  AND EXISTS (                                      -- Efficient existence check
    SELECT 1 FROM customers c 
    WHERE c.customer_id = o.customer_id
  );

Multi-Database Scenarios

🌐 Cross-Database Authorization

Enforce policies across multiple databases:

# Multi-database policy configuration
multi_db_policies:
  customer_360_view:
    databases:
      - db: "crm_postgres"
        table: "customers"
        join_key: "customer_id"
        
      - db: "billing_mysql"  
        table: "invoices"
        join_key: "customer_id"
        
      - db: "support_mongodb"
        collection: "tickets" 
        join_key: "customer_id"
        
    authorization:
      condition: "user.assigned_customers.includes(customer_id)"

🔄 Database Federation

Present unified view across heterogeneous databases:

# Federated query example
federated_views:
  customer_summary:
    sources:
      - postgres://crm.company.com/customers
      - mysql://billing.company.com/accounts  
      - mongodb://support.company.com/tickets
      
    authorization:
      row_filter: "customer_belongs_to_user(customer_id, user.id)"
      column_filter: "role_based_column_access(user.role)"

Deployment & Configuration

🚀 Basic Setup

# Install database proxy
curl -sSL https://get.authonomy.io/dbproxy | bash

# Configure database connection
authonomy dbproxy configure \
  --database postgresql \
  --host db.company.com \
  --port 5432 \
  --proxy-port 5433

# Deploy policies
authonomy dbproxy deploy-policies \
  --policy-set customer_data_v1 \
  --shadow-mode true

⚙️ Production Configuration

# authonomy-dbproxy.yml
database_proxy:
  # Database connections
  upstream:
    host: "db-primary.company.com"
    port: 5432
    database: "production"
    ssl_mode: "require"
    
  # Proxy settings  
  proxy:
    listen_port: 5433
    max_connections: 200
    connection_timeout: "30s"
    
  # Policy configuration
  authorization:
    policy_set: "production_customer_data_v2"
    cache_ttl: "5m" 
    fallback_mode: "deny"  # Deny access if FGA unavailable
    
  # Performance optimization
  performance:
    query_cache_size: "256MB"
    prepared_statement_cache: true
    connection_pooling: true

Migration Strategies

🔄 Gradual Database Migration

Migrate tables incrementally:

# Phased table migration
migration_phases:
  phase_1:
    tables: ["users", "roles"]  # Start with identity tables
    duration: "1_week"
    
  phase_2:  
    tables: ["customers", "accounts"]  # Customer data
    duration: "2_weeks"
    
  phase_3:
    tables: ["orders", "transactions"]  # Transactional data  
    duration: "2_weeks"

🎯 Application-Specific Rollout

Different applications can migrate at different speeds:

# Per-application database migration
applications:
  - name: "customer_portal"
    database_proxy: "enabled"
    tables: ["customers", "orders"]
    migration_status: "complete"
    
  - name: "admin_dashboard"
    database_proxy: "shadow_mode" 
    tables: ["users", "audit_logs"]
    migration_status: "testing"
    
  - name: "legacy_billing"
    database_proxy: "planning"
    tables: ["invoices", "payments"] 
    migration_status: "assessment"

Monitoring & Maintenance

📊 Database Proxy Metrics

Key performance indicators:

• Query processing time: Authorization overhead per query
• Policy evaluation efficiency: Time spent on policy checks
• Cache performance: Hit rates and memory usage
• Connection utilization: Pool efficiency and connection limits

🔍 Authorization Analytics

Data access patterns and authorization insights:

• Data access frequency: Which tables/rows are accessed most
• User behavior analysis: Unusual access patterns or policy violations
• Policy effectiveness: Which policies are triggered most often
• Compliance reporting: Data access audit trails

Next Steps

Continue with Library Integration Enforcement for application-level authorization, or explore Synchronization & Consistency strategies for multi-method deployments.

Need help with database proxy deployment? Schedule a consultation with our database authorization specialists.