How to read all metadata without select permissions to actual data?

Posted on

Question :

I need to create a user account that can read the metadata for all objects from information_schema

I can access the information_schema with any user but only for tables that the user has select privileges. Is there any role (like Oracle’s SELECT_CATALOG_ROLE) that I need?

Answer :

The possible solution depends on exactly which objects you are interested in.


The way I see is half suggested by the documentation:

Only those tables and views are shown that the current user has access to (by way of being the owner or having some privilege).

So, you can set your information_schema_reader role as owner of everything, revoking the privileges (SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER). The backside is:

The right to drop an object, or to alter its definition in any way, is not treated as a grantable privilege; it is inherent in the owner, and cannot be granted or revoked.

So, this is possibly not what you want – the role cannot see the data but can drop the table.

On the other hand, in the above privilege list there is one which seems really innocent (and can be used for checking data in tables only in a very cumbersome way): REFERENCES. You can try to grant this to an otherwise powerless user.

Other objects

Unfortunately, not every object type has such ‘weak’ privileges. A function, for example, is either executable or not by a user – there is no other privilege that can be granted. Depending on your needs (the exact list of types you want to ‘hide’), this may be acceptable.

Leave a Reply

Your email address will not be published. Required fields are marked *