drop schema current_user cascade;Native PostgreSQL Schema Script: postgres.sql
This page documents schema objects parsed from config/sql/native/postgres.sql.
The detailed documentation is split into logical region pages because this schema script is large.
Naming conventions:
-
M_prefix is used for tables in main part of the repo,MA_for audit tables (can be separate) Constraints/indexes use table_column(s)_suffix convention, withPKfor primary key,FKforeign key,IDXfor index,KEYfor unique index.TRis suffix for triggers. -
Names are generally lowercase (despite prefix/suffixes above in uppercase ;-)).
-
Column names are Java style and match attribute names from M-classes (e.g. MObject).
Other notes:
-
TEXTis used instead ofVARCHAR, see: DBA stackexchange We preferCREATE UNIQUE INDEXtoALTER TABLE … ADD CONSTRAINT, unless the column is marked as UNIQUE directly - then the index is implied, don’t create it explicitly. -
For Audit tables see
postgres-audit.sqlright next to this file. For Quartz tables seepostgres-quartz.sql. -
Public schema is not used as of now, everything is in the current user schema. See secure schema usage pattern in PostgreSQL documentation.
-
Just in case CURRENT_USER schema was dropped (fastest way to remove all midpoint objects)
Region documentation
-
Infrastructure - Schema, extensions, enum types, shared identifiers, repository metadata, and support routines.
-
References - Tables storing object and container references such as archetype, projection, role membership, and approver references.
-
Focus and users - Focus base data, users, generic objects, focus identities, and focus-related references.
-
Roles and services - Abstract roles, roles, services, archetypes, policies, applications, and related object tables.
-
Organization - Organization objects, parent organization references, and organization hierarchy closure support.
-
Resources and shadows - Resources, shadows, shadow partitions, connectors, and resource object reference data.
-
System objects - Node, system configuration, security policy, and other system-level repository objects.
-
Reports and dashboards - Reports, report data, dashboards, object collections, forms, function libraries, and related UI/reporting objects.
-
Role analysis - Role analysis sessions, clusters, outliers, detected patterns, and related containers.
-
Lookup tables - Lookup table objects and their key-value row data.
-
Cases and certification - Case management, access certification campaigns, certification cases, work items, and reviewer references.
-
Templates and messages - Object templates, include references, sequences, forms, function libraries, and message templates.
-
Assignments - Assignment and inducement containers, assignment metadata, and assignment reference tables.
-
Tasks - Task objects, task containers, affected objects, triggers, and operation execution data.
-
Simulations - Simulation result objects, processed simulation objects, and simulation event mark references.
-
Marks - Mark objects used to classify and annotate repository objects and events.
-
Schema objects - Schema repository objects.
-
Extension items - Extension item catalog and indexed extension value storage support.
-
Schema versioning - Procedures and metadata used to track and apply native repository schema changes.
Overview
Tables
| Table | Region | Description |
|---|---|---|
Abstract base table for role-like focus objects that can be assigned or requested. |
||
Stores access certification campaign objects. |
||
Stores access certification cases inside certification campaigns. |
||
Stores access certification definition objects. |
||
Stores work items for access certification cases. |
||
Stores assignee references for access certification work items. |
||
Stores candidate references for access certification work items. |
||
Stores application objects used to model applications and application access. |
||
Stores archetype objects used to classify and constrain other objects. |
||
Stores assignment and inducement containers for assignment-holding objects. |
||
Abstract base table for assignment-holding objects, excluding shadows. Represents AssignmentHolderType (all objects except shadows) extending m_object, but still abstract, hence the CHECK (false). No indexes here, always add indexes and referential constraints on concrete sub-tables. |
||
Stores value metadata for assignments and inducements. |
||
Stores create-approver references from assignment metadata. |
||
Stores modify-approver references from assignment metadata. |
||
Stores case objects used for approvals, manual actions, and other case-management workflows. |
||
Stores case work items assigned to users or groups. |
||
Stores assignee references for case work items. |
||
Stores candidate references for case work items. |
||
Stores connector definitions discovered or configured in midPoint. |
||
Stores connector development objects. |
||
Stores remote connector host definitions. Represents ConnectorHostType, see https://docs.evolveum.com/connectors/connid/1.x/connector-server/ |
||
Abstract base table for separately persisted container values. Purely abstract table (no entries are allowed). Represents Containerable/PrismContainerValue. Allows querying all separately persisted containers, but not necessary for the application. |
||
Stores dashboard objects used by the administrative user interface. |
||
Catalog of extension and attribute items indexed in JSON extension storage. |
||
Abstract base table for focus objects such as users, roles, services, and organizations. |
||
Stores focus identity values used by correlation and identity matching. |
||
Stores custom form definitions. |
||
Stores function library objects used by expressions. |
||
Stores generic objects that do not have a dedicated concrete repository table. |
||
Stores lookup table objects used for reusable key-value mappings. |
||
Stores rows of lookup table key-value data. Lookup table row currently doesn’t store whole polystring data for |
||
Stores mark objects used to classify and annotate repository objects and events. |
||
Stores reusable message templates for notifications and message transport. |
||
Stores midPoint cluster node objects. |
||
Abstract base table containing common fields inherited by all repository object tables. |
||
Stores object collection definitions used by views, dashboards, and searches. |
||
Provides generated object identifiers shared by all concrete object tables. |
||
Stores object template definitions. |
||
Stores operation execution records attached to objects. |
||
Stores organization objects and hierarchy-related organization attributes. Represents OrgType, see https://docs.evolveum.com/midpoint/architecture/archive/data-model/midpoint-common-schema/orgtype/ |
||
Stores policy objects that define reusable policy behavior. |
||
Stores event mark references for processed simulation objects. |
||
Default partition for processed object event mark references. |
||
Stores archetype references assigned to assignment-holder objects. |
||
Stores effective mark references attached to assignments. |
||
Stores delegated references for assignment-holder objects. |
||
Stores include references between object templates. |
||
Stores create-approver references from object metadata. |
||
Stores effective mark references attached to objects. |
||
Stores modify-approver references from object metadata. |
||
Stores parent organization references that define the organization hierarchy. |
||
Stores persona references for focus objects. |
||
Stores projection references from focus objects to resource object shadows. |
||
Stores approver references for resource business configuration. |
||
Stores computed role membership references for assignment-holder objects. |
||
Stores target references for task affected object entries. |
||
Abstract base table for object and container references. |
||
Stores report definitions. |
||
Stores report output data objects and their relation to report definitions. |
||
Stores resource objects and resource-specific searchable state. Represents ResourceType, see https://docs.evolveum.com/midpoint/reference/resources/resource-configuration/ |
||
Stores role objects used to model access, business roles, and technical roles. |
||
Stores role analysis cluster objects and their parent cluster references. |
||
Stores detected role analysis patterns inside a role analysis cluster. |
||
Stores role analysis outlier objects and references to analyzed target objects. |
||
Stores partition data for role analysis outliers. |
||
Stores role analysis session objects. |
||
Stores schema objects. |
||
Stores security policy objects. |
||
Stores sequence objects used for generated values. |
||
Stores service objects, including service accounts and application services. Represents ServiceType, see https://docs.evolveum.com/midpoint/reference/deployment/service-account-management/ |
||
Stores resource object shadows and their searchable synchronization state. Represents ShadowType, see https://docs.evolveum.com/midpoint/reference/resources/shadow/ and also https://docs.evolveum.com/midpoint/reference/schema/focus-and-projections/ |
||
Default shadow partition used before resource-specific shadow partitions are created. |
||
Stores definitions of shadow partition tables managed by repository-side partition triggers. |
||
Stores reference-valued shadow attributes for searching and reverse lookup. |
||
Stores simulation result objects. |
||
Stores objects processed by simulations and their before/after state. |
||
Default partition for simulation processed objects. |
||
Stores the system configuration object. |
||
Stores persistent task objects managed by the task manager. Represents persistent TaskType, see https://docs.evolveum.com/midpoint/reference/tasks/task-manager/ |
||
Stores affected object information for task activity reporting and indexing. |
||
Stores trigger containers attached to objects. |
||
Stores frequently used URI values, such as channels and relation QNames, as compact numeric identifiers. Catalog of often used URIs, typically channels and relation Q-names. Never update values of "uri" manually to change URI for some objects (unless you really want to migrate old URI to a new one). URI can be anything, for QNames the format is based on QNameUtil ("prefix-url#localPart"). |
||
Stores user objects and user-specific searchable attributes. |
||
Stores value policy objects, such as password and value generation policies. |
Views
| View | Region | Description | Type |
|---|---|---|---|
Presents regular objects and shadows through a common object-shaped query surface. |
view |
||
Materialized transitive closure of organization parent-child relationships. Trigger on m_ref_object_parent_org marks this view for refresh in one m_global_metadata row. Closure contains also identity (org = org) entries because: * It’s easier to do optimized matrix-multiplication based refresh with them later. * It actually makes some query easier and requires AND instead of OR conditions. * While the table shows that o ⇒ o (⇒ means "is parent of"), this is not the semantics of isParent/ChildOf searches and they never return parameter OID as a result. |
materialized view |
Other schema objects
| Object | Region | Description | Type |
|---|---|---|---|
Stores access certification campaign states used by certification campaign tables. |
enum type |
||
Stores activation administrative status values used by focus, assignment, and resource data. |
enum type |
||
Stores administrative availability values used by resource search data. |
enum type |
||
Stores availability status values used for operational resource and connector state. |
enum type |
||
Identifies separately stored container table categories in the native repository. |
enum type |
||
Stores correlation situation values used by shadow identity matching. |
enum type |
||
Stores execution mode values used by tasks, simulations, and repository-side reporting. |
enum type |
||
Identifies whether an indexed extension item stores a single value or an array of values. |
enum type |
||
Identifies whether an extension item belongs to object extension data or resource attributes. |
enum type |
||
Stores lockout status values used by focus activation data. |
enum type |
||
Stores node operational state values used by node repository objects. |
enum type |
||
Stores processing state values for simulation processed objects. |
enum type |
||
Identifies midPoint object kinds stored in native repository object tables. |
enum type |
||
Stores operation execution record type values used by operation execution containers. |
enum type |
||
Stores operation result status values used by tasks, operation executions, and audit-related data. |
enum type |
||
Stores orientation values for report output and presentation metadata. |
enum type |
||
Stores predefined configuration profile values. |
enum type |
||
Identifies reference table categories used for object and container references. |
enum type |
||
Stores administrative state values used by resource objects. |
enum type |
||
Stores shadow kind values used to classify resource object shadows. |
enum type |
||
Stores synchronization situation values used by shadow lifecycle and synchronization processing. |
enum type |
||
Stores task auto-scaling mode values. |
enum type |
||
Stores task binding values describing whether task execution is tied to a specific node. |
enum type |
||
Stores task execution state values. |
enum type |
||
Stores task recurrence values for single-run and recurring tasks. |
enum type |
||
Stores task scheduling state values. |
enum type |
||
Stores task waiting reason values. |
enum type |
||
Stores task thread stop action values. |
enum type |
||
Stores time interval status values. |
enum type |
||
Prevents object OID changes and updates database-managed modification metadata. |
function |
||
Releases the shared OID row after a concrete object row is deleted. |
function |
||
Assigns a new OID to inserted object rows or reserves an explicitly provided OID. |
function |
||
Creates or attaches resource-specific shadow partitions when partition definitions are inserted. |
function |
||
Drops a shadow partition table when its partition definition is deleted. |
function |
||
Attaches shadow partition tables when their partition definition is updated. |
function |
||
Creates a processed-object partition for a simulation result when partitioning is enabled. |
function |
||
Drops the processed-object partition when a partitioned simulation result is deleted. |
function |
||
Marks organization closure data for refresh when parent organization references change. |
function |
||
Marks organization closure data for refresh when organization rows are inserted or deleted. |
function |
||
Applies numbered main repository schema changes exactly once per database instance. |
procedure |
||
Refreshes the organization closure materialized view when a refresh is needed or explicitly forced. |
procedure |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Marks organization closure data for refresh after organization rows are inserted or deleted. |
trigger |
||
Marks organization closure data for refresh after organization rows are truncated. |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Marks organization closure data for refresh after parent organization reference row changes. |
trigger |
||
Marks organization closure data for refresh after parent organization references are truncated. |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Drops shadow partition tables when partition definitions are deleted. |
trigger |
||
Creates shadow partition tables from inserted partition definitions. |
trigger |
||
Updates shadow partition attachment state from partition definition changes. |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Creates a processed-object partition after inserting a partitioned simulation result. |
trigger |
||
Drops a processed-object partition before deleting a partitioned simulation result. |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Removes the object OID registry entry when rows are deleted from |
trigger |
||
Maintains the object OID registry when rows are inserted into |
trigger |
||
Maintains object update metadata before rows are updated in |
trigger |
||
Enables fuzzy string matching functions used by repository search features. |
extension |
||
Enables indexing support for integer array columns used by repository search tables. |
extension |
||
Enables trigram indexes used for efficient text search. |
extension |
||
Creates the schema used by the native PostgreSQL repository objects for the current database user. |
schema |
||
Ensures UUID generation support by enabling pgcrypto when gen_random_uuid() is not already available. |
DO block |
||
Adds the |
DO block |
||
Adds discriminator columns to |
DO block |
Was this page helpful?
YES
NO
Thanks for your feedback