PostgreSQL Revoking Permissions from pg_catalog tables

let me help you about this:

• 1st: because the pg_catalog is owned by the superuser postgres, so make sure you login to the server with this role:pg_catalog schema permission

• 2nd: make sure you connect to the right database that needs to GRANT/REVOKE permissions on. GRANT/REVOKE only affect to the current database that you connected to. That means after you login with superuser account, issue: \c [the db] to connect to that database, the shell will change to: [the db]=>

• 3rd: tables in pg_catalog defaults granted SELECT to PUBLIC: tables in pg_catalog. So, you have to run REVOKE SELECT FROM PUBLIC and then GRANT SELECT to appropriate users:

  REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC;

  GRANT SELECT ON TABLE [table] TO [user];

  For list tables in a database: pg_class and pg_namespace.

And that's all :)

What you are trying to accomplish is denied in PostgreSQL by design.

If a user could not access pg_catalog schema (as you try to do with REVOKE commands), he/she would not be able to run even simplest SELECT query - planner would have no access to table definitions.

Your goal might be achieved by REVOKE'ing access to all schemas - hence locking user only in his private schema (with CREATE SCHEMA AUTHORIZATION username).

If any rights are already GRANT'ed to public, you cannot block them selectively for one user - you can only REVOKE ... FROM public.

HTH, please ask if that's not clear.