Configuring Identity Broker for use with PostgreSQL

,Introduced with Identity Broker v5.3, a PostgreSQL database server can be used instead of Microsoft SQL Server. The following instructions details the process of configuring Identity Broker for use with a PostgreSQL database.

Creating the PostgreSQL Database

Log onto the PostgreSQL server with pgAdmin or via the command line tool and create a new database. Ensure the new database is being targeted and run the Identity Broker Install PostgreSQL script which is located at <InstallDir>/Database/IdentityBroker Install PostgreSql.sql.

If the PostgreSQL server user account name is not the default postgres, then the above install script will need to be edited to reflect this. Open the SQL script in a text editor and replace all instances of postgres with the correct account name. Note that if the database is hosted on an Azure Database for PostgreSQL server which displays the account name as <name>@<server> then only the name component preceding the @ symbol should be used in the script.

Configure Identity Broker

Locate the following extensibility file<InstallDir>/Service/Extensibility/Unify.Framework.Data.DataEnginePlugInKey.extensibility.config.xml. Remove or comment out the existing dataConnection element. In its place, add the following:

<dataConnection name="postgres" repository="Unify.IdentityBroker" type="PostgreSql" connectionString="Host=;Port=;Database=;Username=;Password=" />

Ensure the following values are supplied as part of the connectionString attribute

HostThe host address for the PostgreSql server.
PortThe port for the PostgreSql server.
DatabaseThe name of the Identity Broker PostgreSql database.
UsernameThe name PostgreSql user account for Identity Broker to use
PasswordThe password for the specified PostgreSql user account.

The connectionString attribute can be further customized to meet environment specific requirements. Note that non-standard connection string values may produce unintended behaviour in Identity Broker. See here more for information.

Optimising Performance of Entity Reads

Depending on how Identity Broker is configured and the number of entities being stored by the PostgreSQL, performance may be improved by adding indexes to certain schema fields. The addition of unneeded indexes will reduce performance in other areas and so should not be done without consideration. Key candidates for schema fields that should be considered are:

Adapter reflection with relational transformations

Field TypeBenefits Observed In
Connector schema fields marked as Key.

For best results, if multiple fields on a connector are key fields, all should be indexed.
Processing connector deletes, adapter reflection with relational transformations
Right-side relationship keys of Group, Multi-Value Group, Foreign Multi-Value Group and Join transformationsAdapter reflection with relational transformations
Fields which contribute to fields used as either right or left side relationship keys in the above transformations

For example: Connector field groupname renamed with a Rename Transformation to gname which is used as a Join Transformations left-side relationship key. groupname should be considered for an index. Furthermore, if groupname was, instead of being a connector field, generated by another transformation, the field or fields it is generated from should also be considered for indexes. 
Adapter reflection with relational transformations
Fields being filtered by on an OData or SCIM gatewaysReading of entities though these gateways
Fields which are set as the user id lookup field on a SCIM gatewayReading of entities though this gateway

Once the schema fields to index have been determined, the index can be created with the following SQL query:

CREATE INDEX unique_name_of_index            -- Give the index a unique name ie: entity_fieldname_index
    ON public."Entity" USING btree
    (("EntityValues"->'field_name'))         -- Replace field_name with schema field name.
    TABLESPACE pg_default;

Is this article helpful for you?