Yuriy Gavrilov

Welcome to my personal place for love, peace and happiness 🤖

Follow this blog

Trino + OTP верификация запросов в bi – параноя.mode = true

Представьте: дашборд в BI видят все сотрудники. Закрыть его нельзя — так исторически сложилось. Но данные нужно скрыть. Решение — динамическая фильтрация на уровне SQL: пользователь вводит логин и одноразовый код (TOTP), а запрос показывает только те строки, которые ему разрешены.

Это не полноценная система безопасности, но это работающий барьер, который можно внедрить без изменения BI-системы. В статье — пошаговая инструкция: от демо-таблиц до настройки прав в Trino, генерации QR-кодов и увеличения окна действия OTP.

Архитектура

  • `catalog.schema.dashboard_users` — справочник сотрудников: логин, имя, отдел, должность, руководитель, TOTP-секрет, флаг активности.
  • `catalog.schema.dashboard_data` — данные дашборда. У каждой строки есть политика доступа: `ALL`, `OWNER`, `DEPARTMENT`, `MANAGER`, `DIRECTOR`.
  • Функция `verify_totp` — Python UDF, проверяет OTP-код по секрету и текущему времени.
  • Итоговый запрос — аутентифицирует пользователя и фильтрует строки по атрибутам.

Пользователь вводит логин и OTP как параметры BI. Если они верны — получает свои данные. Если нет — пустой результат.

Шаг 1. Демо-таблицы

Создадим две таблицы в вымышленной схеме `catalog.schema`.

CREATE TABLE catalog.schema.dashboard_users (
    login         VARCHAR,
    employee_name VARCHAR,
    department    VARCHAR,
    position_name VARCHAR,
    manager_login VARCHAR,
    totp_secret   VARCHAR,
    is_active     BOOLEAN
);

INSERT INTO catalog.schema.dashboard_users VALUES
    ('vasya', 'Василий', 'sales',    'manager',  'ivan',  'JBSWY3DPEHPK3PXP', true),
    ('petya', 'Пётр',    'sales',    'analyst',  'vasya', 'KRSXG5CTMVRXEZLU', true),
    ('masha', 'Мария',   'finance',  'analyst',  'ivan',  'NB2W45DFOIZA',     true),
    ('ivan',  'Иван',    'management','director', null,   'MFRGGZDFMZTWQ2LK', true);

CREATE TABLE catalog.schema.dashboard_data (
    row_id        BIGINT,
    row_title     VARCHAR,
    amount        DECIMAL(12,2),
    owner_login   VARCHAR,
    department    VARCHAR,
    manager_login VARCHAR,
    access_policy VARCHAR
);

INSERT INTO catalog.schema.dashboard_data VALUES
    (1, 'Личная строка Васи',       1000.00, 'vasya', 'sales',   'ivan',  'OWNER'),
    (2, 'Личная строка Пети',       2000.00, 'petya', 'sales',   'vasya', 'OWNER'),
    (3, 'Общая строка отдела Sales',3000.00, null,    'sales',   null,    'DEPARTMENT'),
    (4, 'Общая строка Finance',     4000.00, null,    'finance', null,    'DEPARTMENT'),
    (5, 'Строка команды Васи',      5000.00, 'petya', 'sales',   'vasya', 'MANAGER'),
    (6, 'Строка команды Ивана',     6000.00, 'masha', 'finance', 'ivan',  'MANAGER'),
    (7, 'Директорская строка',      7000.00, null,    null,      null,    'DIRECTOR'),
    (8, 'Публичная строка',         8000.00, null,    null,      null,    'ALL');

Либо можно сгенерировать коды разово прямо в запросе.

Шаг 1.1. Массовая генерация TOTP-секретов и QR-кодов

Когда пользователей много, вручную придумывать Base32-секреты неудобно. Можно сгенерировать их прямо в Trino при вставке в таблицу `dashboard_users`. Ниже — готовый блок для демо и тестов.

Важно: `random()` в Trino не является криптостойким генератором. Для продакшена секреты лучше генерировать вне Trino (например, через `openssl rand -base64 20` или Python `pyotp.random_base32()`) и вставлять уже готовыми. Для демонстрации и параноидального режима «лишь бы закрыть дашборд» этого достаточно.

Вставка пользователей с автоматической генерацией секретов

INSERT INTO catalog.schema.dashboard_users (
    login,
    employee_name,
    department,
    position_name,
    manager_login,
    totp_secret,
    is_active
)
SELECT
    login,
    employee_name,
    department,
    position_name,
    manager_login,

    -- Генерация Base32-секрета длиной 16 символов (80 бит)
    array_join(
        transform(
            sequence(1, 16),
            i -> substr(
                'ABCDEFGHIJKLMNOPQRSTUVWXYZ234567',
                CAST(floor(random() * 32) + 1 AS INTEGER),
                1
            )
        ),
        ''
    ) AS totp_secret,

    true AS is_active
FROM (
    VALUES
        ('vasya', 'Василий', 'sales',     'manager',  'ivan'),
        ('petya', 'Пётр',    'sales',     'analyst',  'vasya'),
        ('masha', 'Мария',   'finance',   'analyst',  'ivan'),
        ('ivan',  'Иван',    'management','director', CAST(NULL AS VARCHAR))
) AS src (
    login,
    employee_name,
    department,
    position_name,
    manager_login
);

Как получить строку для QR-кода

После вставки можно сразу сформировать URI формата `otpauth://`, который останется только превратить в QR-код любым генератором:

SELECT
    login,
    totp_secret,
    'otpauth://totp/DemoBI:' || login
        || '?secret=' || totp_secret
        || '&issuer=DemoBI' AS otpauth_uri
FROM catalog.schema.dashboard_users;

Пример результата:

login totp_secret otpauth_uri
vasya JBSWY3DPEHPK3PXP otpauth://totp/DemoBI:vasya?secret=JBSWY3DPEHPK3PXP&issuer=DemoBI
petya KRSXG5CTMVRXEZLU otpauth://totp/DemoBI:petya?secret=KRSXG5CTMVRXEZLU&issuer=DemoBI

Эту строку можно вставить в любой генератор QR-кодов (онлайн или офлайн), а затем отсканировать приложением:

  • Google Authenticator,
  • Yandex ID,
  • KeePass (с плагином TOTP),
  • и любым другим менеджером паролей, поддерживающим TOTP.

Настройка длины секрета

  • `sequence(1, 16)` — 16 символов Base32 = 80 бит. Этого достаточно для большинства случаев и именно такую длину часто используют по умолчанию.
  • Если хотите более длинный секрет, замените `16` на `32`. Тогда получится 160 бит.
  • Алфавит `’ABCDEFGHIJKLMNOPQRSTUVWXYZ234567’` — это стандартный Base32-алфавит (без цифр 0, 1, 8, 9). Менять его не нужно.

Если нужен детерминированный секрет (не рекомендуется)

Иногда для тестов хотят, чтобы секрет зависел от логина. Можно использовать хеш:

substr(
    upper(to_base32(from_utf8(login || 'some_salt'))),
    1, 16
) AS totp_secret

Но это небезопасно: зная логин и соль, можно вычислить секрет. Для реальной эксплуатации используйте случайную генерацию вне Trino.

Шаг 2. Inline-функция для тестов в DBeaver

В DBeaver можно использовать `WITH FUNCTION` — Python-код прямо в запросе. Это удобно для отладки, но не работает в BI, потому что BI схлопывает запрос в одну строку и ломает форматирование Python.

WITH FUNCTION verify_totp(secret VARCHAR, code VARCHAR, time_step BIGINT)
RETURNS BOOLEAN
LANGUAGE PYTHON
WITH (handler = 'verify_handler')
AS $$
import hmac, hashlib, struct, base64

def verify_handler(secret_b32, code, time_step):
    if secret_b32 is None or code is None or time_step is None:
        return False

    code = str(code).strip()
    if not code.isdigit() or len(code) != 6:
        return False

    secret = str(secret_b32).strip().replace(" ", "").upper()
    padding = "=" * ((8 - len(secret) % 8) % 8)

    try:
        key = base64.b32decode(secret + padding, casefold=True)
    except Exception:
        return False

    current = int(time_step // 30)   # 30 секунд — стандарт TOTP
    for offset in (-1, 0, 1):
        counter = current + offset
        msg = struct.pack(">Q", counter)
        digest = hmac.new(key, msg, hashlib.sha1).digest()
        o = digest[19] & 15
        expected = (struct.unpack(">I", digest[o:o+4])[0] & 0x7fffffff) % 1000000
        expected_code = f"{expected:06d}"
        if hmac.compare_digest(expected_code, code):
            return True
    return False
$$
SELECT verify_totp('JBSWY3DPEHPK3PXP', '123456', CAST(to_unixtime(now()) AS BIGINT));

Важно: тело функции должно начинаться с новой строки после `$$`. Если BI отправляет всё в одну строку — будет ошибка `Function definition must start with a newline after opening quotes`.

Шаг 3. Регистрация функции администратором

Чтобы BI-запросы не содержали Python-код, функцию нужно создать один раз на стороне Trino. Это делает администратор или пользователь с правами на создание функций.

DROP FUNCTION IF EXISTS catalog.schema.verify_totp(VARCHAR, VARCHAR, BIGINT);

CREATE FUNCTION catalog.schema.verify_totp(
    secret VARCHAR,
    code VARCHAR,
    time_step BIGINT
)
RETURNS BOOLEAN
LANGUAGE PYTHON
WITH (handler = 'verify_handler')
AS $$
import hmac, hashlib, struct, base64

def verify_handler(secret_b32, code, time_step):
    if secret_b32 is None or code is None or time_step is None:
        return False

    code = str(code).strip()
    if not code.isdigit() or len(code) != 6:
        return False

    secret = str(secret_b32).strip().replace(" ", "").upper()
    padding = "=" * ((8 - len(secret) % 8) % 8)

    try:
        key = base64.b32decode(secret + padding, casefold=True)
    except Exception:
        return False

    current = int(time_step // 30)
    for offset in (-1, 0, 1):
        counter = current + offset
        msg = struct.pack(">Q", counter)
        digest = hmac.new(key, msg, hashlib.sha1).digest()
        o = digest[19] & 15
        expected = (struct.unpack(">I", digest[o:o+4])[0] & 0x7fffffff) % 1000000
        expected_code = f"{expected:06d}"
        if hmac.compare_digest(expected_code, code):
            return True
    return False
$$;

Проверка:

SELECT catalog.schema.verify_totp(
    'JBSWY3DPEHPK3PXP',
    '465617',
    CAST(to_unixtime(now()) AS BIGINT)
) AS is_valid;

Примечание: в вашей среде создание функции может быть не разрешено без настройки прав. Об этом — следующий шаг.

Шаг 4. Настройка прав доступа

Нужно разрешить группе admins создавать функции в схеме `catalog.schema`, а группе bi_users — только вызывать `verify_totp`. В Trino это делается через system access control.

Вариант A. File-based ACL

Файл `/etc/trino/access-control.properties`:

access-control.name=file
security.config-file=/etc/trino/rules/access-control.json
security.refresh-period=1m

Файл `access-control.json`:

{
  "catalogs": [
    {
      "group": "admins",
      "catalog": "catalog",
      "allow": "all"
    },
    {
      "group": "bi_users",
      "catalog": "catalog",
      "allow": "read-only"
    }
  ],
  "schemas": [
    {
      "group": "admins",
      "catalog": "catalog",
      "schema": "schema",
      "owner": true
    },
    {
      "group": "bi_users",
      "catalog": "catalog",
      "schema": "schema",
      "owner": false
    }
  ],
  "functions": [
    {
      "group": "admins",
      "catalog": "catalog",
      "schema": "schema",
      "function": ".*",
      "privileges": ["EXECUTE", "GRANT_EXECUTE", "OWNERSHIP"]
    },
    {
      "group": "bi_users",
      "catalog": "catalog",
      "schema": "schema",
      "function": "verify_totp",
      "privileges": ["EXECUTE"]
    }
  ]
}

Вариант B. OPA (Rego)

Если Trino использует OPA, политика может выглядеть так:

package trino

default allow := false

is_admin {
    input.context.identity.groups[_] == "admins"
}

is_bi_user {
    input.context.identity.groups[_] == "bi_users"
}

is_target_schema {
    input.action.resource.catalog.name == "catalog"
    input.action.resource.schema.name == "schema"
}

is_verify_totp {
    input.action.resource.catalog.name == "catalog"
    input.action.resource.schema.name == "schema"
    input.action.resource.function.name == "verify_totp"
}

allow {
    is_admin
    is_target_schema
    input.action.operation == "CreateFunction"
}

allow {
    is_admin
    is_target_schema
    input.action.operation == "DropFunction"
}

allow {
    is_admin
    is_target_schema
    input.action.operation == "ExecuteFunction"
}

allow {
    is_bi_user
    is_verify_totp
    input.action.operation == "ExecuteFunction"
}

Точные названия операций (`CreateFunction`, `ExecuteFunction`) лучше уточнить в логах OPA при тестовом запросе.

Шаг 5. Запрос для BI (без Python)

После регистрации функции BI-запрос становится обычным SQL. Пользователь вводит `:login` и `:otp` как параметры.

WITH auth_user AS (
    SELECT
        login,
        employee_name,
        department,
        position_name,
        manager_login
    FROM catalog.schema.dashboard_users
    WHERE login = lower(trim(':login'))
      AND is_active = true
      AND catalog.schema.verify_totp(
            totp_secret,
            trim(':otp'),
            CAST(to_unixtime(now()) AS BIGINT)
          )
    LIMIT 1
)
SELECT
    d.row_id,
    d.row_title,
    d.amount,
    d.owner_login,
    d.department,
    d.manager_login,
    d.access_policy,
    a.login AS current_login,
    a.employee_name AS current_employee_name,
    a.department AS current_department,
    a.position_name AS current_position_name,
    CASE
        WHEN d.access_policy = 'ALL' THEN 'Видно всем авторизованным'
        WHEN d.access_policy = 'OWNER' AND d.owner_login = a.login THEN 'Видно владельцу строки'
        WHEN d.access_policy = 'DEPARTMENT' AND d.department = a.department THEN 'Видно сотруднику отдела'
        WHEN d.access_policy = 'MANAGER' AND d.manager_login = a.login THEN 'Видно руководителю'
        WHEN d.access_policy = 'DIRECTOR' AND a.position_name = 'director' THEN 'Видно директору'
        ELSE null
    END AS access_reason
FROM catalog.schema.dashboard_data d
CROSS JOIN auth_user a
WHERE
       d.access_policy = 'ALL'
    OR (d.access_policy = 'OWNER' AND d.owner_login = a.login)
    OR (d.access_policy = 'DEPARTMENT' AND d.department = a.department)
    OR (d.access_policy = 'MANAGER' AND d.manager_login = a.login)
    OR (d.access_policy = 'DIRECTOR' AND a.position_name = 'director');

Важно про параметры: если BI подставляет их как сырой текст, нужны кавычки: `’:login’`, `’:otp’`. Если BI использует bind-параметры — кавычки не нужны: `:login`, `:otp`. В предыдущих ошибках (`Column ‘jbswy3dpehpk3pxp’ cannot be resolved`) видно, что в вашем случае нужны именно кавычки.

Шаг 6. Логика доступа

  • `auth_user` — возвращает 0 строк, если логин не найден, пользователь неактивен или OTP неверный.
  • `CROSS JOIN auth_user` — если `auth_user` пуст, результат всего запроса пуст.
  • `WHERE` с `OR` — оставляет только те строки, которые разрешены политикой:
    • `ALL` — всем авторизованным;
    • `OWNER` — владельцу (`owner_login = a.login`);
    • `DEPARTMENT` — сотруднику того же отдела;
    • `MANAGER` — руководителю (`manager_login = a.login`);
    • `DIRECTOR` — директору (`position_name = ‘director’`).

Пример: `vasya` увидит свои строки, строки отдела `sales`, строки, где он руководитель, и публичные. `petya` — только свои, отдел `sales` и публичные.

Шаг 7. Генерация QR-кода для OTP

Каждому пользователю нужно выдать TOTP-секрет и удобно передать его в приложение-аутентификатор. Стандартный способ — использовать URI формата `otpauth://`.

Пример для пользователя `vasya`:

otpauth://totp/DemoBI:vasya?secret=JBSWY3DPEHPK3PXP&issuer=DemoBI

Здесь:

  • `DemoBI:vasya` — метка, которая отобразится в приложении. Можно заменить на название отчёта, отдела или что угодно, например `SalesDashboard:vasya`.
  • `secret=JBSWY3DPEHPK3PXP` — Base32-секрет.
  • `issuer=DemoBI` — имя издателя, обычно название компании или системы.

Эту строку можно вставить в любой генератор QR-кодов (онлайн или офлайн). После генерации QR-код сканируется приложением:

  • Google Authenticator,
  • Yandex ID,
  • KeePass (с плагином TOTP),
  • и любым другим менеджером паролей, поддерживающим TOTP.

Это стандарт, поэтому совместимость широкая. QR-код можно распечатать или отправить пользователю.

Совет: если у вас много пользователей, сгенерируйте QR-коды автоматически из таблицы `dashboard_users`, подставляя логин и секрет.

Шаг 8. Увеличение окна действия OTP (2–5 минут)

По умолчанию TOTP-код действует 30 секунд. Это может быть неудобно: пользователь не успевает ввести код. Можно увеличить окно действия, изменив функцию.

В Python-коде есть строка:

current = int(time_step // 30)

Здесь `30` — это количество секунд в одном временном шаге. Если заменить `30` на `300`, то код будет действителен 5 минут (300 секунд). При этом приложение-аутентификатор продолжит генерировать коды каждые 30 секунд, но сервер будет принимать любой код, сгенерированный в течение этих 5 минут. Это удобно и безопасно: окно не бесконечное, но достаточное, чтобы успеть ввести код.

Пример изменённой функции:

current = int(time_step // 300)   # 5 минут
for offset in (-1, 0, 1):
    ...

Если оставить смещения `-1, 0, 1`, то общее окно станет 15 минут (5 минут назад, текущие 5 минут, 5 минут вперёд). Это может быть избыточно. Лучше убрать смещения и проверять только текущий интервал:

current = int(time_step // 300)
counter = current
msg = struct.pack(">Q", counter)
...
# без цикла for

Тогда код будет действителен ровно 5 минут.

Если хотите 2 минуты — используйте `120` вместо `300`.

Важно: после изменения функции её нужно пересоздать ( `DROP FUNCTION` и `CREATE FUNCTION` ). Все пользователи автоматически начнут работать с новым окном.

Шаг 9. Безопасность

  1. Секреты TOTP нельзя хранить в открытой схеме. Лучше вынести `dashboard_users` в закрытую схему, например `security.users`.
  2. BI-пользователи не должны иметь прямой доступ к таблице с секретами. Только к функции и к данным.
  3. OTP-код попадает в логи Trino и BI. Логи могут быть доступны администраторам или храниться в системе. Это не смертельно, потому что код короткоживущий, но помните: любой, кто видит лог, может увидеть введённый OTP. Если окно действия увеличено до 5 минут, риск возрастает. По возможности используйте реального пользователя из аутентификации BI, а не ручной ввод логина.
  4. Права на создание функций — только у группы `admins`. BI-пользователям — только `EXECUTE` на конкретную функцию.

Заключение

Мы построили параноидальный, но рабочий механизм: OTP-аутентификация + динамическая фильтрация строк на уровне SQL. Это не заменяет полноценный RBAC, но позволяет быстро закрыть дашборд от посторонних глаз без изменения BI-системы.

Что проверено: inline-функция в DBeaver, запрос с фильтрацией, логика доступа.
Что требует настройки: регистрация функции и права в Trino. После настройки прав (шаг 4) функция создаётся один раз, а BI-запросы становятся чистыми и безопасными.

Удачи в паранойе! :)

Follow this blog
Send
Share
Tweet
Pin
Ctrl ←Современный семантический слой на dbt + MetricFlow + DuckDB: макросы вместо пакетов
© Yuriy Gavrilov - B[u]g - for charity.gavrilov.eth, 2022–2026 RSS
Powered by Aegea