PgSQL SSL

[volga629-1]

Hello Everyone, Is possible adjust PgSQL connection string and allow sslmode parameter be configurable from config file. Right now based on what I see in main.go connection string is set sslmode=disable

New option

 appFlags.DatabaseSSLMode = flag.String("database-sslmode", "disable", "database-sslmode")

Default value should be "disable"

[adubovikov]

you did almost everything, just make a PR and we will accept it!

[volga629-1]

I am not good at go, to make it right. It just pure observation. Did small correction set default value to disable.

[adubovikov]

https://github.com/sipcapture/homer-app/commit/9c01bf7e00c78c169508bf477e6a8b45aa6ed8d0

https://jdbc.postgresql.org/documentation/head/ssl-client.html

[adubovikov]

please retest it and give a feedback

also do this:

https://github.com/sipcapture/homer/stargazers

[volga629-1]

Wow, thank you for prompt help. Do still need the line for conf file ?

[adubovikov]

https://github.com/sipcapture/homer-app/commit/9c01bf7e00c78c169508bf477e6a8b45aa6ed8d0#diff-7ddfb3e035b42cd70649cc33393fe32cR579

in the config at database's part

sslmode: allow or require

[volga629-1]

Patch test

+ cd homer-app-1.2.4
+ /usr/bin/chmod -Rf a+rX,u+w,g-w,o-w .
Patch #1 (homer-app-pgsql-ssl-v1.24.patch):
+ echo 'Patch #1 (homer-app-pgsql-ssl-v1.24.patch):'
+ /usr/bin/patch --no-backup-if-mismatch -p1 --fuzz=0
patching file main.go
Hunk #1 succeeded at 576 (offset 2 lines).
Hunk #2 succeeded at 629 (offset 2 lines).
Hunk #3 succeeded at 683 (offset 2 lines).
Executing(%build): /bin/sh -e /var/tmp/rpm-tmp.22vHRv
+ RPM_EC=0

But seems when it tries connect database it not use config file at all. Look like it will require command line sslmode options. Config file is configured remote database and last option in section set sslmode: "require"

{
  "database_data": {
    "LocalNode": {
      "help": "Settings for PGSQL Database (data)",
      "node": "LocalNode",
      "user": "labmaster@pgsql-n1",
      "pass": "",
      "name": "homer_data",
      "keepalive": true,
      "host": "pgsql-n1",
      "sslmode": "require"
    }
  },

[root@voice-diag ~]# homer-app -create-config-db -webapp-config-path=/etc/homer-app

CONNECT to DB ROOT STRING: [host=localhost user=postgres dbname=postgres sslmode=disable port=5432]

{"level":"error","msg":"dial tcp [::1]:5432: connect: connection refused","time":"2020-10-05T22:01:18+08:00"}
{"level":"error","msg":"Couldn't establish connection. Please be sure you can have correct passworddial tcp [::1]:5432: connect: connection refused","time":"2020-10-05T22:01:18+08:00"}
{"level":"error","msg":"Try run: sudo -u postgres psql -c \"ALTER USER postgres PASSWORD 'postgres';\"","time":"2020-10-05T22:01:18+08:00"}
panic: dial tcp [::1]:5432: connect: connection refused

goroutine 1 [running]:
main.checkAdminFlags()
    /builddir/build/BUILD/homer-app-1.2.4/main.go:1128 +0xef3
main.main()
    /builddir/build/BUILD/homer-app-1.2.4/main.go:222 +0x1f6

[volga629-1]

Something like this

--- a/main.go   2020-10-05 10:32:39.296112433 -0400
+++ b/main.go   2020-10-05 10:41:10.554841427 -0400
@@ -117,6 +117,7 @@
    DatabaseHomerPassword     *string    `json:"homer_password"`
    DatabaseHomerConfig       *string    `json:"db_homer_config"`
    DatabaseHomerData         *string    `json:"db_homer_data"`
+   DatabaseSSLMode           *string    `json:"db_sslmode"`
    PathWebAppConfig          *string    `json:"path_webapp"`
    LogPathWebApp             *string    `json:"path_log_webapp"`
    LogName                   *string    `json:"log_name_webapp"`
@@ -174,6 +175,7 @@
    appFlags.DatabaseHomerPassword = flag.String("database-homer-password", "homer_password", "database-homer-password")
    appFlags.DatabaseHomerConfig = flag.String("database-homer-config", "homer_config", "database-homer-config")
    appFlags.DatabaseHomerData = flag.String("database-homer-data", "homer_data", "database-homer-data")
+   appFlags.DatabaseSSLMode = flag.String("database-sslmode", "disable", "database-sslmode")

    appFlags.PathWebAppConfig = flag.String("webapp-config-path", "/usr/local/homer/etc", "the path to the webapp config file")
    appFlags.LogName = flag.String("webapp-log-name", "", "the name prefix of the log file.")
@@ -223,10 +225,10 @@

    /* now check if we do write to config */
    if *appFlags.SaveHomerDbConfigToConfig {
-       applyDBDataParamToConfig(appFlags.DatabaseHomerUser, appFlags.DatabaseHomerPassword, appFlags.DatabaseHomerConfig, appFlags.DatabaseHost, appFlags.DatabaseHomerNode)
+       applyDBDataParamToConfig(appFlags.DatabaseHomerUser, appFlags.DatabaseHomerPassword, appFlags.DatabaseSSLMode, appFlags.DatabaseHomerConfig, appFlags.DatabaseHost, appFlags.DatabaseHomerNode)
        os.Exit(0)
    } else if *appFlags.SaveHomerDbDataToConfig {
-       applyDBConfigParamToConfig(appFlags.DatabaseHomerUser, appFlags.DatabaseHomerPassword, appFlags.DatabaseHomerData, appFlags.DatabaseHost)
+       applyDBConfigParamToConfig(appFlags.DatabaseHomerUser, appFlags.DatabaseSSLMode, appFlags.DatabaseHomerPassword, appFlags.DatabaseHomerData, appFlags.DatabaseHost)
        os.Exit(0)
    }

@@ -1078,7 +1080,8 @@
            appFlags.DatabaseRootPassword,
            appFlags.DatabaseRootDB,
            appFlags.DatabaseHost,
-           appFlags.DatabasePort)
+           appFlags.DatabasePort,
+           appFlags.DatabaseSSLMode)

        if err != nil {
            logrus.Error("Couldn't establish connection. Please be sure you can have correct password", err)
@@ -1099,7 +1102,8 @@
            appFlags.DatabaseRootPassword,
            appFlags.DatabaseRootDB,
            appFlags.DatabaseHost,
-           appFlags.DatabasePort)
+           appFlags.DatabasePort,
+           appFlags.DatabaseSSLMode)

        if err != nil {
            logrus.Error("Couldn't establish connection. Please be sure you can have correct password", err)
@@ -1120,7 +1124,8 @@
            appFlags.DatabaseRootPassword,
            appFlags.DatabaseRootDB,
            appFlags.DatabaseHost,
-           appFlags.DatabasePort)
+           appFlags.DatabasePort,
+           appFlags.DatabaseSSLMode)

        if err != nil {
            logrus.Error("Couldn't establish connection. Please be sure you can have correct password", err)
@@ -1145,7 +1150,8 @@
            appFlags.DatabaseRootPassword,
            appFlags.DatabaseRootDB,
            appFlags.DatabaseHost,
-           appFlags.DatabasePort)
+           appFlags.DatabasePort,
+           appFlags.DatabaseSSLMode)

        if err != nil {
            logrus.Error("Couldn't establish connection. Please be sure you can have correct password", err)
@@ -1159,7 +1165,8 @@
            appFlags.DatabaseRootPassword,
            appFlags.DatabaseHomerData,
            appFlags.DatabaseHost,
-           appFlags.DatabasePort)
+           appFlags.DatabasePort,
+           appFlags.DatabaseSSLMode)

        if err != nil {
            logrus.Error("Couldn't establish connection to data. Please be sure you can have correct password", err)
@@ -1179,7 +1186,8 @@
            appFlags.DatabaseRootPassword,
            appFlags.DatabaseRootDB,
            appFlags.DatabaseHost,
-           appFlags.DatabasePort)
+           appFlags.DatabasePort,
+           appFlags.DatabaseSSLMode)

        if err != nil {
            logrus.Error("Couldn't establish connection. Please be sure you can have correct password", err)
@@ -1201,7 +1209,8 @@
            appFlags.DatabaseRootPassword,
            appFlags.DatabaseRootDB,
            appFlags.DatabaseHost,
-           appFlags.DatabasePort)
+           appFlags.DatabasePort,
+           appFlags.DatabaseSSLMode)

        if err != nil {
            logrus.Error("Couldn't establish connection. Please be sure you can have correct password", err)
@@ -1224,6 +1233,7 @@
        appFlags.DatabaseRootDB,
        appFlags.DatabaseHost,
        appFlags.DatabasePort,
+       appFlags.DatabaseSSLMode,
    )

    if err != nil {
@@ -1246,6 +1256,7 @@
        appFlags.DatabaseHomerData,
        appFlags.DatabaseHost,
        appFlags.DatabasePort,
+       appFlags.DatabaseSSLMode,
    )

    if err != nil {

[adubovikov]

sorry, what are you trying to do ? Use homer-app to create schema/users ?

[volga629-1]

Yes, initial configuration at first. Then use from config file. All connections with PgSQL and SSL homer 7

[adubovikov]

ok, this is something differfent. Let me add the param there as well

[adubovikov]

https://github.com/sipcapture/homer-app/commit/32498e33958b661222000f22fb92ecdf0019c7e2

[volga629-1]

Is require to add in func applyDBDataParamToConfig and func applyDBConfigParamToConfig the *sslmode ?

[adubovikov]

done

[adubovikov]

any feedback ?

[volga629-1]

I am working on release of RPM . I will post feedback soon.

[volga629-1]

Again, huge thank you for prompt help and all work. SSL works right now as expected. I released rpm for f32 server.

CONNECT to DB ROOT STRING: [host= user= dbname=postgres sslmode=require port=5432 password=]

HOMER - create db [homer_config] with [name=homermaster] 

(/builddir/build/BUILD/homer-app-1.2.4/migration/migration.go:84) 
[2020-10-06 01:16:58]  [2779.58ms]  CREATE DATABASE homer_config OWNER homermaster  
[0 rows affected or returned ] 

DONE 

[adubovikov]

super. If you can, please star our project :-)

https://github.com/sipcapture/homer/stargazers

have a nice evening