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

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

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:
          min_length: 16
          max_length: 32
          disallowed_characters: "!@#$%^&*()"
          preferred: false

      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')))

Credential rotation

Rotate credentials for existing accounts:

credential_rotation:
  credentials:
    random_password:
      min_length: 16
      max_length: 32
      preferred: true
  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.