Security

last person joined: yesterday 

Forum to discuss Enterprise security using HPE Aruba Networking NAC solutions (ClearPass), Introspect, VIA, 360 Security Exchange, Extensions, and Policy Enforcement Firewall (PEF).
Back to discussions
Expand all | Collapse all

Checking if airgroups_shared_user contains a specific userid

This thread has been viewed 0 times

  • 1.  Checking if airgroups_shared_user contains a specific userid

    Posted May 24, 2019 09:41 AM

    Need a bit of sql to check whether a specific text string ( userid) is in the comma delimited list of userids in airgroup_shared_user.

     

    Anyone ?

     

    Rgds

    Alex

     



  • 2.  RE: Checking if airgroups_shared_user contains a specific userid
    Best Answer

    EMPLOYEE
    Posted May 25, 2019 12:18 PM

    Try a regex against the word boundary.

     

    https://www.postgresql.org/docs/9.3/functions-matching.html

     

    I only tested at CLI, and note you need to double escape the word boundary W.  The '?' makes it optional to catch starting and ending with.

     

    WHERE CAST(attributes->'airgroup_shared_user' AS TEXT) ~ E'\\W?67\\W?';



  • 3.  RE: Checking if airgroups_shared_user contains a specific userid

    Posted May 28, 2019 09:48 AM

    Many thanks for the pointer. I actually ended up using

     

    select COUNT(*) AS shared_user_count FROM tips_guest_users WHERE ((guest_type = 'DEVICE') AND (CAST(attributes->'airgroup_shared_user' AS TEXT) ~ E'.*%{Authentication:Username}.*'))

     

    This returns an integer value

    0 = no one has shared an airgroup device with this user

    >0 = someone has configured an airgroup device to be sharable with this userid

     

    The above value is then used in our eduroam service to apply a given set of ACLs to a wifi session so a user can see a given set of airgroup server devices



  • 4.  RE: Checking if airgroups_shared_user contains a specific userid

    EMPLOYEE
    Posted May 28, 2019 10:18 AM

    Great.  Post any other details for your service if you think they prove useful.

     

    Do note your regex is a basic contains check.  I am not even sure the .* are needed as contains is default if not using ^ and/or $ boundaries.  If your usernames are emails, it is safe due to inherent uniqueness contraints, but if it is something with just names you may get overlap, e.g. paulgaultier would match someone having jeanpaulgaultier.  The \\W in my example accounts for this scenario.  I think my example would fail for emails though as @ is one of the boundaries.