PostgreSQL Revoking Permissions from pg_catalog tables

Question

Is there a way I can revoke permissions from a user to the catalog objects (i.e. information_schema) and PostgreSQL tables (i.e. pg_catalog)? I've tried several things and scoured the net. I'm not having any luck. The only thing I read that is partially helpful is I may not want to remove "public" from the system tables in case user defined functions rely on an object in one of those schemas. The commands below are a small snap shot of what I have not gotten to work with the exception of a single table.

REVOKE ALL PRIVILEGES ON SCHEMA pg_catalog FROM PUBLIC; -- didn't work
REVOKE ALL PRIVILEGES ON SCHEMA pg_catalog FROM public; -- didn't work
REVOKE ALL PRIVILEGES ON SCHEMA pg_catalog FROM user1; -- didn't work
REVOKE SELECT ON pg_catalog.pg_roles FROM user1; -- worked
REVOKE SELECT ON pg_catalog.pg_database FROM user1;  -- didn't work

REVOKE ALL PRIVILEGES ON SCHEMA pg_catalog FROM g_users; -- didn't work
REVOKE SELECT ON pg_catalog.pg_database FROM g_users;  -- didn't work

Any ideas? Or is this just not possible? Thanks...

Leslie

Solution 1

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 :)

Solution 2

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.

Relevant documentation:

• Creating a Schema
• Schemas and Privileges