# CockroachDB Security Penetration Testing Rule ## Overview This rule provides comprehensive security testing methodologies for CockroachDB deployments, covering authentication vulnerabilities, authorization flaws, network security, data exposure risks, and configuration weaknesses. It includes practical testing scripts, common attack vectors, and defensive measures to ensure robust database security. ## Implementation ### 1. Authentication Security Testing #### Certificate-Based Authentication Testing ```bash #!/bin/bash # Test certificate validation and expiration # Check certificate expiration check_cert_expiry() { local cert_file=$1 echo "Checking certificate expiration for: $cert_file" if [ -f "$cert_file" ]; then expiry_date=$(openssl x509 -in "$cert_file" -noout -enddate | cut -d= -f2) echo "Certificate expires: $expiry_date" # Check if cert expires within 30 days if openssl x509 -checkend 2592000 -noout -in "$cert_file"; then echo "✓ Certificate is valid for at least 30 days" else echo "⚠ Certificate expires within 30 days!" fi else echo "✗ Certificate file not found: $cert_file" fi } # Test certificate chain validation validate_cert_chain() { local ca_cert=$1 local node_cert=$2 echo "Validating certificate chain..." if openssl verify -CAfile "$ca_cert" "$node_cert"; then echo "✓ Certificate chain is valid" else echo "✗ Certificate chain validation failed" fi } # Test weak certificate configurations test_weak_certs() { echo "Testing for weak certificate configurations..." # Check key length key_length=$(openssl x509 -in certs/node.crt -noout -text | grep "RSA Public-Key" | grep -o "[0-9]\+ bit") echo "Key length: $key_length" if [[ "$key_length" == *"2048"* ]] || [[ "$key_length" == *"4096"* ]]; then echo "✓ Key length is acceptable" else echo "⚠ Weak key length detected" fi # Check signature algorithm sig_alg=$(openssl x509 -in certs/node.crt -noout -text | grep "Signature Algorithm" | head -1) echo "Signature Algorithm: $sig_alg" if [[ "$sig_alg" == *"sha256"* ]] || [[ "$sig_alg" == *"sha384"* ]] || [[ "$sig_alg" == *"sha512"* ]]; then echo "✓ Strong signature algorithm" else echo "⚠ Weak signature algorithm detected" fi } # Run certificate security tests check_cert_expiry "certs/ca.crt" check_cert_expiry "certs/node.crt" check_cert_expiry "certs/client.root.crt" validate_cert_chain "certs/ca.crt" "certs/node.crt" test_weak_certs ``` #### Password Policy Testing ```sql -- Test password strength requirements DO $$ DECLARE weak_passwords TEXT[] := ARRAY['password', '123456', 'admin', 'test', 'cockroach', '', 'a']; password TEXT; result TEXT; BEGIN FOREACH password IN ARRAY weak_passwords LOOP BEGIN EXECUTE format('CREATE USER test_user_%s WITH PASSWORD %L', md5(random()::text), password); RAISE NOTICE 'VULNERABILITY: Weak password accepted: %', password; EXECUTE format('DROP USER test_user_%s', md5(random()::text)); EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'SECURE: Password rejected: % - %', password, SQLERRM; END; END LOOP; END $$; -- Test password reuse policies CREATE OR REPLACE FUNCTION test_password_reuse() RETURNS VOID AS $$ DECLARE test_user TEXT := 'pentest_user_' || md5(random()::text); BEGIN -- Create user with initial password EXECUTE format('CREATE USER %I WITH PASSWORD ''initial_password''', test_user); -- Try to change to same password BEGIN EXECUTE format('ALTER USER %I WITH PASSWORD ''initial_password''', test_user); RAISE NOTICE 'VULNERABILITY: Password reuse allowed for user %', test_user; EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'SECURE: Password reuse blocked for user %', test_user; END; -- Cleanup EXECUTE format('DROP USER %I', test_user); END; $$ LANGUAGE plpgsql; SELECT test_password_reuse(); ``` ### 2. Authorization and Privilege Escalation Testing #### Role-Based Access Control Testing ```sql -- Test for privilege escalation vulnerabilities CREATE OR REPLACE FUNCTION test_privilege_escalation() RETURNS TABLE( test_name TEXT, vulnerability_found BOOLEAN, details TEXT ) AS $$ DECLARE test_user TEXT := 'pentest_user_' || md5(random()::text); admin_user TEXT := 'pentest_admin_' || md5(random()::text); BEGIN -- Create test users EXECUTE format('CREATE USER %I', test_user); EXECUTE format('CREATE USER %I WITH CREATEDB CREATEROLE', admin_user); -- Test 1: Regular user trying to create admin user RETURN QUERY SELECT 'CREATE_ADMIN_USER'::TEXT, TRUE::BOOLEAN, 'Testing if regular user can create admin users'::TEXT; BEGIN EXECUTE format('SET ROLE %I', test_user); EXECUTE format('CREATE USER evil_admin WITH CREATEROLE'); RETURN QUERY SELECT 'CREATE_ADMIN_USER'::TEXT, TRUE::BOOLEAN, 'VULNERABILITY: Regular user created admin user'::TEXT; EXCEPTION WHEN OTHERS THEN RETURN QUERY SELECT 'CREATE_ADMIN_USER'::TEXT, FALSE::BOOLEAN, 'SECURE: Regular user cannot create admin users'::TEXT; END; -- Test 2: SQL injection in role names RESET ROLE; BEGIN EXECUTE format('CREATE ROLE "test''; DROP TABLE users; --"'); RETURN QUERY SELECT 'SQL_INJECTION_ROLE'::TEXT, TRUE::BOOLEAN, 'VULNERABILITY: SQL injection in role name'::TEXT; EXCEPTION WHEN OTHERS THEN RETURN QUERY SELECT 'SQL_INJECTION_ROLE'::TEXT, FALSE::BOOLEAN, 'SECURE: SQL injection blocked in role name'::TEXT; END; -- Test 3: Grant option abuse BEGIN EXECUTE format('GRANT SELECT ON TABLE users TO %I WITH GRANT OPTION', test_user); EXECUTE format('SET ROLE %I', test_user); EXECUTE format('GRANT SELECT ON TABLE users TO %I', admin_user); RETURN QUERY SELECT 'GRANT_OPTION_ABUSE'::TEXT, TRUE::BOOLEAN, 'VULNERABILITY: Grant option can be abused'::TEXT; EXCEPTION WHEN OTHERS THEN RETURN QUERY SELECT 'GRANT_OPTION_ABUSE'::TEXT, FALSE::BOOLEAN, 'SECURE: Grant option properly restricted'::TEXT; END; -- Cleanup RESET ROLE; EXECUTE format('DROP USER IF EXISTS %I', test_user); EXECUTE format('DROP USER IF EXISTS %I', admin_user); EXECUTE 'DROP ROLE IF EXISTS "test''; DROP TABLE users; --"'; END; $$ LANGUAGE plpgsql; -- Run privilege escalation tests SELECT * FROM test_privilege_escalation(); ``` #### Database Object Access Testing ```sql -- Test unauthorized access to system tables and functions CREATE OR REPLACE FUNCTION test_system_access() RETURNS TABLE( object_type TEXT, object_name TEXT, access_granted BOOLEAN, risk_level TEXT ) AS $$ DECLARE test_user TEXT := 'pentest_limited_' || md5(random()::text); system_tables TEXT[] := ARRAY[ 'pg_authid', 'pg_shadow', 'pg_user', 'crdb_internal.cluster_settings', 'crdb_internal.node_runtime_info', 'crdb_internal.gossip_network' ]; system_functions TEXT[] := ARRAY[ 'crdb_internal.force_error', 'crdb_internal.node_executable_version', 'pg_read_file', 'pg_ls_dir' ]; obj TEXT; can_access BOOLEAN; BEGIN -- Create limited user EXECUTE format('CREATE USER %I', test_user); EXECUTE format('SET ROLE %I', test_user); -- Test system table access FOREACH obj IN ARRAY system_tables LOOP BEGIN EXECUTE format('SELECT COUNT(*) FROM %s LIMIT 1', obj); can_access := TRUE; EXCEPTION WHEN OTHERS THEN can_access := FALSE; END; RETURN QUERY SELECT 'SYSTEM_TABLE'::TEXT, obj, can_access, CASE WHEN can_access THEN 'HIGH' ELSE 'LOW' END; END LOOP; -- Test system function access FOREACH obj IN ARRAY system_functions LOOP BEGIN EXECUTE format('SELECT %s()', obj); can_access := TRUE; EXCEPTION WHEN OTHERS THEN can_access := FALSE; END; RETURN QUERY SELECT 'SYSTEM_FUNCTION'::TEXT, obj, can_access, CASE WHEN can_access THEN 'CRITICAL' ELSE 'LOW' END; END LOOP; -- Cleanup RESET ROLE; EXECUTE format('DROP USER %I', test_user); END; $$ LANGUAGE plpgsql; -- Run system access tests SELECT * FROM test_system_access() WHERE access_granted = TRUE; ``` ### 3. Network Security Testing #### Connection Security Testing ```bash #!/bin/bash # Test network security configurations # Test insecure connections test_insecure_connections() { echo "Testing insecure connection acceptance..." # Try to connect without TLS if cockroach sql --insecure --host=localhost --execute="SELECT 1" 2>/dev/null; then echo "⚠ VULNERABILITY: Insecure connections accepted" else echo "✓ SECURE: Insecure connections rejected" fi # Test weak TLS configurations echo "Testing TLS configuration..." openssl s_client -connect localhost:26257 -tls1 2>/dev/null | grep -q "Verify return code: 0" && { echo "⚠ VULNERABILITY: TLS 1.0 accepted" } || { echo "✓ SECURE: TLS 1.0 rejected" } openssl s_client -connect localhost:26257 -tls1_1 2>/dev/null | grep -q "Verify return code: 0" && { echo "⚠ VULNERABILITY: TLS 1.1 accepted" } || { echo "✓ SECURE: TLS 1.1 rejected" } } # Test port exposure test_port_exposure() { echo "Testing port exposure..." # Check if admin UI is accessible without authentication if curl -s -k "https://localhost:8080" | grep -q "CockroachDB"; then echo "⚠ VULNERABILITY: Admin UI accessible without auth" else echo "✓ SECURE: Admin UI requires authentication" fi # Test for open ports open_ports=$(nmap -sS -O localhost 2>/dev/null | grep "open" | wc -l) if [ "$open_ports" -gt 2 ]; then echo "⚠ WARNING: Multiple open ports detected ($open_ports)" else echo "✓ SECURE: Minimal port exposure" fi } # Test connection limits test_connection_limits() { echo "Testing connection limits..." # Attempt connection flooding for i in {1..100}; do cockroach sql --certs-dir=certs --host=localhost --execute="SELECT 1" & done wait echo "Connection flood test completed" } # Run network security tests test_insecure_connections test_port_exposure test_connection_limits ``` #### SQL Injection Testing ```sql -- Comprehensive SQL injection testing suite CREATE OR REPLACE FUNCTION test_sql_injection() RETURNS TABLE( test_case TEXT, injection_type TEXT, payload TEXT, vulnerable BOOLEAN, impact TEXT ) AS $$ DECLARE payloads TEXT[] := ARRAY[ ''''; DROP TABLE users; --', ''' OR ''1''=''1'' --', ''' UNION SELECT password FROM users --', '''; INSERT INTO users VALUES (''hacker'', ''pass''); --', ''' OR 1=1 AND (SELECT COUNT(*) FROM users) > 0 --', '''; COPY users TO ''/tmp/users.csv'' --', ''' OR pg_sleep(10) --', '''; SELECT pg_read_file(''/etc/passwd'') --' ]; payload TEXT; test_table TEXT := 'pentest_injection_' || md5(random()::text); vulnerable_found BOOLEAN; BEGIN -- Create test table EXECUTE format('CREATE TABLE %I (id INT, name TEXT)', test_table); EXECUTE format('INSERT INTO %I VALUES (1, ''test'')', test_table); FOREACH payload IN ARRAY payloads LOOP vulnerable_found := FALSE; -- Test in WHERE clause BEGIN EXECUTE format('SELECT * FROM %I WHERE name = %L', test_table, payload); EXCEPTION WHEN OTHERS THEN -- Expected - injection blocked END; -- Test in INSERT statement BEGIN EXECUTE format('INSERT INTO %I (name) VALUES (%L)', test_table, payload); EXCEPTION WHEN OTHERS THEN -- Expected - injection blocked END; -- Test in UPDATE statement BEGIN EXECUTE format('UPDATE %I SET name = %L WHERE id = 1', test_table, payload); EXCEPTION WHEN OTHERS THEN -- Expected - injection blocked END; RETURN QUERY SELECT 'SQL_INJECTION'::TEXT, 'CLASSIC'::TEXT, payload, vulnerable_found, CASE WHEN vulnerable_found THEN 'CRITICAL' ELSE 'BLOCKED' END; END LOOP; -- Cleanup EXECUTE format('DROP TABLE %I', test_table); END; $$ LANGUAGE plpgsql; -- Test for stored procedure injection CREATE OR REPLACE FUNCTION test_stored_proc_injection() RETURNS VOID AS $$ DECLARE malicious_proc TEXT := 'CREATE OR REPLACE FUNCTION evil_func() RETURNS TEXT AS $evil$ BEGIN RETURN (SELECT password FROM users LIMIT 1); END; $evil$ LANGUAGE plpgsql;'; BEGIN -- Try to create malicious stored procedure BEGIN EXECUTE malicious_proc; RAISE NOTICE 'VULNERABILITY: Malicious stored procedure created'; EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'SECURE: Malicious stored procedure blocked'; END; END; $$ LANGUAGE plpgsql; -- Run SQL injection tests SELECT * FROM test_sql_injection(); SELECT test_stored_proc_injection(); ``` ### 4. Data Exposure and Leakage Testing #### Sensitive Data Discovery ```sql -- Scan for sensitive data patterns CREATE OR REPLACE FUNCTION scan_sensitive_data() RETURNS TABLE( table_name TEXT, column_name TEXT, data_type TEXT, sensitivity_level TEXT, sample_count INT ) AS $$ DECLARE rec RECORD; sensitive_patterns TEXT[] := ARRAY[ 'password', 'passwd', 'pwd', 'secret', 'token', 'key', 'ssn', 'social', 'credit', 'card', 'cvv', 'pin', 'email', 'phone', 'address', 'dob', 'birth' ]; pattern TEXT; query TEXT; count_result INT; BEGIN -- Scan all user tables for sensitive column names FOR rec IN SELECT schemaname, tablename, attname, format_type(atttypid, atttypmod) as type FROM pg_attribute a JOIN pg_class c ON a.attrelid = c.oid JOIN pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname NOT IN ('information_schema', 'pg_catalog', 'crdb_internal', 'pg_extension') AND a.attnum > 0 AND NOT a.attisdropped LOOP FOREACH pattern IN ARRAY sensitive_patterns LOOP IF lower(rec.attname) LIKE '%' || pattern || '%' THEN -- Count non-null values query := format('SELECT COUNT(*) FROM %I.%I WHERE %I IS NOT NULL', rec.schemaname, rec.tablename, rec.attname); EXECUTE query INTO count_result; RETURN QUERY SELECT rec.tablename, rec.attname, rec.type, CASE WHEN pattern IN ('password', 'passwd', 'pwd', 'secret', 'token', 'key') THEN 'CRITICAL' WHEN pattern IN ('ssn', 'social', 'credit', 'card', 'cvv') THEN 'HIGH' ELSE 'MEDIUM' END, count_result; END IF; END LOOP; END LOOP; END; $$ LANGUAGE plpgsql; -- Test for data encryption CREATE OR REPLACE FUNCTION test_data_encryption() RETURNS TABLE( table_name TEXT, column_name TEXT, appears_encrypted BOOLEAN, encryption_strength TEXT ) AS $$ DECLARE rec RECORD; sample_data TEXT; is_encrypted BOOLEAN; strength TEXT; BEGIN -- Check sensitive columns for encryption FOR rec IN SELECT * FROM scan_sensitive_data() WHERE sensitivity_level IN ('CRITICAL', 'HIGH') LOOP -- Get sample data EXECUTE format('SELECT %I FROM %I WHERE %I IS NOT NULL LIMIT 1', rec.column_name, rec.table_name, rec.column_name) INTO sample_data; -- Basic encryption detection is_encrypted := FALSE; strength := 'NONE'; -- Check for hash patterns (bcrypt, scrypt, etc.) IF sample_data ~ '^\$2[ayb]\$[0-9]{2}\$' THEN is_encrypted := TRUE; strength := 'STRONG'; ELSIF sample_data ~ '^[a-fA-F0-9]{32}$' THEN is_encrypted := TRUE; strength := 'WEAK'; ELSIF sample_data ~ '^[a-fA-F0-9]{64}$' THEN is_encrypted := TRUE; strength := 'MEDIUM'; ELSIF LENGTH(sample_data) > 20 AND sample_data ~ '^[A-Za-z0-9+/]*={0,2}$' THEN is_encrypted := TRUE; strength := 'UNKNOWN'; END IF; RETURN QUERY SELECT rec.table_name, rec.column_name, is_encrypted, strength; END LOOP; END; $$ LANGUAGE plpgsql; -- Run data exposure tests SELECT * FROM scan_sensitive_data(); SELECT * FROM test_data_encryption() WHERE appears_encrypted = FALSE; ``` #### Backup Security Testing ```bash #!/bin/bash # Test backup security and exposure test_backup_security() { echo "Testing backup security..." # Test backup encryption cockroach dump --insecure mydb > /tmp/test_backup.sql 2>/dev/null if [ -f "/tmp/test_backup.sql" ]; then echo "⚠ VULNERABILITY: Unencrypted backup created" # Check for sensitive data in backup if grep -i "password\|secret\|token" /tmp/test_backup.sql >/dev/null; then echo "⚠ CRITICAL: Sensitive data found in backup" fi # Check backup permissions backup_perms=$(stat -c "%a" /tmp/test_backup.sql) if [ "$backup_perms" != "600" ]; then echo "⚠ VULNERABILITY: Backup has weak permissions ($backup_perms)" fi rm -f /tmp/test_backup.sql else echo "✓ SECURE: Backup creation properly restricted" fi # Test backup authentication if cockroach dump --insecure --host=remote-host mydb >/dev/null 2>&1; then echo "⚠ VULNERABILITY: Remote backup without authentication" else echo "✓ SECURE: Remote backup requires authentication" fi } # Test log file security test_log_security() { echo "Testing log file security..." # Find CockroachDB log files log_files=$(find /var/log -name "*cockroach*" 2>/dev/null) for log_file in $log_files; do if [ -r "$log_file" ]; then echo "Checking log file: $log_file" # Check for sensitive data in logs if grep -i "password\|secret\|token" "$log_file" >/dev/null 2>&1; then echo "⚠ CRITICAL: Sensitive data found in log file" fi # Check log permissions log_perms=$(stat -c "%a" "$log_file") if [ "$log_perms" -gt 640 ]; then echo "⚠ VULNERABILITY: Log file has weak permissions ($log_perms)" fi fi done } test_backup_security test_log_security ``` ### 5. Configuration Security Testing #### Cluster Settings Security Audit ```sql -- Audit security-related cluster settings CREATE OR REPLACE FUNCTION audit_security_settings() RETURNS TABLE( setting_name TEXT, current_value TEXT, security_impact TEXT, recommendation TEXT ) AS $$ DECLARE setting RECORD; impact TEXT; recommendation TEXT; BEGIN FOR setting IN SELECT variable, value FROM [SHOW CLUSTER SETTINGS] WHERE variable ~ '(auth|security|ssl|tls|password|session|timeout)' LOOP -- Analyze each security setting CASE WHEN setting.variable = 'server.auth_log.sql_connections.enabled' THEN impact := CASE WHEN setting.value = 'false' THEN 'HIGH' ELSE 'LOW' END; recommendation := CASE WHEN setting.value = 'false' THEN 'Enable connection logging' ELSE 'Current setting is secure' END; WHEN setting.variable = 'server.auth_log.sql_sessions.enabled' THEN impact := CASE WHEN setting.value = 'false' THEN 'HIGH' ELSE 'LOW' END; recommendation := CASE WHEN setting.value = 'false' THEN 'Enable session logging' ELSE 'Current setting is secure' END; WHEN setting.variable = 'server.host_based_authentication.configuration' THEN impact := CASE WHEN setting.value = '' THEN 'MEDIUM' ELSE 'LOW' END; recommendation := CASE WHEN setting.value = '' THEN 'Configure HBA rules' ELSE 'Review HBA configuration' END; WHEN setting.variable = 'sql.auth.resolve_membership_single_scan' THEN impact := 'LOW'; recommendation := 'Monitor for performance impact'; ELSE impact := 'UNKNOWN'; recommendation := 'Manual review required'; END CASE; RETURN QUERY SELECT setting.variable, setting.value, impact, recommendation; END LOOP; END; $$ LANGUAGE plpgsql; -- Test for dangerous cluster settings CREATE OR REPLACE FUNCTION test_dangerous_settings() RETURNS TABLE( test_name TEXT, vulnerable BOOLEAN, details TEXT ) AS $$ DECLARE setting_value TEXT; BEGIN -- Test for insecure authentication settings SELECT value INTO setting_value FROM [SHOW CLUSTER SETTINGS] WHERE variable = 'server.auth_log.sql_connections.enabled'; RETURN QUERY SELECT 'AUTH_LOGGING'::TEXT, (setting_value = 'false')::BOOLEAN, CASE WHEN setting_value = 'false' THEN 'Authentication logging disabled' ELSE 'Authentication logging enabled' END; -- Test for weak session settings SELECT value INTO setting_value FROM [SHOW CLUSTER SETTINGS] WHERE variable = 'server.web_session_timeout'; RETURN QUERY SELECT 'SESSION_TIMEOUT'::TEXT, (setting_value::INTERVAL > INTERVAL '24 hours')::BOOLEAN, format('Session timeout: %s', setting_value); -- Test for debug settings in production SELECT value INTO setting_value FROM [SHOW CLUSTER SETTINGS] WHERE variable = 'sql.trace.log_statement_execute'; RETURN QUERY SELECT 'DEBUG_LOGGING'::TEXT, (setting_value = 'true')::BOOLEAN, CASE WHEN setting_value = 'true' THEN 'Debug logging enabled in production' ELSE 'Debug logging disabled' END; END; $$ LANGUAGE plpgsql; -- Run security settings audit SELECT * FROM audit_security_settings() WHERE security_impact IN ('HIGH', 'CRITICAL'); SELECT * FROM test_dangerous_settings() WHERE vulnerable = TRUE; ``` ## Best Practices ### 1. Penetration Testing Methodology - **Reconnaissance**: Gather information about CockroachDB version, configuration, and network topology - **Scanning**: Identify open ports, services, and potential vulnerabilities - **Enumeration**: Extract detailed information about users, roles, and database structure - **Exploitation**: Attempt to exploit identified vulnerabilities - **Post-Exploitation**: Assess the impact and potential for privilege escalation - **Reporting**: Document findings with severity levels and remediation steps ### 2. Security Testing Automation - Integrate security tests into CI/CD pipelines - Use automated vulnerability scanners regularly - Implement continuous security monitoring - Schedule regular penetration testing assessments - Maintain security test documentation and results ### 3. Defensive Security Measures - Enable comprehensive audit logging - Implement network segmentation and firewalls - Use strong authentication and encryption - Regular security updates and patches - Monitor for suspicious activities and anomalies ### 4. Compliance and Governance - Follow industry security standards (NIST, ISO 27001) - Implement security policies and procedures - Regular security training for development teams - Maintain incident response procedures - Document security controls and their effectiveness ## Common Issues ### 1. False Positives in Security Scans **Problem**: Automated scanners reporting vulnerabilities that don't actually exist. **Solution**: ```sql -- Verify scanner findings manually CREATE OR REPLACE FUNCTION verify_vulnerability( vuln_type TEXT, test_payload TEXT ) RETURNS BOOLEAN AS $$ DECLARE is_vulnerable BOOLEAN := FALSE; BEGIN -- Custom verification logic based on vulnerability type CASE vuln_type WHEN 'SQL_INJECTION' THEN BEGIN EXECUTE format('SELECT 1 WHERE 1=1 AND %s', test_payload); is_vulnerable := TRUE; EXCEPTION WHEN OTHERS THEN is_vulnerable := FALSE; END; WHEN 'PRIVILEGE_ESCALATION' THEN -- Add specific test logic is_vulnerable := FALSE; END CASE; RETURN is_vulnerable; END; $$ LANGUAGE plpgsql; ``` ### 2. Performance Impact of Security Tests **Problem**: Security tests causing performance degradation or system instability. **Solution**: ```bash # Rate-limited security testing #!/bin/bash rate_limited_test() { local test_command="$1" local delay="$2" echo "Running rate-limited test: $test_command" eval "$test_command" sleep "$delay" } # Example usage rate_limited_test "test_sql_injection()" 5 rate_limited_test "test_privilege_escalation()" 10 ``` ### 3. Test Data Cleanup **Problem**: Security tests leaving test data or users in the system. **Solution**: ```sql -- Comprehensive cleanup function CREATE OR REPLACE FUNCTION cleanup_pentest_artifacts() RETURNS VOID AS $$ DECLARE test_user RECORD; test_table RECORD; test_role RECORD; BEGIN -- Remove test users FOR test_user IN SELECT usename FROM pg_user WHERE usename LIKE 'pentest_%' LOOP EXECUTE format('DROP USER %I', test_user.usename); RAISE NOTICE 'Cleaned up test user: %', test_user.usename; END LOOP; -- Remove test tables FOR test_table IN SELECT tablename FROM pg_tables WHERE tablename LIKE 'pentest_%' LOOP EXECUTE format('DROP TABLE %I', test_table.tablename); RAISE NOTICE 'Cleaned up test table: %', test_table.tablename; END LOOP; -- Remove test roles FOR test_role IN SELECT rolname FROM pg_roles WHERE rolname LIKE 'pentest_%' LOOP EXECUTE format('DROP ROLE %I', test_role.rolname); RAISE NOTICE 'Cleaned up test role: %', test_role.rolname; END LOOP; END; $$ LANGUAGE plpgsql; -- Run cleanup SELECT cleanup_pentest_artifacts(); ``` ### 4. Testing in Production Environments **Problem**: Security testing potentially disrupting production systems. **Solution**: ```bash # Production-safe testing approach #!/bin/bash if [[ "$ENVIRONMENT" == "production" ]]; then echo "Production environment detected. Using safe testing mode." # Read-only tests only run_readonly_security_tests() { echo "Running read-only security assessment..." # Configuration audits # Permission reviews # Log analysis # No destructive tests } run_readonly_security_tests else echo "Non-production environment. Running full security test suite." # Full penetration testing fi ```