Мульти-тенантные БД с PostgreSQL Row Security

-- Включение RLS для таблицы
ALTER TABLE tenant_data ENABLE ROW LEVEL SECURITY;
 
-- Создание политики, ограничивающей видимость строк
CREATE POLICY tenant_isolation_policy ON tenant_data
    USING (tenant_id = current_setting('app.current_tenant')::INTEGER);

CREATE POLICY complex_policy ON tenant_data
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant')::INTEGER)
    WITH CHECK (tenant_id = current_setting('app.current_tenant')::INTEGER AND STATUS <> 'deleted');

CREATE POLICY role_based_policy ON tenant_data
    USING (
        CASE 
            WHEN CURRENT_USER = 'admin' THEN TRUE  -- Администраторы видят все строки
            WHEN pg_has_role(CURRENT_USER, 'reader', 'member') THEN tenant_id = current_setting('app.current_tenant')::INTEGER
            ELSE FALSE  -- Остальные не видят ничего
        END
    );

-- Создаем таблицу тенантов
CREATE TABLE tenants (
    tenant_id SERIAL PRIMARY KEY,
    tenant_name VARCHAR(100) NOT NULL,
    active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Создаем таблицу пользователей с привязкой к тенантам
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL REFERENCES tenants(tenant_id),
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    ROLE VARCHAR(20) NOT NULL DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(tenant_id, username),
    UNIQUE(email)
);
 
-- Создаем таблицу с данными, которые будем разграничивать
CREATE TABLE customer_data (
    data_id SERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL REFERENCES tenants(tenant_id),
    customer_name VARCHAR(100) NOT NULL,
    contact_info JSONB,
    STATUS VARCHAR(20) DEFAULT 'active',
    created_by INTEGER REFERENCES users(user_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Включаем RLS для таблицы с данными клиентов
ALTER TABLE customer_data ENABLE ROW LEVEL SECURITY;
 
-- Создаем базовую политику изоляции данных по тенантам
CREATE POLICY tenant_isolation ON customer_data
    USING (tenant_id = current_setting('app.current_tenant_id', TRUE)::INTEGER);

-- Создаем функцию для установки контекста тенанта
CREATE OR REPLACE FUNCTION set_tenant_context(p_tenant_id INTEGER)
RETURNS VOID AS $$
BEGIN
    -- Проверяем существование тенанта
    IF NOT EXISTS (SELECT 1 FROM tenants WHERE tenant_id = p_tenant_id) THEN
        RAISE EXCEPTION 'Tenant with ID % does not exist', p_tenant_id;
    END IF;
    
    -- Устанавливаем параметр сессии
    PERFORM set_config('app.current_tenant_id', p_tenant_id::TEXT, FALSE);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
 
-- Создаем функцию для получения текущего тенанта
CREATE OR REPLACE FUNCTION get_current_tenant_id()
RETURNS INTEGER AS $$
BEGIN
    RETURN current_setting('app.current_tenant_id', TRUE)::INTEGER;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

-- Создаем тенантов
INSERT INTO tenants (tenant_name) VALUES ('Acme Corp'), ('Globex Inc');
 
-- Получаем IDs тенантов
DO $$
DECLARE
    acme_id INTEGER;
    globex_id INTEGER;
BEGIN
    SELECT tenant_id INTO acme_id FROM tenants WHERE tenant_name = 'Acme Corp';
    SELECT tenant_id INTO globex_id FROM tenants WHERE tenant_name = 'Globex Inc';
    
    -- Создаем пользователей для каждого тенанта
    INSERT INTO users (tenant_id, username, email, password_hash, ROLE)
    VALUES 
        (acme_id, 'john', 'john@acme.com', 'hash123', 'admin'),
        (acme_id, 'sarah', 'sarah@acme.com', 'hash456', 'user'),
        (globex_id, 'mike', 'mike@globex.com', 'hash789', 'admin'),
        (globex_id, 'lisa', 'lisa@globex.com', 'hash012', 'user');
    
    -- Создаем данные для каждого тенанта
    INSERT INTO customer_data (tenant_id, customer_name, contact_info)
    VALUES 
        (acme_id, 'Customer A', '{"phone": "123-456-7890", "address": "123 Main St"}'),
        (acme_id, 'Customer B', '{"phone": "123-456-7891", "address": "124 Main St"}'),
        (globex_id, 'Customer X', '{"phone": "987-654-3210", "address": "456 Oak Ave"}'),
        (globex_id, 'Customer Y', '{"phone": "987-654-3211", "address": "457 Oak Ave"}');
END $$;

-- Устанавливаем контекст первого тенанта (Acme Corp)
SELECT set_tenant_context(1);
 
-- Запрашиваем данные клиентов
SELECT * FROM customer_data;
-- Должны видеть только клиентов Acme Corp
 
-- Переключаемся на контекст второго тенанта
SELECT set_tenant_context(2);
 
-- Запрашиваем данные клиентов
SELECT * FROM customer_data;
-- Должны видеть только клиентов Globex Inc

-- Создаем перечисление для возможных ролей
CREATE TYPE user_role AS ENUM ('tenant_admin', 'manager', 'regular_user', 'readonly');
 
-- Модифицируем таблицу пользователей
ALTER TABLE users 
DROP COLUMN ROLE,
ADD COLUMN ROLE user_role NOT NULL DEFAULT 'regular_user';
 
-- Создаем функцию авторизации
CREATE OR REPLACE FUNCTION authorize_user(p_username TEXT, p_tenant_id INTEGER)
RETURNS JSONB AS $$
DECLARE
    v_user_data JSONB;
BEGIN
    SELECT jsonb_build_object(
        'user_id', user_id,
        'tenant_id', tenant_id,
        'username', username,
        'role', ROLE
    ) INTO v_user_data
    FROM users
    WHERE username = p_username AND tenant_id = p_tenant_id;
    
    IF v_user_data IS NULL THEN
        RAISE EXCEPTION 'User not found or not authorized for this tenant';
    END IF;
    
    -- Устанавливаем контекст пользователя и тенанта
    PERFORM set_config('app.current_tenant_id', p_tenant_id::TEXT, FALSE);
    PERFORM set_config('app.current_user_id', (v_user_data->>'user_id'), FALSE);
    PERFORM set_config('app.current_user_role', (v_user_data->>'role'), FALSE);
    
    RETURN v_user_data;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Удаляем предыдущую политику
DROP POLICY IF EXISTS tenant_isolation ON customer_data;
 
-- Создаем более сложную политику с учетом ролей
CREATE POLICY tenant_isolation ON customer_data
USING (
    tenant_id = current_setting('app.current_tenant_id')::INTEGER
    AND (
        current_setting('app.current_user_role') IN ('tenant_admin', 'manager') 
        OR STATUS <> 'archived'
    )
);
 
-- Добавляем политику для операций обновления
CREATE POLICY tenant_data_update ON customer_data
FOR UPDATE
USING (tenant_id = current_setting('app.current_tenant_id')::INTEGER)
WITH CHECK (
    tenant_id = current_setting('app.current_tenant_id')::INTEGER
    AND current_setting('app.current_user_role') IN ('tenant_admin', 'manager')
);

CREATE OR REPLACE FUNCTION check_tenant_access()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.tenant_id <> current_setting('app.current_tenant_id')::INTEGER THEN
        RAISE EXCEPTION 'Cannot modify data belonging to another tenant';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER enforce_tenant_access
BEFORE INSERT OR UPDATE ON customer_data
FOR EACH ROW EXECUTE FUNCTION check_tenant_access();

-- Принудительно применяем RLS даже для владельца таблицы
ALTER TABLE customer_data FORCE ROW LEVEL SECURITY;
 
-- Создаем специальную роль для администрирования таблицы
CREATE ROLE app_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_admin;
 
-- Создаем политику специально для администраторов
CREATE POLICY admin_access ON customer_data
TO app_admin
USING (TRUE);

CREATE OR REPLACE FUNCTION test_access_as_user(
    p_tenant_id INTEGER,
    p_username TEXT,
    p_query TEXT
) RETURNS TABLE (RESULT JSON) AS $$
DECLARE
    v_orig_tenant TEXT;
    v_orig_user TEXT;
    v_orig_role TEXT;
BEGIN
    -- Сохраняем текущий контекст
    v_orig_tenant := current_setting('app.current_tenant_id', TRUE);
    v_orig_user := current_setting('app.current_user_id', TRUE);
    v_orig_role := current_setting('app.current_user_role', TRUE);
    
    -- Устанавливаем новый контекст
    PERFORM authorize_user(p_username, p_tenant_id);
    
    -- Выполняем запрос
    RETURN QUERY EXECUTE p_query;
    
    -- Восстанавливаем контекст
    IF v_orig_tenant IS NOT NULL THEN
        PERFORM set_config('app.current_tenant_id', v_orig_tenant, FALSE);
    END IF;
    IF v_orig_user IS NOT NULL THEN
        PERFORM set_config('app.current_user_id', v_orig_user, FALSE);
    END IF;
    IF v_orig_role IS NOT NULL THEN
        PERFORM set_config('app.current_user_role', v_orig_role, FALSE);
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Тестируем доступ пользователя из Acme Corp
SELECT * FROM test_access_as_user(1, 'john', 'SELECT * FROM customer_data');
 
-- Проверяем, что пользователь не может видеть данные другого тенанта
SELECT * FROM test_access_as_user(1, 'john', 
    'SELECT * FROM customer_data WHERE tenant_id = 2');

-- Создаем таблицу для связей между тенантами
CREATE TABLE tenant_relationships (
    parent_tenant_id INTEGER REFERENCES tenants(tenant_id),
    child_tenant_id INTEGER REFERENCES tenants(tenant_id),
    relationship_type VARCHAR(50) NOT NULL,
    PRIMARY KEY (parent_tenant_id, child_tenant_id),
    CHECK (parent_tenant_id <> child_tenant_id)
);
 
-- Создаем таблицу для доступа пользователей к множеству тенантов
CREATE TABLE user_tenant_access (
    user_id INTEGER REFERENCES users(user_id),
    tenant_id INTEGER REFERENCES tenants(tenant_id),
    access_level VARCHAR(50) NOT NULL,
    granted_by INTEGER REFERENCES users(user_id),
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, tenant_id)
);

-- Функция для проверки доступа пользователя к тенанту
CREATE OR REPLACE FUNCTION user_has_tenant_access(p_user_id INTEGER, p_tenant_id INTEGER)
RETURNS BOOLEAN AS $$
BEGIN
    -- Прямой доступ через основную привязку
    IF EXISTS (SELECT 1 FROM users WHERE user_id = p_user_id AND tenant_id = p_tenant_id) THEN
        RETURN TRUE;
    END IF;
    
    -- Доступ через явные права на дополнительных тенантов
    IF EXISTS (SELECT 1 FROM user_tenant_access 
               WHERE user_id = p_user_id AND tenant_id = p_tenant_id) THEN
        RETURN TRUE;
    END IF;
    
    -- Доступ через иерархию тенантов (рекурсивный)
    RETURN EXISTS (
        WITH RECURSIVE tenant_hierarchy AS (
            -- Базовый случай: тенанты, к которым у пользователя есть прямой доступ
            SELECT t.tenant_id
            FROM users u
            JOIN tenants t ON u.tenant_id = t.tenant_id
            WHERE u.user_id = p_user_id
            
            UNION
            
            SELECT uta.tenant_id
            FROM user_tenant_access uta
            WHERE uta.user_id = p_user_id
            
            UNION ALL
            
            -- Рекурсивный случай: дочерние тенанты
            SELECT tr.child_tenant_id
            FROM tenant_relationships tr
            JOIN tenant_hierarchy th ON tr.parent_tenant_id = th.tenant_id
        )
        SELECT 1 FROM tenant_hierarchy WHERE tenant_id = p_tenant_id
    );
END;
$$ LANGUAGE plpgsql STABLE;

-- Обновляем политику с учетом иерархии доступа
DROP POLICY IF EXISTS tenant_isolation ON customer_data;
 
CREATE POLICY tenant_hierarchy_isolation ON customer_data
USING (
    user_has_tenant_access(
        current_setting('app.current_user_id')::INTEGER,
        tenant_id
    )
);

-- Создаем таблицу для кэширования результатов проверки доступа
CREATE TABLE access_cache (
    user_id INTEGER NOT NULL,
    tenant_id INTEGER NOT NULL,
    has_access BOOLEAN NOT NULL,
    calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, tenant_id)
);
 
-- Функция для получения кэшированного результата доступа
CREATE OR REPLACE FUNCTION get_cached_tenant_access(p_user_id INTEGER, p_tenant_id INTEGER)
RETURNS BOOLEAN AS $$
DECLARE
    v_result BOOLEAN;
    v_cache_ttl INTERVAL = '15 minutes';
BEGIN
    -- Проверяем кэш
    SELECT has_access INTO v_result
    FROM access_cache
    WHERE user_id = p_user_id 
      AND tenant_id = p_tenant_id
      AND calculated_at > (CURRENT_TIMESTAMP - v_cache_ttl);
      
    -- Если кэш действителен, возвращаем результат
    IF FOUND THEN
        RETURN v_result;
    END IF;
    
    -- Иначе вычисляем заново
    v_result := user_has_tenant_access(p_user_id, p_tenant_id);
    
    -- Обновляем кэш
    INSERT INTO access_cache (user_id, tenant_id, has_access)
    VALUES (p_user_id, p_tenant_id, v_result)
    ON CONFLICT (user_id, tenant_id) 
    DO UPDATE SET has_access = v_result, calculated_at = CURRENT_TIMESTAMP;
    
    RETURN v_result;
END;
$$ LANGUAGE plpgsql STABLE;

-- Запрос без RLS (быстрое выполнение)
SELECT * FROM customer_data WHERE STATUS = 'active';
 
-- С активированным RLS запрос фактически преобразуется в:
SELECT * FROM customer_data 
WHERE STATUS = 'active' 
AND tenant_id = current_setting('app.current_tenant_id')::INTEGER;

-- Создание индекса для поля tenant_id
CREATE INDEX idx_customer_data_tenant_id ON customer_data(tenant_id);
 
-- Для более сложных политик могут потребоваться составные индексы
CREATE INDEX idx_customer_data_tenant_status ON customer_data(tenant_id, STATUS);

-- Менее оптимальная политика с подзапросом
CREATE POLICY complex_policy ON customer_data
USING (
  tenant_id = current_setting('app.current_tenant_id')::INTEGER
  AND data_id IN (SELECT data_id FROM allowed_data)
);
 
-- Более оптимальная политика с джойном
CREATE POLICY optimized_policy ON customer_data
USING (
  tenant_id = current_setting('app.current_tenant_id')::INTEGER
  AND EXISTS (SELECT 1 FROM allowed_data ad WHERE ad.data_id = customer_data.data_id)
);

CREATE OR REPLACE FUNCTION bulk_process_tenant_data(p_tenant_id INTEGER)
RETURNS VOID AS $$
BEGIN
  -- Проверка авторизации
  IF current_setting('app.current_tenant_id')::INTEGER <> p_tenant_id THEN
    RAISE EXCEPTION 'Unauthorized access';
  END IF;
  
  -- Выполнение операции без накладных расходов RLS
  UPDATE customer_data SET processed = TRUE 
  WHERE tenant_id = p_tenant_id AND processed = FALSE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Включение мониторинга запросов
ALTER SYSTEM SET pg_stat_statements.track = 'all';
SELECT pg_reload_conf();
 
-- Анализ самых медленных запросов с наибольшим временем выполнения
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;
 
-- Мониторинг использования индексов
SELECT relname, idx_scan, seq_scan
FROM pg_stat_user_tables
WHERE idx_scan < seq_scan
ORDER BY seq_scan DESC;

-- Анализ плана выполнения запроса
EXPLAIN ANALYZE
SELECT * FROM customer_data WHERE STATUS = 'active';

cache_key = f"{query_hash}_{tenant_id}_{user_role}"

-- Создание материализованного представления для тенанта
CREATE MATERIALIZED VIEW tenant_1_active_customers AS
SELECT * FROM customer_data
WHERE tenant_id = 1 AND STATUS = 'active';
 
-- Обновление представления по расписанию
REFRESH MATERIALIZED VIEW tenant_1_active_customers;

-- Создание секционированной таблицы
CREATE TABLE customer_data_partitioned (
  data_id SERIAL,
  tenant_id INTEGER NOT NULL,
  customer_name VARCHAR(100) NOT NULL,
  STATUS VARCHAR(20) DEFAULT 'active',
  PRIMARY KEY (tenant_id, data_id)
) PARTITION BY LIST (tenant_id);
 
-- Создание секций для каждого тенанта
CREATE TABLE customer_data_tenant_1 PARTITION OF customer_data_partitioned
FOR VALUES IN (1);
 
CREATE TABLE customer_data_tenant_2 PARTITION OF customer_data_partitioned
FOR VALUES IN (2);

-- Определение основной таблицы контактов
CREATE TABLE contacts (
  contact_id BIGSERIAL PRIMARY KEY,
  tenant_id INTEGER NOT NULL,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255),
  phone VARCHAR(50),
  STATUS VARCHAR(20) DEFAULT 'active',
  custom_fields JSONB,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Включение RLS и создание базовой политики
ALTER TABLE contacts ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON contacts
USING (tenant_id = current_setting('app.tenant_id')::INTEGER);

CREATE TABLE contacts_partition (
  contact_id BIGSERIAL,
  tenant_id INTEGER NOT NULL,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255),
  phone VARCHAR(50),
  STATUS VARCHAR(20) DEFAULT 'active',
  custom_fields JSONB,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (tenant_id, contact_id)
) PARTITION BY RANGE (tenant_id);
 
CREATE TABLE contacts_1_100 PARTITION OF contacts_partition
FOR VALUES FROM (1) TO (101);
 
CREATE TABLE contacts_101_200 PARTITION OF contacts_partition
FOR VALUES FROM (101) TO (201);

CREATE OR REPLACE FUNCTION search_tenant_contacts(
  p_tenant_id INTEGER,
  p_query TEXT
) RETURNS SETOF contacts AS $$
BEGIN
  -- Проверка контекста безопасности
  IF current_setting('app.tenant_id')::INTEGER <> p_tenant_id THEN
    RAISE EXCEPTION 'Unauthorized access attempt';
  END IF;
  
  RETURN QUERY
  SELECT * FROM contacts
  WHERE tenant_id = p_tenant_id
  AND to_tsvector('english', name || ' ' || COALESCE(email, '') || ' ' || COALESCE(phone, ''))
      @@ to_tsquery('english', p_query);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Создание схемы для общих данных
CREATE SCHEMA common;
 
-- Создание схемы для данных тенантов
CREATE SCHEMA tenant_data;
 
-- Таблица образовательных учреждений
CREATE TABLE common.institutions (
  institution_id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address TEXT,
  contact_info JSONB,
  subscription_plan VARCHAR(50),
  active BOOLEAN DEFAULT TRUE
);
 
-- Таблица студентов с RLS
CREATE TABLE tenant_data.students (
  student_id SERIAL PRIMARY KEY,
  institution_id INTEGER REFERENCES common.institutions(institution_id),
  full_name VARCHAR(255) NOT NULL,
  date_of_birth DATE,
  grade_level INTEGER,
  enrollment_date DATE,
  academic_data JSONB
);
 
ALTER TABLE tenant_data.students ENABLE ROW LEVEL SECURITY;

-- Функция для временного предоставления доступа к данным другого учреждения
CREATE OR REPLACE FUNCTION grant_temporary_access(
  source_institution_id INTEGER,
  target_institution_id INTEGER,
  purpose TEXT,
  duration INTERVAL
) RETURNS UUID AS $$
DECLARE
  access_id UUID;
BEGIN
  -- Генерируем уникальный идентификатор сессии доступа
  access_id := gen_random_uuid();
  
  -- Записываем информацию о предоставленном доступе
  INSERT INTO common.access_grants (
    access_id,
    source_institution_id,
    target_institution_id,
    purpose,
    granted_at,
    expires_at,
    granted_by
  ) VALUES (
    access_id,
    source_institution_id,
    target_institution_id,
    purpose,
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP + duration,
    current_setting('app.user_id')::INTEGER
  );
  
  RETURN access_id;
END;
$$ LANGUAGE plpgsql;
 
-- Политика RLS, учитывающая временные разрешения
CREATE POLICY institution_data_access ON tenant_data.students
USING (
  institution_id = current_setting('app.institution_id')::INTEGER
  OR EXISTS (
    SELECT 1 FROM common.access_grants
    WHERE source_institution_id = current_setting('app.institution_id')::INTEGER
    AND target_institution_id = institution_id
    AND expires_at > CURRENT_TIMESTAMP
  )
);

-- Создание представления с анонимизированными данными
CREATE OR REPLACE VIEW tenant_data.anonymized_students AS
SELECT
  student_id,
  institution_id,
  CONCAT(LEFT(full_name, 1), '***') AS full_name,
  EXTRACT(YEAR FROM date_of_birth) AS birth_year,
  grade_level,
  EXTRACT(YEAR FROM enrollment_date) AS enrollment_year,
  jsonb_build_object(
    'avg_score', academic_data->'avg_score',
    'attendance_rate', academic_data->'attendance_rate'
  ) AS academic_metrics
FROM
  tenant_data.students;
 
-- Политика RLS для анонимизированного представления
ALTER VIEW tenant_data.anonymized_students ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY anonymized_data_access ON tenant_data.anonymized_students
FOR SELECT
USING (TRUE);  -- Доступно для всех учреждений

-- Таблица документов с метаданными о совместном доступе
CREATE TABLE documents (
  document_id BIGSERIAL PRIMARY KEY,
  tenant_id INTEGER NOT NULL,
  title VARCHAR(200) NOT NULL,
  content TEXT,
  STATUS VARCHAR(50) DEFAULT 'draft',
  is_shared BOOLEAN DEFAULT FALSE,
  shared_with INTEGER[],  -- Массив ID тенантов для совместного доступа
  security_level INTEGER DEFAULT 1,
  created_by INTEGER NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Создание политики RLS с учетом совместного доступа
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY documents_access_policy ON documents
USING (
  tenant_id = current_setting('app.current_tenant_id')::INTEGER 
  OR (
    is_shared = TRUE AND 
    current_setting('app.current_tenant_id')::INTEGER = ANY(shared_with)
  )
);

-- Триггер для проверки прав при изменении статуса совместного доступа
CREATE OR REPLACE FUNCTION check_sharing_permissions()
RETURNS TRIGGER AS $$
BEGIN
  -- Проверка, что пользователь принадлежит к тенанту-владельцу документа
  IF NEW.tenant_id <> current_setting('app.current_tenant_id')::INTEGER THEN
    IF NEW.is_shared <> OLD.is_shared OR NEW.shared_with <> OLD.shared_with THEN
      RAISE EXCEPTION 'Unauthorized attempt to modify sharing settings';
    END IF;
  END IF;
  
  -- Логирование операций совместного доступа
  IF NEW.is_shared AND (OLD.is_shared IS NULL OR NOT OLD.is_shared) THEN
    INSERT INTO audit.sharing_events (
      document_id, tenant_id, shared_with, user_id, event_type
    ) VALUES (
      NEW.document_id, NEW.tenant_id, NEW.shared_with, 
      current_setting('app.current_user_id')::INTEGER, 'SHARE'
    );
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER documents_sharing_trigger
BEFORE UPDATE ON documents
FOR EACH ROW
WHEN (NEW.is_shared IS DISTINCT FROM OLD.is_shared OR NEW.shared_with IS DISTINCT FROM OLD.shared_with)
EXECUTE FUNCTION check_sharing_permissions();

-- Функция для проверки JWT и установки контекста безопасности
CREATE OR REPLACE FUNCTION set_context_from_jwt(p_token TEXT)
RETURNS JSONB AS $$
DECLARE
  payload JSONB;
  valid BOOLEAN;
BEGIN
  -- Декодирование и проверка JWT (упрощенно)
  SELECT 
    jwt_payload INTO payload
  FROM 
    verify_jwt(p_token) AS jwt_payload;
  
  IF payload IS NULL THEN
    RAISE EXCEPTION 'Invalid JWT token';
  END IF;
  
  -- Установка параметров сессии
  PERFORM set_config('app.current_tenant_id', payload->>'tenant_id', FALSE);
  PERFORM set_config('app.current_user_id', payload->>'sub', FALSE);
  PERFORM set_config('app.current_roles', payload->>'roles', FALSE);
  
  RETURN payload;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE SCHEMA audit;
 
CREATE TABLE audit.data_access_events (
  event_id BIGSERIAL PRIMARY KEY,
  tenant_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  patient_id INTEGER NOT NULL,
  record_type VARCHAR(50),
  operation VARCHAR(10) NOT NULL,
  accessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  client_ip INET,
  access_reason TEXT
);
 
-- Триггер для автоматической записи событий доступа
CREATE OR REPLACE FUNCTION log_patient_data_access()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit.data_access_events (
    tenant_id, user_id, patient_id, record_type, operation, client_ip, access_reason
  ) VALUES (
    current_setting('app.tenant_id')::INTEGER,
    current_setting('app.user_id')::INTEGER,
    NEW.patient_id,
    TG_TABLE_NAME,
    TG_OP,
    inet_client_addr(),
    current_setting('app.access_reason', TRUE)
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Запрещаем прямое изменение таблицы аудита
REVOKE INSERT, UPDATE, DELETE ON audit.data_access_events FROM PUBLIC;
 
-- Установка RLS для контроля доступа к журналам
ALTER TABLE audit.data_access_events ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY audit_isolation ON audit.data_access_events
FOR SELECT
USING (
  tenant_id = current_setting('app.tenant_id')::INTEGER
  AND (current_setting('app.user_role') = 'compliance_officer' 
       OR current_setting('app.user_role') = 'admin')
);

-- Таблица объектов инфраструктуры с географическими данными
CREATE TABLE infrastructure_objects (
  object_id SERIAL PRIMARY KEY,
  tenant_id INTEGER NOT NULL,
  name VARCHAR(100) NOT NULL,
  TYPE VARCHAR(50) NOT NULL,
  STATUS VARCHAR(20) DEFAULT 'active',
  location GEOMETRY(POINT, 4326),
  responsibility_zone INTEGER REFERENCES zones(zone_id)
);
 
-- Политика доступа с учётом зоны ответственности
ALTER TABLE infrastructure_objects ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY geo_tenant_policy ON infrastructure_objects
USING (
  tenant_id = current_setting('app.tenant_id')::INTEGER
  AND (
    responsibility_zone IN (
      SELECT zone_id FROM user_responsibility_zones 
      WHERE user_id = current_setting('app.user_id')::INTEGER
    )
    OR current_setting('app.user_role') = 'supervisor'
  )
);

-- Функция для проверки прав доступа на основе географического положения
CREATE OR REPLACE FUNCTION check_location_access(
  p_location GEOMETRY, 
  p_user_id INTEGER
) RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM user_responsibility_zones u
    JOIN zones z ON u.zone_id = z.zone_id
    WHERE u.user_id = p_user_id
    AND ST_Contains(z.boundary, p_location)
  );
END;
$$ LANGUAGE plpgsql STABLE;