---

-- ============================================================================
-- Аудит безопасности PostgreSQL
-- ============================================================================
\echo '================================================================================'
\echo '                    АУДИТ БЕЗОПАСНОСТИ POSTGRESQL'
\echo '================================================================================'
\echo ''
\echo 'Дата проверки:' `SELECT TO_CHAR(NOW(), 'DD.MM.YYYY HH24:MI:SS');`
\echo 'Версия СУБД:' `SELECT version();`
\echo '================================================================================'

-- ============================================================================
-- СУБД.1.1 - Учетные записи по умолчанию и с простыми паролями
-- ============================================================================
\echo ''
\echo '>>> СУБД.1.1 - Учетные записи по умолчанию и с простыми паролями'
\echo '--------------------------------------------------------------------------------'

SELECT 
    'INFO' as status,
    'Всего пользователей с возможностью входа: ' || COUNT(*) as result
FROM pg_roles 
WHERE rolcanlogin = true;

SELECT 
    CASE 
        WHEN rolname IN ('postgres', 'admin', 'test', 'user', 'guest', 'root', 'sa') 
        THEN 'WARNING'
        ELSE 'OK'
    END as status,
    'Пользователь: ' || rolname || 
    ' | Суперпользователь: ' || rolsuper || 
    ' | Может войти: ' || rolcanlogin as result
FROM pg_roles
WHERE rolcanlogin = true
  AND rolname IN ('postgres', 'admin', 'test', 'user', 'guest', 'root', 'sa')
ORDER BY rolname;

-- ============================================================================
-- СУБД.1.2 - Парольная политика
-- ============================================================================
\echo ''
\echo '>>> СУБД.1.2 - Парольная политика'
\echo '--------------------------------------------------------------------------------'

SELECT 
    CASE 
        WHEN setting = 'scram-sha-256' THEN 'OK'
        ELSE 'WARNING'
    END as status,
    'password_encryption = ' || setting as result
FROM pg_settings 
WHERE name = 'password_encryption';

SELECT 
    CASE 
        WHEN setting = 'on' THEN 'OK'
        ELSE 'WARNING'
    END as status,
    'log_connections = ' || setting as result
FROM pg_settings 
WHERE name = 'log_connections';

SELECT 
    CASE 
        WHEN setting = 'on' THEN 'OK'
        ELSE 'WARNING'
    END as status,
    'log_disconnections = ' || setting as result
FROM pg_settings 
WHERE name = 'log_disconnections';

-- ============================================================================
-- СУБД.1.3 - Одинаковые пароли у администраторов
-- ============================================================================
\echo ''
\echo '>>> СУБД.1.3 - Одинаковые пароли у суперпользователей'
\echo '--------------------------------------------------------------------------------'

SELECT 
    CASE 
        WHEN COUNT(*) > 0 THEN 'WARNING'
        ELSE 'OK'
    END as status,
    'Найдено суперпользователей с одинаковыми паролями: ' || COUNT(*) as result
FROM (
    SELECT passwd 
    FROM pg_shadow 
    WHERE usesuper = true AND passwd IS NOT NULL
    GROUP BY passwd 
    HAVING COUNT(*) > 1
) as duplicates;

SELECT 
    'DETAIL: ' || passwd || ' -> ' || array_agg(usename)::text as result
FROM pg_shadow 
WHERE usesuper = true AND passwd IS NOT NULL
GROUP BY passwd 
HAVING COUNT(*) > 1;

-- ============================================================================
-- СУБД.1.4 - Неиспользуемые учетные записи
-- ============================================================================
\echo ''
\echo '>>> СУБД.1.4 - Неиспользуемые учетные записи'
\echo '--------------------------------------------------------------------------------'

SELECT 
    'INFO' as status,
    'Активных сессий сейчас: ' || COUNT(*) as result
FROM pg_stat_activity 
WHERE usename IS NOT NULL;

SELECT 
    'WARNING' as status,
    'Пользователи без активных сессий (возможно неиспользуемые): ' || 
    array_agg(rolname)::text as result
FROM pg_roles r
LEFT JOIN pg_stat_activity a ON r.rolname = a.usename
WHERE a.usename IS NULL 
  AND r.rolcanlogin = true
  AND r.rolname NOT IN ('postgres');

-- ============================================================================
-- СУБД.1.5 - Неограниченные подключения с любых IP
-- ============================================================================
\echo ''
\echo '>>> СУБД.1.5 - Настройка listen_addresses'
\echo '--------------------------------------------------------------------------------'

SELECT 
    CASE 
        WHEN setting = '*' OR setting ~ '0\.0\.0\.0' THEN 'WARNING'
        WHEN setting = 'localhost' OR setting = '' THEN 'OK'
        ELSE 'INFO'
    END as status,
    'listen_addresses = ' || setting as result
FROM pg_settings 
WHERE name = 'listen_addresses';

-- ============================================================================
-- СУБД.1.6 - Пользователи с полными правами (Full Access)
-- ============================================================================
\echo ''
\echo '>>> СУБД.1.6 - Пользователи с расширенными правами'
\echo '--------------------------------------------------------------------------------'

SELECT 
    CASE 
        WHEN rolsuper THEN 'WARNING'
        ELSE 'INFO'
    END as status,
    'User: ' || rolname || 
    ' | SUPER: ' || rolsuper || 
    ' | CREATEDB: ' || rolcreatedb || 
    ' | CREATEROLE: ' || rolcreaterole || 
    ' | REPLICATION: ' || rolreplication as result
FROM pg_roles
WHERE rolcanlogin = true
  AND (rolsuper OR rolcreatedb OR rolcreaterole OR rolreplication)
ORDER BY rolsuper DESC, rolname;

-- ============================================================================
-- СУБД.1.7 - Одинаковые учетки для разных приложений
-- ============================================================================
\echo ''
\echo '>>> СУБД.1.7 - Использование одних учеток разными приложениями'
\echo '--------------------------------------------------------------------------------'

SELECT 
    'INFO' as status,
    'User: ' || usename || 
    ' | Apps: ' || array_agg(DISTINCT application_name)::text || 
    ' | Connections: ' || COUNT(*) as result
FROM pg_stat_activity
WHERE usename IS NOT NULL
GROUP BY usename
HAVING COUNT(DISTINCT application_name) > 1
ORDER BY COUNT(*) DESC;

-- ============================================================================
-- СУБД.1.8 - Права на системные таблицы
-- ============================================================================
\echo ''
\echo '>>> СУБД.1.8 - Права на системные схемы'
\echo '--------------------------------------------------------------------------------'

SELECT 
    'WARNING' as status,
    'Grantee: ' || grantee || 
    ' | Schema: ' || table_schema || 
    ' | Privilege: ' || privilege_type as result
FROM information_schema.role_table_grants
WHERE table_schema IN ('pg_catalog', 'information_schema')
  AND grantee NOT IN ('postgres', 'pg_monitor', 'pg_read_all_settings')
LIMIT 10;

-- ============================================================================
-- СУБД.1.9 и СУБД.1.10 - Доступ со всех интерфейсов и ограничения по IP
-- ============================================================================
\echo ''
\echo '>>> СУБД.1.9/1.10 - Настройки сетевого доступа'
\echo '--------------------------------------------------------------------------------'

SELECT 
    CASE 
        WHEN setting = '*' THEN 'WARNING'
        ELSE 'OK'
    END as status,
    'listen_addresses = ' || setting as result
FROM pg_settings 
WHERE name = 'listen_addresses';

SELECT 
    CASE 
        WHEN setting = 'on' THEN 'OK'
        ELSE 'WARNING'
    END as status,
    'ssl = ' || setting as result
FROM pg_settings 
WHERE name = 'ssl';

-- ============================================================================
-- СУБД.1.11 - Шифрование
-- ============================================================================
\echo ''
\echo '>>> СУБД.1.11 - Настройки шифрования'
\echo '--------------------------------------------------------------------------------'

SELECT 
    CASE 
        WHEN setting = 'scram-sha-256' THEN 'OK'
        ELSE 'WARNING'
    END as status,
    'password_encryption = ' || setting as result
FROM pg_settings 
WHERE name = 'password_encryption';

SELECT 
    CASE 
        WHEN setting = 'on' THEN 'OK'
        ELSE 'WARNING'
    END as status,
    'ssl = ' || setting as result
FROM pg_settings 
WHERE name = 'ssl';

-- ============================================================================
-- СУБД.1.12 - SSL/TLS параметры
-- ============================================================================
\echo ''
\echo '>>> СУБД.1.12 - Параметры SSL/TLS'
\echo '--------------------------------------------------------------------------------'

SELECT 
    CASE 
        WHEN setting IN ('TLSv1.2', 'TLSv1.3') THEN 'OK'
        ELSE 'WARNING'
    END as status,
    'ssl_min_protocol_version = ' || setting as result
FROM pg_settings 
WHERE name = 'ssl_min_protocol_version';

SELECT 
    'INFO' as status,
    'ssl_ciphers = ' || setting as result
FROM pg_settings 
WHERE name = 'ssl_ciphers';

-- ============================================================================
-- СУБД.1.13 - Доступность журналов аудита
-- ============================================================================
\echo ''
\echo '>>> СУБД.1.13 - Настройки логирования'
\echo '--------------------------------------------------------------------------------'

SELECT 
    CASE 
        WHEN setting IN ('stderr', 'csvlog', 'syslog') THEN 'OK'
        ELSE 'WARNING'
    END as status,
    'log_destination = ' || setting as result
FROM pg_settings 
WHERE name = 'log_destination';

SELECT 
    CASE 
        WHEN setting = 'on' THEN 'OK'
        ELSE 'WARNING'
    END as status,
    'logging_collector = ' || setting as result
FROM pg_settings 
WHERE name = 'logging_collector';

SELECT 
    CASE 
        WHEN setting != 'none' THEN 'OK'
        ELSE 'WARNING'
    END as status,
    'log_statement = ' || setting as result
FROM pg_settings 
WHERE name = 'log_statement';

SELECT 
    CASE 
        WHEN setting = 'on' THEN 'OK'
        ELSE 'WARNING'
    END as status,
    'log_connections = ' || setting as result
FROM pg_settings 
WHERE name = 'log_connections';

-- ============================================================================
-- СУБД.1.14 - Ограничения на попытки ввода пароля
-- ============================================================================
\echo ''
\echo '>>> СУБД.1.14 - Блокировка при неудачных попытках входа'
\echo '--------------------------------------------------------------------------------'

SELECT 
    'INFO' as status,
    'PostgreSQL не имеет встроенной блокировки. Рекомендуется использовать PAM.' as result;

-- ============================================================================
-- СУБД.1.15 - Контроль доступа к файлам БД
-- ============================================================================
\echo ''
\echo '>>> СУБД.1.15 - Опасные функции и права'
\echo '--------------------------------------------------------------------------------'

SELECT 
    CASE 
        WHEN rolsuper THEN 'WARNING'
        ELSE 'OK'
    END as status,
    'Пользователи с правами суперпользователя (могут использовать COPY FROM PROGRAM): ' || 
    array_agg(rolname)::text as result
FROM pg_roles
WHERE rolsuper = true;

-- ============================================================================
-- СУБД.1.16 - Другие уязвимости конфигурации
-- ============================================================================
\echo ''
\echo '>>> СУБД.1.16 - Дополнительные проверки конфигурации'
\echo '--------------------------------------------------------------------------------'

SELECT 
    CASE 
        WHEN setting::integer > 0 THEN 'OK'
        ELSE 'WARNING'
    END as status,
    'log_min_duration_statement = ' || setting || ' ms' as result
FROM pg_settings 
WHERE name = 'log_min_duration_statement';

SELECT 
    CASE 
        WHEN setting = 'on' THEN 'OK'
        ELSE 'WARNING'
    END as status,
    'row_security = ' || setting as result
FROM pg_settings 
WHERE name = 'row_security';

-- ============================================================================
-- СУБД.1.17 - Уязвимости ПО (версия)
-- ============================================================================
\echo ''
\echo '>>> СУБД.1.17 - Версия PostgreSQL'
\echo '--------------------------------------------------------------------------------'

SELECT 
    'INFO' as status,
    version() as result;

SELECT 
    CASE 
        WHEN (SELECT split_part(version(), ' ', 2)) < '14' THEN 'WARNING'
        ELSE 'OK'
    END as status,
    'Рекомендуется использовать PostgreSQL 14 или выше' as result;

-- ============================================================================
-- Итоговая статистика
-- ============================================================================
\echo ''
\echo '================================================================================'
\echo '                           ИТОГОВАЯ СТАТИСТИКА'
\echo '================================================================================'

SELECT 
    'Всего ролей: ' || COUNT(*) as total_roles
FROM pg_roles;

SELECT 
    'Суперпользователей: ' || COUNT(*) as superusers
FROM pg_roles 
WHERE rolsuper = true;

SELECT 
    'Ролей с возможностью входа: ' || COUNT(*) as login_roles
FROM pg_roles 
WHERE rolcanlogin = true;

SELECT 
    'Активных подключений: ' || COUNT(*) as active_connections
FROM pg_stat_activity 
WHERE backend_type = 'client backend';

\echo '================================================================================'
\echo '                         АУДИТ ЗАВЕРШЕН'
\echo '================================================================================'