baton-sql: database integration

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

connect:
  scheme: "postgres"        # or "mysql", "oracle", "sqlserver", "sqlite"
  host: "${DB_HOST}"
  port: "5432"
  database: "${DB_NAME}"
  user: "${DB_USER}"
  password: "${DB_PASS}"

resource_types:
  user:
    name: "User"
    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"

grants:
  - query: |
      SELECT u.username as user_id, g.name as group_name
      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"

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

entitlements:
  query: |
    SELECT id, name, description FROM permissions
  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, created_at)
            VALUES (?<user_id>, ?<group_id>, NOW())
      revoke:
        queries:
          - |
            DELETE FROM group_members
            WHERE user_id = ?<user_id> AND group_id = ?<group_id>

resource_types:
  user:
    account_provisioning:
      schema:
        - name: "username"
          type: "string"
          required: true
        - name: "email"
          type: "string"
          required: true

      credentials:
        random_password:
          min_length: 16
          max_length: 32
          preferred: true

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

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"

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