Supabase•2w ago

Can't read tables with service role

I was developing a full blown API via edge functions locally, everything worked smoothly. I may have over engineered the function code, but i see no reason why it works locally but fails remotely. Details: im using the latest supabase-js (2.54) both locally and remotely. I have made custom rate limiting code, that reads values from the db for various actions that I can declare individually. ( i will continue in multiple posts on this thread)

49 Replies

axxyOP•2w ago

I didn't want to repeat myself with CORS and generic error handling in every edge function, so I created a function wrapper that does some of that generic stuff

export function createApiHandler(
  handler: (req: Request, supabaseAdmin: SupabaseClient) => Promise<Response>,
  options?: { actionType?: string }
) {
  return async (req: Request): Promise<Response> => {
     ...

    try {
      
      const supabaseAdmin = createAdminClient();

      // 3. Perform rate limiting if an actionType is specified in the options.
      if (options?.actionType) {
        const authHeader = req.headers.get('Authorization');
        let userId: string | undefined;

        // If there's an auth header, try to resolve the user ID from the JWT.
        if (authHeader) {
          const supabase:SupabaseClient = createClient(
            Deno.env.get('SUPABASE_URL')!, 
            Deno.env.get('SUPABASE_ANON_KEY')!, 
            { global: { headers: { Authorization: authHeader } } }
          );
          const { data: { user } } = await supabase.auth.getUser();
          if (user) {
            userId = user.id;
          }
        }
        
        // Always get the IP as a fallback for anonymous or unauthenticated requests.
        const ip = req.headers.get('x-forwarded-for')?.split(',').shift()?.trim();

        // Call the rate-limiting logic using the client we already created.
        const { error: rateLimitError } = await checkRateLimit(supabaseAdmin, {
          actionType: options.actionType,
          userId: userId, // Will be undefined if user is not logged in
          ip: ip,         // checkRateLimit will use this if userId is falsy
        });

        // If the user is rate-limited, stop execution and return the error.
        if (rateLimitError) {
          return rateLimitError;
        }
      }

      // 4. Execute the core business logic handler.
      // We inject the `supabaseAdmin` client we created into the handler.
      return await handler(req, supabaseAdmin);

    } catch (error) {
    ...
    }
  };
}

export function createApiHandler(
  handler: (req: Request, supabaseAdmin: SupabaseClient) => Promise<Response>,
  options?: { actionType?: string }
) {
  return async (req: Request): Promise<Response> => {
     ...

    try {
      
      const supabaseAdmin = createAdminClient();

      // 3. Perform rate limiting if an actionType is specified in the options.
      if (options?.actionType) {
        const authHeader = req.headers.get('Authorization');
        let userId: string | undefined;

        // If there's an auth header, try to resolve the user ID from the JWT.
        if (authHeader) {
          const supabase:SupabaseClient = createClient(
            Deno.env.get('SUPABASE_URL')!, 
            Deno.env.get('SUPABASE_ANON_KEY')!, 
            { global: { headers: { Authorization: authHeader } } }
          );
          const { data: { user } } = await supabase.auth.getUser();
          if (user) {
            userId = user.id;
          }
        }
        
        // Always get the IP as a fallback for anonymous or unauthenticated requests.
        const ip = req.headers.get('x-forwarded-for')?.split(',').shift()?.trim();

        // Call the rate-limiting logic using the client we already created.
        const { error: rateLimitError } = await checkRateLimit(supabaseAdmin, {
          actionType: options.actionType,
          userId: userId, // Will be undefined if user is not logged in
          ip: ip,         // checkRateLimit will use this if userId is falsy
        });

        // If the user is rate-limited, stop execution and return the error.
        if (rateLimitError) {
          return rateLimitError;
        }
      }

      // 4. Execute the core business logic handler.
      // We inject the `supabaseAdmin` client we created into the handler.
      return await handler(req, supabaseAdmin);

    } catch (error) {
    ...
    }
  };
}

as u can see, it creates an admin supabase admin client here and then passes it down the wrapped function, though I assume that should give no problems the rate limiting function reads the config on rate limiting rules by finding the appropriate row in app_config table like so

export async function checkRateLimit(
  supabaseAdmin: SupabaseClient,
  params: { actionType: string, userId?: string, ip?: string }
) {
  ...

  const { data: config, error: configError } = await supabaseAdmin
    .from('app_config')
    .select('key, value') 
    .in('key', [`${actionType}_limit_count`, `${actionType}_limit_minutes`]); // << THIS CODE SEES TO FAIL
  
  if (configError || !config || config.length < 2) {
    console.error(`Rate limit config missing in 'app_config' for action: ${actionType}`); // I GET THIS ERROR MSG IN LOG
    return { error: errorResponse(500, "Server configuration error.") }; 
  }


  const limitCountStr = config.find(c => c.key === `${actionType}_limit_count`)?.value;
  const limitMinutesStr = config.find(c => c.key === `${actionType}_limit_minutes`)?.value;

  // Add a check to ensure we actually found the values before parsing.
  if (!limitCountStr || !limitMinutesStr) {
    console.error(`Incomplete rate limit config in 'app_config' for action: ${actionType}`);
    return { error: errorResponse(500, "Server configuration error.") };
  }
  
  //... there was code that logged the action into a different table

  // 4. Enforce the limit.
  if (count !== null && count >= limitCount) {
    const message = `Too Many Requests. Limit is ${limitCount} requests per ${limitMinutes} minutes.`;
    return { error: errorResponse(429, message) };
  }

  // 5. If all checks pass, the action is allowed.
  return { error: null };
}

export async function checkRateLimit(
  supabaseAdmin: SupabaseClient,
  params: { actionType: string, userId?: string, ip?: string }
) {
  ...

  const { data: config, error: configError } = await supabaseAdmin
    .from('app_config')
    .select('key, value') 
    .in('key', [`${actionType}_limit_count`, `${actionType}_limit_minutes`]); // << THIS CODE SEES TO FAIL
  
  if (configError || !config || config.length < 2) {
    console.error(`Rate limit config missing in 'app_config' for action: ${actionType}`); // I GET THIS ERROR MSG IN LOG
    return { error: errorResponse(500, "Server configuration error.") }; 
  }


  const limitCountStr = config.find(c => c.key === `${actionType}_limit_count`)?.value;
  const limitMinutesStr = config.find(c => c.key === `${actionType}_limit_minutes`)?.value;

  // Add a check to ensure we actually found the values before parsing.
  if (!limitCountStr || !limitMinutesStr) {
    console.error(`Incomplete rate limit config in 'app_config' for action: ${actionType}`);
    return { error: errorResponse(500, "Server configuration error.") };
  }
  
  //... there was code that logged the action into a different table

  // 4. Enforce the limit.
  if (count !== null && count >= limitCount) {
    const message = `Too Many Requests. Limit is ${limitCount} requests per ${limitMinutes} minutes.`;
    return { error: errorResponse(429, message) };
  }

  // 5. If all checks pass, the action is allowed.
  return { error: null };
}

I am at a total loss of why it fails, especially since it works flawlessly on my local supabase instance

axxyOP•2w ago

i imported the migration, so all values were present

axxyOP•2w ago

most notably this function fails (though all rate limited in this way functions fail)

...
const ACTION_TYPE = 'anon_post_upload';
...
async function handleGeneratePostUrl(req, supabaseAdmin) {
  // 1. Validate Input
  const { fileType } = await req.json();
  if (!fileType) {
    return errorResponse(400, 'Bad Request: "fileType" is required.');
  }
  if (!ALLOWED_MIME_TYPES.includes(fileType)) {
    return errorResponse(400, `Bad Request: File type "${fileType}" is not allowed.`);
  }
  // 2. Business Logic: Generate a unique path and the signed URL
  // A UUID ensures no filename collisions. We append the file extension for clarity.
  const fileExt = fileType.split('/')[1];
  const imagePath = `${crypto.randomUUID()}.${fileExt}`;
  const { data, error } = await supabaseAdmin.storage.from('posts').createSignedUploadUrl(imagePath, {
    upsert: false
  });
  if (error) {
    console.error('Error creating post upload URL:', error);
    return errorResponse(500, 'Could not create signed URL for post upload.');
  }
  // 3. Log the successful action for rate limiting.
  // Since this is an anonymous endpoint, we log the IP address.
  const ip = req.headers.get('x-forwarded-for')?.split(',').shift()?.trim();
  await supabaseAdmin.from('action_logs').insert({
    ip_address: ip,
    action_type: ACTION_TYPE
  });
  // 4. Return the success response
  const responseData = {
    ...data,
    requiredHeaders: {
      'Content-Type': fileType
    }
  };
  return new Response(JSON.stringify(responseData), {
    status: 200,
    headers: {
      'Content-Type': 'application/json',
      ...CORS_HEADERS
    }
  });
}
// Activate the handler with our declarative rate limiting.
// The wrapper will automatically use the IP address since the user is not authenticated.
Deno.serve(createApiHandler(handleGeneratePostUrl, {
  actionType: ACTION_TYPE
}));

...
const ACTION_TYPE = 'anon_post_upload';
...
async function handleGeneratePostUrl(req, supabaseAdmin) {
  // 1. Validate Input
  const { fileType } = await req.json();
  if (!fileType) {
    return errorResponse(400, 'Bad Request: "fileType" is required.');
  }
  if (!ALLOWED_MIME_TYPES.includes(fileType)) {
    return errorResponse(400, `Bad Request: File type "${fileType}" is not allowed.`);
  }
  // 2. Business Logic: Generate a unique path and the signed URL
  // A UUID ensures no filename collisions. We append the file extension for clarity.
  const fileExt = fileType.split('/')[1];
  const imagePath = `${crypto.randomUUID()}.${fileExt}`;
  const { data, error } = await supabaseAdmin.storage.from('posts').createSignedUploadUrl(imagePath, {
    upsert: false
  });
  if (error) {
    console.error('Error creating post upload URL:', error);
    return errorResponse(500, 'Could not create signed URL for post upload.');
  }
  // 3. Log the successful action for rate limiting.
  // Since this is an anonymous endpoint, we log the IP address.
  const ip = req.headers.get('x-forwarded-for')?.split(',').shift()?.trim();
  await supabaseAdmin.from('action_logs').insert({
    ip_address: ip,
    action_type: ACTION_TYPE
  });
  // 4. Return the success response
  const responseData = {
    ...data,
    requiredHeaders: {
      'Content-Type': fileType
    }
  };
  return new Response(JSON.stringify(responseData), {
    status: 200,
    headers: {
      'Content-Type': 'application/json',
      ...CORS_HEADERS
    }
  });
}
// Activate the handler with our declarative rate limiting.
// The wrapper will automatically use the IP address since the user is not authenticated.
Deno.serve(createApiHandler(handleGeneratePostUrl, {
  actionType: ACTION_TYPE
}));

i double checked that the app_config table is not malformed. Locally, RLS policies were disabled since I was using the admin client to access it, but remotely i enabled read access for all for testing, and it still doesn't work right now im thinking passing down the admin client is an issue? But code without this type of rate limiting works I would love to find a place to keep looking for the bug in project id: ayaldagjacwuwllsmefn

garyaustin•2w ago

You don't need to post your project id here. This is a user helping user forum. I'll glance thru this later if no one else comes along. Also you should state if you are using the new API KEYs or asymmetric JWT's.

axxyOP•2w ago

>Also you should state if you are using the new API KEYs or asymmetric JWT's. I'm using the old anon and service role jwt yeah hmm could it be that my project has no way of using the old service jwts?

silentworks•2w ago

You mentioned you created an admin client but you haven't showed us how that is even created. Also you have a second client in your code which is using the anon key which could potentially mess with your admin client depending on how it was created.

axxyOP•2w ago

just this

axxyOP•2w ago

does creating an anon client invalidate the admin client?

silentworks•2w ago

You need to tell it not to use session and turn off some other features else both clients could end up sharing the same session. Example of how to create a service role client can be found here https://github.com/orgs/supabase/discussions/15860

axxyOP•2w ago

thanks, this looks like it could be exactly the issue! It's too late to work on this, but I'll let you know if this fixes it when I wake up does it make sense for it to work in a local instance without these options? Why is there a difference between local and remote behaviour?

silentworks•2w ago

I wouldn't know why there is a difference between the two.

axxyOP•2w ago

alright, I guess we may actually know more if this does fix the issue, as it's not certain yet

silentworks•2w ago

In local you are unlikely sending the authorization header, whilst on hosted this is always sent to en edge function (well this was true when using the old api keys, not so sure if this is still the same with the new ones).

axxyOP•2w ago

i was sending the authorization header with the jwt anon key And I'm still using the old api keys, I didn't figure there's a new standard. I will move to them but only after I fix this issue

silentworks•2w ago

I'm not suggesting you move to them here, I'm just stating they work differently. You should read up on them before moving to them.

axxyOP•2w ago

unfortunately this has not fixed the error or changed the behaviour in any way okay i have made a test function that just tests the ability to do db queries

axxyOP•2w ago

i get this error

axxyOP•2w ago

im totally at a loss are the old jwts deprecated now? it would appear i can't make any db requests in the edge functions themselves, at least not with .from()?

garyaustin•2w ago

If the apikey was wrong you would get an APIkey error. Does your DB work from any sort of app at all? Check the status of the instance on the home page. Maybe restart the instance. That error sounds like PostgREST (the API) can't access its internal data from the db.

axxyOP•2w ago

seems to be fine

axxyOP•2w ago

didn't help ill try doing it from the client library oh and The DB does work HeidiSQL is able to connect to it via the pooler

garyaustin•2w ago

Where are you getting that error message from?

axxyOP•2w ago

I log the error here

axxyOP•2w ago

it's the error object

axxyOP•2w ago

the tests table is just this

garyaustin•2w ago

Take a look in the API Gateway log and see if there is any more info.

axxyOP•2w ago

the raw log msg

message.txt

garyaustin•2w ago

There is no table name in the URL...

axxyOP•2w ago

huhh yeah that's very weird i didn't even notice is my request in js bad?

axxyOP•2w ago

this request alone should be enough for the correct api call , right?

axxyOP•2w ago

wait im confused okay im not sure what was that , sent the request again and it's in there

axxyOP•2w ago

"GET | 503 | 18.198.4.105 | 96e913f07f7dbb3e | https://ayaldagjacwuwllsmefn.supabase.co/rest/v1/tests?select=*&key=eq.Marco | Deno/1.45.2 (variant; SupabaseEdgeRuntime/1.68.3)"

"GET | 503 | 18.198.4.105 | 96e913f07f7dbb3e | https://ayaldagjacwuwllsmefn.supabase.co/rest/v1/tests?select=*&key=eq.Marco | Deno/1.45.2 (variant; SupabaseEdgeRuntime/1.68.3)"

seems correct

garyaustin•2w ago

That looks fine. Did you restart the instance?

axxyOP•2w ago

yes

garyaustin•2w ago

For some reason your PostgREST server is not working..

axxyOP•2w ago

https://tenor.com/view/vylerria-jadeyanh-smoking-gif-26734115

Tenor

garyaustin•2w ago

I've never seen anything useful in the PostgREST log but look there.

axxyOP•2w ago

i was already thinking how convenient supabase is and then something always is supposed to break lol

"Failed to load the schema cache using db-schemas=pg_pgrst_no_exposed_schemas and db-extra-search-path=public,extensions. {\"code\":\"3F000\",\"details\":null,\"hint\":null,\"message\":\"schema \\\"pg_pgrst_no_exposed_schemas\\\" does not exist\"}"

"Failed to load the schema cache using db-schemas=pg_pgrst_no_exposed_schemas and db-extra-search-path=public,extensions. {\"code\":\"3F000\",\"details\":null,\"hint\":null,\"message\":\"schema \\\"pg_pgrst_no_exposed_schemas\\\" does not exist\"}"

did i mess up the db with a migration?

garyaustin•2w ago

Do you have the API turned off and is public in the exposed schema list. https://supabase.com/dashboard/project/_/settings/api

axxyOP•2w ago

oh god it is is it disabled by default?

garyaustin•2w ago

No But it is an option when you create the instance to turn it off.

axxyOP•2w ago

wow, it works AHAHA thanks so much! what a stupid mistake

garyaustin•2w ago

The error was not very useful either from them.

axxyOP•2w ago

i suppose it makes sense - no schemas exposed for the public API

garyaustin•2w ago

Your last error message was clear, that was when I realized to ask about the API being on. The one to your rest request was vague.

axxyOP•2w ago

yep many layers of indirection probably makes it hard to know for sure from the pov of postgrest thanks a lot this made me realize how bad my own error logging is, I obscured the actual error even further

Gaming

Programming

Can't read tables with service role

Did you find this page helpful?