Build a custom connector with Baton-SQL

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}"

app_name: "Internal User Directory"

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

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"

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:
  strategy: "offset"    # or "cursor"
  primary_key: "id"

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

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:
  - 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"

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"

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>

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

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

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>

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

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

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

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"