Control: Ensure 'log_hostname' database flag for Cloud SQL PostgreSQL instance is set appropriately

Description

PostgreSQL logs only the IP address of the connecting hosts. The log_hostname flag controls the logging of hostnames in addition to the IP addresses logged. The performance hit is dependent on the configuration of the environment and the host name resolution setup. This parameter can only be set in the postgresql.conf file or on the server command line.

Usage

Run the control in your terminal:

powerpipe control run terraform_gcp_compliance.control.sql_instance_postgresql_log_hostname_database_flag_configured

Snapshot and share results via Turbot Pipes:

powerpipe login
powerpipe control run terraform_gcp_compliance.control.sql_instance_postgresql_log_hostname_database_flag_configured --share

SQL

This control uses a named query:

  select
address as resource,
    case
      when coalesce(trim((attributes_std ->> 'database_version')), '') = '' then 'alarm'
      when (attributes_std ->> 'database_version') not like 'POSTGRES%' then 'skip'
      when (attributes_std -> 'settings' -> 'database_flags' ->> 'name') = 'log_hostname' and
        (attributes_std -> 'settings' -> 'database_flags' ->> 'value') = 'on'
      then 'ok'
      else 'alarm'
    end as status,
    split_part(address, '.', 2) || case
      when coalesce(trim((attributes_std ->> 'database_version')), '') = ''
      then ' ''database_version'' is not defined'
      when (attributes_std ->> 'database_version') not like 'POSTGRES%'
      then ' not a PostgreSQL database'
      when (attributes_std -> 'settings') is null then ' ''settings'' is not defined'
      when (attributes_std -> 'settings' -> 'database_flags') is null then ' ''settings.database_flags'' is not defined'
      when coalesce(trim((attributes_std -> 'settings' -> 'database_flags' ->> 'name')), '') = ''
      then ' ''settings.database_flags.name'' is not defined'
      when coalesce(trim((attributes_std -> 'settings' -> 'database_flags' ->> 'value')), '') = ''
      then ' ''settings.database_flags.value'' is not defined'
      when (attributes_std -> 'settings' -> 'database_flags' ->> 'name') <> 'log_hostname'
      then ' ''log_hostname'' database flag not set'
      when (attributes_std -> 'settings' -> 'database_flags' ->> 'name') = 'log_hostname' and
        (attributes_std -> 'settings' -> 'database_flags' ->> 'value') = 'on'
      then ' ''log_hostname'' database flag set to ''on'''
      else ' ''log_hostname'' database flag set to ''off'''
    end || '.' reason
    , path || ':' || start_line
  from
    terraform_resource
  where
    type = 'google_sql_database_instance';

Control: Ensure 'log_hostname' database flag for Cloud SQL PostgreSQL instance is set appropriately

Description

Usage

SQL

Tags