4.4 Preparing a PostgreSQL Database for Identity Governance

You can install PostgreSQL and create the databases for Identity Governance if you do not want the installation program to create these. The installation program can create the databases, tables, views, and other artifacts in the databases. The program needs the name of the databases to represent the operations, archive, data collection, provisioning workflow, and analytics databases for Identity Governance.

However, your database administrator might prefer to create the schemas, as well as the database artifacts, rather than allowing the installation process to do so. Your database administrator can choose to complete the following actions before you install Identity Governance:

4.4.1 Adding the JDBC File to the Application Server

To run queries against the database, add the JDBC file to the application server.

  1. Ensure that you do not have an older version of the JDBC file in the:

    • Linux: /opt/netiq/idm/apps/tomcat/lib directory

    • Windows: c:\netiq\idm\apps\tomcat\lib directory

  2. When you install Identity Governance, the installation program places the correct JDBC file in the:

    • Linux: /opt/netiq/idm/apps/tomcat/lib directory

    • Windows: c:\netiq\idm\apps\tomcat\lib directory

4.4.2 Creating the PostgreSQL Databases Before Installation

Your database administrator can choose to create the databases for Identity Governance before you run the installation. Otherwise, the installation program can generate the databases.

  1. Install a supported version of PostgreSQL. For more information, see Section 1.9.2, Database Server System Requirements.

  2. Create the databases and roles for igops, igdcs, igwf, and igara using the following commands:

    CREATE ROLE operations_db_name LOGIN password 'password';
CREATE ROLE archive_db_name LOGIN password 'password';
CREATE ROLE data_collection_db_name LOGIN password 'password';
CREATE ROLE workflow_db_name LOGIN password 'password';
CREATE ROLE analytics_db_name LOGIN password 'password';
CREATE ROLE ig_report_role NOLOGIN;
CREATE DATABASE igops WITH OWNER = operations_db_name ENCODING = 'UTF8';
CREATE DATABASE igarc WITH OWNER = archive_db_name ENCODING = 'UTF8';
CREATE DATABASE igdcs WITH OWNER = data_collection_db_name ENCODING = 'UTF8';
CREATE DATABASE igwf WITH OWNER = workflow_db_name ENCODING = 'UTF8';
CREATE DATABASE igara WITH OWNER = analytics_db_name ENCODING = 'UTF8';
GRANT EXECUTE ON igops.max_risk_level to igrptuser;
GRANT EXECUTE ON igops.min_risk_level to igrptuser;
GRANT EXECUTE ON igops.risk_value to igrptuser;

  3. (Optional) If you are installing Identity Reporting, also use the following commands:

    CREATE DATABASE "igrpt" WITH OWNER "pg_admin_user" TEMPLATE = template0 ENCODING = 'UTF8';
CREATE ROLE idm_rpt_cfg WITH LOGIN PASSWORD 'idm_rpt_cfg_password';
GRANT CREATE ON SCHEMA public TO idm_rpt_cfg;
FOR table_info IN SELECT * from pg_tables where schemaname = 'idm_rpt_cfg' and tableowner != 'idm_rpt_cfg' LOOP
  cmd := 'ALTER TABLE idm_rpt_cfg.' || table_info.tablename || ' OWNER TO idm_rpt_cfg';
  EXECUTE cmd;
END LOOP;

  4. Specify the same password for all databases.

    NOTE:The installation process for Identity Governance requires you to specify one password that applies to all databases. After installing Identity Governance, you can modify the passwords to be unique for each database.

  5. Create the reporting user igrptuser.

    CREATE ROLE "igrptuser" PASSWORD 'igrptuser_password' LOGIN;

  6. Grant the reporting role to the reporting user.

    GRANT IG_REPORT_ROLE TO "igrptuser";

  7. When you install Identity Governance, specify one of the following settings:

    • Configure database now > Update, if you want the installation program to generate or update the schemas, tables, and views when you migrate from Identity Governance 3.0 to 3.5

    • Configure database now > Use only existing, if your database is already set up correctly with all schemas, roles, and users

    • Generate SQL for later, if your database administrator wants to generate the schemas, tables, and views

    • No database configuration, for using additional nodes in clustered environment

    For more information about using SQL statements after installation, see Section 6.1, Configuring the Databases after Installation.

4.4.3 Creating a Temporary PostgreSQL Database Administrator for the Installation Process

The installation process requires the password for an administrator account in PostgreSQL that can create databases, roles, tables, views, and other artifacts in the databases. You can avoid specifying the password for the postgres account by creating a temporary administrator for the installation process to use.

The temporary account must have the following properties:

  • LOGIN

  • SUPERUSER

  • CREATEDB

  • CREATEROLE

The temporary account must have privileges to complete the following tasks:

  • create databases

  • create roles

  • assign ownership of each database to a role so that this role can then create tables, views, and other artifacts within the databases that it owns

  • grant connect on a database to a role

  • grant one role to another.

During installation, you can also select Generate SQL for later, which prevents the installation program from creating the tables, views, and artifacts in the Identity Governance or Identity Reporting databases. Instead, the program generates a SQL file for each database, which your database administrator can run to update each database. For more information about using the SQL files, see Section 6.1, Configuring the Databases after Installation.