feat: implement SearchOrganizationPATs admin RPC by AmanGIT07 · Pull Request #1482 · raystack/frontier
Summary
- Add
SearchOrganizationPATsadmin RPC to list all PATs in an org with enriched data - Returns PAT details with created_by info (user title, email), scopes (role + resources), and timestamps
- Supports RQL filtering, search, sorting, and pagination
- For "all projects" scope PATs, resolves actual project IDs from SpiceDB (grouped by user to minimize calls)
- Requires superadmin auth (
IsSuperUser)
How it works
SQL (2 queries)
Uses subquery pagination — inner subquery selects paginated PATs with RQL applied, outer query joins users + policies for enrichment:
SELECT p.id, p.title, u.title, u.email, pol.role_id, pol.resource_type, ... FROM ( SELECT ... FROM user_pats p LEFT JOIN users u ON p.user_id = u.id WHERE p.org_id = ? AND p.deleted_at IS NULL -- RQL filters/search/sort here LIMIT 30 OFFSET 0 ) p LEFT JOIN users u ON p.user_id = u.id LEFT JOIN policies pol ON pol.principal_id = p.id AND pol.principal_type = 'app/pat' ORDER BY p.created_at DESC, p.id, pol.role_id
Returns multiple rows per PAT (one per policy). Go code groups rows by PAT ID to build nested scopes.
Scope resolution
- Specific project scope: resource_ids populated directly from policies table
- All-projects scope (pat_granted): resolved via SpiceDB LookupResources per unique user, populating actual project IDs the user has access to
- Org scope: resource_ids contains the org ID
RQL support
┌──────────────────┬────────┬────────┬──────┐
│ Field │ Filter │ Search │ Sort │
├──────────────────┼────────┼────────┼──────┤
│ id │ ✓ │ ✓ │ │
├──────────────────┼────────┼────────┼──────┤
│ title │ ✓ │ ✓ │ ✓ │
├──────────────────┼────────┼────────┼──────┤
│ created_by_title │ ✓ │ ✓ │ ✓ │
├──────────────────┼────────┼────────┼──────┤
│ created_by_email │ ✓ │ ✓ │ ✓ │
├──────────────────┼────────┼────────┼──────┤
│ created_at │ ✓ │ │ ✓ │
├──────────────────┼────────┼────────┼──────┤
│ expires_at │ ✓ │ │ ✓ │
├──────────────────┼────────┼────────┼──────┤
│ last_used_at │ ✓ │ │ ✓ │
└──────────────────┴────────┴────────┴──────┘
Supported filter operators: eq, neq, gt, gte, lt, lte, like, notlike, ilike, notilike, empty, notempty, in, notin
Default and max limit: 30 (reduced from standard 50 due to SpiceDB enrichment cost).
Sample request/response
Request
{
"org_id": "67801432-d115-4a03-9fb0-839341c56632",
"query": {
"search": "aman",
"limit": 10,
"offset": 0,
"sort": [{"name": "created_at", "order": "desc"}]
}
}
Response
{
"organization_pats": [
{
"id": "b1186e25-0332-41b6-a85c-67ba17612e52",
"title": "ci-pipeline-token",
"created_by": {
"id": "9bd455fb-1957-4fa9-b663-0eb1872c4baf",
"title": "Aman Prasad",
"email": "aman@pixxel.co.in"
},
"scopes": [
{
"role_id": "b5763e25-fc5b-42fc-8657-85e03e197395",
"resource_type": "app/organization",
"resource_ids": ["67801432-d115-4a03-9fb0-839341c56632"]
},
{
"role_id": "c4095deb-0cfd-4f24-9259-a022079f181b",
"resource_type": "app/project",
"resource_ids": ["f06fa1f6-6adf-4a6d-a12c-e22a8ab78529"]
}
],
"created_at": "2026-03-24T16:15:28.164Z",
"expires_at": "2026-04-29T15:15:37.870Z",
"last_used_at": "2026-03-25T10:30:00Z"
}
...
],
"pagination": {
"offset": 0,
"limit": 10,
"total_count": 42
}
}
Filter example
{
"org_id": "67801432-...",
"query": {
"filters": [
{"name": "created_by_email", "operator": "ilike", "string_value": "%@pixxel%"},
{"name": "expires_at", "operator": "lt", "string_value": "2026-04-01T00:00:00Z"}
]
}
}