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
Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can run a revoke command. You can revoke any combination of SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, or ALL.
The REVOKE command revokes previously granted privileges from one or more roles. The key word PUBLIC refers to the implicitly defined group of all roles. See the description of the GRANT command for the meaning of the privilege types.
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 :)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With