基于Pac4j+Shiro+Keycloak的权限（授权）管理配置咨询

背景 #

正在搭建Shiro与Keycloak的集成环境，已找到Keycloak OIDC对应的shiro.ini配置。此前单独使用Shiro时，基于JDBC Realm管理权限，系统中有permission_role_object等SQL表，配置过以下JDBC查询语句：

jdbcRealm.userRolesQuery = ...
jdbcRealm.permissionsQuery = ...

问题 #

如何让用于Keycloak认证的pac4jRealm关联到现有的SQL权限表？我猜测是否可以直接在shiro.ini中添加：

pac4jRealm.userRolesQuery = ...
pac4jRealm.permissionsQuery = ...

当前shiro.ini配置如下：

[main]
#### Session
sessionIdCookie=org.apache.shiro.web.servlet.SimpleCookie
#sessionIdCookie.path = /
sessionIdCookie.httpOnly = true
sessionIdCookie.name = sid
sessionIdCookie.domain = localhost
sessionIdCookie.maxAge=28800000
sessionIdCookie.secure = true
sessionIdCookie.sameSite = LAX

sessionManager=org.apache.shiro.web.session.mgt.DefaultWebSessionManager
sessionManager.sessionIdCookie=$sessionIdCookie
sessionManager.sessionIdCookieEnabled=true
securityManager.sessionManager=$sessionManager
# Session Timeout nach 8 Stunden
sessionManager.globalSessionTimeout= 28800000

sessionListener1= de.dpunkt.myaktion.util.MySessionListener1
sessionManager.sessionListeners=$sessionListener1

# Session validation = 5 minutes
sessionManager.sessionValidationInterval = 300000


#sessionManager = org.apache.shiro.web.session.mgt.DefaultWebSessionManager
#securityManager.sessionMode=native


sessionValidationScheduler=org.apache.shiro.session.mgt.ExecutorServiceSessionValidationScheduler
sessionValidationScheduler.interval = 60000
sessionValidationScheduler.sessionManager=$sessionManager
sessionManager.sessionValidationScheduler=$sessionValidationScheduler
sessionManager.deleteInvalidSessions=true
#sessionFactory=org.apache.shiro.session.mgt.OnlineSessionFactory
#sessionManager.sessionFactory=$sessionFactory
#securityManager.subjectDAO.sessionStorageEvaluator.sessionStorageEnabled = false

#Keycloack
authcStrategy = org.apache.shiro.authc.pam.AtLeastOneSuccessfulStrategy

authenticator = org.apache.shiro.authc.pam.ModularRealmAuthenticator
securityManager.authenticator = $authenticator
securityManager.authenticator.authenticationStrategy = $authcStrategy
securityManager.realms = $jdbcRealm, $tcRealm, $pac4jRealm

# Caching
cacheManager = org.apache.shiro.cache.MemoryConstrainedCacheManager
securityManager.cacheManager = $cacheManager

# Using default form based security filter org.apache.shiro.web.filter.authc.FormAuthenticationFilter
authc = org.apache.shiro.web.filter.authc.FormAuthenticationFilter
authc.loginUrl = /common/login.jsf
authc.successUrl = /portal/dashboard.jsf


# Redirect to an access denied page if user does not have access rights
#[roles]
#roles.unauthorizedUrl = /common/access-denied.jsf
#perms.unauthorizedUrl = /accessdenied.jsp

anyofpermission = com.myapp.util.CustomPermissionsAuthorizationFilter

# Protected URLs
[urls]
/service = noSessionCreation, anon
    
## OTHER
/WEB-INF/layout/portal/** = authc
/portal/** = authc
/admin/** = authc
/community/** = authc

解决方案 #

直接给pac4jRealm配置userRolesQuery和permissionsQuery是行不通的——因为pac4jRealm核心负责OIDC认证流程，并没有内置JDBC权限查询的逻辑。要关联SQL权限表，需要扩展pac4jRealm并自定义权限加载逻辑：

1. 自定义Pac4jRealm子类

创建自定义Realm，重写权限获取方法，通过JDBC查询加载SQL中的角色和权限：

public class CustomPac4jRealm extends Pac4jRealm {
    private JdbcTemplate jdbcTemplate;
    private String userRolesQuery;
    private String permissionsQuery;

    @Override
    protected AuthorizationInfo doGetAuthorizationInfo(PrincipalCollection principals) {
        // 获取Keycloak认证后的用户唯一标识（根据实际返回值调整，比如username/email）
        String userIdentifier = getAvailablePrincipal(principals).toString();
        SimpleAuthorizationInfo authInfo = new SimpleAuthorizationInfo();

        // 查询用户角色
        List<String> roles = jdbcTemplate.queryForList(userRolesQuery, new Object[]{userIdentifier}, String.class);
        authInfo.addRoles(roles);

        // 查询用户权限
        List<String> permissions = jdbcTemplate.queryForList(permissionsQuery, new Object[]{userIdentifier}, String.class);
        authInfo.addStringPermissions(permissions);

        return authInfo;
    }

    // 注入依赖的setter方法
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void setUserRolesQuery(String userRolesQuery) {
        this.userRolesQuery = userRolesQuery;
    }

    public void setPermissionsQuery(String permissionsQuery) {
        this.permissionsQuery = permissionsQuery;
    }
}

2. 在shiro.ini中配置自定义Realm

替换原有pac4jRealm为自定义类，同时配置数据源、JDBC模板以及权限查询语句（可复用原有JDBC Realm的数据源）：

[main]
# 复用原有数据源配置（如果已存在）
dataSource = com.mchange.v2.c3p0.ComboPooledDataSource
dataSource.driverClass = com.mysql.cj.jdbc.Driver
dataSource.jdbcUrl = jdbc:mysql://localhost:3306/your_database
dataSource.user = db_username
dataSource.password = db_password

# 配置JdbcTemplate用于SQL查询
jdbcTemplate = org.springframework.jdbc.core.JdbcTemplate
jdbcTemplate.dataSource = $dataSource

# 替换为自定义Pac4jRealm
pac4jRealm = com.your.package.CustomPac4jRealm
# 注入JdbcTemplate和查询语句
pac4jRealm.jdbcTemplate = $jdbcTemplate
pac4jRealm.userRolesQuery = SELECT role_name FROM permission_role_object WHERE username = ?
pac4jRealm.permissionsQuery = SELECT permission FROM permission_role_object WHERE username = ?
# 补充Keycloak OIDC所需的pac4j配置（比如client、callbackUrl等，根据你之前找到的配置补充）

# 其他原有配置保持不变...
securityManager.realms = $jdbcRealm, $tcRealm, $pac4jRealm

3. 注意事项

• 确保项目引入Spring JDBC相关依赖（如spring-jdbc）
• 确认userIdentifier与SQL表中的用户字段匹配（比如Keycloak返回的是email，则查询条件要改为email = ?）
• 可以启用Shiro缓存来优化权限查询性能，避免每次请求都查数据库

内容的提问来源于stack exchange，提问作者vished2000