CODEWITHSUNDEEP
postgresqlinformation-schema
Massimo
Massimo

Reputation: 3470

troubles querying information schema

postgresql server 8.4

With a user with attribute "super user", I can perform this query :

SELECT
        ccu.table_name AS master_table, ccu.column_name AS master_column,
        tc.table_name AS child_table, kcu.column_name AS child_column
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 constraint_type = 'FOREIGN KEY'
ORDER BY master_table, master_column

With a normal user, I have no errors but also no results. Which are the minimal permissions ... grants ... to allow a normal user to query the information schema ?

I tried unsuccesfully

GRANT USAGE ON SCHEMA information_schema to user

and also

grant select on information_schema.constraint_column_usage to user

(and the other two used)

Upvotes: 1

Views: 580

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247225

You will only see objects on which you have some permissions:

  • You cannot see other users' temporary objects.

  • You can see objects whose owner is a role you belong to.

  • You can see objects if you have any privileges on the table or its columns.

To bypass these restrictions, you could create a function with SECURITY DEFINER that belongs to a superuser and runs the query for you.

Then revoke EXECUTE on that function from PUBLIC and grant it to the user who needs it.

CREATE FUNCTION info_schema_query()
   RETURNS TABLE (
      master_table  information_schema.sql_identifier,
      master_column information_schema.sql_identifier,
      child_table   information_schema.sql_identifier,
      child_column  information_schema.sql_identifier
   )
   LANGUAGE sql STABLE SECURITY DEFINER
   SET search_path = information_schema
AS $$SELECT ...$$;

REVOKE EXECUTE ON FUNCTION info_schema_query() FROM PUBLIC;
GRANT  EXECUTE ON FUNCTION info_schema_query() TO j_random_user;

Upvotes: 2

Related Questions