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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s