postgresql 连接数据库后切换角色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2998597/
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
Switch role after connecting to database
提问by Chris Gow
Is it possible to change the postgresql role a user is using when interacting with postgres after the initial connection?
在初始连接后与 postgres 交互时，是否可以更改用户正在使用的 postgresql 角色？
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).
数据库将用于 Web 应用程序，我想在带有连接池的表和模式上使用数据库级规则。从阅读 postgresql 文档来看，如果我最初以具有超级用户角色的用户身份连接，我似乎可以切换角色，但我更喜欢最初以具有最小权限的用户身份连接并根据需要进行切换。切换时必须指定用户的密码就可以了（实际上我更喜欢它）。
What am I missing?
我错过了什么？
Update: I've tried both SET ROLEand SET SESSION AUTHORIZATIONas suggested by @Milen however neither command seems to work if the user is not a superuser:
更新：我已经按照@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
回答by Neil McGuigan
--create a user that you want to use the database as:
create role neil;
--create the user for the web server to connect as:
create role webgui noinherit login password 's3cr3t';
--let webgui set role to neil:
grant neil to webgui; --this looks backwards but is correct.
webguiis now in the neilgroup, so webguican call set role neil. However, webguidid not inherit neil's permissions.
webgui现在在neil组中，所以webgui可以打电话set role neil。但是，webgui没有继承neil的权限。
Later, login as webgui:
稍后，以 webgui 身份登录：
psql -d some_database -U webgui
(enter s3cr3t as password)
set role neil;
webguidoes not need superuserpermission for this.
webgui不需要superuser许可。
You want to set roleat 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:
您希望set role在数据库会话开始时重置它并在会话结束时重置它。在 Web 应用程序中，这分别对应于从数据库连接池中获取连接并释放它。下面是一个使用 Tomcat 的连接池和 Spring Security 的示例：
public class SetRoleJdbcInterceptor extends JdbcInterceptor {
@Override
public void reset(ConnectionPool connectionPool, PooledConnection pooledConnection) {
Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
if(authentication != null) {
try {
/*
use OWASP's ESAPI to encode the username to avoid SQL Injection. Can't use parameters with SET ROLE. Need to write PG codec.
Or use a whitelist-map approach
*/
String username = ESAPI.encoder().encodeForSQL(MY_CODEC, authentication.getName());
Statement statement = pooledConnection.getConnection().createStatement();
statement.execute("set role \"" + username + "\"");
statement.close();
} catch(SQLException exp){
throw new RuntimeException(exp);
}
}
}
@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
if("close".equals(method.getName())){
Statement statement = ((Connection)proxy).createStatement();
statement.execute("reset role");
statement.close();
}
return super.invoke(proxy, method, args);
}
}
回答by Milen A. Radev
Take a look at "SET ROLE"and "SET SESSION AUTHORIZATION".
回答by Serhii Romanov
If someone still needs it (like I do).
如果有人仍然需要它（就像我一样）。
The specified role_name must be a role that the current session user is a member of. https://www.postgresql.org/docs/10/sql-set-role.html
指定的 role_name 必须是当前会话用户所属的角色。 https://www.postgresql.org/docs/10/sql-set-role.html
We need to make the current session user a member of the role:
我们需要使当前会话用户成为角色的成员：
create role myrole;
set role myrole;
grant myrole to myuser;
set role myrole;
produces:
产生：
Role ROLE created.
Error starting at line : 4 in command -
set role myrole
Error report -
ERROR: permission denied to set role "myrole"
Grant succeeded.
Role SET succeeded.
