Security

Reply

Checking if airgroups_shared_user contains a specific userid

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

 

Re: Checking if airgroups_shared_user contains a specific userid

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?';

Re: Checking if airgroups_shared_user contains a specific userid

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

Re: Checking if airgroups_shared_user contains a specific userid

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.

Search Airheads
cancel
Showing results for 
Search instead for 
Did you mean: