[incubator-devlake] branch main updated: fix: db_url add tls=skip-verify and add tls connection (#6145)

[klesh]

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

klesh pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/incubator-devlake.git


The following commit(s) were added to refs/heads/main by this push:
     new 00e4c71ec fix: db_url add tls=skip-verify and add tls connection 
(#6145)
00e4c71ec is described below

commit 00e4c71ecaf0d78e44fb750b05cd8643e22d3dee
Author: abeizn <zikuan...@merico.dev>
AuthorDate: Tue Sep 26 17:23:45 2023 +0800

    fix: db_url add tls=skip-verify and add tls connection (#6145)
    
    * fix: db_url add tls=skip-verify
    
    * fix: add tls connection
    
    * fix: ci lint
    
    * fix: rename addLocal to sanitizeQuery
    
    * fix: adjust tls=custom to DB_URL
    
    ---------
    
    Co-authored-by: Klesh Wong <zhenmian.hu...@merico.dev>
---
 backend/core/runner/db.go                 | 37 ++++++++++++++++++++++++++++---
 backend/core/runner/db_test.go            |  4 ++--
 backend/python/pydevlake/pydevlake/ipc.py |  3 +++
 backend/server/services/locking.go        |  2 +-
 4 files changed, 40 insertions(+), 6 deletions(-)

diff --git a/backend/core/runner/db.go b/backend/core/runner/db.go
index 0a98704a4..72febf504 100644
--- a/backend/core/runner/db.go
+++ b/backend/core/runner/db.go
@@ -19,8 +19,12 @@ package runner
 
 import (
        "context"
+       "crypto/tls"
+       "crypto/x509"
+       "database/sql"
        "fmt"
        "net/url"
+       "os"
        "strings"
        "time"
 
@@ -28,6 +32,7 @@ import (
        "github.com/apache/incubator-devlake/core/dal"
        "github.com/apache/incubator-devlake/core/errors"
        "github.com/apache/incubator-devlake/core/log"
+       tlsMysql "github.com/go-sql-driver/mysql"
        "gorm.io/driver/mysql"
        "gorm.io/driver/postgres"
        "gorm.io/gorm"
@@ -104,11 +109,14 @@ func getUserString(u *url.URL) string {
        return userString
 }
 
-// addLocal adds loc=Local to the query string if it's not already there
-func addLocal(query url.Values) string {
+// sanitizeQuery add default value to query and remove ca-cert from query
+func sanitizeQuery(query url.Values) string {
        if query.Get("loc") == "" {
                query.Set("loc", "Local")
        }
+       if query.Get("ca-cert") != "" {
+               query.Del("ca-cert")
+       }
        return query.Encode()
 }
 
@@ -119,7 +127,30 @@ func getDbConnection(dbUrl string, conf *gorm.Config) 
(*gorm.DB, error) {
        }
        switch strings.ToLower(u.Scheme) {
        case "mysql":
-               dbUrl = fmt.Sprintf("%s@tcp(%s)%s?%s", getUserString(u), 
u.Host, u.Path, addLocal(u.Query()))
+               dbUrl = fmt.Sprintf("%s@tcp(%s)%s?%s", getUserString(u), 
u.Host, u.Path, sanitizeQuery(u.Query()))
+               if u.Query().Get("tls") != "" && u.Query().Get("ca-cert") != "" 
{
+                       rootCertPool := x509.NewCertPool()
+                       pem, err := os.ReadFile(u.Query().Get("ca-cert"))
+                       if err != nil {
+                               return nil, err
+                       }
+                       if ok := rootCertPool.AppendCertsFromPEM(pem); !ok {
+                               return nil, err
+                       }
+                       err = tlsMysql.RegisterTLSConfig("custom", 
&tls.Config{RootCAs: rootCertPool})
+                       if err != nil {
+                               return nil, err
+                       }
+                       db, err := sql.Open("mysql", dbUrl)
+                       if err != nil {
+                               return nil, err
+                       }
+                       gormDB, err := gorm.Open(mysql.New(mysql.Config{
+                               Conn: db,
+                       }), &gorm.Config{})
+
+                       return gormDB, err
+               }
                return gorm.Open(mysql.Open(dbUrl), conf)
        case "postgresql", "postgres", "pg":
                return gorm.Open(postgres.Open(dbUrl), conf)
diff --git a/backend/core/runner/db_test.go b/backend/core/runner/db_test.go
index 550a8fc3e..002960cf0 100644
--- a/backend/core/runner/db_test.go
+++ b/backend/core/runner/db_test.go
@@ -60,8 +60,8 @@ func Test_addLocal(t *testing.T) {
        }
        for _, tt := range tests {
                t.Run(tt.name, func(t *testing.T) {
-                       if got := addLocal(tt.args.query); got != tt.want {
-                               t.Errorf("addLocal() = %v, want %v", got, 
tt.want)
+                       if got := sanitizeQuery(tt.args.query); got != tt.want {
+                               t.Errorf("sanitizeQuery() = %v, want %v", got, 
tt.want)
                        }
                })
        }
diff --git a/backend/python/pydevlake/pydevlake/ipc.py 
b/backend/python/pydevlake/pydevlake/ipc.py
index ee308189d..18a6a7322 100644
--- a/backend/python/pydevlake/pydevlake/ipc.py
+++ b/backend/python/pydevlake/pydevlake/ipc.py
@@ -131,6 +131,9 @@ def create_db_engine(db_url) -> Engine:
         del connect_args['parseTime']
     if 'loc' in connect_args:
         del connect_args['loc']
+    if 'tls' in connect_args:
+        del connect_args['tls']
+        connect_args['ssl'] = {'verify_cert': 'False'}
     try:
         engine = create_engine(base_url, connect_args=connect_args)
         tables = SubtaskRun.metadata.tables
diff --git a/backend/server/services/locking.go 
b/backend/server/services/locking.go
index 6a74ad4da..1adc48dd5 100644
--- a/backend/server/services/locking.go
+++ b/backend/server/services/locking.go
@@ -59,7 +59,7 @@ func lockDatabase() {
        // 3. update the record
        select {
        case <-c:
-       case <-time.After(3 * time.Second):
+       case <-time.After(10 * time.Second):
                panic(fmt.Errorf("locking _devlake_locking_stub timeout, the 
database might be locked by another devlake instance"))
        }
 }