Skip to main content

Documentation Index

Fetch the complete documentation index at: https://www.c1.ai/docs/llms.txt

Use this file to discover all available pages before exploring further.

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

​
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:
DatabaseDSN Format
PostgreSQLpostgres://user:pass@host:5432/dbname
MySQLmysql://user:pass@host:3306/dbname?parseTime=true
SQL Serversqlserver://user:pass@host:1433?database=dbname
Oracleoracle://user:pass@host:1521/service
SQLitesqlite:///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:
PurposeUse forExample
assignmentMembership or role assignment”Member of Engineering Team”
permissionSpecific permission on a resource”Read access to Repository X”
ownershipOwnership 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: "user@example.com"
          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
FieldRequiredDescription
char_setYesThe set of characters to draw from
min_countYesMinimum 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:
FlagDescription
--config-pathPath to YAML configuration file
--client-idC1 client ID
--client-secretC1 client secret
-f, --filePath to save sync data (one-shot mode)
-p, --provisioningEnable provisioning actions
--log-levelLogging 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.