Introduction

This page describes how to configure Azure DataBase for PostgreSQL as Black Duck's data store. Azure Database for PostgreSQL provides added security, and reliability features, such as automatic backups.

Configuration

To work with Azure Database for PostgreSQL from the command line, you must first install the RDBMS extension for Azure CLI by using the following command:

az extension add --name rdbms

Because the database is accessible from all Azure IP addresses, it is imperative that all database users have strong passwords.
You can use the following snippet to generate these passwords after setting the variables PG_ADMIN_PW_FILE and PG_USER_PW_FILE to paths of the files that contain your passwords.

export LC_CTYPE=C
cat /dev/urandom | tr -dc '_A-Z-a-z-0-9\(\)=+!@#\$%&*' | head -c 16 > ${PG_ADMIN_PW_FILE}
cat /dev/urandom | tr -dc '_A-Z-a-z-0-9\(\)=+!@#\$%&*' | head -c 16 > ${PG_USER_PW_FILE}

When you've created your strong passwords, create the database:

az postgres server create --resource-group "${RESOURCE_GROUP_NAME}" --name "${DB_INSTANCE_NAME}"  --location ${ZONE} --admin-user "${PG_ADMIN_USER}" --admin-password "$(cat ${PG_ADMIN_PW_FILE})" --sku-name "GP_Gen4_2" --version "9.6" --ssl-enforcement "Disabled"

Note the following information about some of the parameters that you use:

• The --location value must match that of the Kubernetes cluster.
• You add the --backup-retention parameter to configure how long database backups should be retained.
• You add the --geo-redundant-backup parameter to make database backups geo-redundant.

Refer to the Azure documentation on your backup and restore options.

When the database is created, you must run the initialization script.
To do this, do the following steps:

1. You must create a firewall rule to allow access from your computer,

2. Run the initialization script, and then delete the firewall rule as shown in the following example. 
   This step requires the psql utility. If you do not have it installed and do not want to install it, run the postgres Docker image and use the tool in it.

   # Allow access from our local IP 
   MY_IP="$(dig TXT +short o-o.myaddr.l.google.com @ns1.google.com | awk -F'"' '{ print $2}')" 
       #Enable access from our server 
   az postgres server firewall-rule create --resource-group ${RESOURCE_GROUP_NAME} --server ${DB_INSTANCE_NAME} --name allowLocalMods --start-ip-address ${MY_IP} --end-ip-address ${MY_IP} 
   #Now that we have access, run the database initialization script 
   cat sql/external-postgres-init.pgsql | psql --host=${DB_SERVER_ADDRESS} --port 5432 --user="${PG_ADMIN_USER}@${DB_INSTANCE_NAME}" --dbname=postgres 
   #Set user passwords 
   echo "ALTER ROLE blackduck_user WITH PASSWORD '$(cat ${PG_USER_PW_FILE})';" | psql --host=${DB_SERVER_ADDRESS} --port 5432 --user="${PG_ADMIN_USER}@${DB_INSTANCE_NAME}" --dbname=postgres
   echo "ALTER ROLE blackduck_reporter WITH PASSWORD 'blackduck';" | psql --host=${DB_SERVER_ADDRESS} --port 5432 --user="${PG_ADMIN_USER}@${DB_INSTANCE_NAME}" --dbname=postgres
   # Remove access from our local IP
   az postgres server firewall-rule delete --resource-group ${RESOURCE_GROUP_NAME} --server ${DB_INSTANCE_NAME} --name allowLocalMods --yes
   
   

3. Finally, you must allow access from other Azure IP addresses so that the database is accessible from the Kubernetes cluster:

   az postgres server firewall-rule create --resource-group ${RESOURCE_GROUP_NAME} --server ${DB_INSTANCE_NAME} --name azureIpAccess --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0