[OpenSIPS-Users] Opensips 3.2 from sources testing auth_jwt with postgres database backend.

[Bogdan-Andrei Iancu]

Hi Jonathan,

yeah, that raw query from JWT module is not sql compliant, but mysql 
compliant :D. Could you provide the patch for your fixes, so we can 
evaluate and push forward ?

Thanks,

Bogdan-Andrei Iancu

OpenSIPS Founder and Developer
   https://www.opensips-solutions.com
OpenSIPS eBootcamp 2021
   https://opensips.org/training/OpenSIPS_eBootcamp_2021/

On 12/20/21 6:58 PM, Jonathan Hunter wrote:
>
> Hi Guys,
>
> Note this query seems to work with my postgres;
>
> SELECT a.sip_username,b.secret from jwt_profiles a inner join 
> jwt_secrets b on a.tag = b.corresponding_tag  where 
> a.tag='space-monkey' and extract(epoch from now()) >= b.start_ts and 
> extract(epoch from now()) < b.end_ts;
>
> Doe I need to patch for his or should it work with postgres anyway? 
> Thanks!
>
> Jon
>
> Sent from Mail <https://go.microsoft.com/fwlink/?LinkId=550986> for 
> Windows
>
> ------------------------------------------------------------------------
> *From:* Users <users-bounces at lists.opensips.org> on behalf of Jonathan 
> Hunter <hunterj91 at hotmail.com>
> *Sent:* Monday, December 20, 2021 2:21:08 PM
> *To:* OpenSIPS users mailling list <users at lists.opensips.org>
> *Subject:* [OpenSIPS-Users] Opensips 3.2 from sources testing auth_jwt 
> with postgres database backend.
>
> Hi guys, I am testing the auth_jwt module with a postgres backend 
> database and it appears to be using Mysql syntax, so I am getting 
> error below, as I dont think UNIX_TIMESTAMP is a postgres function and 
> its not happy with the quotes.
>
> See output below, just testing with the example tag;
>
> Dec 20 14:08:56 [13688] --[96gpfj5qgkseqevkhv5a] RT_REGISTER token is 
> eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ0YWciOiJzcGFjZS1tb25rZXkifQ.4JxP0_a-l6uDhjP50JaocheyvgQGhB-0zJsYpFTACkgDec 
> 20 14:08:56 [13688] DBG:auth_jwt:jwt_authorize: Decoded JWT and found 
> claim tag with value space-monkey
>
> Dec 20 14:08:56 [13688] DBG:auth_jwt:jwt_authorize: built JWT raw db 
> query [SELECT a.sip_username,b.secret from jwt_profiles a inner join 
> jwt_secrets b on a.tag = b.corresponding_tag  where 
> a.tag="space-monkey" and UNIX_TIMESTAMP() >= b.start_ts and 
> UNIX_TIMESTAMP() < b.end_ts]
>
> Dec 20 14:08:56 [13688] DBG:db_postgres:db_postgres_submit_query: 
> 0x7fa87dcac018 PQsendQuery(SELECT a.sip_username,b.secret from 
> jwt_profiles a inner join jwt_secrets b on a.tag = b.corresponding_tag 
> where a.tag="space-monkey" and UNIX_TIMESTAMP() >= b.start_ts and 
> UNIX_TIMESTAMP() < b.end_ts)
>
> Dec 20 14:08:56 [13688] DBG:db_postgres:db_postgres_submit_query: 
> 0x7fa87dcac018 PQsendQuery failed: ERROR:  column "space-monkey" does 
> not exist
>
> LINE 1: ...ets b on a.tag = b.corresponding_tag  where a.tag="space-mon...
>
> ^
>
> Query: SELECT a.sip_username,b.secret from jwt_profiles a inner join 
> jwt_secrets b on a.tag = b.corresponding_tag  where 
> a.tag="space-monkey" and UNIX_TIMESTAMP() >= b.start_ts and 
> UNIX_TIMESTAMP() < b.end_ts
>
> Dec 20 14:08:56 [13688] DBG:db_postgres:free_query: 
> PQclear(0x556a28109850) result set
>
> Dec 20 14:08:56 [13688] ERROR:db_postgres:db_postgres_submit_query: 
> 0x7fa87dcac018 PQsendQuery Error: ERROR:  column "space-monkey" does 
> not exist
>
> LINE 1: ...ets b on a.tag = b.corresponding_tag  where a.tag="space-mon...
>
> ^
>
> Query: SELECT a.sip_username,b.secret from jwt_profiles a inner join 
> jwt_secrets b on a.tag = b.corresponding_tag  where 
> a.tag="space-monkey" and UNIX_TIMESTAMP() >= b.start_ts and 
> UNIX_TIMESTAMP() < b.end_ts
>
> Dec 20 14:08:56 [13688] ERROR:core:db_do_raw_query: error while 
> submitting query
>
> Dec 20 14:08:56 [13688] ERROR:auth_jwt:jwt_authorize: raw_query failed
>
> I have installed from sources, am I missing something here or missed a 
> step? Any help would be great as really want to get this module working.
>
> Thanks!
>
> Jon
>
> Sent from Mail 
> <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgo.microsoft.com%2Ffwlink%2F%3FLinkId%3D550986&data=04%7C01%7C%7Ca2a8f434cba54f6ba8a808d9c3c454d4%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756070299989293%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=7ehY4gexw7Rds%2ByLlhPeZ5qKSWEHWbl8UUwvNUeHiFk%3D&reserved=0> 
> for Windows
>
>
> _______________________________________________
> Users mailing list
> Users at lists.opensips.org
> http://lists.opensips.org/cgi-bin/mailman/listinfo/users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.opensips.org/pipermail/users/attachments/20211221/20c44601/attachment.html>