PostgreSQL 从 pg_catalog 表中撤销权限 [英] PostgreSQL Revoking Permissions from pg_catalog tables

问题描述

有什么方法可以撤销用户对目录对象(即 information_schema)和 PostgreSQL 表(即 pg_catalog)的权限?我已经尝试了几件事并在网上搜索.我没有任何运气.我读到的唯一有部分帮助的内容是我可能不想从系统表中删除public"，以防用户定义的函数依赖于这些模式之一中的对象.下面的命令是我没有使用过的除了单个表之外的一些小快照.

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...

莱斯利

推荐答案

让我帮你解决这个问题:

let me help you about this:

• 1st:因为pg_catalog 归超级用户postgres 所有，所以请确保您使用此角色登录服务器:pg_catalog 架构权限

• 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

第二:确保您连接到需要GRANT/REVOKE权限的正确数据库.GRANT/REVOKE 仅影响您连接到的当前 数据库.这意味着在您使用超级用户帐户登录后，发出:c [the db] 以连接到该数据库，shell 将更改为:[the db]=>

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:pg_catalog 中的表默认授予 SELECT 给 PUBLIC:pg_catalog 中的表格.因此，您必须运行 REVOKE SELECT FROM PUBLIC，然后向适当的用户运行 GRANT SELECT:

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:

从 PUBLIC 中撤销对 SCHEMA pg_catalog 中所有表的选择；

REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC;

将表 [表] 上的选择授予 [用户];

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

对于数据库中的列表表:pg_class 和 pg_namespace.

For list tables in a database: pg_class and pg_namespace.

仅此而已:)

这篇关于PostgreSQL 从 pg_catalog 表中撤销权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！