--- title: PostgreSQL repository description: Configure the PostgreSQL Repository and IDM component: pingidm version: 8.1 page_id: pingidm:install-guide:repository-postgresql canonical_url: https://docs.pingidentity.com/pingidm/8.1/install-guide/repository-postgresql.html keywords: ["Installation", "PostgreSQL"] section_ids: postgres-conf-repo-idm: Configure the PostgreSQL repository and IDM postgres-conf-search-array: Configure Array Fields --- # PostgreSQL repository * [Configure the PostgreSQL Repository and IDM](#postgres-conf-repo-idm) * [Configure Searchable Array Fields](#postgres-conf-search-array) ## Configure the PostgreSQL repository and IDM This procedure assumes that a [supported version of PostgreSQL](../release-notes/before-you-install.html#prerequisites-repositories) is installed and running on the local host. *Before starting IDM for the first time*, configure the server to use a PostgreSQL repository. | | | | - | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | The `path/to/openidm/db/postgresql/scripts/createuser.pgsql` script creates an `openidm` database and role, with a default password of `openidm`. The script also grants the appropriate permissions.Edit this script if you want to change the password of the `openidm` role, for example:```sql create database openidm encoding 'utf8'; create role openidm with LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE inherit password 'newPassword'; grant all privileges on database openidm to openidm; ``` | 1. Edit the Postgres client authentication configuration file, `pg_hba.conf`. Add the following entries for the following users: `postgres` and `openidm`: ``` local all openidm trust local all postgres trust ``` 2. As the `postgres` user, execute the `createuser.pgsql` script as follows: ``` psql -U postgres < /path/to/openidm/db/postgresql/scripts/createuser.pgsql CREATE DATABASE CREATE ROLE GRANT ``` 3. Run the `openidm.pgsql` script as the new `openidm` user that you created in the first step: ``` psql -U openidm < /path/to/openidm/db/postgresql/scripts/openidm.pgsql CREATE SCHEMA CREATE TABLE CREATE TABLE CREATE TABLE CREATE INDEX CREATE INDEX ... START TRANSACTION INSERT 0 1 INSERT 0 1 COMMIT CREATE INDEX CREATE INDEX ``` Your database has now been initialized. 4. Run the script that creates the tables required by the workflow engine: ``` psql -d openidm -U openidm < /path/to/openidm/db/postgresql/scripts/flowable.postgres.all.create.sql ``` 5. If you plan to direct audit logs to this repository, run the script that sets up the audit tables: ``` psql -d openidm -U openidm < /path/to/openidm/db/postgresql/scripts/audit.pgsql ``` 6. Remove the default DS repository configuration file (`repo.ds.json`) from your project's `conf/` directory. For example: ``` cd /path/to/openidm/my-project/conf/ rm repo.ds.json ``` 7. Copy the database connection configuration file for PostgreSQL (`datasource.jdbc-default.json`) and the database table file (`repo.jdbc.json`) to your project's configuration directory. For example: ``` cp /path/to/openidm/db/postgresql/conf/datasource.jdbc-default.json my-project/conf/ cp /path/to/openidm/db/postgresql/conf/repo.jdbc.json my-project/conf/ ``` 8. Update the connection configuration to reflect your PostgreSQL deployment. The default connection configuration in the `datasource.jdbc-default.json` file is as follows: ```json { "driverClass" : "org.postgresql.Driver", "jdbcUrl" : "jdbc:postgresql://&{openidm.repo.host}:&{openidm.repo.port}/openidm", "databaseName" : "openidm", "username" : "openidm", "password" : "openidm", "connectionTimeout" : 30000, "connectionPool" : { "type" : "hikari", "minimumIdle" : 20, "maximumPoolSize" : 50 } } ``` If you changed the password in step 1 of this procedure, edit the `datasource.jdbc-default.json` file to set the value for the `password` field to whatever password you set for the `openidm` user. Specify the values for `openidm.repo.host` and `openidm.repo.port` in one of the following ways: > **Collapse: Set in an IDM Properties File** > > Set the values in your `resolver/boot.properties` file: > > ```json > openidm.repo.host = localhost > openidm.repo.port = 5432 > ``` > **Collapse: Set as an Environment Variable** > > Set the properties in the `OPENIDM_OPTS` environment variable and export that variable before startup. You must include the JVM memory options when you set this variable. For example: > > ``` > export OPENIDM_OPTS="-Xmx2048m -Xms2048m -Dopenidm.repo.host=localhost -Dopenidm.repo.port=5432" > /path/to/openidm/startup.sh -p my-project > Executing ./startup.sh... > Using OPENIDM_HOME: /path/to/openidm > Using PROJECT_HOME: /path/to/openidm > Using OPENIDM_OPTS: -Xmx2048m -Xms2048m -Dopenidm.repo.host=localhost -Dopenidm.repo.port=5432 > ... > Using boot properties at /path/to/openidm/resolver/boot.properties > -> OpenIDM version "8.1.0" > OpenIDM ready > ``` 9. PostgreSQL is now set up for use as the internal repository. Make sure that the server starts without errors. 10. Set up indexes to tune the PostgreSQL repository according to your specific deployment. | | | | - | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | No indexes are set by default. If you do not tune the repository correctly by creating the required indexes, the performance of your service can be severely impacted. For example, setting too many indexes can have an adverse effect on performance during managed object creation. Conversely, not indexing fields that are searched will severely impact search performance. | IDM includes a `/path/to/openidm/db/postgresql/scripts/default_schema_optimization.pgsql` script that sets up a number of indexes. This script includes extensive comments on the indexes that are being created. Review the script before you run it to ensure that all the indexes are suitable for your deployment. When you have refined the script for your deployment, execute the script as a user with superuser privileges, so that the required extensions can be created. By default, this is the `postgres` user: ``` psql -U postgres openidm < /path/to/openidm/db/postgresql/scripts/default_schema_optimization.pgsql CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX ``` ## Configure Array Fields Optionally, you can configure arrays in order to perform [REST queries](../objects-guide/queries.html#query-array-reqs). This feature only works for `genericMapping` objects. 1. Edit the `repo.jdbc.json` file to identify which fields/properties are stored as `JSON_LIST` arrays: ```json "genericMapping" : { "managed/*" : { "mainTable" : "managedobjects" }, "managed/role" : { "mainTable": "managedobjects", "properties" : { "/stringArrayField" : { "type" : "JSON_LIST" } } }, ... ``` 2. Edit the `managed.json` file and add the properties to `role`: ```json "stringArrayField" : { "description" : "An array of strings", "title" : "String array field", "viewable" : true, "returnByDefault" : false, "type" : "array", "items" : { "type" : "string", "title" : "Some strings" } } ```