Case-insensitive username in OpenIDM/Postgres

This topic has 1 reply, 1 voice, and was last updated 5 months, 2 weeks ago by [email protected].

  • Author
  • #17531


    I need to make username case-insensitive in an OpenIDM/Postgres setup. Postgres does not support case-insensitivity out of the box. There is an extension called citext you can install to support case-insensitive text data type and string comparison but apparently, it does not work well with jdbc. I know another solution is to use lower/upper functions in repo.jdbc.json. However, queryFilter does not use those predefined queries.
    Does anyone here know the solution?



    Hi Seyed,

    You can make the changes in repo.jdbc.json which is present in conf directory. Add new query inside generic tables like the way as mentioned below:

    “for-userNameCustom” : “SELECT obj.objectid, obj.rev, obj.fullobject::text FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.objecttypes objtype ON = obj.objecttypes_id WHERE lower(json_extract_path_text(fullobject, ‘userName’)) = lower(${uid}) AND objtype.objecttype = ${_resource}”,

    I have used lower function here.

    Now create a new policy in policy.js:

    { “policyId” : “uniqueLC”,
    “policyExec” : “uniqueLC”,
    “policyRequirements” : [“UNIQUE_LC”]

    policyFunctions.uniqueLC = function(fullObject, value, params, property) {
    var queryParams,existing,requestId,requestBaseArray,resourcePath;
    params = params || {};
    if (value && value.length) {
    resourcePath = params.resourcePath || resourceName.parent().toString();

    requestId = resourceName.leaf();

    existing = openidm.query(resourcePath, { “_queryId”: “for-userNameCustom”, “uid”: value });

    if (existing.result.length !== 0 && (!requestId || (existing.result[0]._id != requestId))) {
    return [{“policyRequirement”: “UNIQUE”}];
    return [];

    This will definitely going to resolve your problem.

    Thanks & Regards
    Nisha Garg

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.

©2020 ForgeRock - we provide an identity and access platform to secure every online relationship for the enterprise market, educational sector and even entire countries. Click to view our privacy policy and terms of use.

Log in with your credentials

Forgot your details?