Inconsistent Access to information_schema via Supabase JavaScript Client

[RaoulRSDSchipper]

Discussed in https://github.com/orgs/supabase/discussions/23314

^{Originally posted by RaoulRSDSchipper April 27, 2024}
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]

Closing this as there is an open discussion. Please only open one or the other (unless asked) to reduce efforts and keep knowledge in one place