Pastein: #!/bin/bash # ============================================================================ # Аудит безопасности PostgreSQL (СУБД.1.1

Загрузка данных
#!/bin/bash

# ============================================================================
# Аудит безопасности PostgreSQL (СУБД.1.1 - СУБД.1.17)
# ============================================================================

# Параметры подключения
DB_HOST="${1:-localhost}"
DB_PORT="${2:-5432}"
DB_USER="${3:-postgres}"
DB_NAME="${4:-postgres}"
OUTPUT_FILE="${5:-postgres_audit_detailed_$(date +%Y%m%d_%H%M%S).txt}"

# Цвета для вывода
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color

echo "================================================================================"
echo "           ПОДРОБНЫЙ АУДИТ БЕЗОПАСНОСТИ POSTGRESQL"
echo "           (СУБД.1.1 - СУБД.1.17)"
echo "================================================================================"
echo "Дата: $(date '+%Y-%m-%d %H:%M:%S')"
echo "Хост: $DB_HOST:$DB_PORT"
echo "Пользователь: $DB_USER"
echo "База данных: $DB_NAME"
echo "Отчет: $OUTPUT_FILE"
echo "================================================================================"

# Проверка подключения
if ! psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SELECT 1;" > /dev/null 2>&1; then
    echo -e "${RED}ОШИБКА: Не удалось подключиться к PostgreSQL!${NC}"
    exit 1
fi

{

echo "================================================================================"
echo "                    СУБД.1.1 - ПРОВЕРКА ДОСТУПОВ (pg_hba.conf)"
echo "================================================================================"

echo ""
echo ">>> Путь к файлу pg_hba.conf:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SHOW hba_file;"

echo ""
echo ">>> Проверка на наличие 'trust' (автоматический доступ без пароля):"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT 
    CASE WHEN type = 'trust' THEN 'WARNING' ELSE 'OK' END as status,
    'Тип: ' || type || ' | База: ' || database || ' | Пользователь: ' || usr || ' | Адрес: ' || address as result
FROM pg_hba_file_rules 
WHERE type = 'trust';
" 2>/dev/null || echo "Не удалось прочитать pg_hba_file_rules (требуются права суперпользователя)"

echo ""
echo ">>> Все правила доступа (первые 10):"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT type, database, usr, address, auth_method 
FROM pg_hba_file_rules 
LIMIT 10;
" 2>/dev/null || echo "Требуется доступ суперпользователя"

echo ""
echo "================================================================================"
echo "                    СУБД.1.1 - ВЫГРУЗКА ХЕШЕЙ ПАРОЛЕЙ"
echo "================================================================================"

echo ""
echo ">>> Хеши паролей суперпользователей (для проверки на слабость):"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT rolname, 
       CASE 
           WHEN rolpassword IS NULL THEN 'NO PASSWORD'
           WHEN rolpassword LIKE 'SCRAM-SHA-256%' THEN 'SCRAM-SHA-256 (hash hidden)'
           WHEN rolpassword LIKE 'md5%' THEN substring(rolpassword from 4)
           ELSE 'OTHER'
       END as password_info,
       rolpassword as full_hash
FROM pg_authid 
WHERE rolsuper IS TRUE AND rolpassword IS NOT NULL;
"

echo ""
echo ">>> Форматы хранения паролей всех пользователей:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT 
    rolname,
    CASE 
        WHEN rolpassword IS NULL THEN 'NO PASSWORD'
        ELSE 'PROTECTED (HASH)'
    END AS password_status,
    CASE 
        WHEN rolpassword LIKE 'SCRAM-SHA-256%' THEN 'SCRAM-SHA-256'
        WHEN rolpassword LIKE 'md5%' THEN 'MD5'
        WHEN rolpassword IS NULL THEN 'NONE'
        ELSE 'UNKNOWN'
    END AS password_format
FROM pg_authid
ORDER BY rolname;
"

echo ""
echo "================================================================================"
echo "                    СУБД.1.2 - ПРОВЕРКА ПАРОЛЬНОЙ ПОЛИТИКИ"
echo "================================================================================"

echo ""
echo ">>> Проверка модуля passwordcheck (требования к длине пароля):"
RESULT=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SHOW passwordcheck.min_password_length;" 2>&1)
if echo "$RESULT" | grep -q "ERROR"; then
    echo -e "${YELLOW}Модуль passwordcheck НЕ включен (требуется установка)${NC}"
else
    echo "Минимальная длина пароля: $RESULT"
fi

echo ""
echo ">>> Текущие настройки шифрования паролей:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SHOW password_encryption;"

echo ""
echo "================================================================================"
echo "                    СУБД.1.3 - ПРОВЕРКА НА ОДИНАКОВЫЕ ПАРОЛИ"
echo "================================================================================"

echo ""
echo ">>> Поиск суперпользователей с одинаковыми хешами паролей:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT 
    rolpassword,
    COUNT(*) as count,
    array_agg(rolname) as users_with_same_password
FROM pg_authid 
WHERE rolsuper IS TRUE AND rolpassword IS NOT NULL
GROUP BY rolpassword 
HAVING COUNT(*) > 1;
"

echo ""
echo ">>> Для проверки через hashcat экспортируйте хеши:"
echo ">>> Для SCRAM-SHA-256: hashcat -m 22600 hashes.txt valid_pass.txt"
echo ">>> Для MD5: hashcat -m 12100 hashes.txt valid_pass.txt"
echo ">>> Формат для MD5: hash:username (например: 6e6d193d25867bc582a876a3861fb16e:admin1)"

echo ""
echo "================================================================================"
echo "                    СУБД.1.4 - АКТИВНОСТЬ УЧЕТНЫХ ЗАПИСЕЙ"
echo "================================================================================"

echo ""
echo ">>> Все учетные записи с возможностью входа:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT rolname, rolsuper, rolcanlogin, rolvaliduntil
FROM pg_roles 
WHERE rolcanlogin IS TRUE
ORDER BY rolname;
"

echo ""
echo ">>> Текущая активность (pg_stat_activity):"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT 
    username,
    datname,
    client_addr,
    application_name,
    state,
    query_start
FROM pg_stat_activity 
WHERE backend_type = 'client backend'
ORDER BY username;
"

echo ""
echo ">>> Директория данных и логов (для ручной проверки активности):"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SHOW data_directory;"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SHOW log_directory;"

echo ""
echo ">>> Пример команды для поиска действий в логах:"
echo "grep -E -i 'user=postgres.*(connection authorized|SELECT|INSERT|UPDATE|DELETE|CREATE|DROP)' /var/lib/postgresql/*/log/*.log"

echo ""
echo "================================================================================"
echo "                    СУБД.1.5 - МАКСИМАЛЬНОЕ КОЛИЧЕСТВО ПОДКЛЮЧЕНИЙ"
echo "================================================================================"

echo ""
MAX_CONN=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SHOW max_connections;")
CURRENT_CONN=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SELECT COUNT(*) FROM pg_stat_activity WHERE backend_type = 'client backend';")

echo "max_connections = $MAX_CONN"
echo "Текущих активных подключений: $CURRENT_CONN"

if [ "$MAX_CONN" -gt 1000 ]; then
    echo -e "${YELLOW}️  WARNING: max_connections > 1000 (рекомендуется использовать PgBouncer)${NC}"
elif [ "$MAX_CONN" -eq 100 ]; then
    echo "Стандартное значение (100)"
fi

echo ""
echo "================================================================================"
echo "                    СУБД.1.6 - СУПЕРПОЛЬЗОВАТЕЛИ И ИЗБЫТОЧНЫЕ ПРАВА"
echo "================================================================================"

echo ""
echo ">>> Суперпользователи (роль superuser):"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT rolname 
FROM pg_roles 
WHERE rolsuper IS TRUE;
"

echo ""
echo ">>> Права ALL PRIVILEGES на уровне баз данных:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT 
    datname,
    datacl
FROM pg_database 
WHERE datname NOT LIKE 'template%'
  AND datacl IS NOT NULL;
"

echo ""
echo ">>> Права ALL PRIVILEGES на уровне таблиц (не postgres):"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT 
    table_schema,
    table_name,
    grantee,
    privilege_type
FROM information_schema.role_table_grants 
WHERE grantee NOT IN ('postgres') 
  AND privilege_type = 'ALL PRIVILEGES';
"

echo ""
echo "================================================================================"
echo "                    СУБД.1.7 - ИСПОЛЬЗОВАНИЕ ОДНОЙ УЗ С НЕСКОЛЬКИХ ХОСТОВ"
echo "================================================================================"

echo ""
echo ">>> Анализ подключений по пользователям и адресам:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT 
    username,
    COUNT(DISTINCT client_addr) as unique_ips,
    array_agg(DISTINCT client_addr) as ip_addresses,
    COUNT(*) as total_connections,
    array_agg(DISTINCT application_name) as applications
FROM pg_stat_activity 
WHERE backend_type = 'client backend' 
  AND username IS NOT NULL
GROUP BY username
HAVING COUNT(DISTINCT client_addr) > 1
ORDER BY COUNT(DISTINCT client_addr) DESC;
"

echo ""
echo "================================================================================"
echo "                    СУБД.1.8 - ОПАСНЫЕ АДМИНСКИЕ ФЛАГИ"
echo "================================================================================"

echo ""
echo ">>> Все админские флаги у ролей:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT 
    rolname,
    rolsuper,
    rolcreaterole,
    rolcreatedb,
    rolreplication,
    rolbypassrls
FROM pg_roles
WHERE rolcanlogin = true
ORDER BY rolsuper DESC, rolname;
"

echo ""
echo ">>> Рекурсивные члены суперпользователей (вложенность в группы):"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
WITH RECURSIVE m AS (
    SELECT roleid, member FROM pg_auth_members
    UNION
    SELECT a.roleid, m.member 
    FROM pg_auth_members a 
    JOIN m ON a.member = m.roleid
)
SELECT DISTINCT 
    r.rolname AS member,
    s.rolname AS super
FROM m 
JOIN pg_roles r ON m.member = r.oid 
JOIN pg_roles s ON m.roleid = s.oid 
WHERE s.rolsuper
ORDER BY s.rolname, r.rolname;
"

echo ""
echo ">>> Владельцы объектов в pg_catalog (должен быть только postgres):"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT 
    nspname,
    relname,
    rolname
FROM pg_class c 
JOIN pg_namespace n ON c.relnamespace = n.oid 
JOIN pg_roles r ON c.relowner = r.oid 
WHERE nspname = 'pg_catalog'
  AND rolname != 'postgres'
LIMIT 10;
"

echo ""
echo ">>> GRANT на объекты каталога (проверка ACL):"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT 
    nspname,
    relname,
    relacl
FROM pg_class c 
JOIN pg_namespace n ON c.relnamespace = n.oid 
WHERE nspname = 'pg_catalog' 
  AND relacl IS NOT NULL
LIMIT 10;
"

echo ""
echo "================================================================================"
echo "                    СУБД.1.9 - ИНТЕРФЕЙСЫ СЛУШАНИЯ (listen_addresses)"
echo "================================================================================"

echo ""
LISTEN_ADDR=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SHOW listen_addresses;")
echo "listen_addresses = $LISTEN_ADDR"

if [ "$LISTEN_ADDR" = "*" ] || echo "$LISTEN_ADDR" | grep -q "0.0.0.0"; then
    echo -e "${YELLOW}⚠️  WARNING: БД слушает все интерфейсы (0.0.0.0 или *)${NC}"
    echo "Рекомендуется изменить на конкретный IP или localhost"
    echo "Пример: ALTER SYSTEM SET listen_addresses = '192.168.1.10';"
else
    echo -e "${GREEN}✓ OK: Ограничено конкретными адресами${NC}"
fi

echo ""
echo "================================================================================"
echo "                    СУБД.1.10 - ПРОВЕРКА pg_hba.conf (сетевой доступ)"
echo "================================================================================"

echo ""
echo ">>> Правила с широким доступом (0.0.0.0/0 или ::/0):"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT 
    CASE 
        WHEN address = '0.0.0.0/0' OR address = '::/0' THEN 'WARNING'
        ELSE 'OK'
    END as status,
    type,
    database,
    usr,
    address,
    auth_method
FROM pg_hba_file_rules 
WHERE address = '0.0.0.0/0' OR address = '::/0';
" 2>/dev/null || echo "Требуется доступ суперпользователя"

echo ""
echo ">>> Примеры хороших и плохих настроек:"
echo "ПЛОХО: host all all 0.0.0.0/0 md5"
echo "ХОРОШО: host all all 192.168.1.50/32 md5"

echo ""
echo "================================================================================"
echo "                    СУБД.1.11 - ФОРМАТ ХРАНЕНИЯ ПАРОЛЕЙ"
echo "================================================================================"

echo ""
echo ">>> Настройка для новых паролей:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SHOW password_encryption;"

echo ""
echo ">>> Текущие форматы паролей:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT 
    rolname,
    CASE 
        WHEN rolpassword IS NULL THEN 'NO PASSWORD'
        WHEN rolpassword LIKE 'SCRAM-SHA-256%' THEN 'SCRAM-SHA-256'
        WHEN rolpassword LIKE 'md5%' THEN 'MD5 (устаревший!)'
        ELSE 'OTHER'
    END AS password_format,
    CASE 
        WHEN rolpassword LIKE 'md5%' THEN 'WARNING'
        WHEN rolpassword IS NULL THEN 'WARNING'
        ELSE 'OK'
    END as status
FROM pg_authid
WHERE rolpassword IS NOT NULL
ORDER BY status, rolname;
"

echo ""
echo ">>> Проверка на наличие pgcrypto (шифрование данных):"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT * FROM pg_extension WHERE extname = 'pgcrypto';
"

echo ""
echo "================================================================================"
echo "                    СУБД.1.12 - SSL/TLS НАСТРОЙКИ"
echo "================================================================================"

echo ""
echo ">>> Статус SSL:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SHOW ssl;"

echo ""
echo ">>> Минимальная версия TLS:"
MIN_TLS=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SHOW ssl_min_protocol_version;")
echo "ssl_min_protocol_version = $MIN_TLS"

if [ "$MIN_TLS" = "TLSv1.2" ] || [ "$MIN_TLS" = "TLSv1.3" ]; then
    echo -e "${GREEN}✓ OK: Используются безопасные версии TLS${NC}"
else
    echo -e "${YELLOW}⚠️  WARNING: Устаревшая версия TLS (рекомендуется TLSv1.2 или выше)${NC}"
fi

echo ""
echo ">>> Разрешенные шифры:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SHOW ssl_ciphers;"

echo ""
echo ">>> Проверка через sslscan (внешняя утилита):"
echo "Команда: sslscan --starttls-psql $DB_HOST:$DB_PORT"
echo ""
echo "Плохие результаты (пример):"
echo "  Accepted  TLSv1.0  168 bits  DES-CBC3-SHA"
echo "  Accepted  TLSv1.0  128 bits  RC4-SHA"

echo ""
echo ">>> Проверка pg_hba.conf на требование SSL (hostssl):"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT 
    type,
    database,
    usr,
    address,
    CASE 
        WHEN type = 'hostssl' THEN 'Требует SSL'
        WHEN type = 'host' THEN 'Может без SSL'
        ELSE type
    END as ssl_requirement
FROM pg_hba_file_rules 
WHERE type IN ('host', 'hostssl')
LIMIT 10;
" 2>/dev/null

echo ""
echo "================================================================================"
echo "                    СУБД.1.13 - НАСТРОЙКИ ЛОГИРОВАНИЯ"
echo "================================================================================"

echo ""
echo ">>> Статус сборщика логов:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SHOW logging_collector;"

echo ""
echo ">>> Директории:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SHOW data_directory;"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SHOW log_directory;"

echo ""
echo ">>> Права на файлы логов:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SHOW log_file_mode;"

LOG_MODE=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SHOW log_file_mode;")
if [ "$LOG_MODE" = "0644" ]; then
    echo -e "${YELLOW}⚠️  WARNING: Логи доступны всем (0644). Норма: 0600 или 0640${NC}"
else
    echo -e "${GREEN}✓ OK: Права на логи корректны${NC}"
fi

echo ""
echo ">>> Кто может читать логи из СУБД:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT r.rolname 
FROM pg_roles r 
WHERE r.rolsuper IS TRUE 
   OR r.rolname IN (
       SELECT m.roleid::regrole::text 
       FROM pg_auth_members m 
       WHERE m.member = r.oid 
         AND m.roleid::regrole::text IN ('pg_read_all_stats', 'pg_read_all_settings')
   );
"

echo ""
echo "================================================================================"
echo "                    СУБД.1.14 - ЗАЩИТА ОТ БРУТФОРСА (auth_delay, fail2ban)"
echo "================================================================================"

echo ""
echo ">>> Проверка модуля auth_delay:"
AUTH_DELAY_LIB=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SHOW shared_preload_libraries;")
echo "shared_preload_libraries = $AUTH_DELAY_LIB"

if echo "$AUTH_DELAY_LIB" | grep -q "auth_delay"; then
    echo "Модуль auth_delay загружен"
    psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SHOW auth_delay.milliseconds;"
else
    echo -e "${YELLOW}Модуль auth_delay НЕ загружен${NC}"
fi

echo ""
echo ">>> Проверка fail2ban (требует доступа к ОС):"
echo "Команды для проверки:"
echo "  systemctl status fail2ban"
echo "  fail2ban-client status postgresql"
echo "  cat /etc/fail2ban/jail.local"

echo ""
echo ">>> Проверка методов аутентификации в pg_hba.conf:"
echo "Типы и их значение:"
echo "  md5, scram-sha-256 - встроенные УЗ с паролем"
echo "  ldap, gss - доменные УЗ"
echo "  pam - УЗ ОС с паролем"
echo "  peer - УЗ ОС без пароля (локально)"
echo "  trust - без пароля (опасно!)"

echo ""
echo "================================================================================"
echo "                    СУБД.1.15 - ПРАВА НА ФАЙЛЫ СУБД"
echo "================================================================================"

echo ""
echo ">>> Путь к data_directory:"
DATA_DIR=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SHOW data_directory;")
echo "data_directory = $DATA_DIR"

echo ""
echo ">>> Для Linux проверьте права командой:"
echo "find $DATA_DIR -maxdepth 1 -type d -exec ls -ld {} +"
echo ""
echo "Норма: drwx------ (только postgres)"
echo "Плохо: drwxr-xr-x или наличие Everyone/Users"

echo ""
echo ">>> Для Windows:"
echo "icacls \"C:\Program Files\PostgreSQL\14\data\""
echo ""
echo "Норма: NT SERVICE\postgresql, NT AUTHORITY\SYSTEM, BUILTIN\Administrators"
echo "Плохо: Everyone, Users, Authenticated Users"

echo ""
echo "================================================================================"
echo "                    СУБД.1.16 - ДОПОЛНИТЕЛЬНЫЕ ПРОВЕРКИ КОНФИГУРАЦИИ"
echo "================================================================================"

echo ""
echo ">>> Версия PostgreSQL:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SELECT version();"

echo ""
echo ">>> Время работы сервера:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SELECT NOW() - pg_postmaster_start_time() as uptime;"

echo ""
echo ">>> Размер базы данных:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SELECT pg_size_pretty(pg_database_size(current_database())) as size;"

echo ""
echo ">>> Настройки безопасности:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT name, setting 
FROM pg_settings 
WHERE name IN (
    'row_security',
    'standard_conforming_strings',
    'escape_string_warning'
);
"

echo ""
echo "================================================================================"
echo "                    СУБД.1.17 - ПРОВЕРКА ВЕРСИЙ И РАСШИРЕНИЙ"
echo "================================================================================"

echo ""
echo ">>> Список всех баз данных:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT datname, pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database 
WHERE datistemplate = false
ORDER BY datname;
"

echo ""
echo ">>> Расширения в текущей базе:"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
SELECT extname, extversion, nspname
FROM pg_extension
ORDER BY extname;
"

echo ""
echo ">>> Для проверки всех баз выполните:"
echo "psql -At -c \"SELECT datname FROM pg_database WHERE datistemplate = false;\" | while read db; do"
echo "  echo \"=== База: \$db ===\""
echo "  psql -d \"\$db\" -c \"SELECT extname, extversion FROM pg_extension;\""
echo "done"

echo ""
echo "================================================================================"
echo "                    ИТОГОВАЯ СТАТИСТИКА"
echo "================================================================================"

TOTAL_ROLES=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SELECT COUNT(*) FROM pg_roles;")
SUPERUSERS=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SELECT COUNT(*) FROM pg_roles WHERE rolsuper = true;")
LOGIN_ROLES=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SELECT COUNT(*) FROM pg_roles WHERE rolcanlogin = true;")
ACTIVE_CONN=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SELECT COUNT(*) FROM pg_stat_activity WHERE backend_type = 'client backend';")
TOTAL_DBS=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SELECT COUNT(*) FROM pg_database WHERE datistemplate = false;")

echo "Всего ролей: $TOTAL_ROLES"
echo "Суперпользователей: $SUPERUSERS"
echo "Ролей с возможностью входа: $LOGIN_ROLES"
echo "Активных подключений: $ACTIVE_CONN"
echo "Всего баз данных: $TOTAL_DBS"

echo ""
echo "================================================================================"
echo "                    РЕКОМЕНДАЦИИ"
echo "================================================================================"
echo "1. Убедитесь, что listen_addresses ограничен конкретными IP"
echo "2. Проверьте pg_hba.conf на наличие 'trust' и '0.0.0.0/0'"
echo "3. Используйте scram-sha-256 вместо md5"
echo "4. Включите SSL (ssl = on) и ssl_min_protocol_version = 'TLSv1.2'"
echo "5. Настройте логирование (log_statement, log_connections)"
echo "6. Удалите тестовые учетные записи"
echo "7. Проверьте права на файлы данных и логов"
echo "8. Регулярно обновляйте PostgreSQL и расширения"
echo "================================================================================"

} | tee "$OUTPUT_FILE"

echo ""
echo "================================================================================"
echo "Отчет сохранен в файл: $OUTPUT_FILE"
echo "================================================================================"
Больше возможностей при регистрации:
