<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
</head>
<body>
<font face="monospace">Hi Jonathan,<br>
<br>
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 ?<br>
<br>
Thanks,<br>
</font>
<pre class="moz-signature" cols="72">Bogdan-Andrei Iancu
OpenSIPS Founder and Developer
<a class="moz-txt-link-freetext" href="https://www.opensips-solutions.com">https://www.opensips-solutions.com</a>
OpenSIPS eBootcamp 2021
<a class="moz-txt-link-freetext" href="https://opensips.org/training/OpenSIPS_eBootcamp_2021/">https://opensips.org/training/OpenSIPS_eBootcamp_2021/</a></pre>
<div class="moz-cite-prefix">On 12/20/21 6:58 PM, Jonathan Hunter
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:AS8P195MB1287EFA16738FEF8E303C8C3BD7B9@AS8P195MB1287.EURP195.PROD.OUTLOOK.COM">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<meta name="Generator" content="Microsoft Word 15 (filtered
medium)">
<style>@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}.MsoChpDefault
{mso-style-type:export-only;}div.WordSection1
{page:WordSection1;}</style>
<div class="WordSection1">
<p class="MsoNormal">Hi Guys, </p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Note this query seems to work with my
postgres;</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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;</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Doe I need to patch for his or should it
work with postgres anyway? Thanks!</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Jon</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Sent from <a
href="https://go.microsoft.com/fwlink/?LinkId=550986"
moz-do-not-send="true">
Mail</a> for Windows</p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font style="font-size:11pt"
face="Calibri, sans-serif" color="#000000"><b>From:</b> Users
<a class="moz-txt-link-rfc2396E" href="mailto:users-bounces@lists.opensips.org"><users-bounces@lists.opensips.org></a> on behalf of Jonathan
Hunter <a class="moz-txt-link-rfc2396E" href="mailto:hunterj91@hotmail.com"><hunterj91@hotmail.com></a><br>
<b>Sent:</b> Monday, December 20, 2021 2:21:08 PM<br>
<b>To:</b> OpenSIPS users mailling list
<a class="moz-txt-link-rfc2396E" href="mailto:users@lists.opensips.org"><users@lists.opensips.org></a><br>
<b>Subject:</b> [OpenSIPS-Users] Opensips 3.2 from sources
testing auth_jwt with postgres database backend.</font>
<div> </div>
</div>
<style>@font-face
{font-family:"Cambria Math"}@font-face
{font-family:Calibri}p.x_MsoNormal, li.x_MsoNormal, div.x_MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif}a:link, span.x_MsoHyperlink
{color:blue;
text-decoration:underline}.x_MsoChpDefault
{}div.x_WordSection1
{}</style>
<div link="blue" vlink="#954F72" style="word-wrap:break-word"
lang="EN-GB">
<div class="x_WordSection1">
<p class="x_MsoNormal">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.</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">See output below, just testing with the
example tag;</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">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</p>
<p class="x_MsoNormal">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]</p>
<p class="x_MsoNormal">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)</p>
<p class="x_MsoNormal">Dec 20 14:08:56 [13688]
DBG:db_postgres:db_postgres_submit_query: 0x7fa87dcac018
PQsendQuery failed: ERROR: column "space-monkey" does not
exist</p>
<p class="x_MsoNormal">LINE 1: ...ets b on a.tag =
b.corresponding_tag where a.tag="space-mon...</p>
<p class="x_MsoNormal">
^</p>
<p class="x_MsoNormal">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</p>
<p class="x_MsoNormal">Dec 20 14:08:56 [13688]
DBG:db_postgres:free_query: PQclear(0x556a28109850) result
set</p>
<p class="x_MsoNormal">Dec 20 14:08:56 [13688]
ERROR:db_postgres:db_postgres_submit_query: 0x7fa87dcac018
PQsendQuery Error: ERROR: column "space-monkey" does not
exist</p>
<p class="x_MsoNormal">LINE 1: ...ets b on a.tag =
b.corresponding_tag where a.tag="space-mon...</p>
<p class="x_MsoNormal">
^</p>
<p class="x_MsoNormal">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</p>
<p class="x_MsoNormal">Dec 20 14:08:56 [13688]
ERROR:core:db_do_raw_query: error while submitting query</p>
<p class="x_MsoNormal">Dec 20 14:08:56 [13688]
ERROR:auth_jwt:jwt_authorize: raw_query failed</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">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.</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">Thanks!</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">Jon</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">Sent from <a
href="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"
originalsrc="https://go.microsoft.com/fwlink/?LinkId=550986"
shash="YBtU6yWkXbOY4RT9KWD5w3eDrW2+Wza9duIPU9pp0Vg8TiMM1syrK6OoM/gRiy9T6VN1UY6DKF217/+3oSdiKUBID9FQpN9uZLFxGleEpuI4BVbMeclb30B6qIt5NvRNYu793dPSwh7BireRKWxynhOEsVcWzXytI1oGbOUFsgI="
moz-do-not-send="true">
Mail</a> for Windows</p>
<p class="x_MsoNormal"> </p>
</div>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
Users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Users@lists.opensips.org">Users@lists.opensips.org</a>
<a class="moz-txt-link-freetext" href="http://lists.opensips.org/cgi-bin/mailman/listinfo/users">http://lists.opensips.org/cgi-bin/mailman/listinfo/users</a>
</pre>
</blockquote>
<br>
</body>
</html>