Files
Srinija Kakumanu 05d8c8d4f8 fix(coderd): list endpoints return 500 for API keys with ID-scoped allow lists (#32145)
> [!NOTE]
> This PR was generated by Coder Agents on behalf of @ksrinija.

## Ticket Link

Fixes
[CORE-780](https://linear.app/codercom/issue/CORE-780/bug-list-endpoints-return-500-column-reference-id-is-ambiguous-for-api)

## Description

List endpoints returned 500 with `pq: column reference "id" is
ambiguous` for API keys whose scope has an ID-scoped allow list (for
example `--allow template:<uuid>`). The SQL authorization filters for
workspaces, templates, audit logs and connection logs used the shared
`resourceIDMatcher()`, which emits an unqualified `id`. Their list and
count queries join other tables that also have an `id` column. The ID
check is only generated for specific IDs, so wildcard allow lists
(`template:*`) and tokens without allow lists were not affected.

This PR qualifies the column in each affected converter in
`coderd/rbac/regosql/configs.go`: `workspaces.id`, `t.id`,
`audit_logs.id` and `connection_logs.id`. This follows the existing
pattern used by `chatResourceIDMatcher()` (`chats_expanded.id`) and
`ChatModelConfigConverter` (`cmc.id`).

<details>
<summary>Decision log</summary>

- **Inline qualified matchers instead of changing
`resourceIDMatcher()`.** Other converters share the helper and query
different tables and aliases, so no single prefix inside it is correct
for all of them.
- **Remaining converters checked; none affected.** `UserConverter`,
`MCPServerConfigConverter` and `OrganizationSkillConverter` filter
single-table queries. `AIBridgeInterceptionConverter`'s queries either
read one table or join subqueries that do not expose an `id` column.
`NoACLConverter` and `DefaultVariableConverter` are only used in tests.
This was checked against the query SQL, not run against Postgres.
- **`AIBridgeInterceptionConverter` must keep a bare `id`.** Its queries
refer to the table both as `aibridge_interceptions` and by the alias
`ai`, so qualifying it with either name would break the others.
- **Possible follow-up, not in scope:** change `resourceIDMatcher()` to
take a column argument so each converter names its column explicitly.
Pending team discussion.
- **Tests:** string-match cases in `compile_test.go` document the exact
SQL each converter generates, and need no database fixtures. A
Postgres-backed test (`TestAllowListIDScopedListEndpoints`) also runs
all 4 affected queries, so a later alias or join change that breaks the
qualified column is caught. Both were kept after review discussion.

</details>

## Testing Done

- **SQL generation tests:** added `WorkspaceAllowList`,
`TemplateAllowList`, `AuditLogAllowList` and `ConnectionLogAllowList` to
`TestRegoQueries` in `coderd/rbac/regosql/compile_test.go`. Each
compiles an ID-scoped allow list with the affected converter and checks
the exact generated SQL.
- **Postgres regression test:** added
`TestAllowListIDScopedListEndpoints` in
`coderd/database/dbauthz/allowlist_test.go`. It runs `GetWorkspaces`,
`GetTemplatesWithFilter`, `GetAuditLogsOffset` and
`GetConnectionLogsOffset` against Postgres with the real RBAC authorizer
and an allow list naming one row of each type. Each subtest checks that
the query succeeds and returns only the allow-listed row, out of two.
This catches future alias or join changes that the string tests cannot.
- **Confirmed both tests catch the bug:** with `configs.go` reverted to
`main`, all 4 `TestRegoQueries` cases fail because the generated SQL
contains the bare `id`, and all 4 Postgres subtests fail with `pq:
column reference "id" is ambiguous`. With the fix, all pass.
- **Existing tests:** `go test ./coderd/rbac/...
./coderd/database/dbauthz/...` passes.
- **Lint:** the repo's pre-commit checks (`make pre-commit`, including
`golangci-lint`) pass.
- **CI:** <fill in once checks finish on `e64c7f69c9`>
2026-11-18 09:58:29 -05:00
..