> ## 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.

# 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](https://dist.conductorone.com/ConductorOne/baton-sql): Stable binaries (Windows/Linux/macOS) and container images
* [GitHub repository](https://github.com/conductorone/baton-sql): 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

```yaml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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

```yaml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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:

```yaml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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:

```yaml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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

```yaml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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:

```yaml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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:

```yaml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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:

```yaml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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:

```yaml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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

```yaml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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:

```yaml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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:
          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:

```yaml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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

```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
baton-sql --config-path ./config.yaml --validate-config-only
```

### One-shot mode (local testing)

```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
baton-sql --config-path ./config.yaml -f sync.c1z
baton resources -f sync.c1z
baton grants -f sync.c1z
```

### Service mode with C1

```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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](/baton/deploy).

## Complete example

```yaml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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](https://github.com/conductorone/baton-sql/tree/main/examples).