Postgresql – Inconsistent presence of view in Postgres system tables

postgresqlsystem-tablesview

I am perplexed here. I have two environments, let's say TEST and PROD.

In both environments' Postgres DBs there is a view foo_view visible in my DB client (DBeaver). However, only in PROD can I find that view in the system tables:

select * from information_schema.tables WHERE table_name = 'foo_view'; 
select * from information_schema.views WHERE table_name = 'foo_view';

When executed in PROD, the above SQL returns data that corresponds to the client rendering but not in TEST. I was not the DBA who introduced that view in either of the instances.

I am looking for an explanation as to which possible scenarios could lead to that view not being in the system tables but still visible in the client UI.

Best Answer

The views in information_schema are subject to permission checks.

Specifically, the definition information_schema.tables has this filtering clause at the end (use \d+ information_schema.tables in psql to see this):

  [... long definition cut...]
  WHERE (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char"]))
  AND NOT pg_is_other_temp_schema(nc.oid)
  AND (pg_has_role(c.relowner, 'USAGE'::text) 
   OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text)
    OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text));

information_schema.views has similar restrictions.

If the user issuing the following query

 select * from information_schema.tables WHERE table_name = 'foo_view'

has no privilege whatsoever on foo_view, it's to be expected that this query returns 0 row.

If a user sees different results on this query between databases PROD and TEST , it's presumably because that user has different permissions on this object between databases.

As for the client program, DBeaver, it can show all objects in both databases because presumably it skips these permission checks by addressing directly pg_class, pg_type, pg_namespace and so on (the postgres-specific underlying tables implementing the catalog), like for instance psql, rather than the views in information_schema.

Also when you're connected as a superuser, you should see everything in all cases.

Related Question