id: cloudquery-aws
namespace: company.team

tasks:
  - id: cloudquery
    type: io.kestra.plugin.cloudquery.CloudQueryCLI
    inputFiles:
      config.yml: |
        kind: source
        spec:
          name: aws
          path: cloudquery/aws
          version: "v22.4.0"
          tables: ["aws_s3*"]
          destinations: ["postgresql"]
          spec:
        ---
        kind: destination
        spec:
          name: "postgresql"
          version: "v5.0.3"
          path: "cloudquery/postgresql"
          write_mode: "overwrite-delete-stale"
          spec:
            connection_string: ${PG_CONNECTION_STRING}
    commands:
      - cloudquery sync config.yml --log-console
    env:
      AWS_ACCESS_KEY_ID: "{{ secret('AWS_ACCESS_KEY_ID') }}"
      AWS_SECRET_ACCESS_KEY: "{{ secret('AWS_SECRET_ACCESS_KEY') }}"
      AWS_DEFAULT_REGION: "{{ secret('AWS_DEFAULT_REGION') }}"
      CLOUDQUERY_API_KEY: "{{ secret('CLOUDQUERY_API_KEY') }}"
      PG_CONNECTION_STRING: postgresql://postgres:{{ secret('DB_PASSWORD')
        }}@host.docker.internal:5432/postgres?sslmode=disable

  - id: query_s3_metadata_in_postgres
    type: io.kestra.plugin.jdbc.postgresql.Query
    url: jdbc:postgresql://host.docker.internal:5432/postgres
    username: postgres
    password: "{{ secret('DB_PASSWORD') }}"
    fetchType: FETCH
    sql: |
      SELECT arn, region
      FROM public.aws_s3_buckets
      WHERE block_public_acls IS NOT TRUE
          OR block_public_policy IS NOT TRUE
          OR ignore_public_acls IS NOT TRUE
          OR restrict_public_buckets IS NOT TRUE;