postgresql (设置角色用户)命令如何在SSM项目中使用？

Question

现在项目使用spring + mybatis + druid + postgresql项目中的用户对应于数据库中的用户，所以每次运行springmvc+时，您都要使用(set role user)命令切换用户，然后执行数据库的crud操作。

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

步骤1，从spring security (或shiro)获取用户名。

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

第三步，执行SQL (设置角色用户)进行角色切换。

第四步，执行crud操作。

步骤5，重置数据库连接(重置角色)

Answer 1

这里有一种简单的方法，可以在mybatis-spring的帮助下执行所需的操作。

除非您已经使用过mybatis-spring，否则第一步是更改项目的配置，这样您就可以使用mybatis-spring提供的org.mybatis.spring.SqlSessionFactoryBean获取SqlSessionFactory。

下一步是为连接设置/重置用户角色的实现。在mybatis中，连接生命周期由实现org.apache.ibatis.transaction.Transaction接口的类控制。查询执行器使用该类的实例来获取连接。

简而言之，您需要创建这个类的自己的实现并配置mybatis来使用它。

您的实现可以基于来自mybatis-spring的SpringManagedTransaction，如下所示：

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实现。为此，您需要实现与mybatis-spring提供的org.mybatis.spring.transaction.SpringManagedTransactionFactory类似的TransactionFactory

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) {
  }
}

然后在spring上下文中定义UserRoleAwareSpringManagedTransactionFactory类型的bean，并将其注入到spring上下文中SqlSessionFactoryBeen的transactionFactory属性中。

现在，每当mybatis获得一个Connection时，Transaction的实现都会设置当前spring安全用户来设置角色。

Answer 2

最佳实践是数据库用户就是应用程序。应用程序用户对特定数据/资源的访问应在应用程序中进行控制。应用程序不应依赖数据库来限制数据/资源访问。因此，应用程序用户在数据库中不应具有不同的角色。一个应用程序应该只使用一个数据库用户帐户。

Spring是最佳实践的体现。因此，Spring不实现此功能。如果你想要这样的功能，你需要修改。

关于this，你最好的选择是：

@Autowired JdbcTemplate jdbcTemplate; 

// ...

public runPerUserSql() {

    jdbcTemplate.execute("set role user 'user_1';");
    jdbcTemplate.execute("SELECT 1;");

}

我对此仍然没有太大的信心。除非您正在为多个用户编写pgAdmin you应用程序，否则您应该重新考虑您的方法和设计。