<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@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;}
/* Style Definitions */
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;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:"Courier New";}
p.xmsonormal, li.xmsonormal, div.xmsonormal
{mso-style-name:x_msonormal;
margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style>
</head>
<body lang="EN-GB" link="blue" vlink="#954F72" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal">Hi Bogdan,</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Thanks for the reply.</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I have modified as below so this works with postgres, essentially just changed the syntax as below;</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">~/opensips-3.2/modules/auth_jwt/authorize.c</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">-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",</p>
<p class="MsoNormal">+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",</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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.</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I hope that’s enough detail for you? If you need anything else let me know!</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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">
Mail</a> for Windows</p>
<p class="MsoNormal"><o:p> </o:p></p>
<div style="mso-element:para-border-div;border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal" style="border:none;padding:0cm"><b>From: </b><a href="mailto:bogdan@opensips.org">Bogdan-Andrei Iancu</a><br>
<b>Sent: </b>21 December 2021 08:36<br>
<b>To: </b><a href="mailto:users@lists.opensips.org">OpenSIPS users mailling list</a>;
<a href="mailto:hunterj91@hotmail.com">Jonathan Hunter</a><br>
<b>Subject: </b>Re: [OpenSIPS-Users] Opensips 3.2 from sources testing auth_jwt with postgres database backend.</p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">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>
<br>
</span><o:p></o:p></p>
<pre>Bogdan-Andrei Iancu</pre>
<pre><o:p> </o:p></pre>
<pre>OpenSIPS Founder and Developer</pre>
<pre> <a href="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">https://www.opensips-solutions.com</a></pre>
<pre>OpenSIPS eBootcamp 2021 </pre>
<pre> <a href="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">https://opensips.org/training/OpenSIPS_eBootcamp_2021/</a></pre>
<div>
<p class="MsoNormal">On 12/20/21 6:58 PM, Jonathan Hunter wrote:<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p class="MsoNormal">Hi Guys, <o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Note this query seems to work with my postgres;<o:p></o:p></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;<o:p></o:p></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!<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Jon<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="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%7C5987f85cfd664971e45208d9c45ceac9%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756725636804448%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=Nw1GfZ%2Fjk4DU4iDzQa5KAabF3VKKRSadSNHPIp5H%2FQg%3D&reserved=0">
Mail</a> for Windows<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"><img border="0" width="369" height="2" style="width:3.8472in;height:.0208in" id="Horizontal_x0020_Line_x0020_1" src="cid:image002.png@01D7F655.9B805190"><o:p></o:p></p>
<div id="divRplyFwdMsg">
<p class="MsoNormal"><b><span style="color:black">From:</span></b><span style="color:black"> Users
<a href="mailto:users-bounces@lists.opensips.org"><users-bounces@lists.opensips.org></a> on behalf of Jonathan Hunter
<a 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 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.</span>
<o:p></o:p></p>
<div>
<p class="MsoNormal"> <o:p></o:p></p>
</div>
</div>
<div>
<div>
<p class="xmsonormal">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="xmsonormal"> </p>
<p class="xmsonormal">See output below, just testing with the example tag;</p>
<p class="xmsonormal"> </p>
<p class="xmsonormal"> </p>
<p class="xmsonormal">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="xmsonormal">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="xmsonormal">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="xmsonormal">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="xmsonormal">LINE 1: ...ets b on a.tag = b.corresponding_tag where a.tag="space-mon...</p>
<p class="xmsonormal"> ^</p>
<p class="xmsonormal">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="xmsonormal">Dec 20 14:08:56 [13688] DBG:db_postgres:free_query: PQclear(0x556a28109850) result set</p>
<p class="xmsonormal">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="xmsonormal">LINE 1: ...ets b on a.tag = b.corresponding_tag where a.tag="space-mon...</p>
<p class="xmsonormal"> ^</p>
<p class="xmsonormal">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="xmsonormal">Dec 20 14:08:56 [13688] ERROR:core:db_do_raw_query: error while submitting query</p>
<p class="xmsonormal">Dec 20 14:08:56 [13688] ERROR:auth_jwt:jwt_authorize: raw_query failed</p>
<p class="xmsonormal"> </p>
<p class="xmsonormal"> </p>
<p class="xmsonormal">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="xmsonormal"> </p>
<p class="xmsonormal">Thanks!</p>
<p class="xmsonormal"> </p>
<p class="xmsonormal">Jon</p>
<p class="xmsonormal"> </p>
<p class="xmsonormal">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%7C5987f85cfd664971e45208d9c45ceac9%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756725636804448%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=Nw1GfZ%2Fjk4DU4iDzQa5KAabF3VKKRSadSNHPIp5H%2FQg%3D&reserved=0">
Mail</a> for Windows</p>
<p class="xmsonormal"> </p>
</div>
</div>
<p class="MsoNormal"><br>
<br>
<o:p></o:p></p>
<pre>_______________________________________________</pre>
<pre>Users mailing list</pre>
<pre><a href="mailto:Users@lists.opensips.org">Users@lists.opensips.org</a></pre>
<pre><a href="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">http://lists.opensips.org/cgi-bin/mailman/listinfo/users</a></pre>
</blockquote>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</body>
</html>