Get Connected to SQL Data Sources

This tutorial explains the basic steps to connect the Curity Identity Server to a SQL database. The content includes some demo local deployments for development computers. Once connected, you can plan your real database deployments.

Create a Default Configuration

First, follow these getting started tutorials.

• Get a license file, run an Install using Docker and sign into the Admin UI.
• Run the First Configuration, select All Options and accept all default settings.
• Export Configuration so that you have a curity-config.xml file.

Get Schema Creation Scripts

Get the schema initialization script from the $IDSVR_HOME/etc folder of the Curity Identity Server. For example, you can run the following Docker commands.

docker pull curity.azurecr.io/curity/idsvr
docker run --name curity -d -e PASSWORD=Password1 curity.azurecr.io/curity/idsvr
docker cp curity:/opt/idsvr/etc/ ./etc/
docker rm --force curity

Study the resources and view the SQL schema creation script for your preferred provider, to understand details like tables and columns. In some deployments you may need to provide this script to a database administrator (DBA).

postgres-create_database.sql
mssql-create_database.sql
mysql-create_database.sql
oracle-create_database.sql

Create the Database Schema

Instead of running SQL scripts, you can automate schema creation with the Liquibase tool, which you can read more about in the Upgrade Data Sources tutorial. On every deployment of the Curity Identity Server, run another instance of the Curity Identity Server as a job container. The job container uses high privilege database credentials to run commands that install or upgrade the database schema if required, after which the job container terminates.

You can save the following content to a bash script named init.sh to run in a job container. Run chmod +x init.sh to make the script executable. The example script first uses a sleep statement, for cases where the database server takes a long time to start up. The idsvr -I command creates the schema if required and upgrades it to the product version of the job container.

#!/bin/bash

echo '*** Waiting until the database server is ready ...'
sleep 30

echo '*** Creating and upgrading the schema for the Curity Identity Server ...'
idsvr -I

Configure the Curity Identity Server

You can configure data source details in the Admin UI by navigating to Facilities → Data Sources. The following sections instead demonstrate the XML configuration settings, so that you can plan rollouts to deployment pipelines.

<facilities xmlns="https://curity.se/ns/conf/base">
  <data-sources>
    <data-source>
      <id>default-datasource</id>
      <jdbc xmlns="https://curity.se/ns/ext-conf/jdbc">
        <connection-string>jdbc:postgresql://dbserver/idsvr</connection-string>
        <standard-credentials-mode>
        </standard-credentials-mode>
        <driver>org.postgresql.Driver</driver>
        <password>Password1</password>
        <username>idsvruser</username>
      </jdbc>
    </data-source>
  </data-sources>
...    
</facilities>

Run Example Deployments

The following examples provide getting started Docker deployments for a local computer, with working connections to the main JDBC database servers.

services:
  curity-data:
    image: postgres:latest
    hostname: dbserver
    container_name: dbserver
    environment:
      POSTGRES_USER: 'idsvruser'
      POSTGRES_PASSWORD: 'Password1'
      POSTGRES_DB: 'idsvr'

  curity-data-init:
    image: curity.azurecr.io/curity/idsvr:latest
    hostname: dbserverinitializer
    volumes:
      - ./init.sh:/tmp/init.sh    
    command: ["/tmp/init.sh"]
    environment:
      JDBC_URL: jdbc:postgresql://dbserver:5432/idsvr
      JDBC_USERNAME: 'idsvruser'
      JDBC_PASSWORD: 'Password1'

  curity-idsvr:
    image: curity.azurecr.io/curity/idsvr:latest
    hostname: idsvr
    ports:
      - 6749:6749
      - 8443:8443
    volumes:
      - ./license.json:/opt/idsvr/etc/init/license/license.json
      - ./curity-config.xml:/opt/idsvr/etc/init/config.xml
    environment:
      ADMIN: 'true'

docker exec -it dbserver bash

export PGPASSWORD=Password1 && psql -p 5432 -d idsvr -U idsvruser

SELECT * FROM accounts;

Finalize Deployments

Once connected, plan deployments to real environments that follow the provider's database best practices. Configure a high privilege database user with which to make schema changes, and run the Curity Identity Server as a separate low privilege database user. Use strong database credentials and encrypt data at rest. See the product documentation for details on the Database System Requirements and the finer details for each JDBC Data Source.

Finally, use the techniques in the Configuration as Code tutorial to finalize the database configuration for deployed systems. Use parameterized configuration and cryptographically protect sensitive database values.

<facilities xmlns="https://curity.se/ns/conf/base">
    <data-sources>
      <data-source>
        <id>default-datasource</id>
        <jdbc xmlns="https://curity.se/ns/ext-conf/jdbc">
          <connection-string>#{DB_CONNECTION}</connection-string>
          <standard-credentials-mode>
          </standard-credentials-mode>
          <driver>#{DB_DRIVER}</driver>
          <password>#{DB_PASSWORD}</password>
          <username>#{DB_USER}</username>
          <use-for-audit>true</use-for-audit>
        </jdbc>
      </data-source>
    </data-sources>
...    
</facilities>

Summary

The Curity Identity Server supports the main transactional databases that organizations use for their business data. You can deploy identity data anywhere and follow the same processes and techniques for both business data and identity data. For example, you can use the same database administration standards, for operations like encrypting data at rest, clustering and snapshotting.

Join our Newsletter

Get the latest on identity management, API Security and authentication straight to your inbox.

Email

Subscribe

I agree to Curity's Privacy Policy

Start Free Trial

Try the Curity Identity Server for Free. Get up and running in 10 minutes.

Start Free Trial