Azure Database for PostgreSQL flexible servers support

[andreacoccodi]

Is this a request for help?:

No, we were told to open this after a discussion with John Chiao and Jared Ledgister Is this a BUG REPORT or FEATURE REQUEST? (choose one):

Version of Helm and Kubernetes: Server Version: version.Info{Major:"1", Minor:"19", GitVersion:"v1.19.7", GitCommit:"8ab00ff68a1763b5a506a7073cb8e67b12dfbcd7", GitTreeState:"clean", BuildDate:"2021-03-10T23:40:01Z", GoVersion:"go1.15.5", Compiler:"gc", Platform:"linux/amd64"}

helm version version.BuildInfo{Version:"v3.5.4", GitCommit:"1b5edb69df3d3a08df77c9902dc17af864ff05d1", GitTreeState:"dirty", GoVersion:"go1.16.3"}

Which chart: https://github.com/jfrog/charts/tree/master/stable/artifactory-ha artifactory v7.17.5 chart v4.13.0

What happened: We have installed artifactory-ha via the jfrog-platform chart into an azure AKS cluster, we are utilizing the Azure Database for PostgreSQL flexible server however we ran into several issues during the deployment. We had issues regarding roles and templates as well as the usage of a non default postgres admin user (i.e. not postgres) which lead to us needing to make changes to the https://github.com/jfrog/charts/blob/master/stable/jfrog-platform/files/setupPostgres.sh The roles that Azure PostgreSQL flexible exposes are different (azure_pg_admin) and require additions to the createdb function, we have attached a diff of our version below In addition when using the non default user, we use artifactory_admin, there is no database called artifactory_admin leading to the postgres connections failing. Furthermore, with postgres 12 in the Azure PostgreSQL flexible we found an issue whereby the setupPostgres.sh script was creating the database with ENCODING=‘UTF8’ however the default template template1 utilizes ASCII leading to an inability to create the DB, this is resolved by simply adding TEMPLATE template0 which does allow for overwriting values during creation.

What you expected to happen: postgres-setup-init (artifactory-ha and xray init container) to be able to provision users/databases for artifactory/xray/missioncontrol

How to reproduce it (as minimally and precisely as possible): Deploying the chart in k8s using Azure Postgresql Flexible as database

Anything else we need to know:

@@ -25,12 +25,12 @@
     local pass=$2
     [ ! -z ${user} ] || errorExit "user is empty"
     [ ! -z ${pass} ] || errorExit "password is empty"
-    ${PSQL} $POSTGRES_OPTIONS -tAc "SELECT 1 FROM pg_roles WHERE rolname='${user}'" | grep -q 1 1>/dev/null
+    ${PSQL} $POSTGRES_OPTIONS -d postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='${user}'" | grep -q 1 1>/dev/null
     local rc=$?
     # Create user if doesn't exists
     if [[ ${rc} -ne 0 ]]; then
         echo "Creating user ${user}..."
-        ${PSQL} $POSTGRES_OPTIONS -c "CREATE USER ${user} WITH PASSWORD '${pass}';" 1>/dev/null || errorExit "Failed creating user ${user} on PostgreSQL"
+        ${PSQL} $POSTGRES_OPTIONS -d postgres -c "CREATE USER ${user} WITH PASSWORD '${pass}';" 1>/dev/null || errorExit "Failed creating user ${user} on PostgreSQL"
         echo "Done"
     fi
 }
@@ -41,8 +41,10 @@
     local user=$2
     [ ! -z ${db}   ] || errorExit "db is empty"
     [ ! -z ${user} ] || errorExit "user is empty"
-    if ! ${PSQL} $POSTGRES_OPTIONS -lqt | cut -d \| -f 1 | grep -qw ${db} 1>/dev/null; then
-        ${PSQL} $POSTGRES_OPTIONS -c "CREATE DATABASE ${db} WITH OWNER=${user} ENCODING='UTF8';" 1>/dev/null || errorExit "Failed creating db ${db} on PostgreSQL"
+    if ! ${PSQL} $POSTGRES_OPTIONS -d postgres -lqt | awk -F" " '{print $1}' | grep ${db} 1>/dev/null; then
+        ${PSQL} $POSTGRES_OPTIONS -d postgres -c "GRANT ${user} TO azure_pg_admin;"
+        ${PSQL} $POSTGRES_OPTIONS -d postgres -c "CREATE DATABASE ${db} WITH OWNER=${user} ENCODING='UTF8' TEMPLATE template0;" 1>/dev/null || errorExit "Failed creating db ${db} on PostgreSQL"
+        ${PSQL} $POSTGRES_OPTIONS -d postgres -c "REVOKE ${user} FROM azure_pg_admin;"
     fi
 }

@@ -58,8 +60,10 @@
     [ ! -z ${db}     ] || errorExit "db is empty"
     [ ! -z ${user}   ] || errorExit "user is empty"

+    ${PSQL} $POSTGRES_OPTIONS -d postgres -c "GRANT ${user} TO azure_pg_admin;"
     PGOPTIONS='--client-min-messages=warning' ${PSQL} $POSTGRES_OPTIONS --dbname="${db}" -qc "CREATE SCHEMA IF NOT EXISTS ${schema} AUTHORIZATION ${user}" 1>/dev/null
     ${PSQL} $POSTGRES_OPTIONS -c "GRANT ALL ON SCHEMA ${schema} TO ${user}" --dbname="${db}" 1>/dev/null;
+    ${PSQL} $POSTGRES_OPTIONS -d postgres -c "REVOKE ${user} FROM azure_pg_admin;"
 }

 # Check if postgres db is ready
@@ -68,7 +72,7 @@
     ${PSQL} $POSTGRES_OPTIONS --version > /dev/null 2>&1
     outcome1=$?
     # Execute a simple db function to verify if postgres is up and running
-    ${PSQL} $POSTGRES_OPTIONS -l > /dev/null 2>&1
+    ${PSQL} $POSTGRES_OPTIONS -d postgres -l > /dev/null 2>&1
     outcome2=$?
     if [[ $outcome1 -eq 0 ]] && [[ $outcome2 -eq 0  ]]; then
         return 0
@@ -105,7 +109,7 @@
     # Create user
     createUser "${user}" "${pass}"    
     createDB "${db}" "${user}"
-    ${PSQL} $POSTGRES_OPTIONS -c "GRANT ALL PRIVILEGES ON DATABASE ${db} TO ${user}" 1>/dev/null;
+    ${PSQL} $POSTGRES_OPTIONS -d postgres -c "GRANT ALL PRIVILEGES ON DATABASE ${db} TO ${user}" 1>/dev/null;
 }

[rahulsadanandan]

@andreacoccodi Thanks for reporting. We will check this internally and provide updates.

[jtrix72]

@andreacoccodi , I'm currently trying to deploy jfrog-platform to aks using azure pg PaaS . I having a lot of difficulty getting antifactory and xray to talk to pg. Can you please share the db url syntax you used to get this to work ?

I'm currently using

url: 'jdbc:postgresql://<pg-server-instance>.postgres.database.azure.com:5432/<dbname>?sslmode=require'
user: <username>@<pg-server-instance>

I'm not sure what to use for xray. as the standalone xray chart uses user and actualuser properties to make the db connection.

Any help would be greatly appreciated.

[thomasmRavn]

Hey jtrix72, I work on the same team as andrea. Unfortunately Jfrog doesn't really work on azure. We raised this before they created the unified platform. Which allowed us to overwrite the file https://github.com/jfrog/charts/blob/pre-unified-platform/stable/xray/templates/xray-setup-conf.yaml and customise the script to work for azure (it is just changing the postgres URL selectively)

Looking at the GA release they don't seem to expose the setup script anymore. So I am not sure if this can be done anymore.

[jtrix72]

@thomasmRavn , Thank you for the link and your quick response. I have now put jfrog-platform to one side and am concentrating on the standalone charts till the platform chart reaches better maturity. The individual charts seem to install ok. Keeping fingers crossed that there are no curved balls after dev onboarding :) . Thank you so much for your help.

[thomasmRavn]

good luck mate!!

[robin-wayve]

For anyone else this might benefit: I had to enable a server configuration on the Azure Postgres Flexible Server in order for it to work with Artifactory: azure.extensions=PG_TRGM.