PostgreSQL Allow listing – Copying IP’s from another Database by Name

The purpose of this post is to demonstrate how to copy existing IP Allow listings from one database instance to another in IBM Cloud.

To begin with in order to perform this allow listing on IBM cloud it is assumed that you’ve already installed the IBMCloud Command Line Interface (CLI) (IBM Cloud CLI Getting Started page) and the plugin for IBM Cloud Databases (ICD) (databases-cli-plugin-cdb-reference). This set of scripts also depends on a command line tool JQ (jq) and information for it can be found here.

Building on the second Article PostgreSQL Allow listing – Adding IP’s to Databases by CRN or by Name we know we can add an allow listing using the functions. Next we’ll demonstrate how we build on these to copy from one database to another… So that in the end we have the same IP’s listed on our ‘read-only replica‘.

The full code is at the bottom of this article.. Now to explain how the main function works.

  1. copyICDAllowListFrom <– This expects a from database -f
    • -f from database
    • -d destination database
    • optional parameters are
      • -t = turn on tracing
      • -j out put in json
  2. The script then calls to see if each database exists with this function testICDDBexist
  3. If they exist then we move onto getting the name of the database with getDBbyName
  4. Next is to get the Database Allowed ips from the -f Database
  5. If needed we switch to the region that the -f database is in with icSwitchRegion
  6. Next we get the count of the number of DBALLows so we can loop thru each and apply the value to the destination.
  7. Now that we are ready to loop through the destination we need to icSwitchRegion to that location where the database is located.
  8. Now we loop thru each IP and add it with addAllowMembers.

Note:

While tested only on databases-for-postgresql, the allowlist functionality exists across the platform, so this should - in theory - work for all deployments in the "IBM Cloud Databases" suite, including MongoDB, Redis, Elasticsearch, etc.
 #!/bin/bash
addAllowMembers()
{
	usage()
	{
	echo " -- Usage for addAllowMembers --- 
    -i = listing of ips to add to the allow list 
    -n = name to use in the mesage 
    -m = message to use instead of using a name
	  -c = the CIDR of the database instance to put the allow list on
	  -t = turn on tracing
	  -j = output in json
    Note: it is assumed that when adding allow members you are in the correct region for the add. If you are not in the correct 
          region to do the add then you'll get a 400 message back from the cli.
_____________________________________________
  example: addAllowMembers -i \$toolscluster -n \$name -c \$cidr
_____________________________________________"
  
  }
  unset traceon
  unset toolsCluster
  unset name
while getopts "i:n:c:m:tj" arg; do
    case "${arg}" in
      i ) 
        local toolsCluster=$OPTARG ;;
      n )
        local name=$OPTARG ;;
	  c )
	  	local cidr=$OPTARG ;;
    m )
       local message=$OPTARG ;;
    t )
        traceon='--trace' 
        ;;
	  j )
	  	json="-j" ;;
      * ) 
        usage
        ;;
    esac
  done
    errorstring="ERROR: This command has three mandatory parameters which cannot be empty ---\n
               -c cidr=$cidr \n
               -i ip=$toolsCluster \n
			         -n name=$name \n
               -m message=$message \n
               message or name can be used interchangeably can use message or name but not both
_____________________________________
              "
    if [ -z "$cidr" ] || [ -z "$toolsCluster" ]; then
      echo >&2 "$errorstring"
      usage
      return 1
    elif [ -z "$name" ] && [ -z "$message" ] ; then
      usage
      return 1
    elif [ -n "$name" ] && [ -n "$message" ] ; then
      usage
      return 1  
    fi
	for c in $cidr;
	 do
		for ip in $(echo ${toolsCluster[@]}); 
			do 
				
        if [ -z "$message" ];then
  			  echo "ibmcloud cdb deployment-allowlist-add $c $ip \"Allowlisting $name node external CIDR: $ip \" $traceon $json;"
        	ibmcloud cdb deployment-allowlist-add "$c" "$ip" "Allowlisting $name node external CIDR: $ip" $traceon $json;
        else
          echo "ibmcloud cdb deployment-allowlist-add $c $ip \"$message\" $traceon $json;"
          ibmcloud cdb deployment-allowlist-add "$c" "$ip" "$message" $traceon $json;
        fi
			done  
	done
}

removeAllowMembers()
{
	usage()
	{
		echo " -- Usage for removeAllowMembers --- 
    -i = listing of ips to add to the allow list
	-c = the CIDR of the database instance to put the allow list on
	-t = turn on tracing
	-j = output in json
  Note: it is assumed that when removing allow members you are in the correct region for the delete. If you are not in the correct 
        region to do the add then you'll get a 400 message back from the cli.
_____________________________________________
  example: removeAllowMembers -i \$toolscluster -c \$cidr
_____________________________________________"
  
  }
  unset traceon
  unset toolsCluster
  unset name
while getopts "i:c:tj" arg; do
    case "${arg}" in
      i ) 
        local toolsCluster=$OPTARG ;;
 	  c )
	  	local cidr=$OPTARG ;;
      t )
       local traceon='--trace' 
        ;;
	  j )
	   local	json="-j" ;;
      * ) 
        usage
        ;;
    esac
  done
      local errorstring="ERROR: This command has 2 mandatory parameters which cannot be empty ---\n
               -c cidr=$cidr \n
               -i ip=$toolsCluster \n
			   -n name=$name \n
_____________________________________
              "
    if [ -z "$cidr" ] || [ -z "$toolsCluster" ] ; then
      echo >&2 "$errorstring"
      usage
        return 1
      fi
	for ip in $(echo ${toolsCluster[@]}); 
		do 
     echo "ibmcloud cdb deployment-allowlist-delete $cidr $ip"
		 ibmcloud cdb deployment-allowlist-delete "$cidr" "$ip" 
		done

}
getAllowMembers()
{
	usage()
	{
		echo " -- Usage for getAllowMembers --- 
    -c = the CIDR of the database instance to put the allow list on
	-t = turn on tracing
	-j = output in json
_____________________________________________
  example: getAllowMembers -c \$cidr
_____________________________________________"
  
  }
  unset traceon
  unset toolsCluster
  unset name
while getopts "c:tj" arg; do
    case "${arg}" in
      c )
	  	local cidr=$OPTARG ;;
      t )
       local traceon='--trace' 
        ;;
	  j )
	  	local json="-j" ;;
      * ) 
        usage
        ;;
    esac
done
	for c in $cidr; 
	  do	
		ibmcloud cdb deployment-whitelist-list $c $traceon $json
	done
}
getDBCidrs()
{
	local environment;environment=$(ibmcloud target --output json | jq -r ".account.name")
	local dbs;dbs=$(ibmcloud cdb ls -a -j )
	echo "$dbs" | jq  "[ .[] | {crn: .crn, name: .name, url: .dashboard_url, env: \"$environment\", lastoperationtype: .last_operation.type , region_id: .region_id}]"
}
getAllDBAllows()
{
  local underline="_______________"
	local cidrs;cidrs=$(getDBCidrs)
	echo $cidrs | jq -c '.[]' | while read c; do
    local name;name=$(echo $c | jq -r ".name")
    local crn;crn=$(echo $c | jq -r ".crn")
    echo $underline
    echo "$name crn - $crn"
    getAllowMembers -c "$crn"
    echo $underline
	done

}
getDBAllowsbyName()
{
  usage()
	{
		echo " -- Usage for getDBAllowsbyName --- 
    -n = name of the database
	  -t = turn on tracing
	  -j = output in json
_____________________________________________
  example: getDBAllowsbyName -n \$name
  #returns json object
  example: getDBAllowsbyName -n cooldbname -j
  #returns object from the ibmcli
  example: getDBAllowsbyName -n cooldbname 
  #returns json object
  example: getDBAllowsbyName -n anothercooldbname -j
_____________________________________________"
  
  }

while getopts "n:tj" arg; do
    case "${arg}" in
      n ) 
        local name=$OPTARG ;;
 	    t )
        local traceon='--trace' ;;
	    j )
	  	  local json="-j" ;;
      * ) 
        usage;;
    esac
  done
      errorstring="ERROR: This command has 1 mandatory parameters which cannot be empty ---\n
			   -n name=$name \n
_____________________________________
              "
    if [ -z "$name" ]  ; then
      echo >&2 "$errorstring"
      usage
        return 1
      fi
  local cidrs;cidrs=$(getDBCidrs)
  local db ;db=$(echo $cidrs | jq ".[] | select(.name == \"$name\")")
  local crn;crn=$(echo $db | jq -r ".crn")
  local allows;allows=$(getAllowMembers -c "$crn" $json $traceon)
  echo $allows 
}
copyICDAllowListFrom()
{
  usage()
	{
		echo " -- Usage for copyICDAllowListFrom --- 
    -f = name of the database that allow list is being copied from
	  -d = name of the database that allow list will be applied to
    -t = turn on tracing
	  -j = output in json
_____________________________________________
  example: copyICDAllowListFrom -f \$name -d \$destination
  #returns json object
  example: copyICDAllowListFrom -f coolfrom -d cooldestination
  example: copyICDAllowListFrom -f coolfrom -d cooldestination
_____________________________________________"
  
  }

while getopts "f:d:tj" arg; do
    case "${arg}" in
      f ) 
        local from=$OPTARG ;;
      d )
        local destination=$OPTARG ;;
 	    t )
        local traceon='--trace' ;;
	    j )
	  	  local json="-j" ;;
      * ) 
        usage;;
    esac
  done
      local errorstring="ERROR: This command has 2 mandatory parameters which cannot be empty ---\n
			   -f from=$from \n
         -d destination=$destination \n
_____________________________________
              "
    if [ -z "$from" ] || [ -z "$destination" ] ; then
      echo >&2 "$errorstring"
      usage
        return 1
      fi
      local icdfromExist;icdfromExist=$(testICDDBexist "$from")
      local icdDestinationExist;icdDestinationExist=$(testICDDBexist "$destination") 
    if $icdfromExist; then
      if $icdDestinationExist  ; then
        local destDB;destDB=$(getDBbyName -n "$destination")
        local destCidr;destCidr=$(echo "$destDB" | jq -r '.crn')
        local destRegion;destRegion=$(echo  "$destDB" | jq -r '.region_id')

        local fromDBAllows;fromDBAllows=$(getDBAllowsbyName -n "$from" -j)
        local ipAllowCount;ipAllowCount=$(echo "$fromDBAllows" | jq ".ip_addresses | length")
        local fromDB;fromDB=$(getDBbyName -n "$from")
        
        local fromRegion;fromRegion=$(echo  "$fromDB" | jq -r '.region_id')
        icSwitchRegion "$destRegion"
        for i in {0..$((ipAllowCount - 1))}; do
          local update;update=$(echo "$fromDBAllows" | jq ".ip_addresses[$i]")
          local ipaddr;ipaddr=$(echo "$update" | jq -r '.address')
          local msg;msg=$(echo "$update" | jq -r '.description')
          echo "addAllowMembers -i $ipaddr -m  $msg -c $destCidr"
          addAllowMembers -i "$ipaddr" -m "$msg" -c "$destCidr"
        done
        icSwitchRegion "$fromRegion"
      else
        echo "Error: ICDDatabase destination: $destination does not exist"
      fi
    else
        echo "ERROR: ICDDatabase from: $from does not exist"
    fi
}
testICDDBexist()
{
  usage()
	{
		echo " -- Usage for testICDDBexist --- 
    %1 = the name of the database
  ___________________________________________
  example: testICDDBexist somecoolName
_____________________________________________
return: 
     if found returns 1
     if not found returns 0
     if no name specified usage is displayed
_____________________________________________
"
  }
  if [ -z "$1" ] ; then
    usage
  else
    local name=$1
    local cidrs;cidrs=$(getDBCidrs)
    local db;db=$(echo $cidrs | jq ".[] | select(.name == \"$name\")")
    if [ -z "$db" ] ; then
      return 0
    else
      return 1
    fi
  fi
}
getAllICDReadReplicas()
{
  local underline="_______________"
	local cidrs=$(getDBCidrs)
  echo $cidrs | jq -c '.[]' | while read c; do
    local name=$(echo $c | jq -r ".name")
    local crn=$(echo $c | jq -r ".crn")
    echo $underline
    echo "$name crn - $crn"
    ibmcloud cdb deployment-read-replicas "$name"
    echo $underline
	done

}
icGetRegions()
{
  regionsJson=$(ibmcloud regions --output "JSON")
  echo "$regionsJson" | jq '.[] | .Name'
  
}
icSwitchRegion()
{
  switchtoregion=$1
  regions=icGetRegions
  currentregion=icGetCurrentRegion
  if [[ $currentregion != $switchtoregion ]] ; then
    ibmcloud target -r $switchtoregion 
  fi


}

icGetCurrentRegion()
{
  ibmcloud target -output json | jq ".region.name"
}
getDBbyName()
{
  usage()
	{
		echo " -- Usage for getDBbyName --- 
    -n = name of the database
	  -t = turn on tracing
	  -j = output in json
_____________________________________________
  example: getDBbyName -n \$name
  #returns json object
  example: getDBbyName -n somecoolname -j
  #returns object from the ibmcli
  example: getDBbyName -n someothercoolname
  #returns json object
  example: getDBbyName -n toocoolforschool -j
_____________________________________________"
  
  }

while getopts "n:tj" arg; do
    case "${arg}" in
      n ) 
        local name=$OPTARG ;;
 	    t )
        local traceon='--trace' ;;
	    j )
	  	  local json="-j" ;;
      * ) 
        usage;;
    esac
  done
      errorstring="ERROR: This command has 1 mandaory parameters which cannot be empty ---\n
			   -n name=$name \n
_____________________________________
              "
    if [ -z "$name" ]  ; then
      echo >&2 "$errorstring"
      usage
        return 1
      fi
  cidrs=$(getDBCidrs)
  db=$(echo "$cidrs" | jq ".[] | select(.name == \"$name\")")
  echo "$db" 
}
addAllowMembersByDBName()
{
	usage()
	{
	echo " -- Usage for addAllowMembers --- 
    -i = listing of ips to add to the allow list 
    -n = name to use in the mesage 
    -m = message to use instead of using a name
	  -d = the databasename of the database instance to put the allow list on
	  -t = turn on tracing
	  -j = output in json
    Note: it is assumed that when adding allow members you are in the correct region for the add. If you are not in the correct 
          region to do the add then you'll get a 400 message back from the cli.
_____________________________________________
  example: addAllowMembers -i \$toolscluster -n \$name -n \$dbname
_____________________________________________"
  
  }
  unset traceon
  unset toolsCluster
  unset name
while getopts "i:n:m:d:tj" arg; do
    case "${arg}" in
      i ) 
        local toolsCluster=$OPTARG ;;
      n )
        local name=$OPTARG ;;
	  d )
	  	local dbname=$OPTARG ;;
    m )
       local message=$OPTARG ;;
    t )
        traceon='--trace' 
        ;;
	  j )
	  	json="-j" ;;
      * ) 
        usage
        ;;
    esac
  done
    errorstring="ERROR: This command has three mandatory parameters which cannot be empty ---\n
               -d dbname=$dbname \n
               -i ip=$toolsCluster \n
			         -n name=$name \n
               -m message=$message \n
               message or name can be used interchangeably can use message or name but not both
_____________________________________
              "
    if [ -z "$dbname" ] || [ -z "$toolsCluster" ]; then
      echo >&2 "$errorstring"
      usage
      return 1
    elif [ -z "$name" ] && [ -z "$message" ] ; then
      usage
      return 1
    elif [ -n "$name" ] && [ -n "$message" ] ; then
      usage
      return 1  
    fi
    local cidr;cidr=$(getDBbyName -n "$dbname" -j )
    local dbcidr;dbcidr=$(echo "$cidr" | jq -r '.crn')
    local dbregion;dbregion=$(echo "$cidr"| jq -r '.region_id')
    local currentregion;currentregion=icGetCurrentRegion
    icSwitchRegion "$dbregion"
    addAllowMembers -i "$toolsCluster" -n "$name" -m "$msg" -c "$dbcidr" 
    icSwitchRegion "$currentregion"
}
removeAllowMembersByDBName()
{
	usage()
	{
	echo " -- Usage for addAllowMembers --- 
    -i = listing of ips to remove from the allow list 
    -d = the databasename of the database instance to put the allow list on
	  -t = turn on tracing
	  -j = output in json
_____________________________________________
  example: removeAllowMembersByDBName -i \$toolscluster  -d \$dbname
_____________________________________________"
  
  }
  unset traceon
  unset toolsCluster
  unset name
while getopts "i:n:m:d:tj" arg; do
    case "${arg}" in
      i ) 
        local toolsCluster=$OPTARG ;;
	  d )
	  	local dbname=$OPTARG ;;
    t )
        traceon='--trace' 
        ;;
	  j )
	  	json="-j" ;;
      * ) 
        usage
        ;;
    esac
  done
    errorstring="ERROR: This command has two mandatory parameters which cannot be empty ---\n
               -d dbname=$dbname \n
               -i ip=$toolsCluster \n
               message or name can be used interchangeably can use message or name but not both
_____________________________________
              "
    if [ -z "$dbname" ] || [ -z "$toolsCluster" ]; then
      echo >&2 "$errorstring"
      usage
      return 1
    fi
    local cidr;cidr=$(getDBbyName -n "$dbname" -j )
    local dbcidr;dbcidr=$(echo "$cidr" | jq -r '.crn')
    local dbregion;dbregion=$(echo "$cidr"| jq -r '.region_id')
    local currentregion;currentregion=icGetCurrentRegion
    icSwitchRegion "$dbregion"
    removeAllowMembers -i "$toolsCluster" -c "$dbcidr" 
    icSwitchRegion "$currentregion"
}
icSwitchRegion()
{
  switchtoregion=$1
  regions=icGetRegions
  currentregion=icGetCurrentRegion
  if [[ $currentregion != $switchtoregion ]] ; then
    ibmcloud target -r $switchtoregion 
  fi


}
getDBAllowsbyName()
{
  usage()
	{
		echo " -- Usage for getDBAllowsbyName --- 
    -n = name of the database
	  -t = turn on tracing
	  -j = output in json
_____________________________________________
  example: getDBAllowsbyName -n \$name
  #returns json object
  example: getDBAllowsbyName -n somecoolname -j
  #returns object from the ibmcli
  example: getDBAllowsbyName -n anothercoolname 
  #returns json object
  example: getDBAllowsbyName -n schooliscool -j
_____________________________________________"
  
  }

while getopts "n:tj" arg; do
    case "${arg}" in
      n ) 
        local name=$OPTARG ;;
 	    t )
        local traceon='--trace' ;;
	    j )
	  	  local json="-j" ;;
      * ) 
        usage;;
    esac
  done
      errorstring="ERROR: This command has 1 mandatory parameters which cannot be empty ---\n
			   -n name=$name \n
_____________________________________
              "
    if [ -z "$name" ]  ; then
      echo >&2 "$errorstring"
      usage
        return 1
      fi
  local cidrs;cidrs=$(getDBCidrs)
  local db ;db=$(echo $cidrs | jq ".[] | select(.name == \"$name\")")
  local crn;crn=$(echo $db | jq -r ".crn")
  local allows;allows=$(getAllowMembers -c "$crn" $json $traceon)
  echo $allows 
}
getDBbyName()
{
  usage()
	{
		echo " -- Usage for getDBbyName --- 
    -n = name of the database
	  -t = turn on tracing
	  -j = output in json
_____________________________________________
  example: getDBbyName -n \$name
  #returns json object
  example: getDBbyName -n somecoolname -j
  #returns object from the ibmcli
  example: getDBbyName -n annothercoolname 
  #returns json object
  example: getDBbyName -n toocoolforschool -j
_____________________________________________"
  
  }

while getopts "n:tj" arg; do
    case "${arg}" in
      n ) 
        local name=$OPTARG ;;
 	    t )
        local traceon='--trace' ;;
	    j )
	  	  local json="-j" ;;
      * ) 
        usage;;
    esac
  done
      errorstring="ERROR: This command has 1 mandatory parameters which cannot be empty ---\n
			   -n name=$name \n
_____________________________________
              "
    if [ -z "$name" ]  ; then
      echo >&2 "$errorstring"
      usage
        return 1
      fi
  cidrs=$(getDBCidrs)
  db=$(echo "$cidrs" | jq ".[] | select(.name == \"$name\")")
  echo "$db" 
}
testICDDBexist()
{
  usage()
	{
		echo " -- Usage for testICDDBexist --- 
    %1 = the name of the database
  ___________________________________________
  example: testICDDBexist somecoolName
_____________________________________________
return: 
     if found returns 1
     if not found returns 0
     if no name specified usage is displayed
_____________________________________________
"
  }
  if [ -z "$1" ] ; then
    usage
  else
    local name=$1
    local cidrs;cidrs=$(getDBCidrs)
    local db;db=$(echo $cidrs | jq ".[] | select(.name == \"$name\")")
    if [ -z "$db" ] ; then
      return 0
    else
      return 1
    fi
  fi
}
copyICDAllowListFrom()
{
  usage()
	{
		echo " -- Usage for copyICDAllowListFrom --- 
    -f = name of the database that allow list is being copied from
	  -d = name of the database that allow list will be applied to
    -t = turn on tracing
	  -j = output in json
_____________________________________________
  example: copyICDAllowListFrom -f \$name -d \$destination
  #returns json object
  example: copyICDAllowListFrom -f FromMaster -d ReadOnlyReplica
  example: copyICDAllowListFrom  -f FromMaster -d ReadOnlyReplica
_____________________________________________"
  
  }

while getopts "f:d:tj" arg; do
    case "${arg}" in
      f ) 
        local from=$OPTARG ;;
      d )
        local destination=$OPTARG ;;
 	    t )
        local traceon='--trace' ;;
	    j )
	  	  local json="-j" ;;
      * ) 
        usage;;
    esac
  done
      local errorstring="ERROR: This command has 2 mandatory parameters which cannot be empty ---\n
			   -f from=$from \n
         -d destination=$destination \n
_____________________________________
              "
    if [ -z "$from" ] || [ -z "$destination" ] ; then
      echo >&2 "$errorstring"
      usage
        return 1
      fi
      local icdfromExist;icdfromExist=$(testICDDBexist "$from")
      local icdDestinationExist;icdDestinationExist=$(testICDDBexist "$destination") 
    if $icdfromExist; then
      if $icdDestinationExist  ; then
        local destDB;destDB=$(getDBbyName -n "$destination")
        local destCidr;destCidr=$(echo "$destDB" | jq -r '.crn')
        local destRegion;destRegion=$(echo  "$destDB" | jq -r '.region_id')

        local fromDBAllows;fromDBAllows=$(getDBAllowsbyName -n "$from" -j)
        local ipAllowCount;ipAllowCount=$(echo "$fromDBAllows" | jq ".ip_addresses | length")
        local fromDB;fromDB=$(getDBbyName -n "$from")
        
        local fromRegion;fromRegion=$(echo  "$fromDB" | jq -r '.region_id')
        icSwitchRegion "$destRegion"
        for i in {0..$((ipAllowCount - 1))}; do
          local update;update=$(echo "$fromDBAllows" | jq ".ip_addresses[$i]")
          local ipaddr;ipaddr=$(echo "$update" | jq -r '.address')
          local msg;msg=$(echo "$update" | jq -r '.description')
          echo "addAllowMembers -i $ipaddr -m  $msg -c $destCidr"
          addAllowMembers -i "$ipaddr" -m "$msg" -c "$destCidr"
        done
        icSwitchRegion "$fromRegion"
      else
        echo "Error: ICDDatabase destination: $destination does not exist"
      fi
    else
        echo "ERROR: ICDDatabase from: $from does not exist"
    fi
}                          

I hope this helps someone

Until then keep scripting

Thom

PostgreSQL Allow listing – Adding IP’s to Databases by CRN or by Name

The purpose of this article is to demonstrate how a function was created to get the current allow listing information for PostgreSQL in IBMCloud.

To begin with in order to perform this allow listing on IBM cloud it is assumed that you’ve already installed the IBMCloud Command Line Interface (CLI) (IBM Cloud CLI Getting Started page) and the plugin for IBM Cloud Databases (ICD) (databases-cli-plugin-cdb-reference). This set of scripts also depends on a command line tool JQ (jq) and information for it can be found here.

When you are setting up a Database in IBM cloud you are presented with an option in the Settings page to allow specific IP’s access to your database. This article illustrates how to add a listing to one or many databases.

Building on the first Article PostgreSQL Allow listing we know we can get all the Allowlisting for each database using those functions. Next we’ll talk about how to Add a specific allow listing to a specific database and then add to that by creating another function to add the same item to many databases.

The command that is used from the cli is:

Ibmcloud cdb deployment-allowlist-add (name or cidr) ip message -j -t 

The name of the database or the cidr can be used with this function. Then next is followed up with the ip or if you choose to use a range of IPS you can also whitelist a range. This is the advice on this command from the official documentation: Add an IP address or range to the current allowlist for a deployment. An IP address is an IPv4 or IPv6 address while a range is a masked IPv4 address, for example, 1.2.3.0/24. The description is required to be a human readable string that describes the allowlisted address or range.

To aid in adding an allow this function was created:

addAllowMembers()
{
	usage()
	{
	echo " -- Usage for addAllowMembers --- 
    -i = listing of ips to add to the allow list 
    -n = name to use in the mesage 
    -m = message to use instead of using a name
	  -c = the CIDR of the database instance to put the allow list on
	  -t = turn on tracing
	  -j = output in json
    Note: it is assumed that when adding allow members you are in the correct region for the add. If you are not in the correct 
          region to do the add then you'll get a 400 message back from the cli.
_____________________________________________
  example: addAllowMembers -i \$toolscluster -n \$name -c \$cidr
  example: addAllowMembers -i \$toolscluster -m \$msg -c \$cidr
_____________________________________________"
  
  }
  unset traceon
  unset toolsCluster
  unset name
while getopts "i:n:c:m:tj" arg; do
    case "${arg}" in
      i ) 
        local toolsCluster=$OPTARG ;;
      n )
        local name=$OPTARG ;;
	  c )
	  	local cidr=$OPTARG ;;
    m )
       local message=$OPTARG ;;
    t )
        traceon='--trace' 
        ;;
	  j )
	  	json="-j" ;;
      * ) 
        usage
        ;;
    esac
  done
    errorstring="ERROR: This command has three mandatory parameters which cannot be empty ---\n
               -c cidr=$cidr \n
               -i ip=$toolsCluster \n
			         -n name=$name \n
               -m message=$message \n
               message or name can be used interchangeably can use message or name but not both
_____________________________________
              "
    if [ -z "$cidr" ] || [ -z "$toolsCluster" ]; then
      echo >&2 "$errorstring"
      usage
      return 1
    elif [ -z "$name" ] && [ -z "$message" ] ; then
      usage
      return 1
    elif [ -n "$name" ] && [ -n "$message" ] ; then
      usage
      return 1  
    fi
	for c in $cidr;
	 do
		for ip in $(echo ${toolsCluster[@]}); 
			do 
				
        if [ -z "$message" ];then
  			  echo "ibmcloud cdb deployment-allowlist-add $c $ip \"Allowlisting $name node external CIDR: $ip \" $traceon $json;"
        	ibmcloud cdb deployment-allowlist-add "$c" "$ip" "Allowlisting $name node external CIDR: $ip" $traceon $json;
        else
          echo "ibmcloud cdb deployment-allowlist-add $c $ip \"$message\" $traceon $json;"
          ibmcloud cdb deployment-allowlist-add "$c" "$ip" "$message" $traceon $json;
        fi
			done  
	done
}


Now that we have a function that wraps the IBMCloud CLI command. We can now create a series of functions to add the allow listing by name:

addAllowMembersByDBName()
{
	usage()
	{
	echo " -- Usage for addAllowMembers --- 
    -i = listing of ips to add to the allow list 
    -n = name to use in the mesage 
    -m = message to use instead of using a name
	  -d = the databasename of the database instance to put the allow list on
	  -t = turn on tracing
	  -j = output in json
    Note: it is assumed that when adding allow members you are in the correct region for the add. If you are not in the correct 
          region to do the add then you'll get a 400 message back from the cli.
_____________________________________________
  example: addAllowMembers -i \$toolscluster -n \$name -n \$dbname
_____________________________________________"
  
  }
  unset traceon
  unset toolsCluster
  unset name
while getopts "i:n:m:d:tj" arg; do
    case "${arg}" in
      i ) 
        local toolsCluster=$OPTARG ;;
      n )
        local name=$OPTARG ;;
	  d )
	  	local dbname=$OPTARG ;;
    m )
       local message=$OPTARG ;;
    t )
        traceon='--trace' 
        ;;
	  j )
	  	json="-j" ;;
      * ) 
        usage
        ;;
    esac
  done
    errorstring="ERROR: This command has three mandatory parameters which cannot be empty ---\n
               -d dbname=$dbname \n
               -i ip=$toolsCluster \n
			         -n name=$name \n
               -m message=$message \n
               message or name can be used interchangeably can use message or name but not both
_____________________________________
              "
    if [ -z "$dbname" ] || [ -z "$toolsCluster" ]; then
      echo >&2 "$errorstring"
      usage
      return 1
    elif [ -z "$name" ] && [ -z "$message" ] ; then
      usage
      return 1
    elif [ -n "$name" ] && [ -n "$message" ] ; then
      usage
      return 1  
    fi
    local cidr;cidr=$(getDBbyName -n "$dbname" -j )
    local dbcidr;dbcidr=$(echo "$cidr" | jq -r '.crn')
    local dbregion;dbregion=$(echo "$cidr"| jq -r '.region_id')
    local currentregion;currentregion=icGetCurrentRegion
    icSwitchRegion "$dbregion"
    addAllowMembers -i "$toolsCluster" -n "$name" -m "$msg" -c "$dbcidr" 
    icSwitchRegion "$currentregion"
}
icGetCurrentRegion()
{
  ibmcloud target -output json | jq ".region.name"
}
icGetRegions()
{
  regionsJson=$(ibmcloud regions --output "JSON")
  echo "$regionsJson" | jq '.[] | .Name'
  
}
icSwitchRegion()
{
  switchtoregion=$1
  regions=icGetRegions
  currentregion=icGetCurrentRegion
  if [[ $currentregion != $switchtoregion ]] ; then
    ibmcloud target -r $switchtoregion 
  fi
}

addAllowMembersByDBName – This function allows the user to add ips to a database based on the database name. This function calls the following children functions which aid in switching between regions during the script run:

icSwitchRegion – This function calls icGetRegions and icGetCurrentRegion to see if the user’s context needs to switch to the region that the database is in ($1 parameter).

icGetCurrentRegion – this function gets the current region the user is in.

icGetRegions – this function gets the available IBMCloud regions

I hope this helps someone

Until then keep scripting

PostgreSQL Allow listing

The purpose of this article is to demonstrate how a function was created to get the current allow listing information for PostgreSQL in IBMCloud.

To begin with in order to perform this allow listing on IBM cloud it is assumed that you’ve already installed the IBMCloud Command Line Interface (CLI) (IBM Cloud CLI Getting Started page) and the plugin for IBM Cloud Databases (ICD) (databases-cli-plugin-cdb-reference). This set of scripts also depends on a command line tool JQ (jq) and information for it can be found here.

When you are setting up a Database in IBM cloud you are presented with an option in the Settings page to allow specific IP’s access to your database. This article illustrates just how to do this with the CLI.

To get all the Ips for a specific cluster in IBM cloud this command can be utilized:

ibmcloud cdb deployment-allowlist-list databaseName

This works great if you just want to get for a single database instance. What if you want to get for all the instances in your account.. To do this we must first get all the database names in the account and this can be done with this handy cli command:

ibmcloud cdb ls -a 

This command will list all the databases you have in the instance you are logged into… Now to stitch it together you can get all the databases and get the results from ls-a into json format and construct a json that has the detail you need in it.. You can then send that to deployment-allowlist-list for each database and get the corresponding allow list. Here are those functions that do just that:

getDBCidrs()
{
	local environment;environment=$(ibmcloud target --output json | jq -r ".account.name")
	local dbs;dbs=$(ibmcloud cdb ls -a -j )
	echo "$dbs" | jq  "[ .[] | {crn: .crn, name: .name, url: .dashboard_url, env: \"$environment\", lastoperationtype: .last_operation.type , region_id: .region_id}]"
}
getAllDBAllows()
{
  local underline="_______________"
	local cidrs;cidrs=$(getDBCidrs)
	echo $cidrs | jq -c '.[]' | while read c; do
    local name;name=$(echo $c | jq -r ".name")
    local crn;crn=$(echo $c | jq -r ".crn")
    echo $underline
    echo "$name crn - $crn"
    getAllowMembers -c "$crn"
    echo $underline
	done

}
getAllowMembers()
{
	usage()
	{
		echo " -- Usage for getAllowMembers --- 
    -c = the CIDR of the database instance to put the allow list on
	-t = turn on tracing
	-j = output in json
_____________________________________________
  example: getAllowMembers -c \$cidr
_____________________________________________"
  
  }
while getopts "c:tj" arg; do
    case "${arg}" in
      c )
	  	local cidr=$OPTARG ;;
      t )
       local traceon='--trace' 
        ;;
	  j )
	  	local json="-j" ;;
      * ) 
        usage
        ;;
    esac
done
	for c in $cidr; 
	  do	
		ibmcloud cdb deployment-whitelist-list $c $traceon $json
	done
}

getDBCidrs – This function gets the CIDRS for each database and constructs a json return with the following items :

crn: identifier in the IBMCloud for the resource.

name: Database name

url: url to the database

env: the name of the account you are logged into as the Environment name

lastoperationtype: Last operation

region_id: The region in the IBMCloud this database is in

getAllowMembers – This function gets the allow members for a given database

getAllDBAllows – This function gets all the database allows across a IBMCloud account

Hopefully this helps someone…

Look for additional articles on this series Next article will be on how to add allow listings to your database instances in IBMCloud.

until then

Keep scripting

Findit BASH Version

In the spirit of short scripts I needed another version of FINDit like you may see in my PowerShell post. Here is that very thing in BASH/ZSH

findit()
{
  #$1 is the path to search for a string
  #$2 is the string to search and find
   usage()
    {
      echo " -- Usage for findit --- 
      \$1 is the path to search for a string
      \$2 is the string to search and find
    __________________________
    example: findit $HOME/.zshrc powerlevel10k
    output:  /Users/thom.schumacher/.zshrc:10:ZSH_THEME=\"powerlevel10k/powerlevel10k\"
            /Users/thom.schumacher/.zshrc:50:source ~/powerlevel10k/powerlevel10k.zsh-theme
    __________________________"
    }
  if [ -z "$1" ] || [ -z "$2" ]; then
    echo >&2 'Need 2 parameters which are not empty'
    usage
  return 1
  fi
  grep -rnw "$1" -e "$2"

}

findit . echo

This will search from your current directory and tell you ever file that it finds the word Echo in.

with the filename and the line it found it.

Following a URL in a BASH function

I had need to ensure a Url was valid.. So i decided to write a function that I could use in my bash session for just that type of thing:

followedUrlValid()
  usage()
  {
    echo " -- Usage for followedUrlValid --- 
    \$1 = the account you wish to login to
      looks for the http_code from getting the url if valid returns true if not returns false
      if the url has redirects it'll follow the redirects
    "
  }
  if [ -z "$1" ]; then
    echo >&2 'Need 1 parameters which are not empty'
    usage
    return 1
  fi
   returnval='false'
   value=$(curl -o /dev/null -LIsw '%{http_code}\n' $1)
   if [ "$value" = 200 ]; then
    returnval='true'
  fi
  echo "$returnval"

Below you can see two uses of this function in your bash/zsh prompt

followedUrlValid https://google.com
true

followedUrlValid https://invalidurl.okiedokie
false

Parsing path in ZSH

Lately i’ve had to work with a new operating system (apple) and it’s built in shell is ZSH.

With that i’ve had to understand how to parse strings in it compared to KSH and Bash. One interesting thing I found is that that the standard $PATH variable in zsh is already split into an array for you in this variable $path. So to test and see if something is in your path like BASH you can simply use that variable to your advantage like this:


getBashVersion()
{
  for p in $path;do
    b="$p/bash"
    if [[ -f "$b" ]]; then
       echo "_____$(echo $p)______"
       $b --version | grep version
       echo "_____________"
    else
      echo "no bash found in $p"
    fi
  done

}

Once you’ve sourced this in your profile you can run like this:

> getBashVersion

no bash found in /Users/username/py3/bin
_____/usr/local/bin______
GNU bash, version 5.1.16(1)-release (x86_64-apple-darwin21.1.0)
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
_____________
no bash found in /usr/bin
_____/bin______
GNU bash, version 3.2.57(1)-release (x86_64-apple-darwin21)
_____________
no bash found in /usr/sbin
no bash found in /sbin
no bash found in /usr/local/MacGPG2/bin
no bash found in /Users/username/src/Python/environments/ekp-regress/bin
no bash found in /Users/username/.cargo/bin
no bash found in /Users/username/.cargo/bin

Hopefully this helps someone

Until then keep scripting

thom

(Findit) Locating previously written scripts

Another utility Function that I like to use a lot. Especially since I have quite a few scripts is this script that I and a friend worked on together:

function findit
{
    param
    (
    [Parameter(Mandatory=$True,Position=0)][string]$SearchString,
    [Parameter(Mandatory=$False)]$Path = "$env:USERPROFILE\Documents",
    [Parameter(Mandatory=$False)]$Filter = "*.ps1"
    )
$launcher = "psedit"    
if($host.name -eq "ConsoleHost")
{ $launcher = "notepad"}
elseif($Host.name -eq "Visual Studio Code Host")
{ $launcher = 'code' }

$s = Get-ChildItem -Path $Path -Filter $Filter -Recurse | Select-String $SearchString | select path, @{n="MatchingLines";e={"$($_.LineNumber.tostring("000")): $($_.Line -replace "^[ \t]*",'')"}} | group path | select name, @{n="Matches";e={$_.Group.MatchingLines  | Out-String}} | Out-GridView -PassThru 
 foreach ($t in $s){ iex "$launcher $($t.name)"}
}  

What this will do is Search through the path you pass and look for a file that has a specific item in it… it will then popup out-gridview and let you choose which files to open.

Until then

Keep Scripting

Quick Script – 64bit vs 32bit

I needed a quick way to find out if a process was running in 32bit mode.

Here is the script to do just that:

get-process |Where-Object{$_.modules.modulename -contains "wow64.dll"}

if you want to validate that the numbers match here are some additional querys

$process = Get-Process
$32Bit = ($process | Where-object{ if((@($_.modules.modulename) -contains "wow64.dll")){$_}})
$64Bit = ($process | where-object { if((@($_.modules.modulename) -notContains "wow64.dll")){$_}})

($32bit.count + $64Bit.count) -eq $process.count


Hope this helps someone

Until then keep Scripting

Add a type Accelerator to PowerShell

Ever wanted to See what type accelerators you have in your current session or better yet add one to your current session.

Well this article will show you two functions for this type of thing:

Function Get-TypeAccelerators{
[psobject].Assembly.GetType("System.Management.Automation.TypeAccelerators")::get
}
Function Add-TypeAcclerator {

param([string]$acceleratorName, [string]$acceleratorClassName)

$accel =[PowerShell].assembly.gettype("System.Management.Automation.TypeAccelerators")
$accleratorAdd = "`$accel::Add(`"$acceleratorName`",[$acceleratorClassName])"
Invoke-expression $accleratorAdd
$builtinField = $accel.Getfield("builtinTypeAccelerators", [System.Reflection.BindingFlags]"Static,NonPublic")
$builtinField.SetValue($builtinField, $accel::Get)
}
Add-TypeAcclerator -acceleratorName FileInfo -acceleratorClassName System.IO.FileSystemInfo

Now if you type in get-typeaccelerators you’ll see the one you added:


Get-TypeAccelerators

Key                          Value                                                              
---                          -----                                                              
Alias                        System.Management.Automation.AliasAttribute                        
AllowEmptyCollection         System.Management.Automation.AllowEmptyCollectionAttribute         
AllowEmptyString             System.Management.Automation.AllowEmptyStringAttribute             
....                   
FileInfo                     System.IO.FileSystemInfo                                           

See this article for further information:

Adding Type Accelerators in the PowerShell 5.0 April 2015 Preview – TechNet Articles – United States (English) – TechNet Wiki (microsoft.com)

Following WebSite Redirects

In order to check header values from a HTTP get sometimes you need to follow a Re-direct to be able to inspect those headers.

This post will show how you how to follow web site redirects:

First we start with creating a Web request object from System.Net.WebRequest

$url = "some url that has redirects"
$request = [System.Net.WebRequest]::Create($url)

With the object for webrequest in the variable request we can set the properties of that object and set the property AllowAutoRedirect to false.

$url = "https://www.google.com"
$request = [System.Net.WebRequest]::Create($url)

Now ask the $request object for a response.

$response=$request.GetResponse()

if($response.Statuscode.toString() -eq "Found")
{}

For each type of response (HttpWebResponse), we can find the methods and properties for that class and in turn take action on them. The first one to take action is the status code. There are a number of response types that you can check for in our case we want to check for any of the status’ that would cause a redirect. Which for the sake of demonstration we’ll start with “Redirect — 302”.

elseif($response.statuscode.tostring() -eq 'Redirect')
{
  $stream = $response.getresponseStream()
  $streamReader = [System.IO.Streamreader]::new($stream)
  $html = $streamReader.ReadtoEnd()
}

When a redirect is detected, the html that is with the redirect must be read to know where the redirect is going to. This is done through creation of a Stream reader to read the stream on the response object. To find the redirect path a small funtion is introduced to read the html object and find the path to where we need to follow the next redirect:

function Get-RedirectPath
{
param($html)
$h = convertfrom-html $html
if((($h.all.tags('Title)) | select-Object -expandProperty text).toString() -eq "Object moved")
{
   ($H.all.tags('a')) | select-object -first 1 -Expandproperty pathname
}
elseif((($h.all.tags('Title)) | select-Object -expandProperty text).toString() -eq "Document moved")
{
  ($H.all.tags('a')) | select-object -first 1 -Expandproperty pathname
}
else
{$null}
}

This function takes the html raw text and converts it to html. When converted to Html we can then look through the tags find the title and look for Document or object moved and get the value in the ‘a’ tag for where the redirect is to. In addition to following the redirect the other requirement was to get the headers in each redirect. Get-Headers is a function created for just this purpose.

Function Get-headers
{
   param([System.Net.HttpWebResponse]$HttpWebResponse)
   $headerHash = @{}
  foreach($header in $HttpWebResponse.headers)
  {
   $headerhash += @{$header = $response.GeteEsponseHeader($header)}
  }
 $headerHash
}

Get-Headers expects and object type of System.Net.HttpWebResponse. Since we have that object type we can call one of its methods to get the response headers. And add each header to a hash table.

If there are no headers then an empty Hashtable will be returned.

Lastly for pass back to the caller the other requirement was to include the $html, $headerhash, $redirect status code, and the date time. In a custom object so decisions could be made on whether or not to follow into the next redirect. Redirect status code is only added when we have a Redirect any other status code that isn’t a redirect will not contain this property.

The full completed scripts are found in this GIST:

Function Get-RedirectedUrl
{
param($url)
function Get-RedirectPath
{
param($html)
$h = convertfrom-html $html
if((($h.all.tags('Title')) | select-Object expandProperty text).toString() -eq "Object moved")
{
($H.all.tags('a')) | select-object first 1 Expandproperty pathname
}
elseif((($h.all.tags('Title')) | select-Object expandProperty text).toString() -eq "Document moved")
{
($H.all.tags('a')) | select-object first 1 Expandproperty pathname
}
else
{$null}
}
Function Get-headers
{
param([System.Net.HttpWebResponse]$HttpWebResponse)
$headerHash = @{}
foreach($header in $HttpWebResponse.headers)
{
$headerhash += @{$header = $response.GetresponseHeader($header)}
}
$headerHash
}
function ConvertFrom-html
{
param([string]$html)
$h = new-object com "HTMLFile"
$H.IHTMLDocument2_write($html)
$h
}
$request = [System.Net.WebRequest]::Create($url)
$response=$request.GetResponse()
$stream = $response.GetREsponseStream()
$streamREader = [System.IO.StreamREader]::new($stream)
$html = $streamREader.ReadToEnd()
if($response.Statuscode.toString() -eq "Found")
{
$headers = get-headers $response
$headers += @{html = Convertfrom-html $html}
$headers +=@{datetime = Get-Date}
[pscustomobject]$headers
}
elseif($response.Statuscode.toString() -eq "MovedPermanently")
{
$headers = get-headers $response
$headers +=@{redirect = $redirect}
$headers += @{html = Convertfrom-html $html}
$headers +=@{datetime = Get-Date}
[pscustomobject]$headers
}
elseif($response.Statuscode.toString() -eq "Redirect")
{
$headers = get-headers $response
$headers +=@{redirect = $redirect}
$headers += @{html = Convertfrom-html $html}
$headers +=@{datetime = Get-Date}
[pscustomobject]$headers
}
elseif($response.Statuscode.toString() -eq "OK")
{
$headers = get-headers $response
$headers +=@{redirect = $redirect}
$headers += @{html = Convertfrom-html $html}
$headers +=@{datetime = Get-Date}
[pscustomobject]$headers
}
if($streamREader)
{$streamREader.close()}
if($response)
{$response.close()}
}
function get-redirectedUrls
{
Param($url)
$urlcheckobject=@{}
$Uri = [uri]$url
$url2check = $url
do{
$value = get-redirectedUrl url $url2check
if($value.redirect)
{
$Url2check = "$(uri.scheme)://$($uri.Host)/$($value.redirect)"
$value |add-member MemberType NoteProperty name url value $Url2check
$urlcheckobject += $value
}
else
{
$url2check = $Null
}
}
until ($url2check -eq $null)
$urlcheckobject
}