Changing database server password in DBaaS PostgreSQL
Note:
When setting the PostgreSQL password in DBaaS, ensure that the password does not contain the following special characters:
equal (=), double quote ("), single quote ('), percentage (%), at sign (@), ampersand (&), question mark (?), underscore (_), and hash (#)
- Launch an Azure CLI pod into the AKS cluster using the following command:
$ kubectl run az-cli --image=mcr.microsoft.com/azure-cli:2.53.0 --command sleep infinity
Note:
Access to Azure Key Vault is restricted to specific subnets. Passwords stored in Azure Key Vault can be easily updated from a pod running in AKS.
Connecting to Postgres database using Azure requires installing rdbms-connections. This functionality is applicable to azure-cli 2.53.0.
- Exec into the Azure CLI pod as follows:
$ kubectl exec -it az-cli -- /bin/ash
- From Azure CLI pod, log into Azure account:
$ az login --scope https://graph.microsoft.com//.default
- (Optional) Create a key vault policy to allow the current user to retrieve the database credential.
Obtain the name of your resource group, key vault and ID of the current user by using the following respective commands:
Resource group name:
$ RESOURCE_GROUP=<resource_group_name>
Key vault name:
$ KEY_VAULT_NAME=$(az keyvault list --resource-group $RESOURCE_GROUP --resource-type vault | jq -r '.[].name')
Current user ID name:
$ USER_ID=$(az account show | jq -r '.user.name')
Create a key vault access policy as follows:
$ az keyvault set-policy -n $KEY_VAULT_NAME --upn $USER_ID --resource-group $RESOURCE_GROUP --secret-permissions all
- Obtain the login name for the key vault (DBADMINUSER):
$ DBADMINUSER=$(az keyvault secret show --vault-name $KEY_VAULT_NAME --name dbadminlogin | jq -r .value)
- Obtain the password for the key vault (OLD_DBADMINPASSWORD):
$ OLD_DBADMINPASSWORD=$(az keyvault secret show --vault-name $KEY_VAULT_NAME --name dbadminpassword | jq -r .value)
- Obtain the server name (DBSERVER):
DBSERVER=$(az postgres flexible-server list --resource-group $RESOURCE_GROUP | jq -r '.[].name')
- (Optional) Verify the current password encryption method by using the following command:
az postgres flexible-server execute -p "$OLD_DBADMINPASSWORD" -u $DBADMINUSER -n $DBSERVER -d postgres -q "SELECT * from azure_roles_authtype();" -o table
Following message is displayed:
Successfully connected to twilk-db. Ran Database Query: 'SELECT * from azure_roles_authtype();' Retrieving first 30 rows of query output, if applicable. Closed the connection to twilk-db Authtype Rolename ---------- ------------------------- NOLOGIN azuresu NOLOGIN pg_database_owner NOLOGIN pg_read_all_data NOLOGIN pg_write_all_data NOLOGIN pg_monitor NOLOGIN pg_read_all_settings NOLOGIN pg_read_all_stats NOLOGIN pg_stat_scan_tables NOLOGIN pg_read_server_files NOLOGIN pg_write_server_files NOLOGIN pg_execute_server_program NOLOGIN pg_signal_backend NOLOGIN azure_pg_admin NOLOGIN replication MD5 nbdbadmin
To install
rdbms-connectextension, ensure that you select the Y option. If installing the extension fails in theaz-clicontainer, then some dependencies must be missing. Install the missing dependencies with apk add gcc musl-dev command and try again.The rdbms-connect utility can also be installed using the az extension add --name rdbms-connect command.
The nbdbadmin auth type must be SCRAM-256. Resetting the password as follows will re-encrypt the password correctly.
- Set the new password as follows:
Before setting the new password ensure that you know your database server name or obtain it by using the following command:
NEW_DBADMINPASSWORD="<new_password>"
Use the following command to set the new password:
az postgres flexible-server execute -p $OLD_DBADMINPASSWORD -u $DBADMINUSER -n $DBSERVER -d postgres -q "ALTER USER\"nbdbadmin\" WITH PASSWORD '$NEW_DBADMINPASSWORD';"
Or
If you are only trying to re-encrypt the current password without changing it, use the following command:
az postgres flexible-server execute -p $OLD_DBADMINPASSWORD -u $DBADMINUSER -n $DBSERVER -d postgres -q "ALTER USER\"nbdbadmin\" WITH PASSWORD '$OLD_DBADMINPASSWORD';"
Note:
You can reset the flexible server password by using the following command. This command does not require az extension and potentially could be run outside of the az-cli container.
az postgres flexible-server update -g $RESOURCE_GROUP -n $DBSERVER --admin-password <password>
- Use the following command to verify if the password is using the correct encryption method (SCRAM-SHA-256):
az postgres flexible-server execute -p "$OLD_DBADMINPASSWORD" -u $DBADMINUSER -n $DBSERVER -d postgres -q "SELECT * from azure_roles_authtype();" -o table
Successfully connected to twilk-db. Ran Database Query: 'SELECT * from azure_roles_authtype();' Retrieving first 30 rows of query output, if applicable. Closed the connection to twilk-db Authtype Rolename ---------- ------------------------- NOLOGIN azuresu NOLOGIN pg_database_owner NOLOGIN pg_read_all_data NOLOGIN pg_write_all_data NOLOGIN pg_monitor NOLOGIN pg_read_all_settings NOLOGIN pg_read_all_stats NOLOGIN pg_stat_scan_tables NOLOGIN pg_read_server_files NOLOGIN pg_write_server_files NOLOGIN pg_execute_server_program NOLOGIN pg_signal_backend NOLOGIN azure_pg_admin NOLOGIN replication SCRAM-256 nbdbadmin
- Store the updated password in key vault:
Note:
This step can be skipped if the password is not changed.
az keyvault secret set --vault-name $KEY_VAULT_NAME --name dbadminpassword --value "$NEW_DBADMINPASSWORD"
- (Optional) Delete the key vault access policy created in step 4 above:
$ az keyvault delete-policy -n $KEYVAULT --upn $USER_ID
- Exit from the azure CLI pod:
$ exit
- Delete the az CLI pod:
$ kubectl delete pod az-cli
- (Applicable only for an existing cloudscale deployment) Restart the primary pod:
$ kubectl rollout restart "statefulset/${PRIMARY}" --namespace "${NAMESPACE}"
In the above command,
NAMESPACE is the namespace containing your NetBackup deployment
PRIMARY is the name of primary pod's stateful set
Use the following command to obtain NAMESPACE and PRIMARY:
$ kubectl get --namespace "${NAMESPACE}" primaryserver -o jsonpath='{.items[0].status.attributes.resourceName}'
- Use lambda function to change the password.
LAMBDA_ARN is the ARN of the password changing lambda function. This can be obtained from the lambda function page on AWS console.
NEW_PASSWORD is the new password to be used.
$ aws lambda invoke --function-name $LAMBDA_ARN \ --cli-binary-format raw-in-base64-out --payload '{"password":"$NEW_PASSWORD"}' \ response_file
- Wait for database to be available.
Obtain the POSTGRESQL_ID (database identifier)of your RDS Postgres database from the RDS database page of the AWS console, using the following command:
$ aws rds wait db-instance-available --db-instance-identifier $POSTGRESQL_ID
- Restart the primary pod:
$ kubectl rollout restart "statefulset/${PRIMARY}" --namespace "${NAMESPACE}"
In the above command,
NAMESPACE is the namespace containing your NetBackup deployment
PRIMARY is the name of primary pod's stateful set
Use the following command to obtain NAMESPACE and PRIMARY:
$ kubectl get --namespace "${NAMESPACE}" primaryserver -o jsonpath='{.items[0].status.attributes.resourceName}'