Postgres JDBC - Entitlements not showing in Manage User Access > Remove Access

[Viraj (Viraj Taywade)]

I am working on a PostgreSQL JDBC connector integration with SailPoint IdentityIQ and facing an issue where entitlements aggregated from PostgreSQL are not appearing under Manage User Access > Remove Access.
The entitlements have been assigned to the user, are clearly visible in the PostgreSQL database, and are also showing correctly on the Identity > Entitlements tab inside SailPoint. However they do not appear when trying to remove access via Manage User Access.

[neel193 (Neelmadhav Panda)]

@Viraj Please execute Refresh task on users with option: Refresh Identity Entitlements for all links & Refresh assigned, detected roles and promote additional entitlements. This should allow entitlements to visible on manaye user access page.

[Viraj (Viraj Taywade)]

Did, still aint able to see entitlements of Postgres applicaiton but i can see the other entitlements of linux application assigned to that user

[neel193 (Neelmadhav Panda)]

@Viraj Could you please share your app xml ? Need to review schema attributes and confirm what is the entitlement attribute?

[Viraj (Viraj Taywade)]

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE Application PUBLIC "sailpoint.dtd" "sailpoint.dtd">
<Application connector="sailpoint.connector.JDBCConnector"
  featuresString="DISCOVER_SCHEMA, PROVISIONING, SYNC_PROVISIONING, DIRECT_PERMISSIONS, SEARCH, ENABLE, UNLOCK, ADDITIONAL_ACCOUNT_REQUEST, ACCOUNT_ONLY_REQUEST, PASSWORD"
  icon="databaseIcon"
  id="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
  name="Postgres"
  profileClass=""
  type="JDBC">
  <Attributes>
    <Map>
      <entry key="SQL" value="SELECT &#xD;&#xA;    r.rolname       AS name,&#xD;&#xA;    r.rolcanlogin   AS can_login,&#xD;&#xA;    r.rolvaliduntil AS expiry_date,&#xD;&#xA;    role.rolname    AS granted_role&#xD;&#xA;FROM pg_roles r&#xD;&#xA;LEFT JOIN pg_auth_members mem ON mem.member = r.oid&#xD;&#xA;LEFT JOIN pg_roles role ON mem.roleid = role.oid&#xD;&#xA;WHERE r.rolname IS NOT NULL&#xD;&#xA;  AND r.rolname &lt;&gt; &apos;null&apos;&#xD;&#xA;ORDER BY r.rolname;"/>
      <entry key="afterProvisioningRule"/>
      <entry key="aggregationMode">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="aggregationPartitioned">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="aggregationType" value="account"/>
      <entry key="beforeProvisioningRule"/>
      <entry key="compositeDefinition"/>
      <entry key="connMaxRetry" value="3"/>
      <entry key="connRetryEnable">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="connWaitTimeForRetry" value="2"/>
      <entry key="deltaTable"/>
      <entry key="driverClass" value="org.postgresql.Driver"/>
      <entry key="getDeltaSQL"/>

      <!-- Group (Entitlement) Aggregation SQL -->
      <entry key="group.SQL" value="SELECT rolname AS name&#xD;&#xA;FROM   pg_roles&#xD;&#xA;WHERE  rolcanlogin = false&#xD;&#xA;ORDER  BY rolname;"/>
      <entry key="group.aggregationMode">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="group.deltaTable"/>
      <entry key="group.driverClass" value="org.postgresql.Driver"/>
      <entry key="group.getDeltaSQL"/>
      <entry key="group.getObjectSQL" value="SELECT rolname AS name&#xD;&#xA;FROM   pg_roles&#xD;&#xA;WHERE  rolname = &apos;%name%&apos;;"/>
      <entry key="group.isPermissionEnabled">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="group.mergeRows">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="group.password" value="1:ACP:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"/>
      <entry key="group.url" value="jdbc:postgresql://localhost:5432/identity_db"/>
      <entry key="group.useExecuteQuery">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="group.useStoredProcedureDelta">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="group.useStoredProcedureDirectPermission">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="group.useStoredProcedureGetObject">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="group.useStoredProcedureSqlStmt">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="group.user" value="sailpoint_user"/>

      <entry key="isPermissionEnabled">
        <value><Boolean></Boolean></value>
      </entry>

      <!-- Provisioning Rules -->
      <entry key="jdbcCreateProvisioningRule"  value="PostgreSQL_CreateAccount_Rule"/>
      <entry key="jdbcDisableProvisioningRule" value="PostgreSQL_DisableAccount_Rule"/>
      <entry key="jdbcEnableProvisioningRule"  value="PostgreSQL_EnableAccount_Rule"/>
      <entry key="jdbcModifyProvisioningRule"  value="PostgreSQL_Modify_Rule"/>
      <entry key="jdbcExceptionBucketing">
        <value><Boolean>true</Boolean></value>
      </entry>

      <entry key="mergeRows">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="nativeChangeDetectionAttributeScope" value="entitlements"/>
      <entry key="nativeChangeDetectionAttributes"/>
      <entry key="nativeChangeDetectionEnabled">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="nativeChangeDetectionOperations"/>
      <entry key="noPermissions" value="false"/>
      <entry key="operations" value="Create, Modify, Delete, Enable, Disable, Unlock, SetPassword"/>
      <entry key="partitionMode">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="partitionStatements"/>
      <entry key="password" value="1:ACP:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"/>
      <entry key="provisionRule" value="operationRule"/>
      <entry key="sysDescriptions">
        <value>
          <Map>
            <entry key="en_US"/>
          </Map>
        </value>
      </entry>
      <entry key="templateApplication" value="JDBC Template"/>
      <entry key="testConnSQL"/>
      <entry key="url" value="jdbc:postgresql://localhost:5432/identity_db"/>
      <entry key="useExecuteQuery">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="useStoredProcedureDelta">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="useStoredProcedureDirectPermission">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="useStoredProcedureGetObject">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="useStoredProcedurePartition">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="useStoredProcedureSqlStmt">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="useStoredProcedureTestConn">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="user" value="sailpoint_user"/>
    </Map>
  </Attributes>

  <CorrelationRule>
    <Reference class="sailpoint.object.Rule" id="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" name="postgrescorrelation"/>
  </CorrelationRule>

  <CustomizationRule>
    <Reference class="sailpoint.object.Rule" id="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" name="PostgresDisableCustomisation"/>
  </CustomizationRule>

  <Owner>
    <Reference class="sailpoint.object.Identity" id="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" name="spadmin"/>
  </Owner>

  <ProvisioningForms>
    <Form name="PostgreSQL_CreateAccount_Form" objectType="account" type="Create">
      <Attributes>
        <Map>
          <entry key="pageTitle" value="PostgreSQL_CreateAccount_Form"/>
        </Map>
      </Attributes>
      <Section label="Create Account" name="Create Account">

        <!-- Username auto-generated from employeeId: q + employeeId -->
        <Field displayName="Username" name="name" required="true" type="string">
          <Script>
            <Source>
              import sailpoint.object.Identity;
              String empId = null;
              if (identity != null) {
                empId = (String) identity.getAttribute("employeeId");
              }
              if (empId != null &amp;&amp; !empId.trim().equals("")) {
                return "q" + empId.trim();
              }
              return null;
            </Source>
          </Script>
        </Field>

        <!-- Password auto-generated: 16 chars, upper+lower+digit+special -->
        <Field displayName="Password" name="password" required="true" type="secret">
          <Script>
            <Source>
              import java.security.SecureRandom;
              String chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&amp;*";
              SecureRandom rng = new SecureRandom();
              StringBuilder sb = new StringBuilder(16);
              sb.append(chars.charAt(rng.nextInt(26)));
              sb.append(chars.charAt(26 + rng.nextInt(26)));
              sb.append(chars.charAt(52 + rng.nextInt(10)));
              sb.append(chars.charAt(62 + rng.nextInt(chars.length() - 62)));
              for (int i = 4; i &lt; 16; i++) {
                sb.append(chars.charAt(rng.nextInt(chars.length())));
              }
              char[] arr = sb.toString().toCharArray();
              for (int i = arr.length - 1; i > 0; i--) {
                int j = rng.nextInt(i + 1);
                char tmp = arr[i]; arr[i] = arr[j]; arr[j] = tmp;
              }
              return new String(arr);
            </Source>
          </Script>
        </Field>

        <Field displayName="Can Login" name="can_login" required="true" type="string" value="true"/>

        <!-- Expiry Date set to 2 years from today -->
        <Field displayName="Expiry Date" name="expiry_date" required="true" type="string">
          <Script>
            <Source>
              import java.util.Calendar;
              import java.text.SimpleDateFormat;
              Calendar cal = Calendar.getInstance();
              cal.add(Calendar.YEAR, 2);
              SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
              return sdf.format(cal.getTime());
            </Source>
          </Script>
        </Field>

        <Field displayName="Granted Role" multi="true" name="granted_role" type="string"/>

      </Section>
    </Form>
  </ProvisioningForms>

  <Schemas>
    <!-- Account Schema -->
    <Schema
      displayAttribute="name"
      id="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
      identityAttribute="name"
      instanceAttribute=""
      nativeObjectType="account"
      objectType="account"
      permissionsRemediationModificationType="None">
      <AttributeDefinition name="name"         type="string"/>
      <AttributeDefinition name="can_login"    type="string"/>
      <AttributeDefinition name="expiry_date"  type="string"/>
      <AttributeDefinition entitlement="true" managed="true" multi="true"
        name="granted_role" schemaObjectType="group" type="string"/>
    </Schema>

    <!-- Group (Entitlement) Schema -->
    <Schema
      descriptionAttribute=""
      displayAttribute="name"
      id="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
      identityAttribute="name"
      instanceAttribute=""
      nativeObjectType="group"
      objectType="group"
      permissionsRemediationModificationType="None">
      <AttributeDefinition name="name" type="string"/>
    </Schema>
  </Schemas>

</Application>

[neel193 (Neelmadhav Panda)]

@Viraj Schema looks good. Could you please also confirm are you having any RequestObjectSelector rule in your enviornment? Please ping me over chat for quicker communication,.

[haideralishaik (Shaik Haiderali)]

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE Application PUBLIC "sailpoint.dtd" "sailpoint.dtd">
<Application connector="sailpoint.connector.JDBCConnector"
  featuresString="DISCOVER_SCHEMA, PROVISIONING, SYNC_PROVISIONING, DIRECT_PERMISSIONS, SEARCH, ENABLE, UNLOCK, ADDITIONAL_ACCOUNT_REQUEST, ACCOUNT_ONLY_REQUEST, PASSWORD"
  icon="databaseIcon"
  id="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
  name="Postgres"
  profileClass=""
  type="JDBC">
  <Attributes>
    <Map>
      <entry key="SQL" value="SELECT &#xD;&#xA;    r.rolname       AS name,&#xD;&#xA;    r.rolcanlogin   AS can_login,&#xD;&#xA;    r.rolvaliduntil AS expiry_date,&#xD;&#xA;    role.rolname    AS granted_role&#xD;&#xA;FROM pg_roles r&#xD;&#xA;LEFT JOIN pg_auth_members mem ON mem.member = r.oid&#xD;&#xA;LEFT JOIN pg_roles role ON mem.roleid = role.oid&#xD;&#xA;WHERE r.rolname IS NOT NULL&#xD;&#xA;  AND r.rolname &lt;&gt; &apos;null&apos;&#xD;&#xA;ORDER BY r.rolname;"/>
      <entry key="afterProvisioningRule"/>
      <entry key="aggregationMode">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="aggregationPartitioned">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="aggregationType" value="account"/>
      <entry key="beforeProvisioningRule"/>
      <entry key="compositeDefinition"/>
      <entry key="connMaxRetry" value="3"/>
      <entry key="connRetryEnable">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="connWaitTimeForRetry" value="2"/>
      <entry key="deltaTable"/>
      <entry key="driverClass" value="org.postgresql.Driver"/>
      <entry key="getDeltaSQL"/>

      <!-- Group (Entitlement) Aggregation SQL -->
      <entry key="group.SQL" value="SELECT rolname AS name&#xD;&#xA;FROM   pg_roles&#xD;&#xA;WHERE  rolcanlogin = false&#xD;&#xA;ORDER  BY rolname;"/>
      <entry key="group.aggregationMode">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="group.deltaTable"/>
      <entry key="group.driverClass" value="org.postgresql.Driver"/>
      <entry key="group.getDeltaSQL"/>
      <entry key="group.getObjectSQL" value="SELECT rolname AS name&#xD;&#xA;FROM   pg_roles&#xD;&#xA;WHERE  rolname = &apos;%name%&apos;;"/>
      <entry key="group.isPermissionEnabled">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="group.mergeRows">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="group.password" value="1:ACP:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"/>
      <entry key="group.url" value="jdbc:postgresql://localhost:5432/identity_db"/>
      <entry key="group.useExecuteQuery">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="group.useStoredProcedureDelta">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="group.useStoredProcedureDirectPermission">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="group.useStoredProcedureGetObject">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="group.useStoredProcedureSqlStmt">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="group.user" value="sailpoint_user"/>

      <entry key="isPermissionEnabled">
        <value><Boolean></Boolean></value>
      </entry>

      <!-- Provisioning Rules -->
      <entry key="jdbcCreateProvisioningRule"  value="PostgreSQL_CreateAccount_Rule"/>
      <entry key="jdbcDisableProvisioningRule" value="PostgreSQL_DisableAccount_Rule"/>
      <entry key="jdbcEnableProvisioningRule"  value="PostgreSQL_EnableAccount_Rule"/>
      <entry key="jdbcModifyProvisioningRule"  value="PostgreSQL_Modify_Rule"/>
      <entry key="jdbcExceptionBucketing">
        <value><Boolean>true</Boolean></value>
      </entry>

      <entry key="mergeRows">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="nativeChangeDetectionAttributeScope" value="entitlements"/>
      <entry key="nativeChangeDetectionAttributes"/>
      <entry key="nativeChangeDetectionEnabled">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="nativeChangeDetectionOperations"/>
      <entry key="noPermissions" value="false"/>
      <entry key="operations" value="Create, Modify, Delete, Enable, Disable, Unlock, SetPassword"/>
      <entry key="partitionMode">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="partitionStatements"/>
      <entry key="password" value="1:ACP:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"/>
      <entry key="provisionRule" value="operationRule"/>
      <entry key="sysDescriptions">
        <value>
          <Map>
            <entry key="en_US"/>
          </Map>
        </value>
      </entry>
      <entry key="templateApplication" value="JDBC Template"/>
      <entry key="testConnSQL"/>
      <entry key="url" value="jdbc:postgresql://localhost:5432/identity_db"/>
      <entry key="useExecuteQuery">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="useStoredProcedureDelta">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="useStoredProcedureDirectPermission">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="useStoredProcedureGetObject">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="useStoredProcedurePartition">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="useStoredProcedureSqlStmt">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="useStoredProcedureTestConn">
        <value><Boolean></Boolean></value>
      </entry>
      <entry key="user" value="sailpoint_user"/>
    </Map>
  </Attributes>

  <CorrelationRule>
    <Reference class="sailpoint.object.Rule" id="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" name="postgrescorrelation"/>
  </CorrelationRule>

  <CustomizationRule>
    <Reference class="sailpoint.object.Rule" id="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" name="PostgresDisableCustomisation"/>
  </CustomizationRule>

  <Owner>
    <Reference class="sailpoint.object.Identity" id="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" name="spadmin"/>
  </Owner>

  <ProvisioningForms>
    <Form name="PostgreSQL_CreateAccount_Form" objectType="account" type="Create">
      <Attributes>
        <Map>
          <entry key="pageTitle" value="PostgreSQL_CreateAccount_Form"/>
        </Map>
      </Attributes>
      <Section label="Create Account" name="Create Account">

        <!-- Username auto-generated from employeeId: q + employeeId -->
        <Field displayName="Username" name="name" required="true" type="string">
          <Script>
            <Source>
              import sailpoint.object.Identity;
              String empId = null;
              if (identity != null) {
                empId = (String) identity.getAttribute("employeeId");
              }
              if (empId != null &amp;&amp; !empId.trim().equals("")) {
                return "q" + empId.trim();
              }
              return null;
            </Source>
          </Script>
        </Field>

        <!-- Password auto-generated: 16 chars, upper+lower+digit+special -->
        <Field displayName="Password" name="password" required="true" type="secret">
          <Script>
            <Source>
              import java.security.SecureRandom;
              String chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&amp;*";
              SecureRandom rng = new SecureRandom();
              StringBuilder sb = new StringBuilder(16);
              sb.append(chars.charAt(rng.nextInt(26)));
              sb.append(chars.charAt(26 + rng.nextInt(26)));
              sb.append(chars.charAt(52 + rng.nextInt(10)));
              sb.append(chars.charAt(62 + rng.nextInt(chars.length() - 62)));
              for (int i = 4; i &lt; 16; i++) {
                sb.append(chars.charAt(rng.nextInt(chars.length())));
              }
              char[] arr = sb.toString().toCharArray();
              for (int i = arr.length - 1; i > 0; i--) {
                int j = rng.nextInt(i + 1);
                char tmp = arr[i]; arr[i] = arr[j]; arr[j] = tmp;
              }
              return new String(arr);
            </Source>
          </Script>
        </Field>

        <Field displayName="Can Login" name="can_login" required="true" type="string" value="true"/>

        <!-- Expiry Date set to 2 years from today -->
        <Field displayName="Expiry Date" name="expiry_date" required="true" type="string">
          <Script>
            <Source>
              import java.util.Calendar;
              import java.text.SimpleDateFormat;
              Calendar cal = Calendar.getInstance();
              cal.add(Calendar.YEAR, 2);
              SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
              return sdf.format(cal.getTime());
            </Source>
          </Script>
        </Field>

        <Field displayName="Granted Role" multi="true" name="granted_role" type="string"/>

      </Section>
    </Form>
  </ProvisioningForms>

  <Schemas>
    <!-- Account Schema -->
    <Schema
      displayAttribute="name"
      id="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
      identityAttribute="name"
      instanceAttribute=""
      nativeObjectType="account"
      objectType="account"
      permissionsRemediationModificationType="None">
      <AttributeDefinition name="name"         type="string"/>
      <AttributeDefinition name="can_login"    type="string"/>
      <AttributeDefinition name="expiry_date"  type="string"/>
      <AttributeDefinition entitlement="true" managed="true" multi="true"
        name="granted_role" schemaObjectType="group" type="string"/>
    </Schema>

    <!-- Group (Entitlement) Schema -->
    <Schema
      descriptionAttribute=""
      displayAttribute="name"
      id="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
      identityAttribute="name"
      instanceAttribute=""
      nativeObjectType="group"
      objectType="group"
      permissionsRemediationModificationType="None">
      <AttributeDefinition name="name" type="string"/>
    </Schema>
  </Schemas>

</Application>

hi @Viraj
granted_role is marked as entitlement="true" and managed="true", but there’s no requestable configuration (ManagedAttribute / Access Request setup)

I would try

• Marking the entitlement as requestable (ManagedAttribute)

• Try an Add Access request first—if it can be added, it will then appear for removal

• Ensure revoke logic exists for granted_role

• Run Identity Refresh after changes

[msingh900 (Manish Singh)]

Hi @Viraj

Can you check if your entitlement is requestable ?

Thanks

[mandarsane]

Hi @Viraj ,

Could you please check if

1. Application has configured any group refresh rule of customization rule which is making entitlements as non requestable.

2. Quicklink population setting if any rule is preventing entitlements from being requested.

[santhirajumunganda]

Hello @Viraj the same issue has been fixed here. Could you please check once?

I'm not able to see the entitlement that user is part of in Manage User Access Page (Remove section)

Upon investigating on this issue, we finally found out why we see this behavior. In our project we have lot of Roles implemented (Business Roles, IT Roles, and SSO), so all our entitlements that are associated with Roles in IIQ will not populate in the remove access page. If the entitlement is configured as requestable, IIQ will allow you to search and add through the “Manage User Access”, but it won’t give result while removing. So the way to remove this entitlements would be if the roles ar…