Инструкция по миграции БД#

Общее описание работы#

В настоящем документе под миграцией понимается реорганизация БД в части количества и размещения на VM используемых БД для PVM к целевой конфигурации. Также данный процесс можно использовать для смены версий Pangolin. Миграция производится путем выгрузки всех данных из исходных БД в разрезе схем и последующей загрузке этих схем в БД назначения.

Используемые инструменты#

  1. Linux (RHEL 7.9).

  2. bash, sed.

  3. pg_dump, psql.

Предварительные условия#

  1. Исполнение скриптов производится на выделенной виртуальной машине Linux (RHEL 7.9) с установленным PostgreSQL (достаточно клиентской части) с версией не ниже чем установленный PostgreSQL на целевой VM (тестирование производилось на Platform V Pangolin 5.2.2. Предварительно необходимо осуществить обновление/инсталляцию версии Platform V Pangolin 5.2.2).

  2. Выделенная виртуальная машина должна иметь сетевой доступ до хостов с исходными БД и до хостов с БД назначения.

  3. В каталоге, где предполагается запускать скрипты, необходимо иметь достаточно дискового пространства для выгрузки данных из исходных БД.

  4. Учетные записи в БД для миграции должны иметь роль «as_admin» (роль «as_admin» в терминологии «Ролевая модель Pangolin»).

  5. Для миграции компонентов AIOM, INDA, ALMGR, BAMN, BUSE, LOGA, MONA, COTE, DRUID (из LGDB): БД назначения (+ роли и учетные записи для компонентов за исключением LGDB) созданы Администратором БД согласно шаблона приведенного в Приложении 1 (требуется права роли «db_admin»).

  6. Для миграции компонента LGDB: В БД из п.5 Администратором БД созданы объекты Abyss согласно шаблону, приведенному в Приложении 2 (требуется права роли «db_admin»).

  7. Для миграции компонента LGDB: В БД из п.5 Администратором БД выданы права на объекты Abyss согласно шаблону, приведенному в Приложении 3 (требуется права роли «db_admin»).

  8. Должна соблюдаться уникальность в наименовании мигрируемых схем в каждой БД назначения.

  9. В процессе миграции невозможно сменить имя схемы. При необходимости это производится отдельной процедурой, выходящей за рамки данного документа.

  10. Непосредственно перед осуществлением миграции необходимо остановить все компоненты PVM для обеспечения целостности данных.

В состав дистрибутива входят файлы скриптов миграции, расположенные по следующему пути ./documentation/documents/installation-guide/md/resources/migration.

Инструкция по использованию (для всех компонентов):#

  1. Поместить вышеуказанные скрипты на VM с установленным PosgreSQL.

  2. Заполнить 01_env_backup.sh с параметрами исходных БД, которые будут мигрироваться.

  3. Заполнить 02_env_restore.sh с параметрами БД, в которые будет производиться миграция.

  4. Заполнить файл паролей /home/postgres/.pgpass (при отсутствии создать) в формате "hostname:port:database:username:password". В нем должны быть указаны все исходные БД и все БД назначения.

  5. Запустить 03_chk_env_backup.sh для проверки правильности заполнения 01_env_backup.sh и /home/postgres/.pgpass.

  6. Запустить 04_chk_env_restore.sh для проверки правильности заполнения 02_env_restore.sh и /home/postgres/.pgpass.

  7. Остановить компоненты, БД которых будут мигрироваться.

  8. Произвести выгрузку данных из БД:

    • в файле 05_run_backup.sh в переменной LIST_FP уточнить список компонент, БД которых подлежат выгрузке;

    • запустить файл 05_run_backup.sh;

    • проконтролировать лог файлы выгрузки в подкаталоге logs.

  9. При повторной миграции рекомендуется произвести удаление существующих схем в БД назначения (Внимание!!! После удаления схем компонента LGDB потребуется повторное выполнение п.6 п.7 предварительных условий):

    • в файле 06_run_cleanup.sh в переменной LIST_FP уточнить список компонент, схемы которых подлежат удалению;

    • запустить файл 06_run_cleanup.sh;

    • проконтролировать лог файлы в подкаталоге logs.

  10. Загрузить данные в БД назначения:

    • в файле 07_run_restore.sh в переменной LIST_FP уточнить список компонент, БД которых подлежат загрузке;

    • в файле 07_run_restore.sh в переменной BACKUP_ID установить дату и время бэкап файлов для загрузки;

    • запустить файл 07_run_restore.sh;

    • проконтролировать лог файлы загрузки в подкаталоге logs.

  11. Раздать необходимые права для ролей ТУЗ:

    • в файле 08_add_right.sh в переменной LIST_FP уточнить список компонент для раздачи прав;

    • запустить файл 08_add_right.sh;

    • проконтролировать лог файлы в подкаталоге logs.

Откат#

Данная процедура миграции не затрагивает исходную БД в части модификации структуры либо изменения данных. По завершению либо неудачному завершению процесса миграции исходная БД остается в неизменном виде. Для отката необходимо произвести следующие действия:

  1. Остановить процесс миграции, если он не закончен.

  2. Запустить компоненты PVM на исходной БД.

  3. Удалить БД назначения.

Внимание!!! Если процесс миграции был завершен, а компоненты PVM были запущены на БД назначения, то процесс отката может привести к потере данных.

Приложение 1#

Шаблон создания БД:

-- ================================================================================================================
-- Шаблон скрипта создания БД для компонентов AIOM, INDA, ALMGR, BAMN, BUSE, LOGA, MONA, COTE, DRUID (из LGDB)
-- ================================================================================================================
-- <id_user_name>    - идентификатор (или можно код компонента) имени пользователя (например aiom, indicator, alert, bam, buse, uloggerm, unimon, tlmcltr, druid)
-- <id_db_name>      - идентификатор (или можно код AC) имени базы данных (предполагается pvm_front_db, pvm_middle_db, pvm_back_db)
-- <id_schema_name>  - идентификатор (или можно код компонента) имени схемы (например aiom, indicator, alert, bam, buse, uloggerm, unimon, tlmcltr, druid)
-- ================================================================================================================
 
-- ======= (выполняется администратором БД)
-- ======= Создание общей(их) БД для PVM ===============================================
 
SET ROLE db_admin;
  
-- Создание TABLESPACE (общая для CREATE DATABASE)
CREATE TABLESPACE <id_db_name>_ts_data OWNER db_admin LOCATION '/pgdata/05/tablespaces/<id_db_name>_ts_data';
GRANT ALL ON TABLESPACE <id_db_name>_ts_data TO as_admin;
    
-- Создание DATABASE
CREATE DATABASE <id_db_name>_db OWNER db_admin TEMPLATE "template0" ENCODING 'UTF-8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8' TABLESPACE <id_db_name>_ts_data;
REVOKE ALL ON SCHEMA public FROM public;
GRANT CREATE ON DATABASE <id_db_name>_db TO as_admin;
-- ======================================================================================
  
  
-- ======= Создание объектов (role, tablespace, user) в БД для компонентов PVM ==========
-- Создание ролей as_TUZ_xxx для каждой компоненты автоматизированной системы
CREATE ROLE "as_TUZ_<id_user_name>" WITH NOLOGIN NOCREATEDB NOSUPERUSER INHERIT NOREPLICATION NOBYPASSRLS NOCREATEROLE CONNECTION LIMIT 0 VALID UNTIL 'infinity';
  
-- Создание TABLESPACE (для каждого компонента)
CREATE TABLESPACE <id_db_name>_<id_user_name>_ts_data OWNER db_admin LOCATION '/pgdata/05/tablespaces/<id_db_name>_<id_user_name>_ts_data';
GRANT ALL ON TABLESPACE <id_db_name>_<id_user_name>_ts_data TO as_admin;
CREATE TABLESPACE <id_db_name>_<id_user_name>_ts_idx OWNER db_admin LOCATION '/pgdata/05/tablespaces/<id_db_name>_<id_user_name>_ts_idx';
GRANT ALL ON TABLESPACE <id_db_name>_<id_user_name>_ts_idx TO as_admin;
  
   
-- Создание учетной записи xxx_migrator для миграций
CREATE USER "<id_user_name>_migrator" WITH ENCRYPTED PASSWORD 'you_password' VALID UNTIL 'infinity' NOINHERIT;
GRANT "as_admin" TO "<id_user_name>_migrator";
ALTER USER <id_user_name>_migrator SET ROLE "as_admin";
ALTER USER "<id_user_name>_migrator" SET search_path TO <id_schema_name>;
ALTER USER "<id_user_name>_migrator" SET default_tablespace TO <id_db_name>_<id_user_name>_ts_data;
   
-- Создание учетной записи xxx_user для работы приложения
CREATE USER "<id_user_name>_user" WITH ENCRYPTED PASSWORD 'you_password' INHERIT;
GRANT "as_TUZ_<id_user_name>" TO "<id_user_name>_user";
ALTER USER <id_user_name>_user SET ROLE "as_TUZ_<id_user_name>";
ALTER USER "<id_user_name>_user" SET search_path TO <id_schema_name>;
ALTER USER "<id_user_name>_user" SET default_tablespace TO <id_db_name>_<id_user_name>_ts_data;
   
-- Создание учетной записи xxx_user_secman для работы приложения (со сменой пароля через Secman)
CREATE USER "<id_user_name>_user_secman" WITH ENCRYPTED PASSWORD 'you_password' INHERIT;
GRANT "as_TUZ_<id_user_name>" TO "<id_user_name>_user_secman";
ALTER USER <id_user_name>_user_secman SET ROLE "as_TUZ_<id_user_name>";
ALTER USER "<id_user_name>_user_secman" SET search_path TO <id_schema_name>;
ALTER USER "<id_user_name>_user_secman" SET default_tablespace TO <id_db_name>_<id_user_name>_ts_data;

Приложение 2#

Шаблон создания объектов Abyss:

-- Создание TABLESPACE
CREATE TABLESPACE <name_tablespace_for_lgdb_data> OWNER db_admin LOCATION '/pgdata/05/tablespaces/<name_tablespace_for_lgdb_data>';
 
CREATE ROLE archive_service_user WITH LOGIN PASSWORD  'you_password';
CREATE ROLE auth_service_user WITH LOGIN PASSWORD  'you_password';
CREATE ROLE flow_service_user WITH LOGIN PASSWORD  'you_password';
CREATE ROLE fulltext_service_user WITH LOGIN PASSWORD  'you_password';
CREATE ROLE group_service_user WITH LOGIN PASSWORD  'you_password';
CREATE ROLE kafka_service_user WITH LOGIN PASSWORD  'you_password';
CREATE ROLE monitoring_service_user WITH LOGIN PASSWORD  'you_password';
CREATE ROLE project_service_user WITH LOGIN PASSWORD  'you_password';
CREATE ROLE pvm_authorization_service_user WITH LOGIN PASSWORD  'you_password';
CREATE ROLE role_service_user WITH LOGIN PASSWORD  'you_password';
CREATE ROLE tracing_service_user WITH LOGIN PASSWORD  'you_password';
 
CREATE ROLE flyway_archive_service_user WITH PASSWORD 'you_password';
CREATE ROLE flyway_auth_service_user WITH PASSWORD 'you_password';
CREATE ROLE flyway_flow_service_user WITH LOGIN PASSWORD  'you_password';
CREATE ROLE flyway_fulltext_service_user WITH LOGIN PASSWORD  'you_password';
CREATE ROLE flyway_group_service_user WITH LOGIN PASSWORD  'you_password';
CREATE ROLE flyway_kafka_service_user WITH LOGIN PASSWORD  'you_password';
CREATE ROLE flyway_monitoring_service_user WITH LOGIN PASSWORD  'you_password';
CREATE ROLE flyway_project_service_user WITH LOGIN PASSWORD  'you_password';
CREATE ROLE flyway_pvm_authorization_service_user WITH LOGIN PASSWORD  'you_password';
CREATE ROLE flyway_role_service_user WITH LOGIN PASSWORD  'you_password';
CREATE ROLE flyway_tracing_service_user WITH LOGIN PASSWORD  'you_password';
 
ALTER ROLE archive_service_user SET search_path = "$user", public;
ALTER ROLE auth_service_user SET search_path = "$user", public;
ALTER ROLE flow_service_user SET search_path = "$user", public;
ALTER ROLE fulltext_service_user SET search_path = "$user", public;
ALTER ROLE group_service_user SET search_path = "$user", public;
ALTER ROLE kafka_service_user SET search_path = "$user", public;
ALTER ROLE monitoring_service_user SET search_path = "$user", public;
ALTER ROLE project_service_user SET search_path = "$user", public;
ALTER ROLE pvm_authorization_service_user SET search_path = "$user", public;
ALTER ROLE role_service_user SET search_path = "$user", public;
ALTER ROLE tracing_service_user SET search_path = "$user", public;
 
ALTER ROLE flyway_archive_service_user SET search_path = archive_service_user, public;
ALTER ROLE flyway_archive_service_user SET default_tablespace TO <name_tablespace_for_lgdb_data>;
ALTER ROLE flyway_auth_service_user SET search_path = auth_service_user, public;
ALTER ROLE flyway_auth_service_user SET default_tablespace TO <name_tablespace_for_lgdb_data>;
ALTER ROLE flyway_flow_service_user SET search_path = flow_service_user, public;
ALTER ROLE flyway_flow_service_user SET default_tablespace TO <name_tablespace_for_lgdb_data>;
ALTER ROLE flyway_fulltext_service_user SET search_path = fulltext_service_user, public;
ALTER ROLE flyway_fulltext_service_user SET default_tablespace TO <name_tablespace_for_lgdb_data>;
ALTER ROLE flyway_group_service_user SET search_path = group_service_user, public;
ALTER ROLE flyway_group_service_user SET default_tablespace TO <name_tablespace_for_lgdb_data>;
ALTER ROLE flyway_kafka_service_user SET search_path = kafka_service_user, public;
ALTER ROLE flyway_kafka_service_user SET default_tablespace TO <name_tablespace_for_lgdb_data>;
ALTER ROLE flyway_monitoring_service_user SET search_path = monitoring_service_user, public;
ALTER ROLE flyway_monitoring_service_user SET default_tablespace TO <name_tablespace_for_lgdb_data>;
ALTER ROLE flyway_project_service_user SET search_path = project_service_user, public;
ALTER ROLE flyway_project_service_user SET default_tablespace TO <name_tablespace_for_lgdb_data>;
ALTER ROLE flyway_pvm_authorization_service_user SET search_path = pvm_authorization_service_user, public;
ALTER ROLE flyway_pvm_authorization_service_user SET default_tablespace TO <name_tablespace_for_lgdb_data>;
ALTER ROLE flyway_role_service_user SET search_path = role_service_user, public;
ALTER ROLE flyway_role_service_user SET default_tablespace TO <name_tablespace_for_lgdb_data>;
ALTER ROLE flyway_tracing_service_user SET search_path = tracing_service_user, public;
ALTER ROLE flyway_tracing_service_user SET default_tablespace TO <name_tablespace_for_lgdb_data>;
 
-- Выдача прав на tablespace
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO archive_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO auth_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO flow_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO fulltext_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO group_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO kafka_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO monitoring_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO project_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO pvm_authorization_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO role_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO tracing_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO flyway_archive_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO flyway_auth_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO flyway_flow_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO flyway_fulltext_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO flyway_group_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO flyway_kafka_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO flyway_monitoring_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO flyway_project_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO flyway_pvm_authorization_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO flyway_role_service_user;
GRANT ALL ON TABLESPACE <name_tablespace_for_lgdb_data> TO flyway_tracing_service_user;
 
-- Создание схем внутри БД
\connect pvm_back_db;
CREATE SCHEMA archive_service_user;
CREATE SCHEMA auth_service_user;
CREATE SCHEMA flow_service_user;
CREATE SCHEMA fulltext_service_user;
CREATE SCHEMA group_service_user;
CREATE SCHEMA kafka_service_user;
CREATE SCHEMA monitoring_service_user;
CREATE SCHEMA project_service_user;
CREATE SCHEMA pvm_authorization_service_user;
CREATE SCHEMA role_service_user;
CREATE SCHEMA tracing_service_user;
 
-- Начисление прав на схему
ALTER SCHEMA archive_service_user OWNER TO flyway_archive_service_user;
ALTER SCHEMA auth_service_user OWNER TO flyway_auth_service_user;
ALTER SCHEMA flow_service_user OWNER TO flyway_flow_service_user;
ALTER SCHEMA fulltext_service_user OWNER TO flyway_fulltext_service_user;
ALTER SCHEMA group_service_user OWNER TO flyway_group_service_user;
ALTER SCHEMA kafka_service_user OWNER TO flyway_kafka_service_user;
ALTER SCHEMA monitoring_service_user OWNER TO flyway_monitoring_service_user;
ALTER SCHEMA project_service_user OWNER TO flyway_project_service_user;
ALTER SCHEMA pvm_authorization_service_user OWNER TO flyway_pvm_authorization_service_user;
ALTER SCHEMA role_service_user OWNER TO flyway_role_service_user;
ALTER SCHEMA tracing_service_user OWNER TO flyway_tracing_service_user;

Приложение 3#

Выдача прав на объекты Abyss:

\connect pvm_back_db;
-- Начисление прав для Archive service
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA archive_service_user TO flyway_archive_service_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA archive_service_user
GRANT  SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLES TO flyway_archive_service_user;
GRANT ALL ON SCHEMA archive_service_user TO flyway_archive_service_user;
REASSIGN OWNED BY archive_service_user TO flyway_archive_service_user;
 
GRANT ALL ON ALL SEQUENCES IN SCHEMA archive_service_user TO archive_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_archive_service_user IN SCHEMA archive_service_user
GRANT ALL ON SEQUENCES TO archive_service_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA archive_service_user TO archive_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_archive_service_user IN SCHEMA archive_service_user
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO archive_service_user;
GRANT USAGE ON SCHEMA archive_service_user TO archive_service_user;
 
-- Начисление прав для Auth service
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA auth_service_user TO flyway_auth_service_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA auth_service_user
GRANT  SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLES TO flyway_auth_service_user;
GRANT ALL ON SCHEMA auth_service_user TO flyway_auth_service_user;
REASSIGN OWNED BY auth_service_user TO flyway_auth_service_user;
 
GRANT ALL ON ALL SEQUENCES IN SCHEMA auth_service_user TO auth_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_auth_service_user IN SCHEMA auth_service_user
GRANT ALL ON SEQUENCES TO auth_service_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA auth_service_user TO auth_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_auth_service_user IN SCHEMA auth_service_user
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO auth_service_user;
GRANT USAGE ON SCHEMA auth_service_user TO auth_service_user;
 
-- Начисление прав для Flow service
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA flow_service_user TO flyway_flow_service_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA flow_service_user
GRANT  SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLES TO flyway_flow_service_user;
GRANT ALL ON SCHEMA flow_service_user TO flyway_flow_service_user;
REASSIGN OWNED BY flow_service_user TO flyway_flow_service_user;
 
GRANT ALL ON ALL SEQUENCES IN SCHEMA flow_service_user TO flow_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_flow_service_user IN SCHEMA flow_service_user
GRANT ALL ON SEQUENCES TO flow_service_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA flow_service_user TO flow_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_flow_service_user IN SCHEMA flow_service_user
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO flow_service_user;
GRANT USAGE ON SCHEMA flow_service_user TO flow_service_user;
 
-- Начисление прав для FullText index service
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA fulltext_service_user TO flyway_fulltext_service_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA fulltext_service_user
GRANT  SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLES TO flyway_fulltext_service_user;
GRANT ALL ON SCHEMA fulltext_service_user TO flyway_fulltext_service_user;
REASSIGN OWNED BY fulltext_service_user TO flyway_fulltext_service_user;
 
GRANT ALL ON ALL SEQUENCES IN SCHEMA fulltext_service_user TO fulltext_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_fulltext_service_user IN SCHEMA fulltext_service_user
GRANT ALL ON SEQUENCES TO fulltext_service_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA fulltext_service_user TO fulltext_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_fulltext_service_user IN SCHEMA fulltext_service_user
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO fulltext_service_user;
GRANT USAGE ON SCHEMA fulltext_service_user TO fulltext_service_user;
 
-- Начисление прав для Group service
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA group_service_user TO flyway_group_service_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA group_service_user
GRANT  SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLES TO flyway_group_service_user;
GRANT ALL ON SCHEMA group_service_user TO flyway_group_service_user;
REASSIGN OWNED BY group_service_user TO flyway_group_service_user;
 
GRANT ALL ON ALL SEQUENCES IN SCHEMA group_service_user TO group_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_group_service_user IN SCHEMA group_service_user
GRANT ALL ON SEQUENCES TO group_service_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA group_service_user TO group_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_group_service_user IN SCHEMA group_service_user
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO group_service_user;
GRANT USAGE ON SCHEMA group_service_user TO group_service_user;
 
-- Начисление прав для Kafka service
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA kafka_service_user TO flyway_kafka_service_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA kafka_service_user
GRANT  SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLES TO flyway_kafka_service_user;
GRANT ALL ON SCHEMA kafka_service_user TO flyway_kafka_service_user;
REASSIGN OWNED BY kafka_service_user TO flyway_kafka_service_user;
 
GRANT ALL ON ALL SEQUENCES IN SCHEMA kafka_service_user TO kafka_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_kafka_service_user IN SCHEMA kafka_service_user
GRANT ALL ON SEQUENCES TO kafka_service_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA kafka_service_user TO kafka_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_kafka_service_user IN SCHEMA kafka_service_user
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO kafka_service_user;
GRANT USAGE ON SCHEMA kafka_service_user TO kafka_service_user;
 
-- Начисление прав для Monitoring service
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA monitoring_service_user TO flyway_monitoring_service_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA monitoring_service_user
GRANT  SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLES TO flyway_monitoring_service_user;
GRANT ALL ON SCHEMA monitoring_service_user TO flyway_monitoring_service_user;
REASSIGN OWNED BY monitoring_service_user TO flyway_monitoring_service_user;
 
GRANT ALL ON ALL SEQUENCES IN SCHEMA monitoring_service_user TO monitoring_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_monitoring_service_user IN SCHEMA monitoring_service_user
GRANT ALL ON SEQUENCES TO monitoring_service_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA monitoring_service_user TO monitoring_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_monitoring_service_user IN SCHEMA monitoring_service_user
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO monitoring_service_user;
GRANT USAGE ON SCHEMA monitoring_service_user TO monitoring_service_user;
 
-- Начисление прав для Project service
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA project_service_user TO flyway_project_service_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA project_service_user
GRANT  SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLES TO flyway_project_service_user;
GRANT ALL ON SCHEMA project_service_user TO flyway_project_service_user;
REASSIGN OWNED BY project_service_user TO flyway_project_service_user;
 
GRANT ALL ON ALL SEQUENCES IN SCHEMA project_service_user TO project_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_project_service_user IN SCHEMA project_service_user
GRANT ALL ON SEQUENCES TO project_service_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA project_service_user TO project_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_project_service_user IN SCHEMA project_service_user
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO project_service_user;
GRANT USAGE ON SCHEMA project_service_user TO project_service_user;
 
-- Начисление прав для PVM auth service
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA pvm_authorization_service_user TO flyway_pvm_authorization_service_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA pvm_authorization_service_user
GRANT  SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLES TO flyway_pvm_authorization_service_user;
GRANT ALL ON SCHEMA pvm_authorization_service_user TO flyway_pvm_authorization_service_user;
REASSIGN OWNED BY pvm_authorization_service_user TO flyway_pvm_authorization_service_user;
 
GRANT ALL ON ALL SEQUENCES IN SCHEMA pvm_authorization_service_user TO pvm_authorization_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_pvm_authorization_service_user IN SCHEMA pvm_authorization_service_user
GRANT ALL ON SEQUENCES TO pvm_authorization_service_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA pvm_authorization_service_user TO pvm_authorization_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_pvm_authorization_service_user IN SCHEMA pvm_authorization_service_user
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO pvm_authorization_service_user;
GRANT USAGE ON SCHEMA pvm_authorization_service_user TO pvm_authorization_service_user;
 
-- Начисление прав для Role service
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA role_service_user TO flyway_role_service_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA role_service_user
GRANT  SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLES TO flyway_role_service_user;
GRANT ALL ON SCHEMA role_service_user TO flyway_role_service_user;
REASSIGN OWNED BY role_service_user TO flyway_role_service_user;
 
GRANT ALL ON ALL SEQUENCES IN SCHEMA role_service_user TO role_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_role_service_user IN SCHEMA role_service_user
GRANT ALL ON SEQUENCES TO role_service_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA role_service_user TO role_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_role_service_user IN SCHEMA role_service_user
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO role_service_user;
GRANT USAGE ON SCHEMA role_service_user TO role_service_user;
 
-- Начисление прав для Tracing service
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA tracing_service_user TO flyway_tracing_service_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA tracing_service_user
GRANT  SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLES TO flyway_tracing_service_user;
GRANT ALL ON SCHEMA tracing_service_user TO flyway_tracing_service_user;
REASSIGN OWNED BY tracing_service_user TO flyway_tracing_service_user;
 
GRANT ALL ON ALL SEQUENCES IN SCHEMA tracing_service_user TO tracing_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_tracing_service_user IN SCHEMA tracing_service_user
GRANT ALL ON SEQUENCES TO tracing_service_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA tracing_service_user TO tracing_service_user;
ALTER DEFAULT PRIVILEGES for role flyway_tracing_service_user IN SCHEMA tracing_service_user
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO tracing_service_user;
GRANT USAGE ON SCHEMA tracing_service_user TO tracing_service_user;