You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@kyuubi.apache.org by ch...@apache.org on 2022/08/22 05:42:31 UTC

[incubator-kyuubi] branch branch-1.6 updated (1e3dc52f1 -> 8b65e0e43)

This is an automated email from the ASF dual-hosted git repository.

chengpan pushed a change to branch branch-1.6
in repository https://gitbox.apache.org/repos/asf/incubator-kyuubi.git


    from 1e3dc52f1 [KYUUBI #3297] [MINOR] Null is replaced by KyuubiSQLException.featureNotSupported()
     new e76f8f7b3 [KYUUBI #3217] [DOCS] Doc for using Marcos in row-level filter in Authz
     new 04f311534 [KYUUBI #3275] [KYUUBI 3269] [DOCS] Doc for JDBC authentication provider
     new 8b65e0e43 [KYUUBI #3222][FOLLOWUP] Fixing placeholder and config of user in JDBC Authentication Provider

The 3 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails.  The revisions
listed as "add" were already present in the repository and have only
been added to this reference.


Summary of changes:
 docs/deployment/settings.md                        |  4 +-
 docs/extensions/server/authentication.rst          |  2 +-
 docs/security/authentication.rst                   |  1 +
 docs/security/authorization/spark/install.md       | 17 ++++++
 docs/security/jdbc.md                              | 49 +++++++++++++++++
 .../org/apache/kyuubi/config/KyuubiConf.scala      | 18 +++----
 .../JdbcAuthenticationProviderImpl.scala           | 63 ++++++++++++----------
 .../scala/org/apache/kyuubi/util/JdbcUtils.scala   |  9 ++++
 .../JdbcAuthenticationProviderImplSuite.scala      | 56 ++++++++++++++-----
 .../org/apache/kyuubi/util/JdbcUtilsSuite.scala    | 13 +++++
 10 files changed, 176 insertions(+), 56 deletions(-)
 create mode 100644 docs/security/jdbc.md


[incubator-kyuubi] 02/03: [KYUUBI #3275] [KYUUBI 3269] [DOCS] Doc for JDBC authentication provider

Posted by ch...@apache.org.
This is an automated email from the ASF dual-hosted git repository.

chengpan pushed a commit to branch branch-1.6
in repository https://gitbox.apache.org/repos/asf/incubator-kyuubi.git

commit 04f311534e61657c80e7a7c0cba18f37cd871cf7
Author: liangbowen <li...@gf.com.cn>
AuthorDate: Fri Aug 19 17:38:05 2022 +0800

    [KYUUBI #3275] [KYUUBI 3269] [DOCS] Doc for JDBC authentication provider
    
    ### _Why are the changes needed?_
    
    fix https://github.com/apache/incubator-kyuubi/issues/3269
    
    Add docs for JDBC auth provider from https://github.com/apache/incubator-kyuubi/issues/3222 shipping within 1.6.0, add jdbc.rst in /docs/security path
    simplify the config docs for kyuubi.authentication.jdbc.query in /docs/deployment/settings.md by moving examples to the docs of jdbc auth provider.
    
    ### _How was this patch tested?_
    - [ ] Add some test cases that check the changes thoroughly including negative and positive cases if possible
    
    - [ ] Add screenshots for manual tests if appropriate
    
    - [x] [Run test](https://kyuubi.apache.org/docs/latest/develop_tools/testing.html#running-tests) locally before make a pull request
    
    Closes #3275 from bowenliang123/doc-jdbc-auth.
    
    Closes #3275
    
    e393f6db [liangbowen] fix config example mistakes
    ab70cc8d [liangbowen] update doc for auth query sql
    ebd5e194 [liangbowen] update doc description
    0b15af59 [liangbowen] typo
    953000fe [liangbowen] typo
    ab944e73 [liangbowen] typo
    e3c3d259 [liangbowen] regenerate conf docs. udpate jdbc.md.
    45429ada [liangbowen] typo
    3b05338e [liangbowen] add docs for jdbc authentication provider
    
    Authored-by: liangbowen <li...@gf.com.cn>
    Signed-off-by: Kent Yao <ya...@apache.org>
---
 docs/deployment/settings.md                        |  2 +-
 docs/security/authentication.rst                   |  1 +
 docs/security/jdbc.md                              | 49 ++++++++++++++++++++++
 .../org/apache/kyuubi/config/KyuubiConf.scala      |  9 +---
 4 files changed, 52 insertions(+), 9 deletions(-)

diff --git a/docs/deployment/settings.md b/docs/deployment/settings.md
index e0a499bdd..e58f15da2 100644
--- a/docs/deployment/settings.md
+++ b/docs/deployment/settings.md
@@ -140,7 +140,7 @@ kyuubi.authentication|NONE|A comma separated list of client authentication types
 kyuubi.authentication.custom.class|&lt;undefined&gt;|User-defined authentication implementation of org.apache.kyuubi.service.authentication.PasswdAuthenticationProvider|string|1.3.0
 kyuubi.authentication.jdbc.driver.class|&lt;undefined&gt;|Driver class name for JDBC Authentication Provider.|string|1.6.0
 kyuubi.authentication.jdbc.password|&lt;undefined&gt;|Database password for JDBC Authentication Provider.|string|1.6.0
-kyuubi.authentication.jdbc.query|&lt;undefined&gt;|Query SQL template with placeholders for JDBC Authentication Provider to execute. Authentication passes if at least one row fetched in the result set.Available placeholders are: <ul><li>`${username}`</li><li>`${password}`</li></ul>eg.: query sql `SELECT 1 FROM auth_table WHERE user=${username} AND passwd=MD5(CONCAT(salt,${password}));` will be prepared as: `SELECT 1 FROM auth_table WHERE user=? AND passwd=MD5(CONCAT(salt,?));` with value [...]
+kyuubi.authentication.jdbc.query|&lt;undefined&gt;|Query SQL template with placeholders for JDBC Authentication Provider to execute. Authentication passes if at least one row fetched in the result set.Available placeholders are `${username}` and `${password}`.|string|1.6.0
 kyuubi.authentication.jdbc.url|&lt;undefined&gt;|JDBC URL for JDBC Authentication Provider.|string|1.6.0
 kyuubi.authentication.jdbc.username|&lt;undefined&gt;|Database username for JDBC Authentication Provider.|string|1.6.0
 kyuubi.authentication.ldap.attrs|mail|Specifies part of the search as an attribute returned by LDAP. For example: mail,name.|seq|1.6.0
diff --git a/docs/security/authentication.rst b/docs/security/authentication.rst
index 515bcf4f5..f16a452c8 100644
--- a/docs/security/authentication.rst
+++ b/docs/security/authentication.rst
@@ -40,6 +40,7 @@ The related configurations can be found at `Authentication Configurations`_
     kerberos
     ../client/advanced/kerberos
     ldap
+    jdbc
     ../extensions/server/authentication
 
 .. _Authentication Configurations: ../deployment/settings.html#authentication
diff --git a/docs/security/jdbc.md b/docs/security/jdbc.md
new file mode 100644
index 000000000..fbf3cd297
--- /dev/null
+++ b/docs/security/jdbc.md
@@ -0,0 +1,49 @@
+<!--
+ - Licensed to the Apache Software Foundation (ASF) under one or more
+ - contributor license agreements.  See the NOTICE file distributed with
+ - this work for additional information regarding copyright ownership.
+ - The ASF licenses this file to You under the Apache License, Version 2.0
+ - (the "License"); you may not use this file except in compliance with
+ - the License.  You may obtain a copy of the License at
+ -
+ -   http://www.apache.org/licenses/LICENSE-2.0
+ -
+ - Unless required by applicable law or agreed to in writing, software
+ - distributed under the License is distributed on an "AS IS" BASIS,
+ - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ - See the License for the specific language governing permissions and
+ - limitations under the License.
+ -->
+
+
+# Configure Kyuubi to Use JDBC Authentication
+
+Kyuubi supports authentication via JDBC query. A query is prepared with username/password value and sent to the database configured in JDBC URL. The authentication passes if the result set is not empty.
+
+The query sql must start with `SELECT`. The SQL statement must start with the SELECT clause. Placeholders are supported and listed below for substitution:
+- `${username}`
+- `${password}`
+
+For example, `SELECT 1 FROM auth_db.auth_table WHERE user=${username} AND 
+passwd=MD5(CONCAT(salt,${password}))` will be prepared as: `SELECT 1 FROM auth_db.auth_table WHERE user=? AND passwd=MD5(CONCAT(salt,?))` with value replacement of `username` and `password` in string type.
+
+## Enable JDBC Authentication 
+
+To enable the jdbc authentication method, we need to
+
+- Put the jdbc driver jar file to `$KYUUBI_HOME/jars` directory to make it visible for
+  the classpath of the kyuubi server.
+- Configure the following properties to `$KYUUBI_HOME/conf/kyuubi-defaults.conf`
+  on each node where kyuubi server is installed.
+
+## Configure the authentication properties
+Configure the following properties to `$KYUUBI_HOME/conf/kyuubi-defaults.conf` on each node where kyuubi server is installed.
+
+```properties
+kyuubi.authentication=JDBC
+kyuubi.authentication.jdbc.driver.class = com.mysql.jdbc.Driver
+kyuubi.authentication.jdbc.url = jdbc:mysql://127.0.0.1:3306/auth_db
+kyuubi.authentication.jdbc.username = bowenliang123
+kyuubi.authentication.jdbc.password = bowenliang123@kyuubi
+kyuubi.authentication.jdbc.query = SELECT 1 FROM auth_table WHERE user=${username} AND passwd=MD5(CONCAT(salt,${password}))
+```
\ No newline at end of file
diff --git a/kyuubi-common/src/main/scala/org/apache/kyuubi/config/KyuubiConf.scala b/kyuubi-common/src/main/scala/org/apache/kyuubi/config/KyuubiConf.scala
index 387efce5e..e6832d06f 100644
--- a/kyuubi-common/src/main/scala/org/apache/kyuubi/config/KyuubiConf.scala
+++ b/kyuubi-common/src/main/scala/org/apache/kyuubi/config/KyuubiConf.scala
@@ -707,14 +707,7 @@ object KyuubiConf {
       .doc("Query SQL template with placeholders " +
         "for JDBC Authentication Provider to execute. " +
         "Authentication passes if at least one row fetched in the result set." +
-        "Available placeholders are: <ul>" +
-        "<li>`${username}`</li>" +
-        "<li>`${password}`</li></ul>" +
-        "eg.: query sql `SELECT 1 FROM auth_table WHERE user=${username} AND " +
-        "passwd=MD5(CONCAT(salt,${password}));` " +
-        "will be prepared as: `SELECT 1 FROM auth_table " +
-        "WHERE user=? AND passwd=MD5(CONCAT(salt,?));`" +
-        " with value replacement of `username` and `password` in string type.")
+        "Available placeholders are `${username}` and `${password}`.")
       .version("1.6.0")
       .stringConf
       .createOptional


[incubator-kyuubi] 03/03: [KYUUBI #3222][FOLLOWUP] Fixing placeholder and config of user in JDBC Authentication Provider

Posted by ch...@apache.org.
This is an automated email from the ASF dual-hosted git repository.

chengpan pushed a commit to branch branch-1.6
in repository https://gitbox.apache.org/repos/asf/incubator-kyuubi.git

commit 8b65e0e435cf8c0ead7b5517e22495f3c97c9c17
Author: liangbowen <li...@gf.com.cn>
AuthorDate: Mon Aug 22 13:36:32 2022 +0800

    [KYUUBI #3222][FOLLOWUP] Fixing placeholder and config of user in JDBC Authentication Provider
    
    ### _Why are the changes needed?_
    
    To fix the config name and placeholder with `username` introduced in https://github.com/apache/incubator-kyuubi/pull/3235 violate this convention as in JDBC driver use `user` keyword used for connection user rather than `username`,
    
    1. change config name from `kyuubi.authentication.jdbc.username` to `kyuubi.authentication.jdbc.user`
    2. change placeholder from `${username}` to `${user}`
    3. update docs and config description related to above changes, and sync the update in jdbc auth docs statement details to config docs.
    4. fix error in throwing AuthenticationException with auth db password. ut added for the fix.
    5. other minor update in docs of custom auth
    
    ### _How was this patch tested?_
    - [x] Add some test cases that check the changes thoroughly including negative and positive cases if possible
    
    - [ ] Add screenshots for manual tests if appropriate
    
    - [x] [Run test](https://kyuubi.apache.org/docs/latest/develop_tools/testing.html#running-tests) locally before make a pull request
    
    Closes #3288 from bowenliang123/jdbc-auth-config-update.
    
    Closes #3222
    
    502703fb [Bowen Liang] skip map for placeholder value lookup
    3733be41 [liangbowen] nit
    ab00525b [liangbowen] nit
    2301c4f0 [liangbowen] fix ut of jdbc auth with wrong_password
    06f0c1bb [liangbowen] remove redundant docs
    ec4565b3 [liangbowen] remove redundant docs
    ae1cce2e [liangbowen] fix compilation error of configLog
    5d14103b [liangbowen] simplify configLog
    6678e657 [liangbowen] reformat
    52c1038e [liangbowen] simplify placeholder checking
    21c2d5ea [liangbowen] check whether placeholders in supported list before conn establishment or authenticate
    7db0adf5 [liangbowen] ut for unknown placeholder
    657de6af [liangbowen] nit
    736b3f22 [liangbowen] refactoring placeholder value lookup, for preventing setString multiple times with "i+1"
    86c89125 [liangbowen] setMaxRows after prepare placeholder, to postpone operation on jdbc conn
    115fae50 [liangbowen] increase test code coverage
    b45b28cb [liangbowen] resultSet returned by executeQuery is never null
    e1c07274 [liangbowen] update ut for redactPassword in JdbcUtils
    b4a52e29 [liangbowen] fix typo in docs of custom auth
    371c2c6e [liangbowen] move redactPassword method to JdbcUtils and add ut.
    a4973c59 [liangbowen] reformat code
    486e1503 [liangbowen] fix error in throwing AuthenticationException with auth db password. add ut for the fix.
    efced90a [liangbowen] update settings.md
    ef97e354 [liangbowen] add SELECT prefix hint for doc of  kyuubi.authentication.jdbc.query
    025f94c7 [liangbowen] fix username to user in JdbcAuthenticationProviderImpl by 1. use config name `kyuubi.authentication.jdbc.user`, 2. use ${user} placeholder instead of ${username}
    
    Lead-authored-by: liangbowen <li...@gf.com.cn>
    Co-authored-by: Bowen Liang <li...@gf.com.cn>
    Signed-off-by: Cheng Pan <ch...@apache.org>
---
 docs/deployment/settings.md                        |  4 +-
 docs/extensions/server/authentication.rst          |  2 +-
 docs/security/jdbc.md                              | 14 ++---
 .../org/apache/kyuubi/config/KyuubiConf.scala      | 11 ++--
 .../JdbcAuthenticationProviderImpl.scala           | 63 ++++++++++++----------
 .../scala/org/apache/kyuubi/util/JdbcUtils.scala   |  9 ++++
 .../JdbcAuthenticationProviderImplSuite.scala      | 56 ++++++++++++++-----
 .../org/apache/kyuubi/util/JdbcUtilsSuite.scala    | 13 +++++
 8 files changed, 116 insertions(+), 56 deletions(-)

diff --git a/docs/deployment/settings.md b/docs/deployment/settings.md
index e58f15da2..e7be40852 100644
--- a/docs/deployment/settings.md
+++ b/docs/deployment/settings.md
@@ -140,9 +140,9 @@ kyuubi.authentication|NONE|A comma separated list of client authentication types
 kyuubi.authentication.custom.class|&lt;undefined&gt;|User-defined authentication implementation of org.apache.kyuubi.service.authentication.PasswdAuthenticationProvider|string|1.3.0
 kyuubi.authentication.jdbc.driver.class|&lt;undefined&gt;|Driver class name for JDBC Authentication Provider.|string|1.6.0
 kyuubi.authentication.jdbc.password|&lt;undefined&gt;|Database password for JDBC Authentication Provider.|string|1.6.0
-kyuubi.authentication.jdbc.query|&lt;undefined&gt;|Query SQL template with placeholders for JDBC Authentication Provider to execute. Authentication passes if at least one row fetched in the result set.Available placeholders are `${username}` and `${password}`.|string|1.6.0
+kyuubi.authentication.jdbc.query|&lt;undefined&gt;|Query SQL template with placeholders for JDBC Authentication Provider to execute. Authentication passes if the result set is not empty.The SQL statement must start with the `SELECT` clause. Available placeholders are `${user}` and `${password}`.|string|1.6.0
 kyuubi.authentication.jdbc.url|&lt;undefined&gt;|JDBC URL for JDBC Authentication Provider.|string|1.6.0
-kyuubi.authentication.jdbc.username|&lt;undefined&gt;|Database username for JDBC Authentication Provider.|string|1.6.0
+kyuubi.authentication.jdbc.user|&lt;undefined&gt;|Database user for JDBC Authentication Provider.|string|1.6.0
 kyuubi.authentication.ldap.attrs|mail|Specifies part of the search as an attribute returned by LDAP. For example: mail,name.|seq|1.6.0
 kyuubi.authentication.ldap.base.dn|&lt;undefined&gt;|LDAP base DN.|string|1.0.0
 kyuubi.authentication.ldap.binddn|&lt;undefined&gt;|The user with which to bind to the LDAP server, and search for the full domain name of the user being authenticated. For example: uid=admin,cn=Directory Manager,ou=users,dc=example,dc=com|string|1.6.0
diff --git a/docs/extensions/server/authentication.rst b/docs/extensions/server/authentication.rst
index f153b4158..ab238040c 100644
--- a/docs/extensions/server/authentication.rst
+++ b/docs/extensions/server/authentication.rst
@@ -66,7 +66,7 @@ Enable Custom Authentication
 
 To enable the custom authentication method, we need to
 
-- put the jar package to ``$KYUUBI_HOME/jars`` directory to make it visible for
+- Put the jar package to ``$KYUUBI_HOME/jars`` directory to make it visible for
   the classpath of the kyuubi server.
 - Configure the following properties to ``$KYUUBI_HOME/conf/kyuubi-defaults.conf``
   on each node where kyuubi server is installed.
diff --git a/docs/security/jdbc.md b/docs/security/jdbc.md
index fbf3cd297..1f5158301 100644
--- a/docs/security/jdbc.md
+++ b/docs/security/jdbc.md
@@ -18,14 +18,14 @@
 
 # Configure Kyuubi to Use JDBC Authentication
 
-Kyuubi supports authentication via JDBC query. A query is prepared with username/password value and sent to the database configured in JDBC URL. The authentication passes if the result set is not empty.
+Kyuubi supports authentication via JDBC query. A query is prepared with user/password value and sent to the database configured in JDBC URL. Authentication passes if the result set is not empty.
 
-The query sql must start with `SELECT`. The SQL statement must start with the SELECT clause. Placeholders are supported and listed below for substitution:
-- `${username}`
+The SQL statement must start with the `SELECT` clause. Placeholders are supported and listed below for substitution:
+- `${user}`
 - `${password}`
 
-For example, `SELECT 1 FROM auth_db.auth_table WHERE user=${username} AND 
-passwd=MD5(CONCAT(salt,${password}))` will be prepared as: `SELECT 1 FROM auth_db.auth_table WHERE user=? AND passwd=MD5(CONCAT(salt,?))` with value replacement of `username` and `password` in string type.
+For example, `SELECT 1 FROM auth_db.auth_table WHERE user=${user} AND 
+passwd=MD5(CONCAT(salt,${password}))` will be prepared as `SELECT 1 FROM auth_db.auth_table WHERE user=? AND passwd=MD5(CONCAT(salt,?))` with value replacement of `user` and `password` in string type.
 
 ## Enable JDBC Authentication 
 
@@ -43,7 +43,7 @@ Configure the following properties to `$KYUUBI_HOME/conf/kyuubi-defaults.conf` o
 kyuubi.authentication=JDBC
 kyuubi.authentication.jdbc.driver.class = com.mysql.jdbc.Driver
 kyuubi.authentication.jdbc.url = jdbc:mysql://127.0.0.1:3306/auth_db
-kyuubi.authentication.jdbc.username = bowenliang123
+kyuubi.authentication.jdbc.user = bowenliang123
 kyuubi.authentication.jdbc.password = bowenliang123@kyuubi
-kyuubi.authentication.jdbc.query = SELECT 1 FROM auth_table WHERE user=${username} AND passwd=MD5(CONCAT(salt,${password}))
+kyuubi.authentication.jdbc.query = SELECT 1 FROM auth_table WHERE user=${user} AND passwd=MD5(CONCAT(salt,${password}))
 ```
\ No newline at end of file
diff --git a/kyuubi-common/src/main/scala/org/apache/kyuubi/config/KyuubiConf.scala b/kyuubi-common/src/main/scala/org/apache/kyuubi/config/KyuubiConf.scala
index e6832d06f..7a1538ade 100644
--- a/kyuubi-common/src/main/scala/org/apache/kyuubi/config/KyuubiConf.scala
+++ b/kyuubi-common/src/main/scala/org/apache/kyuubi/config/KyuubiConf.scala
@@ -688,9 +688,9 @@ object KyuubiConf {
       .stringConf
       .createOptional
 
-  val AUTHENTICATION_JDBC_USERNAME: OptionalConfigEntry[String] =
-    buildConf("kyuubi.authentication.jdbc.username")
-      .doc("Database username for JDBC Authentication Provider.")
+  val AUTHENTICATION_JDBC_USER: OptionalConfigEntry[String] =
+    buildConf("kyuubi.authentication.jdbc.user")
+      .doc("Database user for JDBC Authentication Provider.")
       .version("1.6.0")
       .stringConf
       .createOptional
@@ -706,8 +706,9 @@ object KyuubiConf {
     buildConf("kyuubi.authentication.jdbc.query")
       .doc("Query SQL template with placeholders " +
         "for JDBC Authentication Provider to execute. " +
-        "Authentication passes if at least one row fetched in the result set." +
-        "Available placeholders are `${username}` and `${password}`.")
+        "Authentication passes if the result set is not empty." +
+        "The SQL statement must start with the `SELECT` clause. " +
+        "Available placeholders are `${user}` and `${password}`.")
       .version("1.6.0")
       .stringConf
       .createOptional
diff --git a/kyuubi-common/src/main/scala/org/apache/kyuubi/service/authentication/JdbcAuthenticationProviderImpl.scala b/kyuubi-common/src/main/scala/org/apache/kyuubi/service/authentication/JdbcAuthenticationProviderImpl.scala
index ffd6911a5..e2b1ba476 100644
--- a/kyuubi-common/src/main/scala/org/apache/kyuubi/service/authentication/JdbcAuthenticationProviderImpl.scala
+++ b/kyuubi-common/src/main/scala/org/apache/kyuubi/service/authentication/JdbcAuthenticationProviderImpl.scala
@@ -33,28 +33,26 @@ class JdbcAuthenticationProviderImpl(conf: KyuubiConf) extends PasswdAuthenticat
   with Logging {
 
   private val SQL_PLACEHOLDER_REGEX = """\$\{.+?}""".r
-  private val USERNAME_SQL_PLACEHOLDER = "${username}"
+  private val USER_SQL_PLACEHOLDER = "${user}"
   private val PASSWORD_SQL_PLACEHOLDER = "${password}"
+  private val supportedPlaceholders = Set(
+    USER_SQL_PLACEHOLDER,
+    PASSWORD_SQL_PLACEHOLDER)
 
   private val driverClass = conf.get(AUTHENTICATION_JDBC_DRIVER)
-  private val jdbcUrl = conf.get(AUTHENTICATION_JDBC_URL)
-  private val username = conf.get(AUTHENTICATION_JDBC_USERNAME)
-  private val password = conf.get(AUTHENTICATION_JDBC_PASSWORD)
+  private val authDbJdbcUrl = conf.get(AUTHENTICATION_JDBC_URL)
+  private val authDbUser = conf.get(AUTHENTICATION_JDBC_USER)
+  private val authDbPassword = conf.get(AUTHENTICATION_JDBC_PASSWORD)
   private val authQuery = conf.get(AUTHENTICATION_JDBC_QUERY)
 
-  private val redactedPasswd = password match {
-    case Some(s) if !StringUtils.isBlank(s) => s"${"*" * s.length}(length: ${s.length})"
-    case None => "(empty)"
-  }
-
   checkJdbcConfigs()
 
   implicit private[kyuubi] val ds: DataSource = new DriverDataSource(
-    jdbcUrl.orNull,
+    authDbJdbcUrl.orNull,
     driverClass.orNull,
     new Properties,
-    username.orNull,
-    password.orNull)
+    authDbUser.orNull,
+    authDbPassword.orNull)
 
   /**
    * The authenticate method is called by the Kyuubi Server authentication layer
@@ -75,18 +73,18 @@ class JdbcAuthenticationProviderImpl(conf: KyuubiConf) extends PasswdAuthenticat
 
     try {
       debug(s"prepared auth query: $preparedQuery")
-      JdbcUtils.executeQuery(preparedQuery) { stmt =>
-        stmt.setMaxRows(1) // minimum result size required for authentication
+      JdbcUtils.executeQuery(preparedQuery) { pStmt =>
         queryPlaceholders.zipWithIndex.foreach {
-          case (USERNAME_SQL_PLACEHOLDER, i) => stmt.setString(i + 1, user)
-          case (PASSWORD_SQL_PLACEHOLDER, i) => stmt.setString(i + 1, password)
+          case (USER_SQL_PLACEHOLDER, i) => pStmt.setString(i + 1, user)
+          case (PASSWORD_SQL_PLACEHOLDER, i) => pStmt.setString(i + 1, password)
           case (p, _) => throw new IllegalArgumentException(
               s"Unrecognized placeholder in Query SQL: $p")
         }
+        pStmt.setMaxRows(1) // skipping more result rows to minimize I/O
       } { resultSet =>
-        if (resultSet == null || !resultSet.next()) {
+        if (!resultSet.next()) {
           throw new AuthenticationException("Password does not match or no such user. " +
-            s"user: $user, password: $redactedPasswd")
+            s"user: $user, password: ${JdbcUtils.redactPassword(Some(password))}")
         }
       }
     } catch {
@@ -98,19 +96,20 @@ class JdbcAuthenticationProviderImpl(conf: KyuubiConf) extends PasswdAuthenticat
   }
 
   private def checkJdbcConfigs(): Unit = {
-    def configLog(config: String, value: String): String = s"JDBCAuthConfig: $config = '$value'"
+    val configLog = (config: String, value: Option[String]) =>
+      s"JDBCAuthConfig: $config = '${value.orNull}'"
 
-    debug(configLog("Driver Class", driverClass.orNull))
-    debug(configLog("JDBC URL", jdbcUrl.orNull))
-    debug(configLog("Database username", username.orNull))
-    debug(configLog("Database password", redactedPasswd))
-    debug(configLog("Query SQL", authQuery.orNull))
+    debug(configLog("Driver Class", driverClass))
+    debug(configLog("JDBC URL", authDbJdbcUrl))
+    debug(configLog("Database user", authDbUser))
+    debug(configLog("Database password", Some(JdbcUtils.redactPassword(authDbPassword))))
+    debug(configLog("Query SQL", authQuery))
 
     // Check if JDBC parameters valid
     require(driverClass.nonEmpty, "JDBC driver class is not configured.")
-    require(jdbcUrl.nonEmpty, "JDBC url is not configured.")
-    require(username.nonEmpty, "JDBC username is not configured")
-    // allow empty password
+    require(authDbJdbcUrl.nonEmpty, "JDBC url is not configured.")
+    require(authDbUser.nonEmpty, "JDBC user is not configured.")
+    // allow empty auth db password
     require(authQuery.nonEmpty, "Query SQL is not configured")
 
     val query = authQuery.get.trim.toLowerCase
@@ -119,12 +118,18 @@ class JdbcAuthenticationProviderImpl(conf: KyuubiConf) extends PasswdAuthenticat
     if (!query.contains("where")) {
       warn("Query SQL does not contains 'WHERE' keyword")
     }
-    if (!query.contains(USERNAME_SQL_PLACEHOLDER)) {
-      warn(s"Query SQL does not contains '$USERNAME_SQL_PLACEHOLDER' placeholder")
+    if (!query.contains(USER_SQL_PLACEHOLDER)) {
+      warn(s"Query SQL does not contains '$USER_SQL_PLACEHOLDER' placeholder")
     }
     if (!query.contains(PASSWORD_SQL_PLACEHOLDER)) {
       warn(s"Query SQL does not contains '$PASSWORD_SQL_PLACEHOLDER' placeholder")
     }
+
+    queryPlaceholders.foreach { placeholder =>
+      require(
+        supportedPlaceholders.contains(placeholder),
+        s"Unsupported placeholder in Query SQL: $placeholder")
+    }
   }
 
   private def preparedQuery: String =
diff --git a/kyuubi-common/src/main/scala/org/apache/kyuubi/util/JdbcUtils.scala b/kyuubi-common/src/main/scala/org/apache/kyuubi/util/JdbcUtils.scala
index 29ce98247..df72ee339 100644
--- a/kyuubi-common/src/main/scala/org/apache/kyuubi/util/JdbcUtils.scala
+++ b/kyuubi-common/src/main/scala/org/apache/kyuubi/util/JdbcUtils.scala
@@ -22,6 +22,8 @@ import javax.sql.DataSource
 
 import scala.util.control.NonFatal
 
+import org.apache.commons.lang3.StringUtils
+
 import org.apache.kyuubi.Logging
 
 object JdbcUtils extends Logging {
@@ -95,4 +97,11 @@ object JdbcUtils extends Logging {
       }
     }
   }
+
+  def redactPassword(password: Option[String]): String = {
+    password match {
+      case Some(s) if StringUtils.isNotBlank(s) => s"${"*" * s.length}(length:${s.length})"
+      case _ => "(empty)"
+    }
+  }
 }
diff --git a/kyuubi-common/src/test/scala/org/apache/kyuubi/service/authentication/JdbcAuthenticationProviderImplSuite.scala b/kyuubi-common/src/test/scala/org/apache/kyuubi/service/authentication/JdbcAuthenticationProviderImplSuite.scala
index f35d4f6b0..7c45ed50e 100644
--- a/kyuubi-common/src/test/scala/org/apache/kyuubi/service/authentication/JdbcAuthenticationProviderImplSuite.scala
+++ b/kyuubi-common/src/test/scala/org/apache/kyuubi/service/authentication/JdbcAuthenticationProviderImplSuite.scala
@@ -30,30 +30,31 @@ import org.apache.kyuubi.config.KyuubiConf._
 import org.apache.kyuubi.util.JdbcUtils
 
 class JdbcAuthenticationProviderImplSuite extends KyuubiFunSuite {
-  protected val dbUser: String = "liangbowen"
-  protected val dbPasswd: String = "liangbowen"
+  protected val dbUser: String = "bowenliang123"
+  protected val dbPasswd: String = "bowenliang123@kyuubi"
   protected val authDbName: String = "auth_db"
   protected val dbUrl: String = s"jdbc:derby:memory:$authDbName"
   protected val jdbcUrl: String = s"$dbUrl;create=true"
+  private val authDbDriverClz = "org.apache.derby.jdbc.AutoloadedDriver"
 
   implicit private val ds: DataSource = new DriverDataSource(
     jdbcUrl,
-    "org.apache.derby.jdbc.AutoloadedDriver",
+    authDbDriverClz,
     new Properties,
     dbUser,
     dbPasswd)
 
-  protected val authUser: String = "liangtiancheng"
-  protected val authPasswd: String = "liangtiancheng"
+  protected val authUser: String = "kyuubiuser"
+  protected val authPasswd: String = "kyuubiuuserpassword"
 
   protected val conf: KyuubiConf = new KyuubiConf()
-    .set(AUTHENTICATION_JDBC_DRIVER, "org.apache.derby.jdbc.AutoloadedDriver")
+    .set(AUTHENTICATION_JDBC_DRIVER, authDbDriverClz)
     .set(AUTHENTICATION_JDBC_URL, jdbcUrl)
-    .set(AUTHENTICATION_JDBC_USERNAME, dbUser)
+    .set(AUTHENTICATION_JDBC_USER, dbUser)
     .set(AUTHENTICATION_JDBC_PASSWORD, dbPasswd)
     .set(
       AUTHENTICATION_JDBC_QUERY,
-      "SELECT 1 FROM user_auth WHERE username=${username} and passwd=${password}")
+      "SELECT 1 FROM user_auth WHERE username=${user} and passwd=${password}")
 
   override def beforeAll(): Unit = {
     // init db
@@ -89,10 +90,14 @@ class JdbcAuthenticationProviderImplSuite extends KyuubiFunSuite {
     }
     assert(e1.getMessage.contains("user is null"))
 
+    val wrong_password = "wrong_password"
     val e4 = intercept[AuthenticationException] {
-      providerImpl.authenticate(authUser, "wrong_password")
+      providerImpl.authenticate(authUser, wrong_password)
     }
     assert(e4.isInstanceOf[AuthenticationException])
+    assert(e4.getMessage.contains(s"Password does not match or no such user. " +
+      s"user: $authUser, " +
+      s"password: ${"*" * wrong_password.length}(length:${wrong_password.length})"))
 
     var _conf = conf.clone
     _conf.unset(AUTHENTICATION_JDBC_URL)
@@ -100,9 +105,9 @@ class JdbcAuthenticationProviderImplSuite extends KyuubiFunSuite {
     assert(e5.getMessage.contains("JDBC url is not configured"))
 
     _conf = conf.clone
-    _conf.unset(AUTHENTICATION_JDBC_USERNAME)
+    _conf.unset(AUTHENTICATION_JDBC_USER)
     val e6 = intercept[IllegalArgumentException] { new JdbcAuthenticationProviderImpl(_conf) }
-    assert(e6.getMessage.contains("JDBC username is not configured"))
+    assert(e6.getMessage.contains("JDBC user is not configured"))
 
     _conf = conf.clone
     _conf.unset(AUTHENTICATION_JDBC_QUERY)
@@ -111,12 +116,39 @@ class JdbcAuthenticationProviderImplSuite extends KyuubiFunSuite {
 
     _conf.set(
       AUTHENTICATION_JDBC_QUERY,
-      "INSERT INTO user_auth (username, password) VALUES ('demouser','demopassword');")
+      "INSERT INTO user_auth (user, password) VALUES ('demouser','demopassword');")
     val e9 = intercept[IllegalArgumentException] { new JdbcAuthenticationProviderImpl(_conf) }
     assert(e9.getMessage.contains("Query SQL must start with 'SELECT'"))
 
     _conf.unset(AUTHENTICATION_JDBC_URL)
     val e10 = intercept[IllegalArgumentException] { new JdbcAuthenticationProviderImpl(_conf) }
     assert(e10.getMessage.contains("JDBC url is not configured"))
+
+    _conf = conf.clone
+    _conf.set(AUTHENTICATION_JDBC_QUERY, "SELECT 1 FROM user_auth")
+    new JdbcAuthenticationProviderImpl(_conf)
+
+    _conf.set(AUTHENTICATION_JDBC_QUERY, "SELECT 1 FROM user_auth WHERE passwd=${password}")
+    new JdbcAuthenticationProviderImpl(_conf)
+
+    _conf.set(AUTHENTICATION_JDBC_QUERY, "SELECT 1 FROM user_auth WHERE username=${user}")
+    new JdbcAuthenticationProviderImpl(_conf)
+
+    // unknown placeholder
+    _conf.set(
+      AUTHENTICATION_JDBC_QUERY,
+      "SELECT 1 FROM user_auth WHERE user=${unsupported_placeholder} and username=${user}")
+    val e11 = intercept[IllegalArgumentException] { new JdbcAuthenticationProviderImpl(_conf) }
+    assert(e11.getMessage.contains(
+      "Unsupported placeholder in Query SQL: ${unsupported_placeholder}"))
+
+    // unknown field
+    _conf.set(
+      AUTHENTICATION_JDBC_QUERY,
+      "SELECT 1 FROM user_auth WHERE unknown_column=${user} and passwd=${password}")
+    val e12 = intercept[AuthenticationException] {
+      new JdbcAuthenticationProviderImpl(_conf).authenticate(authUser, authPasswd)
+    }
+    assert(e12.getCause.getMessage.contains("Column 'UNKNOWN_COLUMN' is either not in any table"))
   }
 }
diff --git a/kyuubi-common/src/test/scala/org/apache/kyuubi/util/JdbcUtilsSuite.scala b/kyuubi-common/src/test/scala/org/apache/kyuubi/util/JdbcUtilsSuite.scala
index 1523052af..df1f9aa92 100644
--- a/kyuubi-common/src/test/scala/org/apache/kyuubi/util/JdbcUtilsSuite.scala
+++ b/kyuubi-common/src/test/scala/org/apache/kyuubi/util/JdbcUtilsSuite.scala
@@ -65,5 +65,18 @@ class JdbcUtilsSuite extends KyuubiFunSuite {
       assert(rs.next())
       assert(!rs.next())
     }
+
+    assertResult("****************(length:16)") {
+      JdbcUtils.redactPassword(Some("sample_pass_word"))
+    }
+
+    assertResult("(empty)") {
+      JdbcUtils.redactPassword(Some(""))
+    }
+
+    assertResult("(empty)") {
+      JdbcUtils.redactPassword(None)
+    }
+
   }
 }


[incubator-kyuubi] 01/03: [KYUUBI #3217] [DOCS] Doc for using Marcos in row-level filter in Authz

Posted by ch...@apache.org.
This is an automated email from the ASF dual-hosted git repository.

chengpan pushed a commit to branch branch-1.6
in repository https://gitbox.apache.org/repos/asf/incubator-kyuubi.git

commit e76f8f7b39faeb74ce4c9b39de97652c747d97ba
Author: liangbowen <li...@gf.com.cn>
AuthorDate: Fri Aug 19 09:55:56 2022 +0800

    [KYUUBI #3217] [DOCS] Doc for using Marcos in row-level filter in Authz
    
    ### _Why are the changes needed?_
    
    Support macros in Row-filter condition expression, introduced in Ranger 2.3 ([release notes](https://cwiki.apache.org/confluence/display/RANGER/Apache+Ranger+2.3.0+-+Release+Notes)), is an major feature to significantly simplify the row-filter condition expression in practice by replacing explicit condition query by using user/group's attributes.
    
    - [RANGER-3605](https://issues.apache.org/jira/browse/RANGER-3605) : Support macros in row-filter/condition expressions
    - [RANGER-3550](https://issues.apache.org/jira/browse/RANGER-3550) : support for using user/tag attributes in row-filter expressions and conditions
    Consider user liangtiancheng with attribute born_city = guangzhou, we can define the row filter condition with city='${{USER.born_city}}' with the macro feature.
    
    However, This feature implicit relies on an config named `ranger.plugin.spark.enable.implicit.userstore.enricher` and the default value false will prevent RangerUserStoreEnricher fetching user/group and their attributes. Macros in row-filter condition will fallback to null value (as lack of user attributes value in UserStore of auth context) in script transformation unexpectedly and imperceptibly.
    
    Improving doc of ranger-spark-security.xml to aware of this feature and related config.
    
    ### _How was this patch tested?_
    - [ ] Add some test cases that check the changes thoroughly including negative and positive cases if possible
    
    - [ ] Add screenshots for manual tests if appropriate
    
    - [x] [Run test](https://kyuubi.apache.org/docs/latest/develop_tools/testing.html#running-tests) locally before make a pull request
    
    Closes #3267 from bowenliang123/doc-ranger-macros.
    
    Closes #3217
    
    aee4b201 [liangbowen] plain text
    a7ec3bc9 [liangbowen] update docs and clearify difference between ranger.plugin.hive.policy.cache.dir and ranger.plugin.spark.policy.cache.dir configs
    4887bd1f [liangbowen] simplify with "row filter expressions"
    fa62402f [liangbowen] skip list items
    9dd1cd4c [liangbowen] h5. Using Marcos in Row Level Filters
    849bed5b [liangbowen] add docs for Additional configs for using Marcos in row-level filter
    
    Authored-by: liangbowen <li...@gf.com.cn>
    Signed-off-by: Kent Yao <ya...@apache.org>
---
 docs/security/authorization/spark/install.md | 17 +++++++++++++++++
 1 file changed, 17 insertions(+)

diff --git a/docs/security/authorization/spark/install.md b/docs/security/authorization/spark/install.md
index e04b34680..ec2892230 100644
--- a/docs/security/authorization/spark/install.md
+++ b/docs/security/authorization/spark/install.md
@@ -74,6 +74,23 @@ for pointing to the right Ranger admin server.
 
 </configuration>
 ```
+##### Using Macros in Row Level Filters
+
+Macros are now supported for using user/group/tag in row filter expressions, introduced in [Ranger 2.3](https://cwiki.apache.org/confluence/display/RANGER/Apache+Ranger+2.3.0+-+Release+Notes). This feature helps significantly simplify row filter expressions by using user/group/tag's attributes instead of explicit conditions. Considering a user with an attribute `born_city` of value `Guangzhou `, the row filter condition as `city='${{USER.born_city}}'` will be transformed to `city='Guangz [...]
+   
+```xml
+    <property>
+        <name>ranger.plugin.spark.enable.implicit.userstore.enricher</name>
+        <value>true</value>
+        <description>Enable UserStoreEnricher for fetching user and group attributes if using macros or scripts in row-filters since Ranger 2.3</description>
+    </property>
+
+    <property>
+        <name>ranger.plugin.hive.policy.cache.dir</name>
+        <value>./a ranger hive service name/policycache</value>
+        <description>As Authz plugin reuses hive service def, a policy cache path is required for caching UserStore and Tags for "hive" service def, while "ranger.plugin.spark.policy.cache.dir config" is the path for caching policies in service. </description>
+    </property>    
+```
 
 #### ranger-spark-audit.xml