postgresql 撤消角色对 postgres 数据库的访问权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36232458/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Revoke access to postgres database for a role
提问by sibert
I have created a separate role "newrole" and new schema "newschema" for a certain user that should only execute some stored functions. I have managed to revoke access to schema "public" for the current database.
我为某个用户创建了一个单独的角色“newrole”和新模式“newschema”，这些用户应该只执行一些存储的功能。我已设法撤销对当前数据库的架构“public”的访问权限。
Logged in as "newrole" I still have access to postgres database like this:
以“newrole”身份登录我仍然可以像这样访问 postgres 数据库：
SELECT * FROM pg_user
I want to revoke all access to the postgres database and tried following that not work:
我想撤销对 postgres 数据库的所有访问权限并尝试执行以下操作：
REVOKE ALL ON DATABASE postgres FROM newrole
When logged in as newrole I can still read the postgres database.
当以 newrole 身份登录时，我仍然可以读取 postgres 数据库。
How do I revoke any access to the postgres admin database?
如何撤销对 postgres 管理数据库的任何访问权限？
I have searched a long time but not found anything regarding access to the postgres admin database.
我已经搜索了很长时间，但没有找到任何有关访问 postgres 管理数据库的信息。
TIA,
TIA,
回答by Patrick
This issue has nothing to do with database postgres. Instead, you want to manipulate the catalogof the current database. Every database has a catalog of information on all objects in schema pg_catalog, and in standards-compliant form in schema information_schema, so you should restrict access to those for the role in question and also for the publicrole because every role is also member of that role:
这个问题与数据库无关postgres。相反，您想要操作当前数据库的目录。每个数据库都有一个关于 schema 中所有对象的信息目录pg_catalog，并且在 schema中以符合标准的形式存在information_schema，因此您应该限制对相关角色和角色的访问，public因为每个角色也是该角色的成员：
REVOKE ALL PRIVILEGES ON SCHEMA pg_catalog FROM newrole;
REVOKE ALL PRIVILEGES ON SCHEMA pg_catalog FROM public;
REVOKE ALL PRIVILEGES ON SCHEMA information_schema FROM newrole;
REVOKE ALL PRIVILEGES ON SCHEMA information_schema FROM public;
However, the system does not always honour this accross-the-board restriction, the catalogs are there for a reason and provide important functions in the database. Particularly functions may still execute.
然而，系统并不总是遵守这种全面的限制，目录存在是有原因的，并在数据库中提供重要的功能。特定的功能可能仍会执行。
In general, you do not want to fiddle with the catalogs unless you really know what you are doing.
一般来说，除非您真的知道自己在做什么，否则您不想摆弄目录。
回答by scottjustin5000
you should be able to run this:
你应该能够运行这个：
select * FROM information_schema.table_privileges where grantee = 'newrole';
to display all the privileges for newrole. With that information you should be able to explicitly revoke everything other than access to 'newschema'
显示 newrole 的所有权限。有了这些信息，您应该能够明确撤销除访问“newschema”之外的所有内容
