postgresql(set role user)命令在SSM项目中如何使用？

Question

现在项目使用的是 springmvc+ spring + mybatis + druid + postgresql 项目中的用户与数据库中的用户一一对应，所以每次运行SQL时都要通过(set role user)命令切换用户，然后进行增删改查数据库的操作。

我的问题：因为连接池中有很多连接，所以第一步就是获取数据库的连接，然后切换用户，然后对数据库进行业务SQL的操作。但我不知道这个逻辑应该由项目的哪一部分来处理，因为连接池的连接和SQL的执行都是由底层代码实现的。你有什么好的计划吗？能否给我一个完整的demo，比如如下操作：

步骤1，从spring security（或shiro）获取用户名。

步骤2、从连接池中获取当前使用数据库的连接。

步骤3、执行SQL（设置角色用户）进行角色切换。

步骤4、进行增删改查操作。

第五步、重置数据库连接（重置角色）

Answer 1

这是在mybatis-spring的帮助下完成您需要的操作的简单方法。除非您已经使用 mybatis-spring，否则第一步是更改项目的配置，以便您使用SqlSessionFactorymybatis org.mybatis.spring.SqlSessionFactoryBean-spring 提供的功能。下一步是设置/重置连接的用户角色。在mybatis中，连接生命周期由实现接口的类控制org.apache.ibatis.transaction.Transaction。查询执行器使用此类的实例来获取连接。简而言之，您需要创建自己的此类实现并配置 mybatis 来使用它。您的实现可以基于SpringManagedTransactionmybatis-spring ，并且看起来像：import org.springframework.security.core.Authentication;class UserRoleAwareSpringManagedTransaction extends SpringManagedTransaction {  public UserRoleAwareSpringManagedTransaction(DataSource dataSource) {    super(dataSource);  }  @Override  public Connection getConnection() throws SQLException {    Connection connection = getCurrentConnection();    setUserRole(connection);    return connection;  }  private Connection getCurrentConnection() {    return super.getConnection();  }  @Override  public void close() throws SQLException {    resetUserRole(getCurrentConnection());    super.close();  }    private void setUserRole(Connection connection) {    Authentication authentication = SecurityContextHolder.getContext().getAuthentication();    String username = authentication.getName();    Statement statement = connection.createStatement();    try {      // note that this direct usage of usernmae is a subject for SQL injection      // so you need to use the suggestion from      // https://stackoverflow.com/questions/2998597/switch-role-after-connecting-to-database      // about encoding of the username      statement.execute("set role '" + username + "'");    } finally {      statement.close();    }  }  private void resetUserRole(Connection connection) {    Statement statement = connection.createStatement();    try {      statement.execute("reset role");    } finally {      statement.close();    }  }}现在您需要配置 mybatis 来使用您的Transaction实现。为此，您需要实现TransactionFactory类似于mybatis-springorg.mybatis.spring.transaction.SpringManagedTransactionFactory提供的功能：public class UserRoleAwareSpringManagedTransactionFactory implements TransactionFactory {  @Override  public Transaction newTransaction(DataSource dataSource, TransactionIsolationLevel level, boolean autoCommit) {    return new UserRoleAwareSpringManagedTransaction(dataSource);  }  @Override  public Transaction newTransaction(Connection conn) {    throw new UnsupportedOperationException("New Spring transactions require a DataSource");  }  @Override  public void setProperties(Properties props) {  }}然后UserRoleAwareSpringManagedTransactionFactory在 spring 上下文中定义一个类型的 bean 并将其注入到spring 上下文中transactionFactory的属性中。SqlSessionFactoryBeen现在mybatis每次获取到Connection执行Transaction都会设置当前的spring security用户来设置角色。

Answer 2

最佳实践是数据库用户是应用程序。应用程序用户对特定数据/资源的访问应在应用程序中进行控制。应用程序不应依赖数据库来限制数据/资源访问。因此，应用程序用户不应该在数据库中具有不同的角色。应用程序应仅使用单个数据库用户帐户。Spring是最佳实践的体现。因此Spring并没有实现这个功能。如果你想要这样的功能，你需要破解。您最好的选择是：@Autowired JdbcTemplate jdbcTemplate; // ...public runPerUserSql() {    jdbcTemplate.execute("set role user 'user_1';");    jdbcTemplate.execute("SELECT 1;");}对此我还是没有太大的信心。除非您正在为多个用户编写 pgAdmin Web 应用程序，否则您应该重新考虑您的方法和设计。