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?


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.

| improve this answer | |
  • The account must not be able to modify or execute objects so I'm afraid this approach can't be used. I'm interested in all objects, not only tables. – sjk Oct 8 '15 at 9:12
  • @sjk Then you are out of luck, I'm afraid - this is what the documentation tells you above, and this is what I've seen while experimenting. OTOH, REFERENCES does not allow to modify anything - and if the role lacks CREATE on the schema, this privilege cannot be possibly used. – dezso Oct 8 '15 at 9:31

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.