Inconsistent Access to information_schema via Supabase JavaScript Client

[RaoulRSDSchipper]

Hello,

I'm working on a TypeScript project and trying to retrieve foreign key details for tables in my public schema using a PostgreSQL function. The function executes as expected when run directly in the SQL editor, but it returns an empty array without any errors when I try to fetch it using the JavaScript client with the service role key.

Here is the function definition:

CREATE
OR REPLACE FUNCTION get_foreign_key_details () RETURNS TABLE (
  table_schema text,
  table_name text,
  column_name text,
  foreign_table_name text
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        tc.table_schema::text, 
        tc.table_name::text, 
        kcu.column_name::text, 
        ccu.table_name::text AS foreign_table_name
    FROM 
        information_schema.table_constraints AS tc 
        JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
        JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
    WHERE 
        tc.constraint_type = 'FOREIGN KEY' 
        AND tc.table_schema = 'public';
END;
$$ LANGUAGE plpgsql;

Interestingly, another function that queries information_schema.columns returns results successfully when executed from the JavaScript client using the service role key:

CREATE OR REPLACE FUNCTION get_table_column_details(table_name text)
RETURNS TABLE(column_name text, data_type text) AS $$
BEGIN
    RETURN QUERY EXECUTE format('
        SELECT column_name::text, data_type::text 
        FROM information_schema.columns 
        WHERE table_name = %L 
        AND table_schema = ''public'' 
        ORDER BY ordinal_position', table_name);
END;
$$ LANGUAGE plpgsql;

This inconsistency leads me to wonder if there are restrictions or permissions specifically affecting the table_constraints, key_column_usage, and constraint_column_usage views in information_schema that do not apply to the columns view.

Could there be specific permissions on the service role that restrict access to certain information_schema tables but not others? How should such permissions be configured or verified within Supabase to ensure consistent access through the JavaScript client?

Thank you in advance.

[encima]

You can check the permissions for the service_role using the \du command in psql to ensure the access exists for both (if it doesnt, you can also grant access to the role).

Have you also confirmed that the function is being run as invoker and not definer? (docs)