psql:连接到数据库后如何切换到不同的角色?
是否可以更改用户在初始连接后与 postgres 交互时使用的 postgresql 角色?
数据库将在 Web 应用程序中使用,我想在具有连接池的表和模式上采用数据库级别规则。通过阅读 postgresql 文档,如果我最初以具有超级用户角色的用户身份进行连接,我似乎可以切换角色,但我更愿意最初以具有最小权限的用户身份进行连接,并根据需要进行切换。切换时必须指定用户密码就可以了(事实上我更喜欢它)。
我缺少什么?
更新:我已经按照@Milen的建议尝试了SET ROLE
和SET SESSION AUTHORIZATION
,但是如果用户不这样做,这两个命令似乎都不起作用超级用户:
$ psql -U test
psql (8.4.4)
Type "help" for help.
test=> \du test
List of roles
Role name | Attributes | Member of
-----------+------------+----------------
test | | {connect_only}
test=> \du test2
List of roles
Role name | Attributes | Member of
-----------+------------+----------------
test2 | | {connect_only}
test=> set role test2;
ERROR: permission denied to set role "test2"
test=> \q
Is it possible to change the postgresql role a user is using when interacting with postgres after the initial connection?
The database(s) will be used in a web application and I'd like to employ database level rules on tables and schemas with connection pooling. From reading the postgresql documentation it appears I can switch roles if I originally connect as a user with the superuser role, but I would prefer to initially connect as a user with minimal permissions and switch as necessary. Having to specify the user's password when switching would be fine (in fact I'd prefer it).
What am I missing?
Update: I've tried both SET ROLE
and SET SESSION AUTHORIZATION
as suggested by @Milen however neither command seems to work if the user is not a superuser:
$ psql -U test
psql (8.4.4)
Type "help" for help.
test=> \du test
List of roles
Role name | Attributes | Member of
-----------+------------+----------------
test | | {connect_only}
test=> \du test2
List of roles
Role name | Attributes | Member of
-----------+------------+----------------
test2 | | {connect_only}
test=> set role test2;
ERROR: permission denied to set role "test2"
test=> \q
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
如果有人仍然需要它(就像我一样)。
我们需要使当前会话用户成为角色的成员:
产生:
If someone still needs it (like I do).
We need to make the current session user a member of the role:
produces:
看一下 "SET ROLE" 和 "设置会话授权"。
Take a look at "SET ROLE" and "SET SESSION AUTHORIZATION".
webgui
现在位于neil
组中,因此webgui
可以调用set role neil
。然而,webgui
并没有继承neil
的权限。稍后,以 webgui 身份登录:
webgui
不需要superuser
权限。您希望在数据库会话开始时
设置角色
并在会话结束时重置它。在 Web 应用程序中,这分别对应于从数据库连接池获取连接并释放它。下面是一个使用 Tomcat 的连接池和 Spring Security 的示例:webgui
is now in theneil
group, sowebgui
can callset role neil
. However,webgui
did not inheritneil
's permissions.Later, login as webgui:
webgui
does not needsuperuser
permission for this.You want to
set role
at the beginning of a database session and reset it at the end of the session. In a web app, this corresponds to getting a connection from your database connection pool and releasing it, respectively. Here's an example using Tomcat's connection pool and Spring Security: