[OpenSIPS-Users] Opensips 3.2 from sources testing auth_jwt with postgres database backend.
Bogdan-Andrei Iancu
bogdan at opensips.org
Tue Dec 21 12:48:16 UTC 2021
Could you please try this ?
Regards,
Bogdan-Andrei Iancu
OpenSIPS Founder and Developer
https://www.opensips-solutions.com
OpenSIPS eBootcamp 2021
https://opensips.org/training/OpenSIPS_eBootcamp_2021/
On 12/21/21 1:52 PM, Bogdan-Andrei Iancu wrote:
> Thank you Jonathan,
>
> I guess the proper fix here will be to push the unixtimestamp from the
> code level, rather from DB level - in this way we can get an SQL
> compliant query. Let me send you a patch for testing
>
> Regards,
> Bogdan-Andrei Iancu
>
> OpenSIPS Founder and Developer
> https://www.opensips-solutions.com
> OpenSIPS eBootcamp 2021
> https://opensips.org/training/OpenSIPS_eBootcamp_2021/
> On 12/21/21 12:29 PM, Jonathan Hunter wrote:
>>
>> Hi Bogdan,
>>
>> Thanks for the reply.
>>
>> I have modified as below so this works with postgres, essentially
>> just changed the syntax as below;
>>
>> ~/opensips-3.2/modules/auth_jwt/authorize.c
>>
>> -n = snprintf(p,len," from %.*s a inner join %.*s b on a.%.*s =
>> b.%.*s where a.%.*s=\"%.*s\" and UNIX_TIMESTAMP() >= b.%.*s and
>> UNIX_TIMESTAMP() < b.%.*s",
>>
>> +n = snprintf(p,len," from %.*s a inner join %.*s b on a.%.*s =
>> b.%.*s where a.%.*s=\'%.*s\' and extract(epoch from now()) >= b.%.*s
>> and extract(epoch from now()) < b.%.*s",
>>
>> I would assume there needs some further modification depending on if
>> its MySQL or postgres as currently I have just changed as above and
>> haven’t tested if it works for the other backend types.
>>
>> I hope that’s enough detail for you? If you need anything else let me
>> know!
>>
>> Thanks
>>
>> Jon
>>
>> Sent from Mail <https://go.microsoft.com/fwlink/?LinkId=550986> for
>> Windows
>>
>> *From: *Bogdan-Andrei Iancu <mailto:bogdan at opensips.org>
>> *Sent: *21 December 2021 08:36
>> *To: *OpenSIPS users mailling list <mailto:users at lists.opensips.org>;
>> Jonathan Hunter <mailto:hunterj91 at hotmail.com>
>> *Subject: *Re: [OpenSIPS-Users] Opensips 3.2 from sources testing
>> auth_jwt with postgres database backend.
>>
>> 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 <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.opensips-solutions.com%2F&data=04%7C01%7C%7C5987f85cfd664971e45208d9c45ceac9%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756725636784536%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=gBYthg1JyMKs8BcUY%2Ft3OvVvpzxNBWzIKUDnCJBvtAM%3D&reserved=0>
>> OpenSIPS eBootcamp 2021
>> https://opensips.org/training/OpenSIPS_eBootcamp_2021/ <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fopensips.org%2Ftraining%2FOpenSIPS_eBootcamp_2021%2F&data=04%7C01%7C%7C5987f85cfd664971e45208d9c45ceac9%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756725636794494%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=gIo3IyIeaQJTe59rxRAQP7HJa5Yzv9DBUn%2B%2BANBGqzk%3D&reserved=0>
>>
>> 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://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgo.microsoft.com%2Ffwlink%2F%3FLinkId%3D550986&data=04%7C01%7C%7C5987f85cfd664971e45208d9c45ceac9%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756725636804448%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=Nw1GfZ%2Fjk4DU4iDzQa5KAabF3VKKRSadSNHPIp5H%2FQg%3D&reserved=0>
>> for Windows
>>
>> *From:*Users <users-bounces at lists.opensips.org>
>> <mailto:users-bounces at lists.opensips.org> on behalf of Jonathan
>> Hunter <hunterj91 at hotmail.com> <mailto:hunterj91 at hotmail.com>
>> *Sent:* Monday, December 20, 2021 2:21:08 PM
>> *To:* OpenSIPS users mailling list <users at lists.opensips.org>
>> <mailto: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%7C5987f85cfd664971e45208d9c45ceac9%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756725636804448%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=Nw1GfZ%2Fjk4DU4iDzQa5KAabF3VKKRSadSNHPIp5H%2FQg%3D&reserved=0>
>> for Windows
>>
>>
>>
>> _______________________________________________
>>
>> Users mailing list
>>
>> Users at lists.opensips.org <mailto:Users at lists.opensips.org>
>>
>> http://lists.opensips.org/cgi-bin/mailman/listinfo/users <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Flists.opensips.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fusers&data=04%7C01%7C%7C5987f85cfd664971e45208d9c45ceac9%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756725636814408%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=5gEZIA9TrXbMZhAXuOz5bn0MR99zHJ%2FN8qXvYl615AA%3D&reserved=0>
>>
>
>
> _______________________________________________
> 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/4f518341/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: D090FCD0143F48D6A511F3A0B0D24601.png
Type: image/png
Size: 158 bytes
Desc: not available
URL: <http://lists.opensips.org/pipermail/users/attachments/20211221/4f518341/attachment-0001.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: opensips_3_2_auth_jwt_fix.patch
Type: text/x-patch
Size: 1423 bytes
Desc: not available
URL: <http://lists.opensips.org/pipermail/users/attachments/20211221/4f518341/attachment-0001.bin>
More information about the Users
mailing list