8000 BED-5696 chore: Add migrations for existing agi to agt systems by mistahj67 · Pull Request #1377 · SpecterOps/BloodHound · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

BED-5696 chore: Add migrations for existing agi to agt systems #1377

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 2 commits into from
Apr 22, 2025
Merged

BED-5696 chore: Add migrations for existing agi to agt systems #1377

Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
b5457d7
chore: Add existing ag -> agt migration
mistahj67 Apr 18, 2025
2f27974
chore: address feedback
mistahj67 Apr 22, 2025
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
36 changes: 35 additions & 1 deletion cmd/api/src/database/migration/migrations/v7.4.0.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -34,4 +34,38 @@ CREATE TABLE IF NOT EXISTS asset_group_tag_selector_nodes
updated_at timestamp with time zone,
CONSTRAINT fk_asset_group_tag_selectors_asset_group_tag_selector_nodes FOREIGN KEY (selector_id) REFERENCES asset_group_tag_selectors(id) ON DELETE CASCADE,
PRIMARY KEY (selector_id, node_id)
);
);

-- Migrate existing Tier Zero selectors
WITH inserted_selector AS (
INSERT INTO asset_group_tag_selectors (asset_group_tag_id, created_at, created_by, updated_at, updated_by, name, description, is_default, allow_disable, auto_certify)
SELECT (SELECT id FROM asset_group_tags WHERE name = 'Tier Zero'), current_timestamp, 'SYSTEM', current_timestamp, 'SYSTEM', s.name, s.selector, false, true, false
FROM asset_group_selectors s JOIN asset_groups ag ON ag.id = s.asset_group_id
WHERE ag.tag = 'admin_tier_0' and NOT EXISTS(SELECT 1 FROM asset_group_tag_selectors WHERE name = s.name)
RETURNING id, description
)
INSERT INTO asset_group_tag_selector_seeds (selector_id, type, value) SELECT id, 1, description FROM inserted_selector;

-- Migrate existing Owned selectors
WITH inserted_kind AS (
INSERT INTO kind (name)
SELECT 'Tag_' || replace(name, ' ', '_') as name
FROM asset_groups
WHERE tag = 'owned'
ON CONFLICT DO NOTHING
RETURNING id, name
),
inserted_tag AS (
INSERT INTO asset_group_tags (kind_id, type, name, description, created_at, created_by, updated_at, updated_by)
SELECT ik.id, 3, ag.name, ag.name, current_timestamp, 'SYSTEM', current_timestamp, 'SYSTEM'
FROM inserted_kind ik JOIN asset_groups ag ON ik.name = 'Tag_' || replace(ag.name, ' ', '_')
RETURNING id, name
),
inserted_selector AS (
INSERT INTO asset_group_tag_selectors (asset_group_tag_id, created_at, created_by, updated_at, updated_by, name, description, is_default, allow_disable, auto_certify)
SELECT (SELECT id from inserted_tag), current_timestamp, 'SYSTEM', current_timestamp, 'SYSTEM', s.name, s.selector, false, true, false
FROM asset_group_selectors s JOIN asset_groups ag ON ag.id = s.asset_group_id
WHERE ag.tag = 'owned' and NOT EXISTS(SELECT 1 FROM asset_group_tag_selectors WHERE name = s.name)
RETURNING id, description
)
INSERT INTO asset_group_tag_selector_seeds (selector_id, type, value) SELECT id, 1, description FROM inserted_selector;
Loading
0