<html 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=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<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;}
.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 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">
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 face="Calibri, sans-serif" style="font-size:11pt" color="#000000"><b>From:</b> Users <users-bounces@lists.opensips.org> on behalf of Jonathan Hunter <hunterj91@hotmail.com><br>
<b>Sent:</b> Monday, December 20, 2021 2:21:08 PM<br>
<b>To:</b> OpenSIPS users mailling list <users@lists.opensips.org><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
{}
@page WordSection1
{margin:72.0pt 72.0pt 72.0pt 72.0pt}
div.x_WordSection1
{}
-->
</style>
<div lang="EN-GB" link="blue" vlink="#954F72" style="word-wrap:break-word">
<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=">
Mail</a> for Windows</p>
<p class="x_MsoNormal"> </p>
</div>
</div>
</body>
</html>