WITH data AS ( SELECT p.organization_id o.creator_id AS owner_id ps.name AS permission_set_name FROM policy p JOIN organization o ON (o.id = p.organization_id) JOIN rule r ON (r.policy_id = p.id) JOIN rules__permission_sets rps ON (rps.rule_id = r.id) JOIN permission_set ps ON (ps.id = rps.permission_set_id) WHERE p.name LIKE 'Owner_%' AND ps.name NOT IN ( 'Developer' 'Billing' 'Owner' 'Admin' 'InternalDeveloper' 'OwnerResellerCustomer' 'AdminResellerCustomer') AND r.admin IS TRUE) admin_permission_set_policy AS ( INSERT INTO policy(organization_id principal_id name editable visible) SELECT d.organization_id d.owner_id d.permission_set_name true true FROM data d ON CONFLICT DO NOTHING RETURNING *) admin_permission_set_rule AS ( INSERT INTO rule(policy_id admin) SELECT p.id true FROM admin_permission_set_policy p admin_permission_set_rules__permission_sets AS ( INSERT INTO rules__permission_sets(rule_id permission_set_id) SELECT r.id ps.id FROM admin_permission_set_rule r JOIN admin_permission_set_policy p ON (p.id = r.policy_id) JOIN data d ON (d.organization_id = p.organization_id AND p.name = d.permission_set_name) JOIN permission_set ps ON (ps.name = d.permission_set_name) RETURNING *) SELECT * FROM admin_permission_set_rules__permission_sets WITH internal_rule AS ( SELECT r.id WHERE p.name LIKE 'Project - %' AND ps.name = 'InternalDeveloper') INSERT INTO rules__permission_sets(rule_id permission_set_id) FROM internal_rule r JOIN permission_set ps ON (ps.name = 'InternalProjects') ON CONFLICT DO NOTHING WITH reseller_owner AS ( SELECT o.id AS organization_id FROM g JOIN organization o ON (o.id = g.organization_id) WHERE g.name = 'reseller' GROUP BY o.creator_id o.id) reseller_owner_policy AS ( INSERT INTO policy(organization_id principal_id name editable visible) SELECT o.organization_id o.owner_id 'SCWManaged-ResellerOrganization' false false FROM reseller_owner o reseller_owner_organization_rule AS ( INSERT INTO rule(policy_id organization_id) SELECT p.id p.organization_id FROM reseller_owner_policy p reseller_owner_organization_rules__permission_sets AS ( INSERT INTO rules__permission_sets(rule_id permission_set_id) FROM reseller_owner_organization_rule r JOIN permission_set ps ON (ps.name = 'ResellerFullAccess') SELECT * FROM reseller_owner_policy WITH internal_owner AS ( SELECT FROM organization o JOIN organizations__customer_levels ocl ON (ocl.organization_id = o.id) JOIN customer_level cl ON (cl.id = ocl.customer_level_id) WHERE cl.name = 'iam_organization_internal') internal_owner_policy AS ( INSERT INTO policy(organization_id principal_id name editable visible) SELECT o.organization_id o.owner_id 'SCWManaged-InternalOrganization' false false FROM internal_owner o internal_owner_projects_rule AS ( INSERT INTO rule(policy_id organization_id) FROM internal_owner_policy p internal_owner_projects_rules__permission_sets AS ( INSERT INTO rules__permission_sets(rule_id permission_set_id) FROM internal_owner_projects_rule r JOIN permission_set ps ON (ps.name = 'InternalProjects' OR ps.name = 'InternalDeveloper') internal_owner_organization_rule AS ( INSERT INTO rule(policy_id organization_id) internal_owner_organization_rules__permission_sets AS ( INSERT INTO rules__permission_sets(rule_id permission_set_id) FROM internal_owner_organization_rule r JOIN permission_set ps ON (ps.name = 'InternalOrganization') SELECT * FROM internal_owner_policy WITH owner AS ( SELECT FROM organization o) owner_policy AS ( INSERT INTO policy(organization_id principal_id name editable visible) SELECT o.organization_id o.owner_id 'SCWManaged-Owner' false false FROM owner o owner_projects_rule AS ( INSERT INTO rule(policy_id organization_id) FROM owner_policy p owner_projects_rules__permission_sets AS ( INSERT INTO rules__permission_sets(rule_id permission_set_id) FROM owner_projects_rule r JOIN permission_set ps ON (ps.name = 'AllProductsFullAccess') owner_organization_rule AS ( INSERT INTO rule(policy_id organization_id) owner_organization_rules__permission_sets AS ( INSERT INTO rules__permission_sets(rule_id permission_set_id) FROM owner_organization_rule r JOIN permission_set ps ON (ps.name = 'OrganizationManager' OR ps.name = 'Ownership') SELECT * FROM owner_policy WITH reseller_customer_owner AS ( SELECT JOIN permission_set ps ON (ps.id = rps.permission_set_id AND ps.name = 'OwnerResellerCustomer') reseller_customer_owner_policy AS ( INSERT INTO policy(organization_id principal_id name editable visible) FROM reseller_customer_owner o reseller_customer_owner_projects_rule AS ( INSERT INTO rule(policy_id organization_id) FROM reseller_customer_owner_policy p reseller_customer_owner_projects_rules__permission_sets AS ( INSERT INTO rules__permission_sets(rule_id permission_set_id) FROM reseller_customer_owner_projects_rule r reseller_customer_owner_organization_rule AS ( INSERT INTO rule(policy_id organization_id) reseller_customer_owner_organization_rules__permission_sets AS ( INSERT INTO rules__permission_sets(rule_id permission_set_id) FROM reseller_customer_owner_organization_rule r JOIN permission_set ps ON (ps.name = 'AdminResellerCustomer' OR ps.name = 'OwnerResellerCustomer') SELECT * FROM reseller_customer_owner_policy WITH project_owner_groups_with_api_keys AS ( SELECT DISTINCT(g.id) JOIN principals__groups pg ON (pg.group_id = g.id) JOIN principal p ON (p.id = pg.principal_id AND p.type = 'application') JOIN api_key ak ON (ak.principal_id = p.id) WHERE g.name = 'project_owner' AND g.organization_id = 'b2593aa3-d0e8-4366-89c3-6e666abe1f6f') world_projects AS ( SELECT g.id AS group_id p.organization_id AS organization_id rp.project_id AS project_id ARRAY_AGG(ps.id) AS permission_set_ids CASE WHEN pj.name IS NULL THEN 'default' ELSE pj.name END AS project_name FROM project_owner_groups_with_api_keys g JOIN policy p ON (p.name = FORMAT('Project_owner_%s' g.id)) JOIN rules__projects rp ON (rp.rule_id = r.id) JOIN permission_set ps ON (ps.id = rps.permission_set_id AND ps.name IN ('Developer' 'InternalDeveloper')) LEFT JOIN tmp_db_world_projects pj ON (pj.id = rp.project_id) GROUP BY g.id rp.project_id p.organization_id pj.name) data AS ( SELECT wp.organization_id AS organization_id gen_random_uuid() AS application_id wp.project_id AS project_id wp.group_id AS group_id wp.permission_set_ids AS permission_set_ids FORMAT( 'Project - %s (%s)' LEFT(wp.project_name 43) LEFT(wp.project_id::text 8)) AS name FORMAT( 'API keys on project %s' LEFT(wp.project_name 100)) AS description FROM world_projects wp) project_owner_principals AS ( INSERT INTO principal (id type) SELECT d.application_id 'application' FROM data d) project_owner_applications AS ( INSERT INTO application(id organization_id name description visible editable) SELECT d.application_id d.organization_id d.name d.description true true project_owner_policies AS ( INSERT INTO policy(organization_id principal_id name description visible editable) SELECT a.organization_id d.application_id d.name d.description true true FROM project_owner_applications a JOIN data d ON (d.application_id = a.id) project_owner_rules AS ( INSERT INTO rule(policy_id) SELECT p.id FROM project_owner_policies p project_owner_rules__projects AS ( INSERT INTO rules__projects(rule_id project_id) SELECT r.id d.project_id FROM project_owner_rules r JOIN project_owner_policies p ON (p.id = r.policy_id) JOIN data d ON (d.application_id = p.principal_id) project_owner_rules__permission_sets AS ( INSERT INTO rules__permission_sets(rule_id permission_set_id) SELECT r.id unnest(d.permission_set_ids) AS permission_set_id SELECT * FROM data api_keys_applications AS ( SELECT ak.access_key AS access_key a.id AS application_id rp.project_id AS project_id JOIN api_key ak ON (ak.principal_id = pg.principal_id) JOIN application a ON (a.organization_id = p.organization_id AND a.name LIKE FORMAT('Project - %% (%s)' LEFT(rp.project_id::text 8))) GROUP BY ak.access_key a.id p.organization_id rp.project_id) UPDATE api_key ak SET principal_id = aka.application_id FROM api_keys_applications aka WHERE aka.access_key = ak.access_key WITH reseller_customer_organization AS ( SELECT o.id WHERE p.name = 'SCWManaged-Owner' GROUP BY o.id) reseller_customer_admin_policy AS ( SELECT p.* FROM reseller_customer_organization o JOIN policy p ON (p.organization_id = o.id) WHERE p.name LIKE 'Admin_%') UPDATE policy p SET name = 'Group - admin' FROM reseller_customer_admin_policy rcap WHERE rcap.id = p.id reseller_customer_ops_policy AS ( SELECT p.* WHERE p.name LIKE 'Ops_%') reseller_customer_editors_policy AS ( INSERT INTO policy(organization_id principal_id name editable visible) SELECT p.organization_id p.principal_id 'Group - ops' true true FROM reseller_customer_ops_policy p reseller_customer_editors_projects_rule AS ( INSERT INTO rule(policy_id organization_id) FROM reseller_customer_editors_policy p reseller_customer_editors_projects_rules__permission_sets AS ( INSERT INTO rules__permission_sets(rule_id permission_set_id) FROM reseller_customer_editors_projects_rule r reseller_customer_editors_organization_rule AS ( INSERT INTO rule(policy_id organization_id) reseller_customer_editors_organization_rules__permission_sets AS ( INSERT INTO rules__permission_sets(rule_id permission_set_id) FROM reseller_customer_editors_organization_rule r JOIN permission_set ps ON (ps.name = 'OrganizationReadOnly' OR ps.name = 'BillingBudgetReadOnly' OR ps.name = 'SupportTicketReadOnly') SELECT * FROM reseller_customer_editors_policy UPDATE SET name = 'Administrators' WHERE g.name = 'admin' SET name = 'Editors' WHERE g.name = 'ops' SET name = 'Billing Administrators' WHERE g.name = 'billing' SET name = 'Group - Administrators' WHERE p.name = 'Group - admin' SET name = 'Group - Billing Administrators' WHERE p.name = 'Group - billing' SET name = 'Group - Editors' WHERE p.name = 'Group - ops' WITH developer_rule AS ( SELECT r.id FROM rule r JOIN permission_set ps ON (ps.id = rps.permission_set_id AND ps.name = 'Developer')) FROM developer_rule r RETURNING * WITH developer_permission_set AS ( SELECT ps.id AS permission_set_id FROM permission_set ps WHERE ps.name = 'Developer') DELETE FROM rules__permission_sets rps USING developer_permission_set dps WHERE rps.permission_set_id = dps.permission_set_id WITH internal_developer_permission_set AS ( SELECT ps.id AS permission_set_id WHERE ps.name = 'InternalDeveloper') DELETE FROM rules__permission_sets rps USING internal_developer_permission_set idps WHERE rps.permission_set_id = idps.permission_set_id WITH empty_rule AS ( SELECT r.id LEFT JOIN rules__permission_sets rps ON (rps.rule_id = r.id) WHERE rps.permission_set_id IS NULL AND r.admin IS FALSE) a AS ( DELETE FROM rules__projects rp USING empty_rule er WHERE rp.rule_id = er.id) DELETE FROM rule r USING empty_rule er WHERE r.id = er.id g.organization_id AS organization_id FORMAT('Group - %s' g.name) AS application_name FORMAT('API keys on group %s' g.name) AS application_description JOIN application a ON (a.id = pg.principal_id AND a.name LIKE 'API key %') WHERE g.name <> 'owner' GROUP BY g.id g.organization_id) organization_group_principal AS ( INSERT INTO principal (id type) organization_group_application AS ( INSERT INTO application(id organization_id name description visible editable) SELECT d.application_id d.organization_id d.application_name d.application_description true true organization_group_principals__groups AS ( INSERT INTO principals__groups(principal_id group_id) SELECT d.application_id d.group_id JOIN organization_group_application g ON (g.id = d.application_id) SELECT * FROM organization_group_principals__groups a1.id AS old_application_id a2.id AS new_application_id JOIN application a1 ON (a1.id = pg.principal_id AND a1.name LIKE 'API key %') JOIN api_key ak ON (ak.principal_id = a1.id) JOIN application a2 ON (a2.organization_id = g.organization_id AND a2.name = FORMAT('Group - %s' g.name)) WHERE g.name <> 'owner') SET principal_id = d.new_application_id WHERE d.access_key = ak.access_key WITH applications_without_api_keys AS ( SELECT DISTINCT(a.id) FROM application a LEFT JOIN api_key ak ON (ak.principal_id = a.id) WHERE ak.access_key IS NULL) DELETE FROM principals__groups pg USING applications_without_api_keys awak WHERE pg.principal_id = awak.id SET principal_id = NULL FROM applications_without_api_keys awak WHERE awak.id = p.principal_id WITH applications_without_api_keys AS ( SELECT DISTINCT(a.id) AS id DELETE FROM application a USING applications_without_api_keys awak WHERE a.id = awak.id WITH orphan_applications AS ( SELECT principal.id AS id FROM principal LEFT JOIN application ON application.id = principal.id WHERE principal.type = 'application' AND application.id IS NULL) DELETE FROM principal p USING orphan_applications a WHERE p.id = a.id WITH owner_group AS ( SELECT g.id WHERE g.name = 'owner') DELETE FROM principals__groups pg USING owner_group og WHERE pg.group_id = og.id WITH empty_project_owner_groups AS ( SELECT DISTINCT(g.id) LEFT JOIN principals__groups pg ON (pg.group_id = g.id) WHERE pg.principal_id IS NULL AND g.name = 'project_owner') FROM empty_project_owner_groups epog WHERE epog.id = p.principal_id DELETE FROM g USING empty_project_owner_groups pog WHERE g.id = pog.id WITH orphan_groups AS ( SELECT p.id AS id FROM principal p LEFT JOIN g ON g.id = p.id p.type = 'group' AND g.id IS NULL) DELETE FROM principal p USING orphan_groups g WHERE p.id = g.id WITH empty_owner_groups AS ( SELECT DISTINCT(g.id) AND g.name = 'owner') g USING empty_owner_groups og WHERE g.id = og.id WITH project_owner_policies_without_principal AS ( SELECT * WHERE p.name LIKE 'Project_owner_%' AND p.principal_id IS NULL) a AS ( DELETE FROM rules__projects WHERE rule_id IN ( SELECT r.id FROM project_owner_policies_without_principal p JOIN rule r ON (r.policy_id = p.id))) b AS ( DELETE FROM rules__permission_sets WHERE rule_id IN ( SELECT r.id c AS ( DELETE FROM rule WHERE id IN ( SELECT r.id JOIN rule r ON (r.policy_id = p.id))) DELETE FROM policy p USING project_owner_policies_without_principal pop WHERE p.id = pop.id SET visible = FALSE WHERE p.name LIKE 'SCWManaged-%' AND p.visible IS TRUE SET principal_id = NULL WHERE p.name LIKE 'Owner_%' WITH owner_policies_without_principal AS ( SELECT * FROM owner_policies_without_principal p DELETE FROM policy p USING owner_policies_without_principal pop WHERE p.id = pop.id "" group""