presigned-url-plugin uses user connection to query storage_module, tenant users cannot upload

[theothersideofgod]

Problem

Tenant user calling uploadAppFile mutation gets error:

Error: STORAGE_MODULE_NOT_FOUND

But the storage_module record actually exists in the database.

Execution Flow

Tenant user calls uploadAppFile
    ↓
Plugin queries storage_module with tenant user's connection
    ↓
RLS checks org_memberships_sprt
    ↓
Tenant user not in there → returns 0 rows
    ↓
❌ STORAGE_MODULE_NOT_FOUND

Root Cause

Location: graphile/graphile-presigned-url-plugin/src/plugin.ts:277

const allConfigs = await loadAllStorageModules(txClient, databaseId);
//                                             ↑ user connection (with RLS)

storage_module's RLS policy checks org_memberships_sprt (platform level), but tenant users only exist in app_memberships_sprt (tenant level).

This is correct design: storage_module is the database owner's system config, tenant users should not directly read it.

The issue is: Plugin should not use user connection to read system config.

Evidence

-- Platform user can see storage_module
BEGIN;
SET ROLE authenticated;
SELECT set_config('jwt.claims.user_id', '<platform_user_id>', true);
SELECT COUNT(*) FROM metaschema_modules_public.storage_module WHERE database_id = '<db_id>';
-- Returns 1
ROLLBACK;

-- Tenant user cannot see it
BEGIN;
SET ROLE authenticated;
SELECT set_config('jwt.claims.user_id', '<tenant_user_id>', true);
SELECT COUNT(*) FROM metaschema_modules_public.storage_module WHERE database_id = '<db_id>';
-- Returns 0
ROLLBACK;

Why Root Connection is Safe

Security Point	How It's Checked	Status
databaseId	From jwt_private.current_database_id(), not user input	✅
bucket permission	getBucketConfig checks if user can use this bucket	✅
file RLS	app_files table checks app_memberships_sprt	✅
config exposure	User only gets presigned URL, cannot see endpoint/credentials	✅

Suggested Fix

Modify presigned-url-plugin to use root connection for loadAllStorageModules:

// plugin.ts

// Get rootPgPool from options or context
const rootClient = await getRootPgClient(options);

// Use root to query system config
const allConfigs = await loadAllStorageModules(rootClient, databaseId);

// Other operations (bucket check, file insert) continue using user connection
const bucket = await getBucketConfig(txClient, ...);  // ← Keep RLS

Scope of changes:

• graphile-presigned-url-plugin/src/plugin.ts — use root when querying storage_module
• May need to modify plugin options to pass rootPgPool

Current Workaround

Manually add RLS policies (test environment only):

CREATE POLICY auth_select_all ON metaschema_modules_public.storage_module
  FOR SELECT TO authenticated USING (true);
CREATE POLICY auth_select_all ON metaschema_public.schema
  FOR SELECT TO authenticated USING (true);
CREATE POLICY auth_select_all ON metaschema_public.table
  FOR SELECT TO authenticated USING (true);

Impact

• All tenant users cannot use presigned URL upload functionality
• Only platform users can upload files