Database Schema Migrations#

Since version 11.0, the Curity Identity Server can help administrators perform database schema migrations by using the idsvr command. Schema migrations allows Curity to add/improve features that require changes to the data model.

Basic Procedure#

There are two options the idsvr command accepts that are related to schema migrations:

• -C <changelog> - check a changelog (dry run).
• -L <changelog> - load a changelog (actually apply changes to the database schema).

Before upgrading, it is advisable that the following commands are executed:

1. get a report about the current state of the database schema.

<ENV SETTINGS> idsvr -C default

2. run the schema migration:

<ENV SETTINGS> idsvr -L default

Step 2 may not be necessary on every release, but you can always run it even if it is not needed, since it has no effect in that case.

The value provided for the -C and -L options is a changelog location. Use default to run the changelog provided by Curity (you can find the default changelog at $IDSVR_HOME/etc/liquibase/default.xml, with the changelogs it executes under the core subdirectory).

In cases where you have custom database schemas which you prefer to maintain yourself, it is possible to provide another changelog. Custom changelogs are searched for in the $IDSVR_HOME/etc/liquibase/.

For example, if this command is executed:

<ENV SETTINGS> idsvr -C mycompany/main-changelog.xml

There should be a root changelog file at $IDSVR_HOME/etc/liquibase/mycompany/main-changelog.xml.

Rollback#

In case you need to roll back the schema to a previous tag, use the -R option together with the -t (tag) option, which must be given the value of an existing tag in the changelog.

• -R <changelog> - rollback using a changelog.
• -t <tag> - target tag.

For example, to roll back to the tag called 11.0.0, assuming the schema had been previously migrated to a larger version:

<ENV SETTINGS> idsvr -R default.xml -t 11.0.0

The default changelogs always include tags that match each released version of the Curity Identity Server (starting from version 10.6.0).

Providing Data Source-specific settings#

When running a changelog, the Curity configuration is not used. That’s because database schema migrations require elevated privilege and hence, the credentials used by the migration runner should be different from those used by the Curity Identity Server in normal operation.

For this reason, each data source plugin type must document which settings it expects.

Each setting can be provided multiple times by appending a .<N> suffix to the setting’s name, where <N> is an integer starting from 1, and increasing by one. The plain setting, without suffix, must always be provided.

For example, to configure the JDBC implementation of DatabaseMigrationManager to run three times, with three different connection strings, use the following environment variables:

JDBC_URL=<connection-0>
JDBC_URL.1=<connection-1>
JDBC_URL.2=<connection-2>
JDBC_USERNAME=<user>
JDBC_PASSWORD=<password>

Notice that in the above example, the same username and password will be used for each one of the three connection strings.

You could also provide a different username and password for each run by providing numbered environment settings for all settings.

JDBC_URL=<connection-0>
JDBC_URL.1=<connection-1>
JDBC_URL.2=<connection-2>
JDBC_USERNAME=<user-0>
JDBC_USERNAME.1=<user-1>
JDBC_USERNAME.2=<user-2>
JDBC_PASSWORD=<password-0>
JDBC_PASSWORD.1=<password-1>
JDBC_PASSWORD.2=<password-2>

The rules are simple. For each group of settings, the migration will run for as many times as the longest list of values. If some setting is provided fewer times than the longest list of values, then the last provided value for it is repeated in subsequent runs.

Hence, in the example above, if the following line was removed:

JDBC_PASSWORD.2=<password-2>

Then the final run would’ve executed with the values of JDBC_URL.2, JDBC_USERNAME.2 and JDBC_PASSWORD.1 (which is repeated since there was no .2 value for the password setting).

Environment Setting Sources#

The environment settings mentioned above may be provided by the following sources:

• file system locations.
• environment variables.

This behavior is implemented by the SDK class EnvironmentSettingsProvider, when instantiated via the createDefault factory method.

When looking up environment settings, first a file whose name is the same as the setting name is checked under the /var/run/secrets directory.

If not found, an environment variable is looked up for the setting.

For example, if the FOO environment setting is looked up, the server tries to find the file /var/run/secrets/FOO. If that exists, its contents are returned as the value for the FOO setting. Otherwise, the FOO environment variable is looked up.

JDBC Plugin#

The JDBC Plugin accepts the following environment settings:

• JDBC_URL - the JDBC connection string.
• JDBC_USERNAME (optional) - database username. Must have access to modify the schema.
• JDBC_PASSWORD (optional) - database user password.
• JDBC_RUNTIME_CHANGELOG (optional) - changelog to use. Overrides the value provided by the -C and -L options.

Example of a complete command to run a single JDBC Plugin Schema Migration:

JDBC_URL="<connection-string>" JDBC_USERNAME="<user>" JDBC_PASSWORD="<password>" idsvr -L default

Example using integrated authentication (e.g., MSSQL with Kerberos), where username and password are not needed:

JDBC_URL="jdbc:sqlserver://host;integratedSecurity=true" idsvr -L default

Mixed Authentication#

When running migrations against multiple databases where some use integrated authentication and others require explicit credentials, set JDBC_USERNAME to an empty string for entries that should use integrated authentication. When JDBC_USERNAME is empty, the corresponding JDBC_PASSWORD is ignored, but a value must still be provided to maintain positional alignment with the numbered settings:

JDBC_URL=jdbc:sqlserver://host1;integratedSecurity=true
JDBC_URL.1=jdbc:postgresql://host2/db
JDBC_USERNAME=""
JDBC_USERNAME.1=pguser
JDBC_PASSWORD=ignored
JDBC_PASSWORD.1=pgpass

In this example, the first migration runs against MSSQL using Kerberos (no credentials passed to the driver), while the second runs against PostgreSQL with explicit credentials.

Enabling Server runtime schema migration status check#

If you would like to let server run the schema migration status check (equivalent to the -C option) when it is started up, run the Curity Identity Server providing, at least, the JDBC_URL setting, or equivalent to “connection string” for your data source.

Because it is not possible to provide the -C option when starting up the Curity Identity Server, you can instruct it to use a changelog other than default by explicitly providing the JDBC_RUNTIME_CHANGELOG setting.

The username and password, if not provided via the JDBC_USERNAME and JDBC_PASSWORD settings, respectively, will be the ones configured in the plugin’s configuration. If more than one data source of the same type is configured at the same time, it is undefined which plugin configuration will be used. Hence, in such cases you should provide username and password settings to avoid confusion.

If the JDBC driver handles authentication without explicit credentials (e.g., via Kerberos or integrated authentication configured in the JDBC URL), the JDBC_USERNAME and JDBC_PASSWORD settings can be omitted entirely.

If there’s any schema migrations pending, a WARNING log message will be logged since that means the database may not have the schema required by the current version of the server. It could be dangerous to continue running the server in production if that’s the case.

Liquibase Tracking Tables#

Liquibase uses its own tables to track the state of applied migrations. By default, these tables are created in the connecting user’s default schema (e.g. dbo on MSSQL, public on PostgreSQL).

Storing Tracking Tables in a Different Schema#

If you want Liquibase’s tracking tables to reside in a schema different from the user’s default schema, set the LIQUIBASE_LIQUIBASE_SCHEMA_NAME environment variable:

LIQUIBASE_LIQUIBASE_SCHEMA_NAME=liquibase_meta JDBC_URL="<connection-string>" JDBC_USERNAME="<user>" JDBC_PASSWORD="<password>" idsvr -L default

This controls only where the Liquibase tables are stored. It does not affect where your application tables are created.

Controlling the Target Schema for Database Objects#

The default changelogs shipped with the Curity Identity Server do not hardcode a schema name. This means that all database objects (tables, indexes, etc.) are created in whatever schema the database considers the default for the connecting user. If you need the objects to be created in a different schema, the approach depends on the database system.

PostgreSQL#

Set the currentSchema parameter on the JDBC URL:

JDBC_URL="jdbc:postgresql://host:5432/mydb?currentSchema=myschema" idsvr -L default

MSSQL (SQL Server)#

MSSQL does not support setting a default schema via the JDBC URL or at the session level. The target schema is always determined by the database user’s DEFAULT_SCHEMA property (which defaults to dbo).

To change it:

ALTER USER [john] WITH DEFAULT_SCHEMA = [myschema];

For integrated authentication (Windows/Kerberos), the same applies — the Windows principal is mapped to a database user and that user has a DEFAULT_SCHEMA:

ALTER USER [DOMAIN\john] WITH DEFAULT_SCHEMA = [myschema];

Oracle#

In Oracle, the schema is tied to the connecting user. Tables are created in the user’s own schema by default. The Oracle JDBC driver does not provide a connection string parameter to set the current schema and the schema migration should be run using a user that has the target schema name.

MySQL / MariaDB#

In MySQL, “schema” and “database” are synonymous. Set the target database directly in the JDBC URL:

JDBC_URL="jdbc:mysql://host:3306/myschema" idsvr -L default