Redundant SQL connection. Starting radiusd and Respond with Access-Accept if all databases do not work #2

The task is to allow or prohibit calls to subscribers. If the subscriber has valid status, then we send Access-Accept. If the subscriber calls the emergency service, then we send Access-Accept. In other cases, we send Access-Reject.

I configured two radius server, and two sql server. Replication configured between sql servers.

I write accounting and authentication to the database.

I want that when one database is unavailable, data is written to another database. But if both databases are not available, then we send Access-Accept

Some questions: * Is there a way to not duplicate config for modules sql1 and sql2 because they differ only in ip-address? * Is there a way to start freeradius if one or all databases are down? * What is the correct way to verify that sql1 and sql2 modules are fail without checking "Calling-Station-Id-Is-Exists" for empty string? * How can I simplify this configuration?

My config:

sql sql1 {      driver   = "rlm_sql_mysql"      dialect  = "mysql"

     server   = "localhost"      port     = 3306      login    = "radius"      password = "test"

     query_timeout  = 5

     radius_db = "radius"

     read_clients = no

     # Table to keep radius client info      client_table = "nas"

     # If you want both stop and start records logged to the      # same SQL table, leave this as is.  If you want them in      # different tables, put the start table in acct_table1      # and stop table in acct_table2      acct_table1 = "radacct"      acct_table2 = "radacct"

     # Allow for storing data after authentication      postauth_table = "radpostauth"

     # Tables containing 'check' items      authcheck_table = "radcheck"      groupcheck_table = "radgroupcheck"

     # Tables containing 'reply' items      authreply_table = "radreply"      groupreply_table = "radgroupreply"

     # Table to keep group info      usergroup_table = "radusergroup"

     pool {          start = ${thread[pool].start_servers}          min   = ${thread[pool].min_spare_servers}          max   = ${thread[pool].max_servers}          spare = ${thread[pool].max_spare_servers}

         uses = 0          retry_delay = 30          lifetime = 0          idle_timeout = 60      }

     # This entry should be used for additional instances (sql foo {})      # of the SQL module.      group_attribute = "${.:instance}-SQL-Group"

     # This entry should be used for the default instance (sql {})      # of the SQL module.      #group_attribute = "SQL-Group"

     # Read database-specific queries      # $INCLUDE ${modconfdir}/${.:name}/main/${dialect}/queries.conf      $INCLUDE ${modconfdir}/sql/main/${dialect}/queries.conf }

sql sql2 {      driver   = "rlm_sql_mysql"      dialect  = "mysql"

     server   = "172.27.65.243"      port     = 3306      login    = "radius"      password = "test"

     query_timeout  = 5

     radius_db = "radius"

     read_clients = no

     # Table to keep radius client info      client_table = "nas"

     # If you want both stop and start records logged to the      # same SQL table, leave this as is.  If you want them in      # different tables, put the start table in acct_table1      # and stop table in acct_table2      acct_table1 = "radacct"      acct_table2 = "radacct"

     # Allow for storing data after authentication      postauth_table = "radpostauth"

     # Tables containing 'check' items      authcheck_table = "radcheck"      groupcheck_table = "radgroupcheck"

     # Tables containing 'reply' items      authreply_table = "radreply"      groupreply_table = "radgroupreply"

     # Table to keep group info      usergroup_table = "radusergroup"

     pool {          start = ${thread[pool].start_servers}          min   = ${thread[pool].min_spare_servers}          max   = ${thread[pool].max_servers}          spare = ${thread[pool].max_spare_servers}

         uses = 0          retry_delay = 30          lifetime = 0          idle_timeout = 60      }

     # This entry should be used for additional instances (sql foo {})      # of the SQL module.      group_attribute = "${.:instance}-SQL-Group"

     # This entry should be used for the default instance (sql {})      # of the SQL module.      #group_attribute = "SQL-Group"

     # Read database-specific queries      #$INCLUDE ${modconfdir}/${.:name}/main/${dialect}/queries.conf      $INCLUDE ${modconfdir}/sql/main/${dialect}/queries.conf }

# Create virtual module for SELECTs instantiate {      redundant sql1_or_sql2 {          sql1          sql2      } }

server ats_local {      authorize {          update control {              Calling-Station-Id-Status    = "%{sql1_or_sql2:SELECT gtdb_users.status FROM gtdb_users WHERE aon='%{Calling-Station-Id}'}"              Called-Station-Id-Status     = "%{sql1_or_sql2:SELECT gtdb_users.status FROM gtdb_users WHERE aon='%{Called-Station-Id}'}"

             Calling-Station-Id-Is-Exists = "%{sql1_or_sql2:SELECT COUNT(gtdb_users.status) FROM gtdb_users WHERE aon='%{Calling-Station-Id}'}"              Called-Station-Id-Is-Exists  = "%{sql1_or_sql2:SELECT COUNT(gtdb_users.status) FROM gtdb_users WHERE aon='%{Called-Station-Id}'}"

             Called-Station-Id-Emergency  = "%{sql1_or_sql2:SELECT COUNT(aon) FROM emergency_cid WHERE aon='%{Called-Station-Id}'}"          }

         # These attributes in normal condition must contain the number  >= 0 (because we use sql COUNT() )          # If there is an empty string inside, then sql queries failed for some reason and we return Access-Accept

         if (&control:Calling-Station-Id-Is-Exists == '' || &control:Called-Station-Id-Is-Exists == '') {              update control {                  Auth-Type := Accept              }          }          else {              if (control:Called-Station-Id-Emergency == '1') {                  update control {                      Auth-Type := Accept                  }              }

             # ...              # bunch of elsif for Calling-Station-Id-Status and Called-Station-Id-Status              # ...

             else {                  update control {                      Auth-Type := Reject                  }              }          }      }

     authenticate {      }

     preacct {          acct_unique      }

     $INCLUDE ${confdir}/ats_accounting.conf

     session {      }

     post-auth {          update reply {              cisco-avpair = "h323-credit-time=86400"          }

         if (&User-Name) {              # this not work, because I can't put "ok" to virtual module "sql1_or_sql2"              # sql1_or_sql2              redundant {                  sql1                  sql2                  ok              }          }

         Post-Auth-Type REJECT {              if (&User-Name) {                  redundant {                      sql1                      sql2                      ok                  }              }          }

     }

     pre-proxy {      }

     post-proxy {      } }

See raddb/templates.conf  You can put all of the configuration *except* the "server" file into a "template" in that file.  Then configure two SQL modules:

sql sql1 {     $template sql_template     server = localhost }

sql sql2 {     $template sql_template     server   = 172.27.65.243 }

...

* Is there a way to start freeradius if one or all databases are down?

  See the pool config.  This is documented.  Set "start = 0", and "min = 0"

...

* What is the correct way to verify that sql1 and sql2 modules are fail without checking "Calling-Station-Id-Is-Exists" for empty string?

  Use a "redundant" block.

...

* How can I simplify this configuration?

  It seems OK to me.

  Alan DeKok.

Sorry for repeating the message, I mistakenly deleted all previous messages. Thanks for the help. Everything worked out for me. The config began to look more simple. All my problems were due to the fact that I did not read the documentation. And I also deleted all the comments from the configuration files to make it easier to read, but in the end I made it worse for myself. Now I make a copy and rename the file to "*_with_comments" to look into it if necessary.