Terraform Azure Database for Postgresql

Terraform module to create Azure Database for Postgresql Server.

This module create two groups to manage access control to Postgresql Server:

• A group for administrators
• A group for users

Administrators and Users must be user principals (or, maybe, Service Principal or Managed Idenities).

Usage

Install needed tools

This module use local-exec provisioner to configure database using az cli and psql. This is used because Terraform Provider for Postgresql doesn't support "SET" command. For production, used a script in your CI/CD or IAC tools like Ansible.

Deploy

In order to deploy an Azure Database for Postegresql, the following resources are needed:

• A ressource groupe
• At least one database admistrator (User or server principal)
• If needed, some database users (User or server principal or Managed Identity)

This ressource can be created using terraform, az cli or azure portal.

Then, use this module to deploy an Azure Database for Postegresql:

module "azure_database_postgresql" {
  source                     = "git::https://github.com/0xdbe-terraform/terraform-azure-database-postgresql.git?ref=v2.0.4"
  azure_tenant_id            = data.azurerm_client_config.current.tenant_id
  azure_location             = "eastus"
  application_full_name      = "Hello World"
  application_short_name     = "HW"
  application_environment    = "dev"
  resource_group_name        = module.azure_ressource_group.name
  psql_server_administrators = [data.azurerm_client_config.current.object_id]
  psql_server_users          = ["xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxx"]
}

before deploying, login with an azure account having enough right in order to create Security Group on AzureAD and Ressource Group on Azure

az login --username <ADMIN_USER_PRINCIPAL>

Now, you're ready to deploy

terraform apply

Use with psql

Before using this new Azure Database for Postegresql, some settings must be defined. This must be done using a user principal which is member of administror group.

• Azure authentication

az login --username <ADMIN_USER_PRINCIPAL>

• Get certificate

curl -O --location https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem

• Define connection settings

export SERVER_NAME=psql-server-name
export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms | jq .accessToken | tr -d '"')
export USER_NAME=adm1n157r470r44D

• Database connection

psql "host=${SERVER_NAME}.postgres.database.azure.com \
        sslmode=verify-full \
        sslrootcert=BaltimoreCyberTrustRoot.crt.pem \
        user=${USER_NAME}@${SERVER_NAME} \
        dbname=postgres"

Add users

• Database setting

postgres=> SET aad_validate_oids_in_tenant = off;

• Create a role for a User Principal as User

postgres=> CREATE ROLE "user-name" WITH LOGIN PASSWORD 'object_id' IN ROLE azure_ad_user;

• Create a role for a Security Group as User

postgres=> CREATE ROLE "security-group-name" WITH LOGIN IN ROLE azure_ad_user; 

• Create a role for a Service Principal as User

postgres=> CREATE ROLE "application-name" WITH LOGIN PASSWORD 'app_id' IN ROLE azure_ad_user;

Then, you can run additionnal SQL commands that required admin right (create database, ...).

Test Database connection as User

• Authentication with the appropriate user principal

az login --username <USER_PRINCIPAL>

• Setting server name

export SERVER_NAME=psql-server-hw-dev-XYZ

• Setting Password

export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms | jq .accessToken | tr -d '"')

• Select one of the following command to set user name according object type (user principal, security group or service principal)

export USER_NAME=$(az account list | jq -r '.[] | .user | .name')
export USER_NAME=group-name
export USER_NAME=application-name

• Get certificate

curl -O --location https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem

• Database connection

psql "host=${SERVER_NAME}.postgres.database.azure.com \
        sslmode=verify-full \
        sslrootcert=BaltimoreCyberTrustRoot.crt.pem \
        user=${USER_NAME}@${SERVER_NAME} \
        dbname=appdb"

appdb=> \conninfo

You are connected to database "appdb" as user "group-hw-dev-psql-users@psql-server-hw-dev-XYZ" on host "psql-server-hw-dev-XYZ.postgres.database.azure.com" (address "40.71.8.203") at port "5432".
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

Troubleshooting

Access token not valid for role

psql: error: could not connect to server: FATAL:  Azure AD access token not valid for role group-hw-dev-psql-user (does not contain group ID ba0ea787-9e71-4e9e-834d-71aa7f32361b)
HINT:  Did you specify the correct user name and access token?

• verify that access token includes the new group

jq -R 'split(".") | .[1] | @base64d | fromjson' <<< "$PGPASSWORD"

• Solution

az login

To Do

What still needs to be done:

• Disable default admin generic account
• try do add system assign managed identity (with a simple webapp)
• Allowing to configure SKU and pricing tier
• Restrict network access
• Grant access on databases