0

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.

1 Answer 1

1

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.

2
  • so basically you're saying that DBeaver hacks its way into seeing something that a simple sys table query doesn't show in results, correct?
    – amphibient
    Jun 16, 2018 at 16:38
  • @amphibient: without the judgment that's it's a hack, yes. pg_class and friends are the sys tables. information_schema is not even mandatory, you are allowed to drop it if you have no use for it. Jun 16, 2018 at 18:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.