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