db/PostgreSQL/01-init-authorization.sql - master - softailor/gridient

db/PostgreSQL/01-init-authorization.sql
127 строк6 KB
Jenkins
Auto add
18 фев 2026, 10:19
18 фев 2026, 10:191e6c4f8
-- Инициализация базы данных для авторизации
-- Эта БД хранит данные авторизации отдельно от основной БД Core
-- ВАЖНО: Этот скрипт должен выполняться в контексте БД gridient_authorization

\c gridient_authorization

SET search_path TO public;

-- Таблица пользователей для авторизации
-- ВАЖНО: Эта таблица содержит только минимальные данные для авторизации
-- Полные данные пользователей хранятся в основной БД gridient_db
CREATE TABLE IF NOT EXISTS user_data (
    id SERIAL PRIMARY KEY,
    user_login VARCHAR(255) NOT NULL,	-- Логин (используется для поиска пользователя и связи с auth_users)
    created_by_id INT NOT NULL,		-- Создавший пользователь - внешний ключ к user_data
    created_utc TIMESTAMP NOT NULL,	-- Дата создания
    CONSTRAINT fk_user_data_created_by FOREIGN KEY (created_by_id) REFERENCES user_data(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
);

-- Индекс для быстрого поиска по логину
CREATE INDEX IF NOT EXISTS idx_user_data_login ON user_data(user_login);

-- Таблица групп
CREATE TABLE IF NOT EXISTS "groups" (
    id SERIAL PRIMARY KEY,
    alias VARCHAR(255) NOT NULL,
    caption VARCHAR(255) NOT NULL,
    is_default BOOLEAN NOT NULL,
    CONSTRAINT roles_unique_alias UNIQUE (alias),
    CONSTRAINT roles_unique_caption UNIQUE (caption)
);

-- Вставка стандартных групп
INSERT INTO "groups" (alias, caption, is_default)
VALUES 
    ('administrator', 'Администратор', true),
    ('editor', 'Редактор', true),
    ('reader', 'Читатель', true)
ON CONFLICT (alias) DO NOTHING;

-- Таблица связи списков и групп
-- ВАЖНО: list_id ссылается на списки в основной БД gridient_db (логическая связь, без FK)
CREATE TABLE IF NOT EXISTS list_groups (
    list_id INT NOT NULL,
    group_id INT NOT NULL,
    created_by_id INT NOT NULL,
    created_utc TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    rule INT NOT NULL,
    PRIMARY KEY (list_id, group_id),
    FOREIGN KEY (group_id) REFERENCES "groups"(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (created_by_id) REFERENCES user_data(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_list_groups_list_id ON list_groups(list_id);
CREATE INDEX IF NOT EXISTS idx_list_groups_group_id ON list_groups(group_id);
CREATE INDEX IF NOT EXISTS idx_list_groups_created_by_id ON list_groups(created_by_id);

-- Таблица связи пользователей и групп
CREATE TABLE IF NOT EXISTS user_groups (
    user_id INT NOT NULL,
    group_id INT NOT NULL,
    created_by_id INT NOT NULL,
    created_utc TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, group_id),
    FOREIGN KEY (user_id) REFERENCES user_data(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (group_id) REFERENCES "groups"(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (created_by_id) REFERENCES user_data(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_user_groups_user_id ON user_groups(user_id);
CREATE INDEX IF NOT EXISTS idx_user_groups_group_id ON user_groups(group_id);
CREATE INDEX IF NOT EXISTS idx_user_groups_created_by_id ON user_groups(created_by_id);

-- Создание системного пользователя для использования в скриптах
-- Используем отложенную проверку внешнего ключа для создания первого пользователя
BEGIN;

-- Создаем системного пользователя с id=0, который ссылается сам на себя
INSERT INTO user_data (id, user_login, created_by_id, created_utc)
VALUES (0, 'system', 0, CURRENT_TIMESTAMP)
ON CONFLICT (id) DO NOTHING;

-- Устанавливаем последовательность так, чтобы следующий id был 1
SELECT setval('user_data_id_seq', 1, false);

COMMIT;

-- Создание административного пользователя
-- ВАЖНО: Пользователь admin должен существовать в gridient_authentication (создается в 01-init-auth.sql)
-- и в gridient_db (создается в 01-init.sql)
-- Здесь создаем его только в БД авторизации и добавляем в группу администраторов
BEGIN;

-- Создаем пользователя admin в БД авторизации (если еще не существует)
INSERT INTO user_data (user_login, created_by_id, created_utc)
SELECT 'admin', 0, CURRENT_TIMESTAMP
WHERE NOT EXISTS (SELECT 1 FROM user_data WHERE user_login = 'admin');

-- Добавляем admin в группу администраторов (если еще не добавлен)
INSERT INTO user_groups (user_id, group_id, created_by_id, created_utc)
SELECT 
    (SELECT id FROM user_data WHERE user_login = 'admin'),
    (SELECT id FROM groups WHERE alias = 'administrator'),
    0,
    CURRENT_TIMESTAMP
WHERE NOT EXISTS (
    SELECT 1 FROM user_groups ug
    JOIN user_data u ON ug.user_id = u.id
    JOIN groups g ON ug.group_id = g.id
    WHERE u.user_login = 'admin' AND g.alias = 'administrator'
);

COMMIT;

-- Таблица настроек автора для списков
-- ВАЖНО: list_id ссылается на списки в основной БД gridient_db (логическая связь, без FK)
CREATE TABLE IF NOT EXISTS list_author_settings (
    id SERIAL PRIMARY KEY,
    list_id INT NOT NULL,              -- ID списка из Core БД (логическая связь, без FK)
    read_only_author BOOLEAN,          -- Чтение только элементов, созданных автором
    edit_only_author BOOLEAN,          -- Редактирование только элементов, созданных автором
    created_utc TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_utc TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT list_author_settings_unique_list_id UNIQUE (list_id)
);

CREATE INDEX IF NOT EXISTS idx_list_author_settings_list_id ON list_author_settings(list_id);