Build a custom connector with Baton-SQL

Build a custom connector to sync users, groups, and roles from any SQL database without writing code.

Baton-SQL is a configuration-only connector for SQL databases. Use it for internal user directories, custom applications, or legacy systems where you have database access but no API.

Supported databases

• PostgreSQL
• MySQL
• Microsoft SQL Server
• Oracle
• SAP HANA
• SQLite
• Amazon Redshift
• IBM DB2 (available on request — contact C1’s support team for a build)

Resources

• Official download center: Stable binaries (Windows/Linux/macOS) and container images
• GitHub repository: Source code, issues, and example configurations

Configuration overview

Baton-SQL uses a YAML configuration file that defines:

• Database connection details
• Resource types to sync (users, groups, roles)
• Entitlements that can be granted
• Grants that map principals to entitlements
• Provisioning rules for granting/revoking access

Database connection

You can configure the connection using structured fields or a DSN string.

Structured connection

version: "1"
app_name: "Internal User Directory"
app_description: "Syncs users and groups from PostgreSQL"

connect:
  scheme: "postgres"
  host: "${DB_HOST}"
  port: "5432"
  database: "${DB_NAME}"
  user: "${DB_USER}"
  password: "${DB_PASS}"

DSN connection string

app_name: "Internal User Directory"

connect:
  dsn: "postgres://${DB_USER}:${DB_PASS}@${DB_HOST}:5432/${DB_NAME}"

DSN examples by database:

Database	DSN Format
PostgreSQL	postgres://user:pass@host:5432/dbname
MySQL	mysql://user:pass@host:3306/dbname?parseTime=true
SQL Server	sqlserver://user:pass@host:1433?database=dbname
Oracle	oracle://user:pass@host:1521/service
SQLite	sqlite:///path/to/database.db
Amazon Redshift	postgres://user:[e-mail]:5439/dbname
IBM DB2	db2://user:pass@host:50000/dbname

IBM DB2 requires a separate build bundled with IBM’s client driver and isn’t available from the standard download center. Contact C1’s support team to request a DB2-enabled build. Customers connecting to DB2 are responsible for providing their own IBM Db2 Connect license where required.

Listing resources

The list section defines how to query resources from your database:

resource_types:
  user:
    name: "User"
    description: "User accounts in the system"

    list:
      query: |
        SELECT id, username, email, status, created_at
        FROM users
        WHERE active = true
      pagination:
        strategy: "offset"
        primary_key: "id"
      map:
        id: ".id"
        display_name: ".username"
        traits:
          user:
            emails:
              - ".email"
            status: ".status"

Field mapping

Field mappings use CEL (Common Expression Language) to transform data. The dot syntax references columns from the query result:

map:
  id: ".id"
  display_name: ".first_name + ' ' + .last_name"
  description: "string(.department) + ' department user'"
  traits:
    user:
      emails:
        - ".email"
      status: ".status == 'active' ? 'enabled' : 'disabled'"
      login: ".username"
      profile:
        department: ".department"
        joined_date: ".created_at"

Pagination

pagination:
  strategy: "offset"    # or "cursor"
  primary_key: "id"

• offset: Uses LIMIT and OFFSET in SQL queries
• cursor: Fetches records after a certain key value

Entitlements

Entitlements define permissions that can be granted to resources.

Entitlement purpose

The purpose field tells C1 how to interpret an entitlement:

Purpose	Use for	Example
assignment	Membership or role assignment	”Member of Engineering Team”
permission	Specific permission on a resource	”Read access to Repository X”
ownership	Ownership or administrative control	”Owner of Project Y”

Static entitlements

Static entitlements are predefined in the configuration:

resource_types:
  group:
    static_entitlements:
      - id: "member"
        display_name: "'Member'"
        description: "Group membership"
        purpose: "assignment"
        grantable_to:
          - "user"

Dynamic entitlements

Dynamic entitlements are discovered from the database:

entitlements:
  query: |
    SELECT id, name, description
    FROM permissions
    ORDER BY id ASC
    LIMIT ?<Limit> OFFSET ?<Offset>
  map:
    id: ".id"
    display_name: ".name"
    description: ".description"
    purpose: "permission"
    grantable_to:
      - "user"
      - "group"
  pagination:
    strategy: "offset"
    primary_key: "id"

Grants

Grants define which principals have which entitlements:

grants:
  - query: |
      SELECT u.id as user_id, g.id as group_id
      FROM users u
      JOIN group_members gm ON u.id = gm.user_id
      JOIN groups g ON gm.group_id = g.id
      WHERE g.id = ?<group_id>
    map:
      - principal_id: ".user_id"
        principal_type: "user"
        entitlement_id: "member"
    pagination:
      strategy: "offset"
      primary_key: "user_id"

The ?<group_id> syntax binds the current resource ID to the query parameter.

Conditional grant mapping

Use skip_if to conditionally skip grant mappings:

grants:
  - query: |
      SELECT user_id, role_name FROM user_roles
      LIMIT ?<Limit> OFFSET ?<Offset>
    map:
      - skip_if: ".role_name != resource.ID"
        principal_id: ".user_id"
        principal_type: "user"
        entitlement_id: "member"

Provisioning

Provisioning defines how to grant and revoke access.

Grant and revoke

static_entitlements:
  - id: "member"
    display_name: "'Member'"
    purpose: "assignment"
    grantable_to:
      - "user"
    provisioning:
      vars:
        user_id: "principal.ID"
        group_id: "resource.ID"
      grant:
        no_transaction: false    # Set true to disable transaction wrapping
        queries:
          - |
            INSERT INTO group_members (user_id, group_id, created_at)
            VALUES (?<user_id>, ?<group_id>, NOW())
            ON CONFLICT DO NOTHING
      revoke:
        queries:
          - |
            DELETE FROM group_members
            WHERE user_id = ?<user_id> AND group_id = ?<group_id>

Account provisioning

Create new accounts through C1:

resource_types:
  user:
    account_provisioning:
      schema:
        - name: "username"
          description: "The username for the account"
          type: "string"
          placeholder: "newuser"
          required: true
        - name: "email"
          description: "The email address"
          type: "string"
          placeholder: "[e-mail]"
          required: true

      credentials:
        no_password:
          preferred: true
        random_password:
          preferred: false
          constraints:
            - char_set: "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
              min_count: 2
            - char_set: "abcdefghijklmnopqrstuvwxyz"
              min_count: 2
            - char_set: "0123456789"
              min_count: 2
            - char_set: "!@#$%^&*"
              min_count: 1

      validate:
        vars:
          username: "username"
        query: |
          SELECT id FROM users WHERE username = ?<username>

      create:
        vars:
          username: "input.username"
          email: "input.email"
          password: "password"
        queries:
          - |
            INSERT INTO users (username, email, password_hash)
            VALUES (?<username>, ?<email>, crypt(?<password>, gen_salt('bf')))

Password constraints

Use constraints under random_password to enforce character set rules on generated passwords. Each entry specifies a set of characters and the minimum number of those characters that must appear. When defined, these constraints replace any constraints provided by the platform.

random_password:
  preferred: true
  constraints:
    - char_set: "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
      min_count: 2
    - char_set: "abcdefghijklmnopqrstuvwxyz"
      min_count: 2
    - char_set: "0123456789"
      min_count: 2
    - char_set: "!@#$%^&*"
      min_count: 1

Field	Required	Description
char_set	Yes	The set of characters to draw from
min_count	Yes	Minimum number of characters from this set that must appear in the password (must be > 0)

The min_length, max_length, and disallowed_characters fields on random_password are deprecated. They are accepted for backwards compatibility but have no effect — password length is controlled by the platform, and character set rules should be defined using constraints instead.

Credential rotation

Rotate credentials for existing accounts:

credential_rotation:
  credentials:
    random_password:
      preferred: true
      constraints:
        - char_set: "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
          min_count: 2
        - char_set: "abcdefghijklmnopqrstuvwxyz"
          min_count: 2
        - char_set: "0123456789"
          min_count: 2
        - char_set: "!@#$%^&*"
          min_count: 1
  update:
    vars:
      user_id: "resource_id"
      password: "password"
    queries:
      - |
        UPDATE users SET password_hash = crypt(?<password>, gen_salt('bf'))
        WHERE id = ?<user_id>

Running the connector

Validate configuration

baton-sql --config-path ./config.yaml --validate-config-only

One-shot mode (local testing)

baton-sql --config-path ./config.yaml -f sync.c1z
baton resources -f sync.c1z
baton grants -f sync.c1z

Service mode with C1

baton-sql --config-path ./config.yaml \
  --client-id "$C1_CLIENT_ID" \
  --client-secret "$C1_CLIENT_SECRET" \
  --provisioning

Common flags:

Flag	Description
--config-path	Path to YAML configuration file
--client-id	C1 client ID
--client-secret	C1 client secret
-f, --file	Path to save sync data (one-shot mode)
-p, --provisioning	Enable provisioning actions
--log-level	Logging verbosity (debug, info, warn, error)

For deployment instructions, see Deploy a self-hosted connector.

Complete example

version: "1"
app_name: "Internal User Directory"
app_description: "Syncs users and groups from internal PostgreSQL database"

connect:
  scheme: "postgres"
  host: "${DB_HOST}"
  port: "5432"
  database: "directory"
  user: "${DB_USER}"
  password: "${DB_PASSWORD}"

resource_types:
  user:
    name: "User"
    description: "Internal directory user"

    list:
      query: |
        SELECT id, username, email, first_name, last_name,
               status, department, created_at
        FROM users
        WHERE deleted_at IS NULL
      pagination:
        strategy: "offset"
        primary_key: "id"
      map:
        id: ".id"
        display_name: ".first_name + ' ' + .last_name"
        traits:
          user:
            emails:
              - ".email"
            status: ".status == 'active' ? 'enabled' : 'disabled'"
            profile:
              user_id: ".username"
              first_name: ".first_name"
              last_name: ".last_name"

  group:
    name: "Group"
    description: "User group for access control"

    list:
      query: |
        SELECT id, name, description FROM groups
      pagination:
        strategy: "offset"
        primary_key: "id"
      map:
        id: ".id"
        display_name: ".name"
        description: ".description"

    static_entitlements:
      - id: "member"
        display_name: "'Member'"
        purpose: "assignment"
        grantable_to:
          - "user"
        provisioning:
          vars:
            user_id: "principal.ID"
            group_id: "resource.ID"
          grant:
            queries:
              - |
                INSERT INTO group_members (user_id, group_id, added_at)
                VALUES (?<user_id>, ?<group_id>, NOW())
                ON CONFLICT DO NOTHING
          revoke:
            queries:
              - |
                DELETE FROM group_members
                WHERE user_id = ?<user_id> AND group_id = ?<group_id>

    grants:
      - query: |
          SELECT gm.user_id, g.id as group_id
          FROM group_members gm
          JOIN groups g ON gm.group_id = g.id
          WHERE g.id = ?<group_id>
        map:
          - principal_id: ".user_id"
            principal_type: "user"
            entitlement_id: "member"

Find more examples in the baton-sql repository.