Console
    Start free

    Collect AWS Redshift logs

    Supported in:

    This document explains how to ingest AWS Redshift logs to Google Security Operations using Amazon S3.

    Amazon Redshift is a fully managed, petabyte-scale cloud data warehouse service from Amazon Web Services. It enables fast querying and analytics across large datasets using SQL-based tools and business intelligence applications. Amazon Redshift logs database activity including authentication attempts, connections, disconnections, user definition changes, and queries run against the database. This integration uses Amazon Redshift's native audit logging feature to deliver log files to an S3 bucket, which Google SecOps then ingests using an Amazon S3 V2 feed.

    Before you begin

    Make sure you have the following prerequisites:

    • A Google SecOps instance
    • Privileged access to the AWS Management Consolewith permissions to manage:
      • Amazon Redshift(clusters, parameter groups)
      • Amazon S3(buckets, bucket policies)
      • AWS IAM(users, policies)

    Configure an S3 bucket for Amazon Redshift audit logging

    Amazon Redshift uploads audit log files to an S3 bucket. You must configure the S3 bucket policy to allow the Amazon Redshift service to write log files.

    Create and configure the S3 bucket

    1. Create an Amazon S3 bucketfollowing this user guide: Creating a bucket .

    2. Save the bucket Nameand Regionfor future reference (for example, redshift-audit-logs-to-secops ).

    3. Select the created bucket in the Amazon S3 consoleat https://console.aws.amazon.com/s3 .

    4. Select the Permissionstab.

    5. In the Bucket policysection, click Edit.

    6. Add the following bucket policy (replace redshift-audit-logs-to-secops with your bucket name):

        { 
  
 "Version" 
 : 
  
 "2012-10-17" 
 , 
  
 "Statement" 
 : 
  
 [ 
  
 { 
  
 "Sid" 
 : 
  
 "Put bucket policy needed for audit logging" 
 , 
  
 "Effect" 
 : 
  
 "Allow" 
 , 
  
 "Principal" 
 : 
  
 { 
  
 "Service" 
 : 
  
 "redshift.amazonaws.com" 
  
 }, 
  
 "Action" 
 : 
  
 [ 
  
 "s3:PutObject" 
 , 
  
 "s3:GetBucketAcl" 
  
 ], 
  
 "Resource" 
 : 
  
 [ 
  
 "arn:aws:s3:::redshift-audit-logs-to-secops" 
 , 
  
 "arn:aws:s3:::redshift-audit-logs-to-secops/*" 
  
 ] 
  
 } 
  
 ] 
 }

    7. Click Save changes.

    Enable the user activity log parameter

    The connection log and user log are captured automatically when audit logging is enabled. To also capture the user activity log (which records each query before it runs on the database), you must enable the enable_user_activity_logging database parameter.

    1. Sign in to the Amazon Redshift consoleat https://console.aws.amazon.com/redshiftv2/ .
    2. On the navigation menu, go to Configurations > Workload management.
    3. Select the parameter group associated with your cluster.
    4. Select the Parameterstab.
    5. Click Edit parameters.
    6. Set the enable_user_activity_loggingparameter value to true.

    7. Click Save.

    Enable Amazon Redshift audit logging to S3

    1. Sign in to the Amazon Redshift consoleat https://console.aws.amazon.com/redshiftv2/ .
    2. On the navigation menu, select Clusters, then select the cluster that you want to configure.
    3. Select the Propertiestab.
    4. In the Database configurationspanel, click Edit, then select Edit audit logging.
    5. On the Edit audit loggingpage, select Turn on.
    6. For Log destination, select S3 bucket.
    7. For S3 bucket, select the bucket you configured (for example, redshift-audit-logs-to-secops ).
    8. Optional: Enter an S3 key prefixto organize the log files within the bucket (for example, audit-logs/ ).
    9. Select the logs to export:
      • Connection log- authentication attempts, connections, and disconnections
      • User log- changes to database user definitions
      • User activity log- each query before it runs on the database
    10. Click Save changes.

    For more information, see Database audit logging in the Amazon Redshift Management Guide.

    Configure an IAM user for Google SecOps

    Google SecOps needs an IAM user with read access to the S3 bucket to ingest the exported audit logs.

    1. Create a Userfollowing this user guide: Creating an IAM user .
    2. Select the created User.
    3. Select Security credentialstab.
    4. Click Create Access Keyin section Access Keys.
    5. Select Third-party serviceas Use case.
    6. Click Next.
      • Optional: Add description tag.
    7. Click Create access key.
    8. Click Download .csv fileto save the Access Keyand Secret Access Keyfor future reference.
    9. Click Done.
    10. Select Permissionstab.
    11. Click Add permissionsin section Permissions policies.
    12. Select Add permissions.
    13. Select Attach policies directly.
    14. Search for AmazonS3FullAccesspolicy.
    15. Select the policy.
    16. Click Next.
    17. Click Add permissions.

    Configure a feed in Google SecOps to ingest AWS Redshift logs

    1. Go to SIEM Settings > Feeds.
    2. Click Add New Feed.
    3. On the next page, click Configure a single feed.
    4. Enter a unique name for the Feed name.
    5. Select Amazon S3 V2as the Source type.
    6. Select AWS Redshiftas the Log type.
    7. Click Nextand then click Submit.

    8. Specify values for the following fields:

      • S3 URI: s3://redshift-audit-logs-to-secops/audit-logs/
      • Source deletion option: Select the deletion option according to your preference
      • Maximum File Age: Include files modified in the last number of days (default is 180 days)
      • Access Key ID: User access key with access to the S3 bucket
      • Secret Access Key: User secret key with access to the S3 bucket
      • Asset namespace: The asset namespace
      • Ingestion labels: The label to be applied to the events from this feed

    9. Click Nextand then click Submit.

    UDM mapping table

    Log Field UDM Mapping Logic
    func_label
    		additional.fields Merged
    lpclagg_label
    		additional.fields Merged
    lpcltype_label
    		additional.fields Merged
    msg
    		metadata.description Directly mapped
    has_principal
    		metadata.event_type Mapped: true → STATUS_UPDATE
    has_target_resource
    		metadata.event_type Mapped: true → USER_RESOURCE_ACCESS
    has_user
    		metadata.event_type Mapped: true → USER_UNCATEGORIZED
    column1
    		metadata.product_event_type Directly mapped
    requestID
    		metadata.product_log_id Directly mapped
    column20
    		network.application_protocol_version Directly mapped
    column9
    		network.session_duration.seconds Renamed/mapped
    column21
    		network.session_id Directly mapped
    xid
    		network.session_id Directly mapped
    column11
    		network.tls.cipher Directly mapped
    column10
    		network.tls.version Directly mapped
    column16
    		principal.application Directly mapped
    column3
    		principal.asset.ip Merged
    log_event.src_ip
    		principal.asset.ip Merged
    column3
    		principal.hostname Directly mapped
    column3
    		principal.ip Merged
    log_event.src_ip
    		principal.ip Merged
    region
    		principal.location.name Directly mapped
    column17
    		principal.platform_version Directly mapped
    column4
    		principal.port Directly mapped
    column5
    		principal.process.pid Directly mapped
    pid
    		principal.process.pid Directly mapped
    accountID
    		principal.user.userid Directly mapped
    user_id
    		principal.user.userid Directly mapped
    authmethod_label
    		security_result.detection_fields Merged
    compression_label
    		security_result.detection_fields Merged
    driver_version_label
    		security_result.detection_fields Merged
    iamauthguid_label
    		security_result.detection_fields Merged
    mtu_label
    		security_result.detection_fields Merged
    plugin_name_label
    		security_result.detection_fields Merged
    ssl_compression_label
    		security_result.detection_fields Merged
    ssl_expansion_label
    		security_result.detection_fields Merged
    level
    		security_result.severity Mapped: (?i)Info → INFORMATIONAL , (?i)Error → ERROR , (?i)Warning → MEDIUM
    file
    		target.file.full_path Directly mapped
    expected_bucket_label
    		target.resource.attribute.labels Merged
    expected_s3_prefix_label
    		target.resource.attribute.labels Merged
    sql_query_label
    		target.resource.attribute.labels Merged
    time_zone_label
    		target.resource.attribute.labels Merged
    ClusterIdentifier
    		target.resource.name Directly mapped
    column6
    		target.resource.name Directly mapped
    db
    		target.resource.name Directly mapped
    column7
    		target.user.user_display_name Directly mapped
    user
    		target.user.user_display_name Directly mapped
    N/A
    		 metadata.event_type Constant: USER_RESOURCE_ACCESS
    N/A
    		 security_result.severity Constant: INFORMATIONAL
    N/A
    		 target.resource.type Constant: DATABASE

    Change Log

    View the Change Log for this parser

    Need more help? Get answers from Community members and Google SecOps professionals.

    Create a Mobile Website
    View Site in Mobile | Classic
    Share by: