This work is licensed under the Creative Commons Attribution-ShareAlike 3.0 Unported Li-cense. To view a copy of this license, visit http://creativecommons.org/licenses/by-sa/3.0/.
This database assumes PostgreSQL version 9.2 or later.
# 1.3 Creating, Backing Up and Restoring the Database
This database is integrated into X-Road security server application. The database management functions are embedded into the application user interface.
The database, the database user and the data model is created by the application's installer. The database updates are packaged as application updates and are applied when the application is upgraded. From the technical point of view, the database structure is created and updated using Liquibase(opens new window) tool. The migration scripts can be found both in application source and in file system of the installed application.
Database backup functionality is built into the application. The backup operation can be invoked from the web-based user interface or from the command line. The backup contains dump of all the database structure and contents. When restoring the application, first the software is installed and then the configuration database is restored together with all the other necessary files. This produces a working security server.
Note: backing up of security server does not include message log that is managed using different tools.
This section describes a general mechanism for storing history of the database tables. All the history-aware tables have associated trigger update_history that records all the modifications to data. All the tables of security server database are history-aware, except for
history
databasechangelog
databasechangeloglock
When a row is created, updated or deleted in one of the history-aware tables, the trigger update_history is activated and invokes the stored procedure add_history_rows. For each changed column, add_history_rows inserts a row into the history table. The details of the stored procedures are described in section 1.6.
add_history_rows: Detects the changes made as a result of the operation it is invoked on, and calls the insert_history_row stored procedure to insert a row to the history table, for each changed field. For insertions and deletions, a history record is inserted for each field of the original table.
insert_history_row: Inserts a single row with values corresponding to a changed field in one of the database tables. Invoked by the add_history_rows stored procedure.
Access right of a security server client or a group of clients to use a particular service. An access right record is created when an access right for a service is granted. The record is deleted when the service is removed from the system configuration or the access right is forfeited. The record is never modified.
The security server client who provides the service. References id attribute of CLIENT entity.
subjectid [FK]
bigint
NOT NULL
Identifier of a subject that is authorized to access the service. Can be either a member, a subsystem, global group or local group. References id attribute of IDENTIFIER entity.
rightsgiven
timestamp without time zone
NOT NULL
The time when the access right was granted.
endpoint_id [FK]
bigint
The authorized endpoint. References id attribute of ENDPOINT entity.
Trusted authentication certificate associated with an information system belonging to a particular security server client. A certificate record is created when a certificate is uploaded for a security server client. The record is deleted when the certificate is deleted from the system configuration. The record is never modified.
Member or subsystem that is using this security server. The security server owner is also registered as a client.
For owner, the record is created when the administrator initializes the security server. For security server users, the record is created when the administrator adds new client in the user interface.
The client record is deleted when the administrator removes the client in the user interface. The client record corresponding to the owner cannot be deleted.
The client record is modified when administrator changes parameters in the user interface or when automatic status update occurs (see below).
The field clientstatus shows the progress of registering in central server the connection between this security server client and this security server. Only in “registered” state can the security server exchange messages on behalf of this client.
saved -- initial state. Client enters it immediately after creation. From this state the administrator can send registration request to the central server.
registration in progress -- the administrator has successfully sent registration request to the central server. In this state the security server is waiting for approval of the client registration request. When the security server receives a global configuration that contains connection between the security server and the client, it enters the “registered” state.
registered -- the registration request sent to the central server is approved and the connection between the client and the security server is registered in the global configuration. In this state the security server can exchange messages on behalf of the client.
deletion in progress -- the security server has successfully sent client deletion request to the central server. From this state, the only possible action is to delete the client from security server configuration.
global error -- the client was in state “registered”, but the connection between the client and the security server has been deleted from the global configuration. From this state the administrator can either wait for updated global configuration (in case the deletion was caused by an error), contact the systems administrator of the central server or delete the client.
disabled -- the client is temporarily disabled
disabling in progress_ -- the administrator has successfully sent clientDisable request. When the security server receives updated global configuration, it enters the "disabled" state.
enabling in progress_ -- the administrator has successfully sent clientEnable request. When the security server receives updated global configuration, it returns to "registered" state.
Liquibase migration of the database. A record is created when the administrator updates the software package containing this database and the database structure needs to be modified. The record is never modified or deleted. This table has a technical nature and is not managed by X-Road application software.
The time when the migration was executed. Used with orderexecuted to determine rollback order.
orderexecuted
integer
NOT NULL
The order number in which the migration was executed. Used in addition to dateexecuted to ensure order is correct even when the databases datetime supports poor resolution.
exectype
character varying(10)
NOT NULL
The type of the execution that was performed. Possible values are EXECUTED, FAILED, SKIPPED, RERAN, and MARK_RAN.
md5sum
character varying(35)
The MD5 hash of the migration script when it was executed. Used on each run to ensure there have been no unexpected changes to the migration script.
description
character varying(255)
Short auto-generated human readable description of the migration.
comments
character varying(255)
The comments of the migration.
tag
character varying(255)
The tag of the migration.
liquibase
character varying(20)
The version of the Liquibase that performed the migration.
Lock used by Liquibase to allow only one migration of the database to run at a time. This table has a technical nature and is not managed by X-Road application software.
Member of a local group. A group membership record is created when the administrator adds a new subsystem to a local group. The record is deleted when the administrator removes the subsystem from the local group. The record is never modified.
Operations (insertions, updates and deletions of records) on the tables of this database, for the purpose of auditing. Each record corresponds to the change of a single field. The record is created in the manner described in section 1.4. The record is never modified or deleted.
Identifier that can be used to identify various objects on X-Road. An identifier record is only created together with records of other entities and only one record of each identifier is ever created. For example, if a security server client record is created and its identifier is not found among identifier records, new one is created. The record is never modified or deleted.
Technical attribute, specifying the Java class to which the identifier is mapped. Possible values are C (ClientId), S (ServiceId), CS (CentralServiceId), GG (GlobalGroupId), LG (LocalGroupId), SS (SecurityServerId). The corresponding Java classes are located in the ee.ria.xroad.common.identifier package.
type
character varying(255)
Specifies the type of the object that the identifier identifies. Possible values, defined in enum ee.ria.xroad.common.identifier.XroadObjectType, are MEMBER, SUBSYSTEM, SERVICE, CENTRALSERVICE, GLOBALGROUP, LOCALGROUP, SERVER.
xroadinstance
character varying(255)
X-Road instance identifier. Present in identifiers of all types, except LOCALGROUP.
memberclass
character varying(255)
Member class. Present in identifiers of MEMBER, SUBSYSTEM, SERVER and SERVICE type.
membercode
character varying(255)
Member code. Present in identifiers of MEMBER, SUBSYSTEM, SERVER and SERVICE type.
subsystemcode
character varying(255)
Subsystem code. Present in identifiers of SUBSYSTEM and SERVICE type.
serviceversion
character varying(255)
Service version. Present in identifiers of SERVICE type.
servicecode
character varying(255)
Service code. Present in identifiers of SERVICE type.
groupcode
character varying(255)
Group code. Present in identifiers of GLOBALGROUP and LOCALGROUP type.
servercode
character varying(255)
Security server code. Present in identifiers of SERVER type.
Group of members and/or subsystems. The group is local to a security server client and is used in access rights management. Local groups are connected to a security server client and can only be used for services belonging to that client. A local group record is created when the administrator adds a new local group to a security server client. The record is modified when the administrator changes the description of the group. The record is deleted when the administrator deletes the group or the security server client for whom the group is defined.
The top-level configuration of the security server, specifying the owner and the code of this security server. This table contains only one record that is created when the security server is initialized. The record is never modified or deleted.
Service provided by a security server client. A service record is created when the administrator adds or refreshes a WSDL of a security server client, and a new service description is found in the WSDL. The record is modified if the administrator edits the service parameters in the user interface. The record is deleted when the administrator deletes the WSDL containing the service description or when the administrator deletes the security server client owning the WSDL.
A flag indicating whether the certificate of the service provider should be verified for SSL/TLS connections. NULL value is interpreted as true. Trusted service provider certificates are stored as CERTIFICATE entities.
timeout
integer
The maximum time in seconds that the service provider can take to respond to a query.
servicedescription_id [FK]
bigint
The servicedescription of which this service is part of. References id attribute of SERVICEDESCRIPTION entity.
Timestamping service provider (TSP) that is used by the security server to time-stamp messages stored in the message log. Only connection parameters to the TSP are included. The data needed for verifying time stamps is read from the global configuration. A TSP record is created when the administrator adds a new TSP in the user interface. The record is deleted when the administrator deletes the TSP in the user interface. The record is never modified.
Preferences of the user interface user. A record is created when the user changes the user interface language for the first time. The record is modified on later changes to the language. The record is never deleted.
Pointer to a SERVICEDESCRIPTION containing the descriptions of services provided by a security server client. A SERVICEDESCRIPTION record is created when the administrator adds a new service description to a security server client in the user interface. The record is modified when the administrator refreshes, enables or disables the service description. The record is deleted when the administrator deletes the service description or the security server client owning the service description.
