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.