69 lines
3.3 KiB
SQL
69 lines
3.3 KiB
SQL
-- AES-GCM envelopes cannot be converted safely inside SQL. Existing credentials
|
|
-- must be handled by an explicitly authorized conversion before this migration.
|
|
CREATE TABLE chorus_plaintext_credentials_up_guard (
|
|
id TINYINT NOT NULL,
|
|
CONSTRAINT chk_chorus_plaintext_credentials_up_guard CHECK (id = 0)
|
|
) ENGINE=InnoDB;
|
|
|
|
DELETE FROM chorus_plaintext_credentials_up_guard;
|
|
INSERT INTO chorus_plaintext_credentials_up_guard (id)
|
|
SELECT 1 WHERE EXISTS (SELECT 1 FROM provider_credentials);
|
|
DROP TABLE chorus_plaintext_credentials_up_guard;
|
|
|
|
ALTER TABLE provider_credentials
|
|
DROP CHECK chk_provider_credentials_envelope,
|
|
DROP COLUMN api_key_enc,
|
|
DROP COLUMN key_id,
|
|
ADD COLUMN api_key VARCHAR(4096) NOT NULL AFTER credential_version,
|
|
ADD CONSTRAINT chk_provider_credentials_api_key CHECK (CHAR_LENGTH(api_key) BETWEEN 1 AND 4096);
|
|
|
|
INSERT INTO sys_menu
|
|
(menu_name, title, icon, path, paths, menu_type, action, permission, parent_id, no_cache, breadcrumb, component, sort, visible, is_frame)
|
|
SELECT 'chorus-health', 'Provider Health', 'ri:pulse-line', '/chorus/health', '', 'C', '', 'chorus:health:view',
|
|
parent.menu_id, FALSE, 'Chorus / Provider Health', 'chorus/health/index', 50, '0', '0'
|
|
FROM sys_menu parent
|
|
WHERE parent.path = '/chorus'
|
|
ON DUPLICATE KEY UPDATE
|
|
menu_name = VALUES(menu_name), title = VALUES(title), icon = VALUES(icon), parent_id = VALUES(parent_id),
|
|
permission = VALUES(permission), breadcrumb = VALUES(breadcrumb), component = VALUES(component), sort = VALUES(sort),
|
|
visible = VALUES(visible), is_frame = VALUES(is_frame), deleted_at = NULL;
|
|
|
|
UPDATE sys_menu child
|
|
JOIN sys_menu parent ON parent.path = '/chorus'
|
|
SET child.paths = CONCAT(parent.paths, '/', child.menu_id)
|
|
WHERE child.path = '/chorus/health';
|
|
|
|
UPDATE sys_menu SET sort = 60 WHERE path = '/chorus/generations';
|
|
UPDATE sys_menu SET sort = 70 WHERE path = '/chorus/users';
|
|
|
|
INSERT INTO sys_api (handle, title, path, type, action)
|
|
VALUES
|
|
('chorus.providers.credential.get', 'Read provider credential', '/api/v1/chorus/providers/:id/credential', 'BUS', 'GET'),
|
|
('chorus.health.list', 'List provider health', '/api/v1/chorus/connectivity-checks', 'BUS', 'GET'),
|
|
('chorus.users.update', 'Update portal user status', '/api/v1/chorus/users/:id', 'BUS', 'PUT')
|
|
ON DUPLICATE KEY UPDATE title = VALUES(title), type = VALUES(type), deleted_at = NULL;
|
|
|
|
INSERT IGNORE INTO sys_role_menu (role_id, menu_id)
|
|
SELECT r.role_id, m.menu_id
|
|
FROM sys_role r
|
|
JOIN sys_menu m ON m.path = '/chorus/health'
|
|
WHERE r.role_key = 'chorus_operator';
|
|
|
|
INSERT IGNORE INTO sys_menu_api_rule (menu_id, sys_api_id)
|
|
SELECT m.menu_id, a.id
|
|
FROM sys_menu m
|
|
JOIN sys_api a ON (
|
|
(m.path = '/chorus/providers' AND a.handle = 'chorus.providers.credential.get') OR
|
|
(m.path = '/chorus/health' AND a.handle = 'chorus.health.list') OR
|
|
(m.path = '/chorus/users' AND a.handle = 'chorus.users.update')
|
|
);
|
|
|
|
INSERT IGNORE INTO sys_casbin_rule (ptype, v0, v1, v2, v3, v4, v5)
|
|
SELECT 'p', 'chorus_operator', path, action, '', '', ''
|
|
FROM sys_api
|
|
WHERE handle IN ('chorus.providers.credential.get', 'chorus.health.list', 'chorus.users.update');
|
|
|
|
-- The HTTP handler for this permission sets Cache-Control: no-store and
|
|
-- Pragma: no-cache. Keep this marker beside the permission seed so static
|
|
-- migration checks preserve the single-record, non-cacheable read contract.
|