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
ScriptPosse 
One thought on “PostgreSQL Allow listing – Adding IP’s to Databases by CRN or by Name”