gabi-sql-query

name	gabi-sql-query
description	Run SQL queries against RBAC database in stage or prod using gabi

Gabi SQL Query Tool

Use this skill to run SQL queries against the RBAC database in stage or prod environments.

Workflow - Follow These Steps

BEFORE running any query, you MUST:

0. Check for config.env: Verify .cursor/skills/config.env exists. If missing, inform the user to create it from the example file.
1. Check if TOKEN is set: Verify echo $TOKEN returns a value (or ask the user)
2. Confirm environment: User must specify stage or prod
3. If any are missing: DO NOT run the query. Inform the user and provide instructions.
4. Only after all are confirmed: Proceed with running the query

CRITICAL REQUIREMENTS - READ BEFORE RUNNING QUERIES

NEVER run a query without BOTH of the following:

1. TOKEN environment variable must be set - Check with echo $TOKEN or ask the user
2. Environment must be explicitly specified - Must be either stage or prod

If TOKEN is not set OR environment is not specified, DO NOT run the query. Instead:

• Inform the user that the TOKEN and/or environment is required
• Provide instructions on how to obtain the token (see below)
• Wait for the user to set the TOKEN before proceeding

Prerequisites

Step 0: Check for config.env file

IMPORTANT: Before proceeding, verify that .cursor/skills/config.env exists. If it does not exist:

1. Create .cursor/skills/config.env with your environment-specific settings
2. The script will fail if config.env is missing or incomplete

WARNING: If config.env is missing, the script will use fallback defaults which may not work correctly. Always ensure config.env exists and is properly configured.

Step 1: Obtain the TOKEN

Get the token from the OpenShift Console by pasting the URL into their browser:

• Stage: ${OPENSHIFT_STAGE_CONSOLE} (domain from .cursor/skills/config.env)
• Prod: ${OPENSHIFT_PROD_CONSOLE} (domain from .cursor/skills/config.env)

IMPORTANT: When displaying these URLs to users, read .cursor/skills/config.env and display the actual URLs as clickable markdown links (e.g., [URL text](URL)), but keep the variable references in this skill file.

Once logged in, click on your username in the top right corner and select "Copy login command" to get the token.

Step 2: Set the TOKEN environment variable

export TOKEN=<TOKEN>

Step 3: Verify TOKEN is set

echo $TOKEN

If this returns empty, the TOKEN is not set and queries must NOT be run.

Usage

Only run queries when TOKEN is set AND environment is specified:

sh .cursor/skills/gabi/scripts/gabi.sh <stage|prod> "<SQL>"

Example:

sh .cursor/skills/gabi/scripts/gabi.sh stage "SELECT * FROM management_group LIMIT 1;"

Common SQL Queries

Tenant Queries

Search for tenant by ID

SELECT * FROM api_tenant WHERE id=<tenant_id>

Search for tenant by org ID

SELECT * FROM api_tenant WHERE org_id=<org_id>

Search for tenant by account ID

SELECT * FROM api_tenant WHERE account_id=<account_id>

Get tenant information for a group

SELECT g.id as group_id, g.uuid as group_uuid, g.name as group_name, g.tenant_id, t.id as tenant_id, t.org_id, t.account_id
FROM management_group g
JOIN api_tenant t ON g.tenant_id = t.id
WHERE g.uuid = '<group-uuid-here>';

Group Queries

Search for group by UUID

SELECT * FROM management_group WHERE uuid='<group-uuid-here>'

Search for group by ID

SELECT * FROM management_group WHERE id=<group_id>

List all principals/users in a group

SELECT DISTINCT
    p.id as principal_id,
    p.username,
    p.user_id,
    p.type as principal_type
FROM management_principal p
JOIN management_group_principals gp ON gp.principal_id = p.id
JOIN management_group g ON gp.group_id = g.id
WHERE g.uuid = '<group-uuid-here>'
ORDER BY p.username;

Search for roles assigned to a group (by UUID)

SELECT DISTINCT
    r.id as role_id,
    r.uuid as role_uuid,
    r.name as role_name,
    r.description as role_description,
    pol.id as policy_id,
    pol.name as policy_name
FROM management_role r
JOIN management_policy_roles pr ON pr.role_id = r.id
JOIN management_policy pol ON pr.policy_id = pol.id
JOIN management_group g ON pol.group_id = g.id
WHERE g.uuid = '<group-uuid-here>'
ORDER BY r.name;

Search for roles assigned to a group (by ID)

SELECT DISTINCT
    r.id as role_id,
    r.uuid as role_uuid,
    r.name as role_name,
    r.description as role_description,
    pol.id as policy_id,
    pol.name as policy_name
FROM management_role r
JOIN management_policy_roles pr ON pr.role_id = r.id
JOIN management_policy pol ON pr.policy_id = pol.id
JOIN management_group g ON pol.group_id = g.id
WHERE g.id = <group-id-here>
ORDER BY r.name;

Search for all permissions a group is assigned (by UUID)

SELECT DISTINCT
    p.permission,
    p.application,
    p.resource_type,
    p.verb,
    p.description
FROM management_permission p
JOIN management_access a ON a.permission_id = p.id
JOIN management_role r ON a.role_id = r.id
JOIN management_policy_roles pr ON pr.role_id = r.id
JOIN management_policy pol ON pr.policy_id = pol.id
JOIN management_group g ON pol.group_id = g.id
WHERE g.uuid = '<group-uuid-here>'
ORDER BY p.application, p.resource_type, p.verb;

Search for all permissions a group is assigned (by ID)

SELECT DISTINCT
    p.permission,
    p.application,
    p.resource_type,
    p.verb,
    p.description
FROM management_permission p
JOIN management_access a ON a.permission_id = p.id
JOIN management_role r ON a.role_id = r.id
JOIN management_policy_roles pr ON pr.role_id = r.id
JOIN management_policy pol ON pr.policy_id = pol.id
JOIN management_group g ON pol.group_id = g.id
WHERE g.id = <group-id-here>
ORDER BY p.application, p.resource_type, p.verb;

Role Queries

Search for role by UUID

SELECT * FROM management_role WHERE uuid='<role-uuid-here>'

Search for role by ID

SELECT * FROM management_role WHERE id=<role_id>

Search for permissions assigned to a role (by UUID)

SELECT DISTINCT
    p.id as permission_id,
    p.permission,
    p.application,
    p.resource_type,
    p.verb,
    p.description
FROM management_permission p
JOIN management_access a ON a.permission_id = p.id
JOIN management_role r ON a.role_id = r.id
WHERE r.uuid = '<role-uuid-here>'
ORDER BY p.application, p.resource_type, p.verb;

Search for permissions assigned to a role (by ID)

SELECT DISTINCT
    p.id as permission_id,
    p.permission,
    p.application,
    p.resource_type,
    p.verb,
    p.description
FROM management_permission p
JOIN management_access a ON a.permission_id = p.id
JOIN management_role r ON a.role_id = r.id
WHERE r.id = <role-id-here>
ORDER BY p.application, p.resource_type, p.verb;

Find V1 role and its V2 role mapping

Find the V1 role and its corresponding V2 role (if migrated):

SELECT r.id as role_id, r.uuid as role_uuid, r.name as role_name, rv2.uuid as v2_role_uuid, rv2.name as v2_role_name
FROM management_role r
LEFT JOIN management_rolev2 rv2 ON rv2.v1_source_id = r.id
WHERE r.uuid = '<role-uuid-here>' OR rv2.uuid = '<v2-role-uuid-here>';

Find access entries and permissions for a role

Get all access entries (permissions) for a role:

SELECT a.id as access_id, p.permission, p.application, p.resource_type, p.verb
FROM management_access a
JOIN management_permission p ON a.permission_id = p.id
WHERE a.role_id = <role-id-here>;

Count resource definitions for a role

Count how many resource definitions exist for a role's access entries:

SELECT COUNT(*) as rd_count
FROM management_resourcedefinition rd
JOIN management_access a ON rd.access_id = a.id
WHERE a.role_id = <role-id-here>;

Role Binding Queries

Search for role binding by UUID

SELECT rb.id, rb.uuid, rb.resource_type, rb.resource_id, rv2.uuid as role_uuid, rv2.name as role_name, rv2.type as role_type
FROM management_rolebinding rb
JOIN management_rolev2 rv2 ON rb.role_id = rv2.id
WHERE rb.uuid = '<role-binding-uuid-here>';

Find role bindings for a specific workspace

Find all role bindings bound to a specific workspace:

SELECT rb.id as role_binding_id, rb.uuid as role_binding_uuid, rb.resource_type, rb.resource_id, rv2.uuid as role_uuid, rv2.name as role_name, rbg.group_id, g.uuid as group_uuid, g.name as group_name
FROM management_rolebinding rb
JOIN management_rolev2 rv2 ON rb.role_id = rv2.id
LEFT JOIN management_rolebindinggroup rbg ON rbg.binding_id = rb.id
LEFT JOIN management_group g ON rbg.group_id = g.id
WHERE rb.resource_type = 'workspace' AND rb.resource_id = '<workspace-id-here>';

Find role bindings for a group

Find all workspace role bindings where a specific group is bound:

SELECT rb.uuid as role_binding_uuid, rb.resource_id as workspace_id, w.name as workspace_name
FROM management_rolebinding rb
JOIN management_rolebindinggroup rbg ON rbg.binding_id = rb.id
JOIN management_group g ON rbg.group_id = g.id
LEFT JOIN management_workspace w ON rb.resource_id = w.id::text
WHERE g.uuid = '<group-uuid-here>' AND rb.resource_type = 'workspace';

Find BindingMapping entries for a workspace

Find V1 role binding mappings for a specific workspace (used during V1 to V2 migration):

SELECT bm.id, bm.role_id, bm.resource_type_namespace, bm.resource_type_name, bm.resource_id, bm.mappings
FROM management_bindingmapping bm
WHERE bm.resource_id = '<workspace-id-here>' AND bm.resource_type_name = 'workspace';

Resource Definition Queries

Find resource definitions for a role

Find all resource definitions associated with a role's access entries:

SELECT rd.id, rd.access_id, a.role_id, p.permission, p.application, p.resource_type
FROM management_resourcedefinition rd
JOIN management_access a ON rd.access_id = a.id
JOIN management_permission p ON a.permission_id = p.id
WHERE a.role_id = <role-id-here>;

Note: Resource definitions contain JSON attributeFilter fields that specify which resources (like workspaces) the permission applies to. To view the attribute filter content, you may need to query the JSON field directly or use application code.

Policy Queries

Search for policy by group_id

SELECT * FROM management_policy WHERE group_id=<group_id>

Search for roles assigned to a policy

SELECT pr.*, r.id as role_id, r.uuid as role_uuid, r.name as role_name
FROM management_policy_roles pr
LEFT JOIN management_role r ON pr.role_id = r.id
WHERE pr.policy_id=<policy_id>

Workspace Queries

Search for workspace by ID

SELECT w.id as workspace_id, w.name as workspace_name, w.type as workspace_type, w.description, w.tenant_id, t.org_id, t.account_id
FROM management_workspace w
JOIN api_tenant t ON w.tenant_id = t.id
WHERE w.id = '<workspace-id-here>';

Get workspace parent information

SELECT w.id as workspace_id, w.name as workspace_name, w.parent_id, p.id as parent_workspace_id, p.name as parent_workspace_name, p.type as parent_workspace_type
FROM management_workspace w
LEFT JOIN management_workspace p ON w.parent_id = p.id
WHERE w.id = '<workspace-id-here>';

Count workspaces for a tenant

SELECT COUNT(*) as workspace_count
FROM management_workspace
WHERE tenant_id = <tenant_id>;

List workspaces for a tenant

SELECT w.id as workspace_id, w.name as workspace_name, w.type as workspace_type, w.description, w.parent_id, w.created, w.modified
FROM management_workspace w
WHERE w.tenant_id = <tenant_id>
ORDER BY w.created;

Count workspaces for an org

SELECT COUNT(*) as workspace_count
FROM management_workspace
WHERE tenant_id = (SELECT id FROM api_tenant WHERE org_id = '<org_id>');

List workspaces for an org

SELECT w.id, w.name, w.type, w.created
FROM management_workspace w
JOIN api_tenant t ON w.tenant_id = t.id
WHERE t.org_id = '<org_id>'
ORDER BY w.created;