5.3. Connect to PostgreSQL

Provision a managed Azure Database for PostgreSQL Flexible Server using a reusable Pulumi component, then connect it to the running app.

Step 1 — Create the PostgresFlexibleServer component

Create a new file postgres_flexible_server.py next to __main__.py:

import pulumi
import pulumi_azure_native as azure_native
import pulumi_random


class PostgresFlexibleServer(pulumi.ComponentResource):
    def __init__(
        self,
        name: str,
        version: str = "18",
        storage_size_gb: int = 32,
        sku_name: str = "Standard_B1ms",
        sku_tier: str = azure_native.dbforpostgresql.SkuTier.BURSTABLE,
        opts: pulumi.ResourceOptions = None
    ):
        super().__init__("workshop:azure:PostgresFlexibleServer", name, {}, opts)
        self.child_opts = pulumi.ResourceOptions(parent=self)

        self._rg = azure_native.resources.ResourceGroup(
            name,
            resource_group_name=f"rg-{name}",
            opts=self.child_opts
        )

        rand = pulumi_random.RandomInteger(
            f"{name}-login-suffix",
            min=10000000,
            max=99999999,
            opts=self.child_opts
        )
        self._admin_login = rand.result.apply(lambda n: f"admin{n}")

        password = pulumi_random.RandomPassword(
            f"{name}-password",
            length=16,
            special=False,
            opts=self.child_opts
        )
        self._admin_password = password.result

        self.server = azure_native.dbforpostgresql.Server(
            name,
            server_name=f"psql-{name}",
            resource_group_name=self._rg.name,
            administrator_login=self._admin_login,
            administrator_login_password=self._admin_password,
            sku=azure_native.dbforpostgresql.SkuArgs(
                name=sku_name,
                tier=sku_tier
            ),
            storage=azure_native.dbforpostgresql.StorageArgs(
                storage_size_gb=storage_size_gb
            ),
            version=version,
            opts=self.child_opts
        )

    def add_database(self, db_name: str) -> azure_native.dbforpostgresql.Database:
        return azure_native.dbforpostgresql.Database(
            f"{self.server._name}-{db_name}",
            server_name=self.server.name,
            resource_group_name=self._rg.name,
            database_name=db_name,
            charset="UTF8",
            collation="en_US.utf8",
            opts=self.child_opts
        )

    def get_npgsql_connection_string(
        self,
        db: azure_native.dbforpostgresql.Database
    ) -> pulumi.Output[str]:
        return pulumi.Output.secret(
            pulumi.Output.all(
                self.server.fully_qualified_domain_name,
                db.name,
                self._admin_login,
                self._admin_password
            ).apply(lambda args:
                f"Host={args[0]};Database={args[1]};Username={args[2]};"
                f"Password={args[3]};SSL Mode=Require"
            )
        )

    def add_firewall_rule(
        self,
        name: str,
        start_ip_address: str,
        end_ip_address: str
    ) -> azure_native.dbforpostgresql.FirewallRule:
        return azure_native.dbforpostgresql.FirewallRule(
            f"{self.server._name}-fw-{name}",
            server_name=self.server.name,
            resource_group_name=self._rg.name,
            firewall_rule_name=name,
            start_ip_address=start_ip_address,
            end_ip_address=end_ip_address,
            opts=self.child_opts
        )

Explanation

The component groups three Azure resources — server, database, and firewall rule — under a single Pulumi parent. The credentials are generated internally so no sensitive values are passed by the caller or stored in config.

ResourcePurpose
pulumi_random.RandomIntegerGenerates a unique 8-digit suffix for the admin login
pulumi_random.RandomPasswordGenerates a 16-character alphanumeric admin password
azure_native.dbforpostgresql.ServerThe Flexible Server instance
azure_native.dbforpostgresql.DatabaseThe application database inside the server
azure_native.dbforpostgresql.FirewallRuleAllows connections from the AKS node subnet

get_npgsql_connection_string builds the connection string only after a database is created, combining four Output values — FQDN, database name, login, and password — into a single secret Output[str] in Npgsql format.

SSL Mode=Require

Npgsql’s SSL Mode=Require encrypts the connection without verifying the server certificate. No CA certificate needs to be installed on the app side — Azure PostgreSQL Flexible Server handles TLS termination transparently.

Step 2 — Wire the component into __main__.py

Add the import at the top of __main__.py:

from postgres_flexible_server import PostgresFlexibleServer

Then add the following block inside main(), after the Kubernetes and DNS setup, before the exports:

    pg = PostgresFlexibleServer(f"pg-{suffix}")
    db = pg.add_database("app")
    pg.add_firewall_rule("aks-egress", aks.egress_ip.ip_address, aks.egress_ip.ip_address)

And add the connection string to the exports:

    pulumi.export("db_connection_string", pg.get_npgsql_connection_string(db))

Explanation

The component provisions its own Resource Group (rg-pg-{suffix}), keeping the PostgreSQL lifecycle independent from the rest of the stack. The firewall rule covers the AKS egress IP so app pods can reach the server.

The connection string is exported as a secret stack output. Pulumi encrypts it in state and pulumi stack output will not display it unless --show-secrets is passed.

Regional quota

PostgreSQL Flexible Server is not available in all Azure regions by default. If pulumi up fails with LocationIsOfferRestricted, request a quota increase for your target region: aka.ms/postgres-request-quota-increase

Step 3 — Deploy

pulumi up

The first deployment provisions the server, which typically takes 5–10 minutes. Subsequent updates are fast unless the server itself changes.

Explanation

PostgreSQL Flexible Server is a managed service — Azure handles the VM, OS patches, and high availability. You declare what you want (version, SKU, storage); Azure figures out where and how to run it.

Step 4 — Patch deployment.yaml with the connection string

Add the POSTGRES_CONNECTION_STRING env var to the container spec in deployment.yaml:

      containers:
        - name: messages-app
          image: YOUR_ACR_LOGIN_SERVER/csharp-messages:latest
          ports:
            - containerPort: 8080
          env:
            - name: POSTGRES_CONNECTION_STRING
              value: "${POSTGRES_CONNECTION_STRING}"

Retrieve the connection string, substitute it into the manifest, and apply:

export POSTGRES_CONNECTION_STRING=$(pulumi stack output db_connection_string --show-secrets)
envsubst < deployment.yaml | kubectl apply -f -

Wait for the rollout and verify the app switched from SQLite to PostgreSQL:

kubectl rollout status deployment/messages-app -n messages-app
kubectl port-forward -n messages-app svc/messages-app 8080:80
curl http://localhost:8080/health

Expected response:

{"status":"ok","database":"postgres","sqliteMode":null}

Explanation

envsubst substitutes ${POSTGRES_CONNECTION_STRING} in the YAML with the exported shell variable before the manifest reaches kubectl. The connection string never has to be typed or copy-pasted manually.

The app checks for POSTGRES_CONNECTION_STRING at startup. When the env var is present it opens an NpgsqlConnection instead of SqliteConnection and runs the PostgreSQL schema migration. Messages submitted after the switch are stored in the managed database and survive pod restarts.

Inspect the application log

While the port-forward is running and you browse to http://localhost:8080, stream the live application log in a second terminal:

kubectl logs -n messages-app deployments/messages-app

You will see the startup database-selection log line and one entry per incoming HTTP request.

Complete files

postgres_flexible_server.py

import pulumi
import pulumi_azure_native as azure_native
import pulumi_random


class PostgresFlexibleServer(pulumi.ComponentResource):
    def __init__(
        self,
        name: str,
        version: str = "18",
        storage_size_gb: int = 32,
        sku_name: str = "Standard_B1ms",
        sku_tier: str = azure_native.dbforpostgresql.SkuTier.BURSTABLE,
        opts: pulumi.ResourceOptions = None
    ):
        super().__init__("workshop:azure:PostgresFlexibleServer", name, {}, opts)
        self.child_opts = pulumi.ResourceOptions(parent=self)

        self._rg = azure_native.resources.ResourceGroup(
            name,
            resource_group_name=f"rg-{name}",
            opts=self.child_opts
        )

        rand = pulumi_random.RandomInteger(
            f"{name}-login-suffix",
            min=10000000,
            max=99999999,
            opts=self.child_opts
        )
        self._admin_login = rand.result.apply(lambda n: f"admin{n}")

        password = pulumi_random.RandomPassword(
            f"{name}-password",
            length=16,
            special=False,
            opts=self.child_opts
        )
        self._admin_password = password.result

        self.server = azure_native.dbforpostgresql.Server(
            name,
            server_name=f"psql-{name}",
            resource_group_name=self._rg.name,
            administrator_login=self._admin_login,
            administrator_login_password=self._admin_password,
            sku=azure_native.dbforpostgresql.SkuArgs(
                name=sku_name,
                tier=sku_tier
            ),
            storage=azure_native.dbforpostgresql.StorageArgs(
                storage_size_gb=storage_size_gb
            ),
            version=version,
            opts=self.child_opts
        )

    def add_database(self, db_name: str) -> azure_native.dbforpostgresql.Database:
        return azure_native.dbforpostgresql.Database(
            f"{self.server._name}-{db_name}",
            server_name=self.server.name,
            resource_group_name=self._rg.name,
            database_name=db_name,
            charset="UTF8",
            collation="en_US.utf8",
            opts=self.child_opts
        )

    def get_npgsql_connection_string(
        self,
        db: azure_native.dbforpostgresql.Database
    ) -> pulumi.Output[str]:
        return pulumi.Output.secret(
            pulumi.Output.all(
                self.server.fully_qualified_domain_name,
                db.name,
                self._admin_login,
                self._admin_password
            ).apply(lambda args:
                f"Host={args[0]};Database={args[1]};Username={args[2]};"
                f"Password={args[3]};SSL Mode=Require"
            )
        )

    def add_firewall_rule(
        self,
        name: str,
        start_ip_address: str,
        end_ip_address: str
    ) -> azure_native.dbforpostgresql.FirewallRule:
        return azure_native.dbforpostgresql.FirewallRule(
            f"{self.server._name}-fw-{name}",
            server_name=self.server.name,
            resource_group_name=self._rg.name,
            firewall_rule_name=name,
            start_ip_address=start_ip_address,
            end_ip_address=end_ip_address,
            opts=self.child_opts
        )

Additions to __main__.py

from postgres_flexible_server import PostgresFlexibleServer

# inside main(), after DNS setup:
    pg = PostgresFlexibleServer(f"pg-{suffix}")
    db = pg.add_database("app")
    pg.add_firewall_rule("aks-egress", aks.egress_ip.ip_address, aks.egress_ip.ip_address)

# exports:
    pulumi.export("db_connection_string", pg.get_npgsql_connection_string(db))