Postgres database role permissions

Each database role requires a specific set of permissions to complete the actions attributed to them.

DBA_Role

The DBA_Role requires permissions to:

• Set up and maintain the database management system and Information Store database.
• Create and modify database objects. For example, tables, views, indexes, sequences.
• Troubleshoot performance or other issues. For example, has all privileges on all tables. This can be restricted in some environments.
• Configure high availability.
• Manage recovery activities.

Additionally, the role requires access to the cron schema to initialize the deletion-by-rule objects, and to create scheduled cron jobs for the deletion rules.

The following table provides an overview of the permissions required on the schemas in the Information Store database:

Schema	Permissions	Notes
All	OWNER	Required to create, maintain and give permissions to the database objects.
cron	USAGE	Required for deletion-by-rule.

The create_db_roles.sh script is used to configure the DBA user with all the required role memberships and permissions.

External_ETL_Role

The External_ETL_Role requires permissions to move data from external systems into the Information Store staging tables.

For example, it can be used by an ETL tool - such as DataStage or Informatica - to move and transform data that results in populated staging tables in the Information Store staging schema.

The following table provides an overview of the permissions required on the schemas in the Information Store database:

Schema	Permissions	Notes
IS_Staging	USAGE, SELECT, UPDATE, INSERT, DELETE, TRUNCATE, REFERENCES, TRIGGER	Required to populate the staging tables with date to be ingested or deleted.

In addition to these permissions, users with this role must also be a member of the pg_read_server_files group in order to perform COPY into the external staging tables.

The create_db_roles.sh script is used to create the etl user with all the required role memberships and the grant_permissions_to_roles.sh scripts is used to give the correct permissions.

i2_ETL_Role

The i2_ETL_Role requires permissions to use the i2 Analyze ingestion tools to ingest data from the staging tables into the Information Store.

The following table provides an overview of the permissions required on the schemas in the Information Store database:

Schema	Permissions	Notes
IS_Staging	USAGE, CREATE, SELECT, UPDATE, INSERT, DELETE, TRUNCATE, REFERENCES, TRIGGER	Required by the ingestion tools to create and modify objects during the ingestion process.
IS_Stg	USAGE, CREATE, SELECT, UPDATE, INSERT, DELETE, TRUNCATE, REFERENCES, TRIGGER	Required by the ingestion tools to create and modify objects during the ingestion process.
IS_Meta	USAGE, SELECT, UPDATE, INSERT	Required to update the ingestion history table. SELECT is required to read the schema meta data.
IS_Data	USAGE, SELECT, UPDATE, INSERT, DELETE	Required to drop and create indexes and update statistics as part of the ingestion process.
IS_Public	USAGE, SELECT	Required to delete and create synonyms when enabling merged property views.
IS_Core	USAGE, SELECT	Required to check configuration of the database.

The following table provides an overview of the permissions required on the routines/functions in the Information Store database:

Schema	Permissions	Notes
IS_Core	EXECUTE	Required to check configuration of the database.

The following table provides an overview of the permissions required on the sequences in the Information Store database:

Schema	Permissions	Notes
IS_Data	USAGE, SELECT	Required by the ingestion tools to create and modify objects during the ingestion process.

i2analyze_Role

The i2analyze_Role requires permissions to complete actions required by the Liberty application. These actions include:

• Visual Query, Find Path, Expand, Add to chart, Upload, and Online upgrade.

The following table provides an overview of the permissions required on the schemas in the Information Store database:

Schema	Permissions	Notes
IS_Staging	USAGE, CREATE, SELECT, UPDATE, INSERT, DELETE, TRUNCATE, REFERENCES, TRIGGER	Required to run deletion-by-rule jobs.
IS_Stg	USAGE, CREATE, SELECT, UPDATE, INSERT, DELETE, TRUNCATE, REFERENCES, TRIGGER	Required to upload and delete records via Analyst's Notebook and to run deletion-by-rule jobs.
IS_Meta	USAGE, SELECT, UPDATE, INSERT, DELETE, TRUNCATE, REFERENCES, TRIGGER	Required to process the ingestion history queue.
IS_Data	USAGE, SELECT, UPDATE, INSERT, DELETE, TRUNCATE, REFERENCES, TRIGGER	Required by deletion-by-rule and to upload and delete records via Analyst's Notebook.
IS_Core	USAGE, SELECT, UPDATE, INSERT, DELETE, TRUNCATE, REFERENCES, TRIGGER	Required for online upgrade.
IS_VQ	USAGE, SELECT, UPDATE, INSERT, DELETE, TRUNCATE, REFERENCES, TRIGGER	Required to complete Visual Queries.
IS_FP	USAGE, SELECT, INSERT	Required to complete Find Path operations.
IS_WC	USAGE, SELECT, UPDATE, INSERT, DELETE, TRUNCATE, REFERENCES, TRIGGER	Required to work with Web Charts.
IS_PUBLIC	USAGE	Required to create and send Alerts.

The following table provides an overview of the permissions required on the routines/functions in the Information Store database:

Schema	Permissions	Notes
IS_Core	EXECUTE	Required for online upgrade.
IS_FP	EXECUTE	Required to complete Find Path operations.
IS_PUBLIC	EXECUTE	Required to create and send Alerts.

The following table provides an overview of the permissions required on the sequences in the Information Store database:

Schema	Permissions	Notes
IS_Data	USAGE, SELECT	Required by the ingestion tools to create and modify objects during the ingestion process.
IS_WC	USAGE, SELECT	Required to work with Web Charts.
IS_Core	USAGE, SELECT	Required for online upgrade.

Deletion_By_Rule

The Deletion_By_Rule requires permissions to access/execute objects in the Information Store public schema.

The following table provides an overview of the permissions required on the schemas in the Information Store database:

Schema	Permissions	Notes
IS_Public	SELECT, EXEC	Require to run deletion-by-rule jobs.

The add_user_to_db_role.sh script is used to make the i2etl user a member of the Deletion_By_Rule database role.

i2_Public_Role

The i2_Public_Role requires permissions to access/update objects in the Information Store public schema.

The following table provides an overview of the permissions required on the schemas in the Information Store database:

Schema	Permissions	Notes
IS_Public	ALTER, SELECT, UPDATE, INSERT, DELETE, EXEC	Required to complete actions that require access, modification, and execution permissions to objects in the IS_Public schema. For example, to use custom task scheduling.

The add_user_to_db_role.sh script is used to make the i2analyze user a member of the i2_Public_Role database role.

The database backup operator role

The example also demonstrates how to perform a database backup. The dbb user will perform this action and requires USAGE and SELECT permissions in the following schemas in the Information Store database:

• IS_Staging
• IS_Stg
• IS_Meta
• IS_Data
• IS_Core
• IS_VQ
• IS_FP
• IS_WC

Database users and logins

In the example, a user is created for each role described previously. These users are then used throughout the deployment and administration steps to provide a reference for when each role is required.

The following users and logins are used in the example:

User and login	Description	Secrets
postgres	The super user. The postgres user has full permissions on the database instance. This user creates the Information Store database and roles/users.	The password is in the postgres_PASSWORD file in the environment-secrets/generated-secrets/postgres directory.
i2analyze	The i2analyze user is a member of the i2analyze_Role and i2_Public_Role.	The password is in the i2analyze_PASSWORD file in the environment-secrets/generated-secrets/postgres directory.
etl	The etl user is a member of External_ETL_Role.	The password is in the etl_PASSWORD file in the environment-secrets/generated-secrets/postgres directory.
i2etl	The i2etl user is a member of i2_ETL_Role and Deletion_By_Rule.	The password is in the i2etl_PASSWORD file in the environment-secrets/generated-secrets/postgres directory.
dba	The dba user is a member of DBA_Role.	The password is in the dba_PASSWORD file in the environment-secrets/generated-secrets/postgres directory.
dbb	The dbb user is the database backup user.	The password is in the dbb_PASSWORD file in the environment-secrets/generated-secrets/postgres directory.

The postgres user and login exists on the base Postgres image. The postgres user is used to create the following artifacts:

• Database: ISTORE
• Roles: i2analyze_Role, External_ETL_Role,, i2_ETL_Role, DBA_Role, Deletion_By_Rule and i2_Public_Role
• Users: i2analyze, etl, i2etl, dba, and dbb

The roles and users must be created after the Information Store database is created. And the ownership of the database needs to be given to the DBA role.