Provisioning a Budget-Friendly Azure PostgreSQL Database with Terraform

Hello. You now have a Key Vault and a clear rule: Terraform may create the secret store and access model, but it must not receive secret values. We will apply that rule to the database layer.

This begins the Docker-Based Deployment with Azure Container Apps module. The immediate goal is to create a small PostgreSQL Flexible Server with Terraform, make it reachable for this short-lived lab, and establish an application database, schema, and runtime role. In the next lesson, the FastAPI container will use the resulting connection string through Key Vault.

The design intentionally favors three properties:

• Low cost: Burstable compute, minimum storage, no high availability, and only seven days of backup retention.
• No database password in Terraform state: the server uses Microsoft Entra authentication for administration; the application password is generated and stored directly in Key Vault.
• A simple future connection path: a public database endpoint protected by firewall rules, rather than a VNet and private DNS architecture. Private networking is a sensible production direction, but it would add network components and make the first Container Apps deployment materially more complex.

---

What Azure Database for PostgreSQL Flexible Server provides

Azure Database for PostgreSQL Flexible Server is a managed PostgreSQL service. Azure operates the underlying VM, managed disks, patching mechanisms, automated backups, and the server process; you configure the PostgreSQL version, compute tier, storage, network access, authentication, and databases.

The boundary is comparable to using a managed database in any cloud: you retain responsibility for database users, schemas, queries, migrations, and application-side connection handling. Azure retains responsibility for the database host and much of its operational plumbing.

For a disposable development database, the main choices are as follows:

Decision	Lab choice	Why
Service	PostgreSQL Flexible Server	The current managed PostgreSQL offering for new Azure deployments.
Compute tier	Burstable	Intended for intermittent development, testing, proof-of-concept, and small workloads.
SKU	B_Standard_B1ms	The smallest common Burstable option: 1 vCore and 2 GiB memory.
Storage	32 GiB	The Flexible Server minimum.
High availability	Disabled	Avoids a standby server and its additional cost.
Backup retention	7 days	The minimum documented retention period.
Geo-redundant backup	Disabled	Unnecessary for this lab and potentially more expensive.
Networking	Public endpoint plus narrow firewall rules	Keeps this first Container Apps path approachable.
Administration	Microsoft Entra ID	Avoids putting the server administrator password into Terraform state.

The Burstable tier uses CPU credits. While the database is mostly idle, it accumulates capacity for short bursts. Under persistent CPU load, credits can deplete and performance can degrade sharply. That is acceptable for a small lab database, but it is not a production sizing decision.

Cost guardrail before provisioning

A Flexible Server is not a free configuration object. While it is running, compute and storage contribute to the cost. Storage and backup-related charges can remain even while compute is stopped. Rates vary substantially by region, currency, subscription agreement, and SKU availability.

Before applying the configuration:

1. In Azure Portal, open Create Azure Database for PostgreSQL flexible server.
2. Select your intended region.
3. Select Burstable, then B1ms, and 32 GiB storage.
4. Note the displayed monthly estimate, then cancel the portal creation. Terraform will perform the actual deployment.
5. Decide on a short usage window. For example, keep the database running only while completing this module, stop it between sessions, and destroy it after the Container Apps deployment exercises.

Cost of this practical action: USD 0. The portal estimate and the cancellation create no billable resource.

A budget of USD 50 per month is realistic only if you treat this as a temporary development database. Do not leave it running continuously for a month merely because the workload is idle.

---

Why this Terraform configuration does not use an administrator password

The Microsoft Terraform quickstart for Flexible Server demonstrates a random_password resource passed to administrator_password. It is a useful infrastructure example, but it is not appropriate for the security boundary established in the previous lesson.

A random_password result is stored in Terraform state. So is a normal sensitive variable when Terraform uses it to set a resource argument. Marking it sensitive would redact CLI output, but not remove it from state.

Instead, this lab enables Microsoft Entra-only authentication:

• Your signed-in Entra user becomes the PostgreSQL administrator.
• The database server has a system-assigned managed identity.
• Terraform stores identifiers and configuration, but no database password.
• You will later create a restricted application role and place its password directly in Key Vault.

This is slightly more involved than a password-admin setup, but it gives the database the same secret-handling discipline as the Key Vault lesson.

A tenant-level prerequisite

Microsoft Entra administration requires Azure to resolve Entra identities. In a personal subscription, this often works directly. In a company-managed tenant, an Entra administrator may need to grant the Flexible Server’s managed identity directory lookup permissions, typically through the Directory Readers role or a more narrowly scoped equivalent.

This is a tenant-governance issue, not a resource-group RBAC issue. Being Owner on an Azure subscription does not automatically make someone an Entra directory administrator.

If Terraform fails while creating the Entra administrator, do not work around it by adding a password to Terraform. First, inspect the Azure activity/error details and ask the tenant administrator for the minimum required directory permission for the server’s managed identity.

---

Define the small, public-access lab database

Add the following to the same Terraform root that contains your existing resource group, Key Vault, and provider configuration. The code assumes the resource group is named azurerm_resource_group.lab; replace that local name if yours differs.

First, add a local variables file that is intentionally not committed. Add this entry to .gitignore:

*.local.auto.tfvars

Then create postgresql.local.auto.tfvars:

postgres_server_name = "pg-grasp-lab-yourinitials-4821"
entra_admin_upn      = "your.name@example.com"
developer_ipv4       = "203.0.113.10"

Use your real current public IPv4 address rather than the documentation address shown above. If you use a VPN, this must be the VPN’s public egress IPv4 address. The server name must be globally unique across Azure, not only within your subscription.

Find your Entra sign-in name with:

az ad signed-in-user show --query userPrincipalName -o tsv

The file contains no credential, but keeping it untracked avoids publishing your personal network address and account identifier.

Now add postgresql.tf:

variable "postgres_server_name" {
  description = "Globally unique Azure PostgreSQL Flexible Server name."
  type        = string
}

variable "entra_admin_upn" {
  description = "UPN of the Microsoft Entra PostgreSQL administrator."
  type        = string
}

variable "developer_ipv4" {
  description = "Current public IPv4 address permitted to administer the lab database."
  type        = string
}

resource "azurerm_postgresql_flexible_server" "app" {
  name                = var.postgres_server_name
  resource_group_name = azurerm_resource_group.lab.name
  location            = azurerm_resource_group.lab.location

  version                    = "16"
  sku_name                   = "B_Standard_B1ms"
  storage_mb                 = 32768
  backup_retention_days      = 7
  geo_redundant_backup_enabled = false

  public_network_access_enabled = true

  # Azure needs this identity when using Microsoft Entra authentication.
  identity {
    type = "SystemAssigned"
  }

  # No administrator_password is supplied to Terraform.
  authentication {
    active_directory_auth_enabled = true
    password_auth_enabled         = false
    tenant_id                     = data.azurerm_client_config.current.tenant_id
  }

  tags = {
    project     = "grasp-azure-lab"
    environment = "lab"
    managed_by  = "terraform"
    cleanup     = "after-container-apps-module"
  }
}

resource "azurerm_postgresql_flexible_server_active_directory_administrator" "lab_admin" {
  server_name         = azurerm_postgresql_flexible_server.app.name
  resource_group_name = azurerm_resource_group.lab.name

  tenant_id      = data.azurerm_client_config.current.tenant_id
  object_id      = data.azurerm_client_config.current.object_id
  principal_name = var.entra_admin_upn
  principal_type = "User"
}

resource "azurerm_postgresql_flexible_server_database" "app" {
  name      = "appdb"
  server_id = azurerm_postgresql_flexible_server.app.id
  charset   = "UTF8"
  collation = "en_US.utf8"
}

# Administration from your current workstation only.
resource "azurerm_postgresql_flexible_server_firewall_rule" "developer" {
  name             = "allow-current-developer-ipv4"
  server_id        = azurerm_postgresql_flexible_server.app.id
  start_ip_address = var.developer_ipv4
  end_ip_address   = var.developer_ipv4
}

# This permits Azure-hosted services to reach the public endpoint.
# It is needed for the upcoming Container Apps deployment.
resource "azurerm_postgresql_flexible_server_firewall_rule" "azure_services" {
  name             = "allow-azure-services"
  server_id        = azurerm_postgresql_flexible_server.app.id
  start_ip_address = "0.0.0.0"
  end_ip_address   = "0.0.0.0"
}

output "postgres_host" {
  description = "PostgreSQL hostname, without credentials."
  value       = azurerm_postgresql_flexible_server.app.fqdn
}

output "postgres_database" {
  description = "Application database name."
  value       = azurerm_postgresql_flexible_server_database.app.name
}

The rule with 0.0.0.0 is Azure’s special firewall representation for allowing connections from Azure-hosted services and resources. It does not mean that every host on the public internet may connect. Your workstation still needs its explicit IPv4 rule.

It is nevertheless deliberately broad within Azure. Any Azure workload with valid database credentials could attempt a connection, so credentials and database authorization remain essential. A production system typically replaces this design with private networking, VNet integration, private DNS, and tightly controlled egress. We will not add those components here because they would obscure the basic deployment path and add cost.

Provider note: Microsoft’s older quickstart may show a General Purpose SKU and a password-based administrator. Do not copy those parts into this lab. The configuration above is intentionally smaller and avoids password material in Terraform.

---

Review and provision deliberately

Before provisioning, make sure that the provider version in your existing lock file supports the authentication and Entra administrator resources. Do not blindly run a provider upgrade in an established Terraform repository just to make an error disappear.

Run:

terraform fmt -recursive
terraform validate
terraform plan -out postgresql.tfplan

Review the plan for these expected resources:

azurerm_postgresql_flexible_server.app
azurerm_postgresql_flexible_server_active_directory_administrator.lab_admin
azurerm_postgresql_flexible_server_database.app
azurerm_postgresql_flexible_server_firewall_rule.developer
azurerm_postgresql_flexible_server_firewall_rule.azure_services

Specifically confirm that:

• sku_name is B_Standard_B1ms, not a General Purpose SKU.
• storage_mb is 32768.
• High availability is absent.
• password_auth_enabled is false.
• No administrator_password, random_password, or Key Vault secret-value resource appears.
• Your firewall rule contains the intended public IPv4 address.

Then apply the reviewed plan:

terraform apply postgresql.tfplan

Provisioning may take several minutes. If the Entra administrator creation reports an authorization or directory-resolution error, stop there and resolve the tenant prerequisite described earlier. Do not switch to a state-stored administrator password as a quick fix.

Cost of this practical action: Charges begin once the Flexible Server is created and running. The B1ms compute SKU is usually the lowest-cost server option, but it is still billable, and 32 GiB storage is also billable. Check the portal estimate for your region before applying. For a short lab session, expect a small fraction of the corresponding monthly estimate; leaving the server running continuously is the actual cost risk.

Confirm the non-secret connection details:

terraform output postgres_host
terraform output postgres_database

az postgres flexible-server show \
  --resource-group "$(terraform output -raw resource_group_name)" \
  --name "$(terraform output -raw postgres_server_name)" \
  --query "{state:state, version:version, sku:sku.name, storage:storage.storageSizeGb}" \
  -o json

If your Terraform configuration does not already output the resource group and server name, add these safe outputs:

output "resource_group_name" {
  value = azurerm_resource_group.lab.name
}

output "postgres_server_name" {
  value = azurerm_postgresql_flexible_server.app.name
}

---

Bootstrap an application schema and a least-privilege runtime role

Terraform is responsible for Azure resources. It is not the right place to manage every SQL statement, application table, or database password. Your application schema is data-plane configuration, so create it through a PostgreSQL client.

For this lesson, the initial schema has one operational purpose: it proves that the application role can access its own schema and supports a small database connectivity check later. It is not a substitute for the application’s real migration mechanism. If your FastAPI application already uses Alembic or another migration tool, that tool should later own the actual application tables.

Obtain a short-lived Entra token

You need the PostgreSQL client program, psql, installed locally. Confirm it first:

psql --version

Set non-secret shell variables from Terraform outputs:

export PGHOST="$(terraform output -raw postgres_host)"
export PGDATABASE="$(terraform output -raw postgres_database)"
export ENTRA_ADMIN="$(az ad signed-in-user show --query userPrincipalName -o tsv)"

Now request an Azure Database for PostgreSQL access token. The token is temporarily placed in PGPASSWORD because PostgreSQL clients use that environment variable for the password field during authentication.

export PGPASSWORD="$(az account get-access-token \
  --resource-type oss-rdbms \
  --query accessToken \
  -o tsv)"

Do not print PGPASSWORD, use shell tracing, or save it to a file. Access tokens are short-lived credentials even though they are not long-lived passwords.

Test the administrative connection:

psql \
  "host=$PGHOST port=5432 dbname=$PGDATABASE user=$ENTRA_ADMIN sslmode=require" \
  -c "SELECT current_user, current_database();"

sslmode=require encrypts the connection in transit. For a public production database, certificate validation with verify-full should be part of the final connection design; this lab keeps the bootstrap path focused and short-lived.

Cost of this practical action: The Entra token request and psql query create no meaningful additional Azure infrastructure cost. The already-running database continues to incur its normal compute and storage charges.

Create the schema and application role

The role name below, app_runtime, represents the identity used by the FastAPI backend. It is deliberately different from your Entra administrator. The application should have permission to use only its schema; it should not own the server or administer other databases.

Create a temporary migration file. It contains no password because the password will be passed as a psql variable.

mkdir -p .secrets
chmod 700 .secrets
umask 077

MIGRATION_FILE="$(mktemp .secrets/bootstrap-schema.XXXXXX.sql)"

cat > "$MIGRATION_FILE" <<'SQL'
\set ON_ERROR_STOP on

CREATE ROLE app_runtime LOGIN PASSWORD :'app_password';

CREATE SCHEMA IF NOT EXISTS app AUTHORIZATION CURRENT_USER;

CREATE TABLE IF NOT EXISTS app.healthcheck (
  id         smallint PRIMARY KEY CHECK (id = 1),
  updated_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO app.healthcheck (id)
VALUES (1)
ON CONFLICT (id) DO UPDATE
SET updated_at = now();

GRANT USAGE ON SCHEMA app TO app_runtime;
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA app
TO app_runtime;

ALTER DEFAULT PRIVILEGES IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE
ON TABLES TO app_runtime;
SQL

Generate a URL-safe-enough lab password using hexadecimal characters. This avoids the immediate URL-encoding complications that passwords containing characters such as @, :, or / would create.

APP_PASSWORD="$(openssl rand -hex 24)"

Apply the schema file through the Entra administrator connection:

psql \
  "host=$PGHOST port=5432 dbname=$PGDATABASE user=$ENTRA_ADMIN sslmode=require" \
  --set=app_password="$APP_PASSWORD" \
  -f "$MIGRATION_FILE"

The CREATE ROLE statement is intentionally a one-time bootstrap action. If you run it again unchanged, PostgreSQL will report that app_runtime already exists. That is safer than silently dropping and recreating a role which may already be in use.

Verify permissions by connecting as the new runtime role:

export PGPASSWORD="$APP_PASSWORD"

psql \
  "host=$PGHOST port=5432 dbname=$PGDATABASE user=app_runtime sslmode=require" \
  -c "SELECT id, updated_at FROM app.healthcheck;"

The query should return the single row with id equal to 1. A successful result proves four things at once:

1. The public firewall rule permits your workstation.
2. DNS resolves the Flexible Server hostname.
3. PostgreSQL accepts the runtime credentials.
4. The restricted role can read its application schema.

---

Replace the Key Vault placeholder with the real connection string

The previous lesson created a placeholder secret called database-url. Replace it now without placing its value in Git or Terraform state.

Construct the connection string in the current shell:

export DATABASE_URL="postgresql://app_runtime:$APP_PASSWORD@$PGHOST:5432/$PGDATABASE?sslmode=require"
export KEY_VAULT_NAME="$(terraform output -raw key_vault_name)"

Write the value to a short-lived ignored file and upload it to Key Vault:

SECRET_FILE="$(mktemp .secrets/database-url.XXXXXX)"

printf '%s' "$DATABASE_URL" > "$SECRET_FILE"

az keyvault secret set \
  --vault-name "$KEY_VAULT_NAME" \
  --name "database-url" \
  --file "$SECRET_FILE" \
  --output none

Clean up both the temporary files and shell variables:

rm -f "$MIGRATION_FILE" "$SECRET_FILE"

unset PGPASSWORD
unset APP_PASSWORD
unset DATABASE_URL

Verify the Key Vault secret’s metadata without displaying its value:

az keyvault secret show \
  --vault-name "$KEY_VAULT_NAME" \
  --name "database-url" \
  --query "{id:id, enabled:attributes.enabled, created:attributes.created}" \
  -o json

Then inspect the Terraform state inventory:

terraform state list

You should see the PostgreSQL server, database, Entra administrator, and firewall resources. You should not see:

random_password
azurerm_key_vault_secret

Cost of this practical action: SQL bootstrap operations and Key Vault secret operations are negligible at this scale. The billable component remains the running PostgreSQL server and its allocated storage.

---

Stop between sessions; destroy when this module is complete

Stopping a server is useful when you need to pause work for a day or two:

az postgres flexible-server stop \
  --resource-group "$(terraform output -raw resource_group_name)" \
  --name "$(terraform output -raw postgres_server_name)"

Start it again before the next deployment lesson:

az postgres flexible-server start \
  --resource-group "$(terraform output -raw resource_group_name)" \
  --name "$(terraform output -raw postgres_server_name)"

Stopping prevents active compute use, but it does not make the resource entirely free: storage and backups can still have charges. Azure may also automatically restart a stopped Flexible Server after a limited stopped period, so do not treat “stopped” as a permanent cleanup strategy.

Keep this database only through the Container Apps lessons that need it. When this database is no longer needed, destroy its Terraform-managed resources deliberately. If this Terraform root also contains your Key Vault and registry, do not run an unreviewed full terraform destroy.

Instead, create a targeted destruction plan for the database-related resources:

terraform plan -destroy \
  -target=azurerm_postgresql_flexible_server_database.app \
  -target=azurerm_postgresql_flexible_server_active_directory_administrator.lab_admin \
  -target=azurerm_postgresql_flexible_server_firewall_rule.developer \
  -target=azurerm_postgresql_flexible_server_firewall_rule.azure_services \
  -target=azurerm_postgresql_flexible_server.app \
  -out postgresql.destroy.tfplan

Read the plan, confirm that it deletes the intended database resources only, and then apply it:

terraform apply postgresql.destroy.tfplan

Cost of this practical action: Destroying the server stops future compute and storage charges for the deleted database. Key Vault, Container Registry, and any other resources left in the resource group may still have their own costs, so review the resource group and your budget alert after cleanup.

---

Key takeaways

You now have a small managed PostgreSQL foundation for the application:

• Azure Database for PostgreSQL Flexible Server hosts a managed PostgreSQL 16 server; Azure manages the underlying platform while you manage schemas, roles, and application use.
• The Burstable B1ms SKU, 32 GiB storage, seven-day backup retention, no high availability, and no geo-redundant backups are deliberately chosen for a temporary lab.
• Microsoft Entra administration avoids the unsafe pattern of giving Terraform a database administrator password.
• The FastAPI runtime identity, app_runtime, has permissions only within the app schema.
• The real DATABASE_URL was placed directly in Key Vault, replacing the placeholder without entering Terraform state.
• A public endpoint is acceptable for this contained lab only because firewall rules and credentials constrain access; private networking is the stronger production design.
• Stopping helps during short pauses, but destroying the server is the reliable way to end ongoing database costs.

Next, you will deploy the FastAPI backend from Azure Container Registry to Azure Container Apps and configure it to retrieve the Key Vault-held database connection securely.