Hotspot Freeradius sqlcounter expire_on_login...

RouterOS Scripting
1

Hello,

for the MySQL there is the sample for the sqlcounter “expire_on_login”. But as my current schema is in PostgreSQL maybe someone can help doing things the right way :slight_smile: … ?

For MySQL:

sqlcounter expire_on_login {
counter-name = Expire-After-Initial-Login
check-name = Expire-After
sqlmod-inst = sql
key = User-Name
reset = never
query = “SELECT TIME_TO_SEC(TIMEDIFF(NOW(), acctstarttime))
FROM radacct
WHERE UserName=‘%{%k}’
ORDER BY acctstarttime
LIMIT 1;”
}

For postgreSQL i played different ways but wo success, my sample is:

sqlcounter expire_on_login {
counter-name = Expire-After-Initial-Login
check-name = Expire-After
sqlmod-inst = sql
key = User-Name
reset = never
query = “SELECT EXTRACT(EPOCH FROM AcctStartTime - now())::integer
FROM radacct
WHERE UserName=‘%{%k}’
ORDER BY acctstarttime
LIMIT 1;”
}

… Something is wrong with the query, syntax is ok, but the calculation result is negative and login is permitted “Expire-After”=whatever value in seconds in table radcheck - radius itself checks that rule, makes calculation and says all is OK :frowning:

Maybe someone had implemented this type of counter using PostgreSQL backend

Aleksander