Mongo and operators

classic Classic list List threaded Threaded
8 messages Options
| Threaded
Open this post in threaded view
|

Mongo and operators

Giedrius Baronas
Hello all,

I try mongo db query in default instalation but I get error about key
'op' value ':=' witch is incorrect.
How to solve the use of special characters in a mongo query?

main/mongo/queries.conf

   <85 line>      { \
                 '$project': { \
                         '_id': 0, \
                         'attribute': '$attributes.k', \
                         'value': '$attributes.v', \
                         'op': ':=' \
                 } \
<92 line>        } \

radius -X output:
#(0) sql: ERROR: The op field for attribute ':= = (null)' is NULL, or
non-existent.
#(0) sql: ERROR: You MUST FIX THIS if you want the configuration to
behave as you expect
#(0) sql: ERROR: Value field is empty or NULL, skipping the entire row

Giedrius Baronas
www.baronai.lt


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: Mongo and operators

Jorge Pereira-2
Which version are you running? We need more output https://wiki.freeradius.org/guide/radiusd-X <https://wiki.freeradius.org/guide/radiusd-X>



> On 23 Mar 2021, at 20:56, Giedrius Baronas <[hidden email]> wrote:
>
> Hello all,
>
> I try mongo db query in default instalation but I get error about key 'op' value ':=' witch is incorrect.
> How to solve the use of special characters in a mongo query?
>
> main/mongo/queries.conf
>
>   <85 line>      { \
>                 '$project': { \
>                         '_id': 0, \
>                         'attribute': '$attributes.k', \
>                         'value': '$attributes.v', \
>                         'op': ':=' \
>                 } \
> <92 line>        } \
>
> radius -X output:
> #(0) sql: ERROR: The op field for attribute ':= = (null)' is NULL, or non-existent.
> #(0) sql: ERROR: You MUST FIX THIS if you want the configuration to behave as you expect
> #(0) sql: ERROR: Value field is empty or NULL, skipping the entire row
>
> Giedrius Baronas
> www.baronai.lt
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: Mongo and operators

Giedrius Baronas
I use 3.0.21 radius.

Ready to process requests
(0) Received Access-Request Id 218 from 172.16.238.206:50839 to
172.16.238.205:1812 length 170
(0)   User-Name = "70:5a:b6:9e:a5:6f"
(0)   User-Password = "password"
(0)   NAS-IP-Address = 10.10.10.10
(0)   ADSL-Agent-Remote-Id = "76161"
(0)   NAS-Port-Type = Ethernet
(0)   NAS-Port-Id = "lag-46:1003"
(0)   User-Name = "70:5a:b6:9e:a5:6f"
(0)   User-Password = "password"
(0)   NAS-IP-Address = 10.10.10.10
(0)   ADSL-Agent-Remote-Id = "76161"
(0)   NAS-Port-Type = Ethernet
(0)   NAS-Port-Id = "lag-46:1003"

(0) # Executing section authorize from file
/etc/freeradius/sites-enabled/default

(0)   authorize {

(0)     policy filter_username {

(0)       if (&User-Name) {

(0)       if (&User-Name)  -> TRUE

(0)       if (&User-Name)  {

(0)         if (&User-Name =~ / /) {

(0)         if (&User-Name =~ / /)  -> FALSE

(0)         if (&User-Name =~ /@[^@]*@/ ) {

(0)         if (&User-Name =~ /@[^@]*@/ )  -> FALSE

(0)         if (&User-Name =~ /\.\./ ) {

(0)         if (&User-Name =~ /\.\./ )  -> FALSE

(0)         if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/))  {

(0)         if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/))  
-> FALSE

(0)         if (&User-Name =~ /\.$/)  {

(0)         if (&User-Name =~ /\.$/)   -> FALSE

(0)         if (&User-Name =~ /@\./)  {

(0)         if (&User-Name =~ /@\./)   -> FALSE

(0)       } # if (&User-Name)  = notfound

(0)     } # policy filter_username = notfound

(0)     [preprocess] = ok

(0)     [chap] = noop

(0)     [mschap] = noop

(0)     [digest] = noop

(0) suffix: Checking for suffix after "@"

(0) suffix: No '@' in User-Name = "70:5a:b6:9e:a5:6f", looking up realm NULL

(0) suffix: No such realm "NULL"

(0)     [suffix] = noop

(0) eap: No EAP-Message, not doing EAP

(0)     [eap] = noop

(0)     [files] = noop

(0) sql: EXPAND %{User-Name}

(0) sql:    --> 70:5a:b6:9e:a5:6f

(0) sql: SQL-User-Name set to '70:5a:b6:9e:a5:6f'

rlm_sql (sql): Reserved connection (0)

(0) sql: EXPAND db.radcheck.aggregate([     { '$match': { 'User-Name':
'%{User-Name}' }         },     { '$addFields': {    
'attributes.User-Name': '$User-Name', 'attributes.User-Password':
'$User-Password'                 } },     { '$project': {         '_id':
0,         'attributes': { '$objectToArray': '$attributes' }            
     }     },     { '$unwind': {         'path': '$attributes'        
     }     },     { '$project': {         '_id': 0,         'attribute':
'$attributes.k',         'value': '$attributes.v',         'op' : ':='
             }     }     ])

(0) sql:    --> db.radcheck.aggregate([     { '$match': {    
'User-Name': '70:5a:b6:9e:a5:6f' }         },     { '$addFields': {    
     'attributes.User-Name': '$User-Name',    
'attributes.User-Password': '$User-Password'                 }     },
     { '$project': {         '_id': 0,         'attributes': {
'$objectToArray': '$attributes' }                 }     },     {
'$unwind': {         'path': '$attributes'             }     },     {
'$project': {         '_id': 0,         'attribute': '$attributes.k',
         'value': '$attributes.v',         'op' : ':='             }    
}     ])

(0) sql: Executing select query: db.radcheck.aggregate([     { '$match':
{         'User-Name': '70:5a:b6:9e:a5:6f' }         },     {
'$addFields': {         'attributes.User-Name': '$User-Name',        
'attributes.User-Password': '$User-Password'                 }     },
     { '$project': {         '_id': 0,     'attributes': {
'$objectToArray': '$attributes' }     }     },     { '$unwind': {    
     'path': '$attributes'         }     },     { '$project': {        
'_id': 0, 'attribute': '$attributes.k',         'value':
'$attributes.v',         'op' : ':='             }     }     ])

getting result from row 0 = 0x55a774111cf0

(0) sql: ERROR: The op field for attribute ':= = (null)' is NULL, or
non-existent.

(0) sql: ERROR: You MUST FIX THIS if you want the configuration to
behave as you expect

(0) sql: ERROR: Value field is empty or NULL, skipping the entire row

(0) sql: ERROR: Error parsing user data from database result

(0) sql: ERROR: Error getting check attributes

rlm_sql (sql): Released connection (0)

Need 5 more connections to reach 10 spares

rlm_sql (sql): Opening additional connection (5), 1 of 27 pending slots used

rlm_sql_mongo: Socket initialized.

(0)     [sql] = fail

    } # authorize = fail

(0) Invalid user (sql: The op field for attribute ':= = (null)' is NULL,
or non-existent.): [70:5a:b6:9e:a5:6f] (from client radperf port 0)

(0) Using Post-Auth-Type Reject

(0) # Executing group from file /etc/freeradius/sites-enabled/default

(0)   Post-Auth-Type REJECT {

(0) sql: EXPAND .query

(0) sql:    --> .query

(0) sql: Using query template 'query'

rlm_sql (sql): Reserved connection (1)

(0) sql: EXPAND %{User-Name}

(0) sql:    --> 70:5a:b6:9e:a5:6f

(0) sql: SQL-User-Name set to '70:5a:b6:9e:a5:6f'

(0) sql: EXPAND db.post_auth.findAndModify({ 'query':
{                         'calling_station_id':
'%{User-Name}',                         'nas_ip':
'%{NAS-IP-Address}'                 },                 'update':
{                         '$set': {                                
'update_date':  { '$date': { '$numberLong': '%{expr: (%l * 1000) + (%M /
1000)}' } },                                 'last_event_ts':
'%{Packet-Original-Timestamp}' },                         '$inc':
{                                 'reject_counter':
1                         }, '$setOnInsert': { 'calling_station_id':
'%{User-Name}',                                 'nas_ip':
'%{NAS-IP-Address}', 'creation_date': { '$date': { '$numberLong':
'%{expr: (%l * 1000) + (%M / 1000)}' } }                         }
},                 'upsert': true         })

(0) sql:    --> db.post_auth.findAndModify({ 'query':
{                         'calling_station_id':
'70:5a:b6:9e:a5:6f',                         'nas_ip':
'10.10.10.10'                 },                 'update':
{                         '$set': {                                
'update_date':  { '$date': { '$numberLong': '1616545765289' }
},                                 'last_event_ts':
''                         },                         '$inc':
{                                 'reject_counter':
1                         }, '$setOnInsert': { 'calling_station_id':
'70:5a:b6:9e:a5:6f',                                 'nas_ip':
'10.10.10.10',                                 'creation_date': {
'$date': { '$numberLong': '1616545765289' } }                        
}                 }, 'upsert': true         })

(0) sql: EXPAND /var/log/freeradius/sqllog.sql

(0) sql:    --> /var/log/freeradius/sqllog.sql

(0) sql: Executing query: db.post_auth.findAndModify({                
'query': {                         'calling_station_id':
'70:5a:b6:9e:a5:6f',                         'nas_ip':
'10.10.10.10'                 },                 'update':
{                         '$set': {                                
'update_date':  { '$date': { '$numberLong': '1616545765289' }
},                                 'last_event_ts':
''                         },                         '$inc':
{                                 'reject_counter':
1                         }, '$setOnInsert': { 'calling_station_id':
'70:5a:b6:9e:a5:6f',                                 'nas_ip':
'10.10.10.10',                                 'creation_date': {
'$date': { '$numberLong': '1616545765289' } }                        
}                 }, 'upsert': true         })

rlm_sql_mongo: No document updated for query.

rlm_sql_mongo: Query got no result

(0) sql: SQL query returned: success

(0) sql: 0 record(s) updated

(0) sql: No additional queries configured

rlm_sql (sql): Released connection (1)

(0)     [sql] = noop

(0) attr_filter.access_reject: EXPAND %{User-Name}

(0) attr_filter.access_reject:    --> 70:5a:b6:9e:a5:6f

(0) attr_filter.access_reject: Matched entry DEFAULT at line 11

(0)     [attr_filter.access_reject] = updated

(0)     [eap] = noop

(0)     policy remove_reply_message_if_eap {

(0)       if (&reply:EAP-Message && &reply:Reply-Message) {

(0)       if (&reply:EAP-Message && &reply:Reply-Message)  -> FALSE

(0)       else {

(0)         [noop] = noop

(0)       } # else = noop

(0)     } # policy remove_reply_message_if_eap = noop

(0)   } # Post-Auth-Type REJECT = updated

(0) Delaying response for 1.000000 seconds

Waking up in 0.3 seconds.

Waking up in 0.6 seconds.

(0) Sending delayed response

(0) Sent Access-Reject Id 218 from 172.16.238.205:1812 to
172.16.238.206:50839 length 20

Waking up in 3.9 seconds.


Giedrius Baronas
www.baronai.lt

On 2021-03-24 02:17, Jorge Pereira wrote:

> Which version are you running? We need more output https://wiki.freeradius.org/guide/radiusd-X <https://wiki.freeradius.org/guide/radiusd-X>
>
>
>
>> On 23 Mar 2021, at 20:56, Giedrius Baronas <[hidden email]> wrote:
>>
>> Hello all,
>>
>> I try mongo db query in default instalation but I get error about key 'op' value ':=' witch is incorrect.
>> How to solve the use of special characters in a mongo query?
>>
>> main/mongo/queries.conf
>>
>>    <85 line>      { \
>>                  '$project': { \
>>                          '_id': 0, \
>>                          'attribute': '$attributes.k', \
>>                          'value': '$attributes.v', \
>>                          'op': ':=' \
>>                  } \
>> <92 line>        } \
>>
>> radius -X output:
>> #(0) sql: ERROR: The op field for attribute ':= = (null)' is NULL, or non-existent.
>> #(0) sql: ERROR: You MUST FIX THIS if you want the configuration to behave as you expect
>> #(0) sql: ERROR: Value field is empty or NULL, skipping the entire row
>>
>> Giedrius Baronas
>> www.baronai.lt
>>
>>
>> -
>> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: Mongo and operators

Alan DeKok-2
On Mar 23, 2021, at 8:40 PM, Giedrius Baronas <[hidden email]> wrote:
>
> (0) sql: ERROR: The op field for attribute ':= = (null)' is NULL, or non-existent.

  That's telling.

  The operator name here is ":=".  The value is NULL.  This says that you're returning the fields in the wrong order.

   See  raddb/mods-config/sql/main/mongo/queries.conf

#  These queries compare the check items for the user
#  in ${authcheck_table} and setup the reply items in
#  ${authreply_table}.  You can use any query/tables
#  you want, but the return data for each row MUST
#  be in the  following order:
#
#  0. Row ID (currently unused)
#  1. UserName/GroupName
#  2. Item Attr Name
#  3. Item Attr Value
#  4. Item Attr Operation

  Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: Mongo and operators

Giedrius Baronas
Hi,

I tried different queries but they are failed.

What should be the result of the query so that there is no error message?

[ { attribute: 'User-Name', value: 'test', op: ':=' },
   { attribute: 'User-Password', value: '12345', op: ':=' } ]

OR

[ { 'test', attribute: 'User-Password', value: '12345', op: ':=' } ]

OR

[ { 'User-Name', value: 'test', attribute: 'User-Password', value:
'12345', op: ':=' } ]


On raddb/mods-config/sql/main/mongo/queries.conf examples I see

# Example of Result:
#
# { "attribute" : "User-Name", "Value" : "pippo", "op" : ":=" }
# { "attribute" : "ClearText-Password", "value" : "pwd1", "op" : ":=" }
# { "attribute" : "Cache-TTL", "value" : 1000, "op" : ":=" }


My query

db.radcheck.aggregate([
     {
         '$match': {
             'User-Name': 'test'
         }
     }, {
         '$addFields': {
             'attributes.User-Name': '$User-Name',
             'attributes.User-Password': '$User-Password'
         }
     }, {
         '$project': {
             '_id': 0,
             'attributes': {
                 '$objectToArray': '$attributes'
             }
         }
     }, {
         '$unwind': {
             'path': '$attributes'
         }
     }, {
         '$project': {
             '_id': 0,
             'attribute': '$attributes.k',
             'value': '$attributes.v',
             'op': ':='
         }
     }
])

My result on mongoDB Compass

[ { attribute: 'User-Name', value: 'test', op: ':=' },
   { attribute: 'User-Password', value: '12345', op: ':=' } ]

My error on FreeRadius

getting result from row 0 = 0x5639efc55b90
(0) sql: ERROR: The op field for attribute 'test = (null)' is NULL, or
non-existent.
(0) sql: ERROR: You MUST FIX THIS if you want the configuration to
behave as you expect
(0) sql: ERROR: Value field is empty or NULL, skipping the entire row
(0) sql: ERROR: Error parsing user data from database result
(0) sql: ERROR: Error getting check attributes

Giedrius


On 2021-03-24 14:36, Alan DeKok wrote:

> On Mar 23, 2021, at 8:40 PM, Giedrius Baronas <[hidden email]> wrote:
>> (0) sql: ERROR: The op field for attribute ':= = (null)' is NULL, or non-existent.
>    That's telling.
>
>    The operator name here is ":=".  The value is NULL.  This says that you're returning the fields in the wrong order.
>
>     See  raddb/mods-config/sql/main/mongo/queries.conf
>
> #  These queries compare the check items for the user
> #  in ${authcheck_table} and setup the reply items in
> #  ${authreply_table}.  You can use any query/tables
> #  you want, but the return data for each row MUST
> #  be in the  following order:
> #
> #  0. Row ID (currently unused)
> #  1. UserName/GroupName
> #  2. Item Attr Name
> #  3. Item Attr Value
> #  4. Item Attr Operation
>
>    Alan DeKok.
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: Mongo and operators

Alan DeKok-2
On Mar 29, 2021, at 6:39 PM, Giedrius Baronas <[hidden email]> wrote:
> What should be the result of the query so that there is no error message?
>
> [ { attribute: 'User-Name', value: 'test', op: ':=' },
>   { attribute: 'User-Password', value: '12345', op: ':=' } ]\

  The results should be the same 4 columns as returned by the SQL modules:

ID
Attribute
value
op

{ "id" : 0, "attribute" : "User-Name", "Value" : "pippo", "op" : "==" }
{ "id" : 0, "attribute" : "ClearText-Password", "value" : "pwd1", "op" : ":=" }

  It turns out the documentation was wrong.  I'll go push a fix.

  Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: Mongo and operators

Giedrius Baronas
Hi,

I tried with your wrote output result but with it is failed. I ordering
op to above attribute and work with errors.
I moved the op above the attribute and it helped. The error left is that
op is NULL, which I don't know how to solve.

-------------------=========== incorrect
=============-------------------------
[ { id: '0', attribute: 'User-Name', value: 'test', op: '==' },
   { id: '0', attribute: 'User-Password', value: '12345', op: ':=' } ]

getting result from row 0 = 0x55e3c4760800
(0) sql: ERROR: The op field for attribute 'test = :=' is NULL, or
non-existent.
(0) sql: ERROR: You MUST FIX THIS if you want the configuration to
behave as you expect
(0) sql: ERROR: Failed to create the pair: Invalid vendor name in
attribute name "test"
(0) sql: ERROR: Error parsing user data from database result
(0) sql: ERROR: Error getting check attributes

-------------------=========== incorrect
=============-------------------------
[ { id: '0', op: '==', attribute: 'User-Name', value: 'test' },
   { id: '0', attribute: 'User-Password', op: ':=', value: '12345' } ]

getting result from row 0 = 0x55e3c4774c10
(1) sql: ERROR: The op field for attribute 'User-Name = test' is NULL,
or non-existent.
(1) sql: ERROR: You MUST FIX THIS if you want the configuration to
behave as you expect
getting result from row 1 = 0x55e3c47270a0
(1) sql: ERROR: The op field for attribute ':= = 12345' is NULL, or
non-existent.
(1) sql: ERROR: You MUST FIX THIS if you want the configuration to
behave as you expect
(1) sql: ERROR: Failed to create the pair: Invalid tag for attribute :=
(1) sql: ERROR: Error parsing user data from database result
(1) sql: ERROR: Error getting check attributes

--------------------=========== correct
=============--------------------------
[ { id: '0', op: '==', attribute: 'User-Name', value: 'test' },
   { id: '0', op: ':=', attribute: 'User-Password', value: '12345' } ]

getting result from row 0 = 0x561864ff8b50
(2) sql: ERROR: The op field for attribute 'User-Name = test' is NULL,
or non-existent.
(2) sql: ERROR: You MUST FIX THIS if you want the configuration to
behave as you expect
getting result from row 1 = 0x561865056560
(2) sql: ERROR: The op field for attribute 'User-Password = 12345' is
NULL, or non-existent.
(2) sql: ERROR: You MUST FIX THIS if you want the configuration to
behave as you expect
getting result from row 2 = (nil)
No more rows
(2) sql: User found in radcheck table
Found User-Password == "..."
Are you sure you don't mean Cleartext-Password?
See "man rlm_pap" for more information
(2) sql: Conditional check items matched, merging assignment check items

Giedrius


On 2021-03-30 02:07, Alan DeKok wrote:

> On Mar 29, 2021, at 6:39 PM, Giedrius Baronas <[hidden email]> wrote:
>> What should be the result of the query so that there is no error message?
>>
>> [ { attribute: 'User-Name', value: 'test', op: ':=' },
>>    { attribute: 'User-Password', value: '12345', op: ':=' } ]\
>    The results should be the same 4 columns as returned by the SQL modules:
>
> ID
> Attribute
> value
> op
>
> { "id" : 0, "attribute" : "User-Name", "Value" : "pippo", "op" : "==" }
> { "id" : 0, "attribute" : "ClearText-Password", "value" : "pwd1", "op" : ":=" }
>
>    It turns out the documentation was wrong.  I'll go push a fix.
>
>    Alan DeKok.
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: Mongo and operators

Alan DeKok-2
On Mar 30, 2021, at 6:11 PM, Giedrius Baronas <[hidden email]> wrote:

>
> Hi,
>
> I tried with your wrote output result but with it is failed. I ordering op to above attribute and work with errors.
> I moved the op above the attribute and it helped. The error left is that op is NULL, which I don't know how to solve.
>
> -------------------=========== incorrect =============-------------------------
> [ { id: '0', attribute: 'User-Name', value: 'test', op: '==' },
>   { id: '0', attribute: 'User-Password', value: '12345', op: ':=' } ]
>
> getting result from row 0 = 0x55e3c4760800
> (0) sql: ERROR: The op field for attribute 'test = :=' is NULL, or non-existent.
> (0) sql: ERROR: You MUST FIX THIS if you want the configuration to behave as you expect

  OK... I've rooted through the code a little more.  It's been a while since I looked at the Mongo stuff :(

  I forgot about the "username" field.  So you should use:

{ "id" : 0, "username": "bob", "attribute" : "User-Name", "Value" : "pippo", "op" : "==" }

  I'll see if I can update the examples, and the code to give better error messages.

  Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html