仅允许postgres用户列表角色 [英] Allow only postgres user list roles
问题描述
如何禁止非超级用户在postgresql服务器中查看其他用户?
How to forbid non superusers to see other users in postgresql server?
例如。如果当前登录的用户不是超级用户,则
ex. If currently logged in user is not a superuser then the result from
SELECT *的结果来自pg_roles;
SELECT * from pg_roles;
或
\du
\du
只能是具有其角色的行
推荐答案
您可以撤消对系统目录中的身份验证ID表:
You can revoke access to the authentication IDs table in the system catalogs:
REVOKE SELECT ON pg_catalog.pg_authid FROM public;
REVOKE SELECT ON pg_catalog.pg_auth_members FROM public;
请注意,撤消对 pg_roles
的访问不会足够了,因为 pg_roles
只是 pg_authid
的视图,手动运行视图查询或定义新视图很简单使用相同的查询。 information_schema
视图还直接使用 pg_authid
,并且不会因撤消对 pg_roles $的访问而受影响。 c $ c>。如果您已撤消对
pg_authid
的访问权限,则不必撤消对 pg_roles
的访问权限。
Note that revoking access to pg_roles
is not sufficient, as pg_roles
is just a view over pg_authid
and it's trivial to run the view query manually or define a new view with the same query. The information_schema
views also use pg_authid
directly and are unaffected by revoking access to pg_roles
. It is not necessary to revoke access to pg_roles
if you've revoked access to pg_authid
.
请注意,撤消对全局表的访问仍然是每个数据库的操作。
Be aware that revoking access to global tables is still a per-database operation.
撤消对系统目录的访问可能会有副作用,包括:
Revoking access to system catalogs may have side effects, including:
- 某些系统功能无法按预期运行
- 诸如JDBC之类的工具中的某些元数据操作驱动程序失败
- ...等等
,通常不被支持。
这篇关于仅允许postgres用户列表角色的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!