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