连接到数据库后切换角色 [英] Switch role after connecting to database
问题描述
在初始连接后与postgres进行交互时,是否可以更改用户正在使用的postgresql角色?
Is it possible to change the postgresql role a user is using when interacting with postgres after the initial connection?
该数据库将在Web应用程序中使用,我想在具有连接池的表和模式上采用数据库级规则.通过阅读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).
我想念什么?
更新:我已经按照@Milen的建议尝试了SET ROLE
和SET SESSION AUTHORIZATION
,但是如果用户不是超级用户,这两个命令似乎都不起作用:
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
推荐答案
--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.
webgui
现在位于neil
组中,因此webgui
可以调用set role neil
.但是,webgui
不会继承neil
的权限.
webgui
is now in the neil
group, so webgui
can call set role neil
. However, webgui
did not inherit neil
's permissions.
稍后,以webgui登录:
Later, login as webgui:
psql -d some_database -U webgui
(enter s3cr3t as password)
set role neil;
webgui
不需要superuser
权限.
您要在数据库会话开始时set role
并在会话结束时将其重置.在Web应用程序中,这相当于分别从数据库连接池中获取连接并释放它.这是一个使用Tomcat的连接池和Spring Security的示例:
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:
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);
}
}
这篇关于连接到数据库后切换角色的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!