Table: aws_lambda_functions

SELECT
  DISTINCT
  'Find all lambda functions that have unrestricted access to the internet'
    AS title,
  account_id,
  arn AS resource_id,
  'fail' AS status
FROM
  aws_lambda_functions,
  jsonb_array_elements_text(configuration->'VpcConfig'->'SecurityGroupIds')
    AS sgs,
  jsonb_array_elements_text(configuration->'VpcConfig'->' SubnetIds') AS sns
WHERE
  sns
  IN (
      SELECT
        a->>'SubnetId'
      FROM
        aws_ec2_route_tables,
        jsonb_array_elements(associations) AS a,
        jsonb_array_elements(routes) AS r
      WHERE
        r->>'DestinationCidrBlock' = '0.0.0.0/0'
        OR r->>'DestinationIpv6CidrBlock' = '::/0'
    )
  AND sgs
    IN (
        SELECT
          id
        FROM
          view_aws_security_group_egress_rules
        WHERE
          ip = '0.0.0.0/0' OR ip6 = '::/0'
      )
UNION
  SELECT
    DISTINCT
    'Find all lambda functions that have unrestricted access to the internet'
      AS title,
    account_id,
    arn AS resource_id,
    'fail' AS status
  FROM
    aws_lambda_functions
  WHERE
    (configuration->'VpcConfig'->>'VpcId') IS NULL;

SELECT
  'Lambda functions should be in a VPC' AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN (configuration->'VpcConfig'->>'VpcId') IS NULL
  OR configuration->'VpcConfig'->>'VpcId' = ''
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_lambda_functions;

SELECT
  'Lambda functions should prohibit public access' AS title,
  account_id,
  arn AS resource_id,
  'fail' AS status
FROM
  aws_lambda_functions,
  jsonb_array_elements(
    CASE jsonb_typeof(policy_document->'Statement')
    WHEN 'string' THEN jsonb_build_array(policy_document->>'Statement')
    WHEN 'array' THEN policy_document->'Statement'
    END
  )
    AS statement
WHERE
  statement->>'Effect' = 'Allow'
  AND (
      statement->>'Principal' = '*'
      OR statement->'Principal'->>'AWS' = '*'
      OR (
          CASE jsonb_typeof(statement->'Principal'->'AWS')
          WHEN 'string' THEN jsonb_build_array(statement->'Principal'->>'AWS')
          WHEN 'array' THEN (statement->'Principal'->>'AWS')::JSONB
          END
        )::JSONB
        ? '*'
    );

SELECT
  'Lambda functions should use supported runtimes' AS title,
  f.account_id,
  f.arn AS resource_id,
  CASE WHEN r.name IS NULL THEN 'fail' ELSE 'pass' END AS status
FROM
  aws_lambda_functions AS f
  LEFT JOIN aws_lambda_runtimes AS r ON r.name = f.configuration->>'Runtime'
WHERE
  f.configuration->>'PackageType' != 'Image';