[GENERAL] Revoking access for pg_catalog schema objects

[Saimon]

Hi
I want to restrict access for some user for tables and views in pg_catalog
schema.

After the following command in psql:
REVOKE ALL ON SCHEMA pg_catalog FROM PUBLIC;

Access, for example, for table pg_proc was restricted:
SELECT * from pg_catalog.pg_proc;

ERROR: permission denied for schema pg_catalog

So, it seems that the goal is reached.

But if I run command:
SELECT * from pg_proc;

I receive data from table pg_catalog.pg_proc. I don't know how to explain
this result.

And if I also explicitly revoke access for this table using
REVOKE ALL ON pg_catalog.pg_proc FROM PUBLIC;

Both SELECT queries, with and without schema specifying, will fail.

So, why tables and views are still available after revoking all privileges
from containing system schema?
Is it ok according to the documentation?



--
View this message in context: http://postgresql.nabble.com/Revoking-access-for-pg-catalog-schema-objects-tp5838337.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[Tom Lane]

Post by Saimon
I want to restrict access for some user for tables and views in pg_catalog
schema.

The system is not designed to support this, and you should not expect to
succeed at hiding things this way.

regards, tom lane

[David G Johnston]

Saimon <

Post by Saimon
I want to restrict access for some user for tables and views in
pg_catalog
schema.

The system is not designed to support this, and you should not expect to
succeed at hiding things this way.

I would expect a note at:

http://www.postgresql.org/docs/9.4/interactive/catalogs.html

indicating what you've noted above.

"Furthermore, the contents of each table, unless noted in the table's
description, is viewable by all users and cannot be revoked. In particular,
the contents of functions (pg_proc) are visible even if the user has not
been given permissions sufficient to EXECUTE the function."

A similar note should be added to both the pg_proc page and the "CREATE
FUNCTION" SQL command page. The fact that the contents of a function are
visible even to users unable to execute said function is not something that
would be readily assumed or considered by a novice.

Have I generalized to the point of being incorrect and/or miss where this is
discussed elsewhere in the documentation? While not frequent this seems to
come up enough to warrant documentation of the system's design choices in
this area.

David J.





--
View this message in context: http://postgresql.nabble.com/Revoking-access-for-pg-catalog-schema-objects-tp5838337p5838367.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.