Azure PostgreSQL でプライベートリンクを使用する - 闘うITエンジニアの覚え書き

0_env.sh

#!/bin/bash

# リソース名の接頭文字
PREFIX=XXXXXXXXXXX

# サブスクリプションID
subscriptionId=XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX

# リージョン
region=japanwest

resourceGroup=${PREFIX}Resources

vnetName=${PREFIX}VNet
vnetPrefix=10.1.0.0/16

nsgName=${vnetName}SecGrp
nsgPubRuleName=${nsgName}PubRule
nsgPubInboundPort="8086"

vmSubnetName=${PREFIX}VmSubnet
vmSubnetPrefix=10.1.1.0/24

# 不要
#exSubnetName=${PREFIX}ExSubnet
#exSubnetPrefix=10.1.2.0/24

vmName=${PREFIX}Vm
vmImage=UbuntuLTS      # "az vm image list -o table" で利用可能なイメージの一覧を確認可能
vmIpAddress=10.1.1.5
vmUser=sampleuser

1_resources.sh

#!/bin/bash

# 設定の読み込み
source 0_env.sh

# リソース作成
if [ "$1" == "--create" ]; then

  # リソースグループの作成
  echo az group create
  az group create --name $resourceGroup --location $region

  # NSG(ネットワークセキュリティグループの)作成
  echo az network nsg create
  az network nsg create --resource-group $resourceGroup --name $nsgName

  # NSGルール(Public) SSH
  echo "az network nsg rule create(ssh)"
  az network nsg rule create \
    --resource-group $resourceGroup --nsg-name $nsgName --name ${nsgPubRuleName}2 \
    --access Allow --protocol Tcp --direction Inbound --priority 100 \
    --source-address-prefix Internet --source-port-range "*" --destination-port-range "22"

  # 仮想ネットワーク 及び サブネット作成
  echo az network vnet create
  az network vnet create \
    --name $vnetName --resource-group $resourceGroup \
    --address-prefixes $vnetPrefix --network-security-group $nsgName \
    --subnet-name $vmSubnetName --subnet-prefixes $vmSubnetPrefix

  # VNet統合用のサブネット (不要)
  #echo az vnet subnet create
  #az network vnet subnet create \
  #  --name $exSubnetName \
  #  --resource-group $resourceGroup \
  #  --vnet-name $vnetName \
  #  --address-prefixes $exSubnetPrefix

  # ユーザディレクトリ配下のSSH鍵をバックアップ
  bk_suffix=`date +%Y%m%d%H%M%S`
  if [ -e ~/.ssh/id_rsa ]; then
    mv ~/.ssh/id_rsa ~/.ssh/id_rsa_${bk_suffix}
  fi
  if [ -e ~/.ssh/id_rsa.pub ]; then
    mv ~/.ssh/id_rsa.pub ~/.ssh/id_rsa.pub_${bk_suffix}
  fi

  # 仮想マシンの作成
  echo az vm create
  az vm create \
    --resource-group $resourceGroup --name $vmName --image $vmImage --generate-ssh-keys \
    --vnet-name $vnetName --subnet $vmSubnetName \
    --private-ip-address $vmIpAddress --admin-username $vmUser \
    --public-ip-address-dns-name `echo $vmName | tr '[A-Z]' '[a-z]'`

  # ポート開放
  #az vm open-port --resource-group $resourceGroup --name $vmName --port 80

  # 生成されたSSH鍵を移動( --ssh-dest-key-path が効かない為 )
  mkdir -p pem
  if [ -e ~/.ssh/id_rsa ]; then
    mv ~/.ssh/id_rsa     ./pem/id_rsa_${vmName}
  fi
  if [ -e ~/.ssh/id_rsa.pub ]; then
    mv ~/.ssh/id_rsa.pub ./pem/id_rsa_${vmName}.pub
  fi

  # バックアップしたSSH鍵を戻す
  if [ -e ~/.ssh/id_rsa_${bk_suffix} ]; then
    mv ~/.ssh/id_rsa_${bk_suffix} ~/.ssh/id_rsa
  fi
  if [ -e ~/.ssh/id_rsa.pub_${bk_suffix} ]; then
    mv ~/.ssh/id_rsa.pub_${bk_suffix} ~/.ssh/id_rsa.pub
  fi

fi

# リソース削除
if [ "$1" == "--delete" ]; then
  echo az group delete
  az group delete --name $resourceGroup
fi

0_env_func.sh

#!/bin/bash

source 0_env.sh

# DB接続情報 (ホスト名には作成したプライベートリンクを使用)
DB_HOST=作成したPostgresサーバ名.privatelink.postgres.database.azure.com
DB_PORT=5432
DB_NAME=DB名
DB_USER=DBユーザ名@作成したPostgresサーバ名
DB_PW=DBユーザのパスワード

# Insightのリージョン
insightsRegion=japaneast

# リソースグループ
resourceGroupOrg=${resourceGroup}
resourceGroup=${resourceGroupOrg}Func

# ストレージアカウント名
storageAccountName=${PREFIX}straccount
storageSku=Standard_LRS

# 関数アプリ名
funcAppName=${PREFIX}SampleFunc

# 使用するFunctionsのバージョン
funcVersion=2

# 関数アプリのプラン名
funcPlanName=${PREFIX}plan
funcPlanSku=B1

# Application insights
insightsName=${PREFIX}Insights
insightsDays=30
insightsSetting=""
if [ "$subscriptionId" != "" ]; then
  insightsSetting="--app-insights $insightsName"
fi

2_resources_func.sh

#!/bin/bash

source ./0_env_func.sh

# 関数アプリのデプロイ
deployfunc() {
    cd functions
    exefile=`cat host.json | grep defaultExecutablePath | awk '{print $2}' | sed 's/"//g'`
    rm -rf $exefile
    GOOS=windows GOARCH=amd64 go build -o $exefile
    zip -r ../functions.zip *
    cd ../
    az functionapp deployment source config-zip -g $resourceGroup -n $funcAppName --src functions.zip
    rm -rf functions.zip

    # 関数アプリのURLを表示
    code=`az functionapp keys list -n $funcAppName -g $resourceGroup | grep masterKey | awk '{print $2}' | sed -E 's/("|,)//g'`
    funcAppNameLower=`echo $funcAppName | tr '[:upper:]' '[:lower:]'`
    echo "SampleFunc URL ... https://${funcAppNameLower}.azurewebsites.net/api/SampleFunc?code=${code}"
}

# リソースの作成
if [ "$1" == "--create" ]; then

    #-------------------------------
    # リソースグループの作成
    #-------------------------------
    echo az group create
    az group create --name $resourceGroup --location $region

    #-------------------------------
    # ストレージアカウントの作成
    #-------------------------------
    echo az storage account create
    az storage account create \
      --name $storageAccountName \
      --location $region \
      --resource-group $resourceGroup \
      --sku $storageSku

    #-------------------------------
    # Application Insights 拡張が利用できない場合は追加インストール
    #-------------------------------
    x=`az monitor app-insights --help 2>&1`
    if [ "$?" != "0" ]; then
      az extension add -n application-insights
    fi

    #-------------------------------
    # Application Insights コンポーネント作成
    #-------------------------------
    echo az monitor app-insights component create
    if [ "$subscriptionId" != "" ]; then
      az monitor app-insights component create \
          --app $insightsName \
          --location $insightsRegion \
          --resource-group $resourceGroup \
          --query-access Enabled \
          --retention-time $insightsDays \
          --subscription $subscriptionId
    fi

    #-------------------------------
    # 関数アプリ
    #-------------------------------

    # 関数プランの作成
    echo az functionapp plan create
    az functionapp plan create \
      --name $funcPlanName \
      --resource-group $resourceGroup \
      --location $region \
      --sku $funcPlanSku

    # 関数アプリの作成
    echo az functionapp create
    az functionapp create \
      --name $funcAppName \
      --storage-account $storageAccountName \
      --plan $funcPlanName \
      --resource-group $resourceGroup \
      --functions-version $funcVersion $insightsSetting

    # 関数アプリの環境変数の設定
    echo "az functionapp config appsettings"
    az functionapp config appsettings set \
        --name $funcAppName \
        --resource-group $resourceGroup \
        --settings "DB_HOST=${DB_HOST}" "DB_PORT=${DB_PORT}" "DB_NAME=${DB_NAME}" "DB_USER=${DB_USER}" "DB_PW=${DB_PW}"

    ## 関数アプリのVNet統合(当コマンドはプレビュー版の為、将来で変更/削除される可能性あり)
    #echo az functionapp vnet-integration add
    #az functionapp vnet-integration add \
    #    --name $funcAppName \
    #    --resource-group $resourceGroup \
    #    --vnet $vnetName \
    #    --subnet $exSubnetName

    #-------------------------------
    # 関数アプリのデプロイ
    #-------------------------------

    # すぐにデプロイするとエラー(Timeout)になる場合がある為、少し待つ
    echo "sleep 10 seconds..."
    sleep 10
    deployfunc
fi

# 関数アプリのデプロイのみ
if [ "$1" == "--deployfunc" ]; then
    deployfunc
fi

# リソースの削除
if [ "$1" == "--delete" ]; then
    az group delete --name $resourceGroup -y
    #az group delete --name ${resourceGroupOrg}App -y
fi

functions/host.json

{
    "version": "2.0",
    "httpWorker": {
        "description": {
            "defaultExecutablePath": "server.exe"
        }   
    },  
    "extensions": {
        "queues": {
            "batchSize": 16, 
            "maxDequeueCount": 5,
            "newBatchThreshold": 8
        }   
    },  
    "extensionBundle": {
        "id": "Microsoft.Azure.Functions.ExtensionBundle",
        "version": "[1.*, 2.0.0)"
    }   
}

functions/server.go

package main

import (
    "encoding/json"
    "fmt"
    "log"
    "net/http"
    "os"
    "time"
    "database/sql"
    _ "github.com/lib/pq"
)

type ReturnValue struct {
    Data string
}
type InvokeResponse struct {
    Result map[string]interface{}
}

type InvokeRequest struct {
    Data     map[string]interface{}
    Metadata map[string]interface{}
}

func printDebug(format string, params ...interface{}){
    log.SetOutput(os.Stdout)
    msg := fmt.Sprintf(format, params...)
    log.Printf("[DEBUG] %s\n", msg)
}

func printInfo(format string, params ...interface{}){
    log.SetOutput(os.Stdout)
    msg := fmt.Sprintf(format, params...)
    log.Printf("[INFO] %s\n", msg)
}

func printError(format string, params ...interface{}){
    log.SetOutput(os.Stderr)
    msg := fmt.Sprintf(format, params...)
    log.Printf("[ERROR] %s\n", msg)
    log.SetOutput(os.Stdout)
}

func init(){
    log.SetOutput(os.Stdout)
    log.SetFlags(0)
}

/**
 * 環境変数の取得.
 */
func getEnv(envName string, defaultValue string) string {
    value, exists := os.LookupEnv(envName)
    if exists {
        return value
    } else {
        return defaultValue
    }
}

/**
 * DBのデータを読み取る.
 */
func sampleFuncHandler(w http.ResponseWriter, r *http.Request) {

    printInfo("START sampleFuncHandler")

    defer func(){
        err := recover()
        if err != nil {
            panic(fmt.Sprintf("ERROR sampleFuncHandler, %v\n", err));
        } else {
            printInfo("END sampleFuncHandler")
        }
    }()

    // DBからデータを読み取り
    results := selectData()

    // レスポンスデータ設定
    invokeResponse := InvokeResponse{Result: results}
    js, err := json.Marshal(invokeResponse)
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }

    w.Header().Set("Content-Type", "application/json")
    w.Write(js)
}

/**
 * DBクライアント取得.
 */
func getDbClient() *sql.DB {

    HOST     := getEnv("DB_HOST", "localhost")
    PORT     := getEnv("DB_PORT", "5432")
    DATABASE := getEnv("DB_NAME", "db")
    USER     := getEnv("DB_USER", "user")
    PASSWORD := getEnv("DB_PW"  , "pwd")

    var connectionString string = fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s sslmode=require", HOST, PORT, USER, PASSWORD, DATABASE)
    db, err := sql.Open("postgres", connectionString)
    if err != nil {
        panic(err)
    }

    return db
}

/**
 * データ登録(InfluxDB).
 */
func selectData() map[string]interface{} {

    printInfo("START selectData")

    result := make(map[string]interface{}, 0)

    // DB接続
    client := getDbClient()

    defer func(){
        err := recover()
        if client != nil {
            client.Close()
        }
        if err != nil {
            panic(err)
        }
    }()

    sql_statement := "SELECT id, col1, col2 from sampletable1"
    rows, err := client.Query(sql_statement)
    if err != nil {
        panic(err)
    }

    items := make([]map[string]interface{}, 0)
    for rows.Next() {
        var id int
        var col1 int
        var col2 int
        switch err := rows.Scan(&id, &col1, &col2); err {
        case sql.ErrNoRows:
            printDebug("No rows were returned")
            break
        case nil:
            printDebug("Data row = (%v, %v, %v, %v)\n", id, col1, col2)
            rec := make(map[string]interface{}, 0)
            rec["id"] = id
            rec["col1"] = col1
            rec["col2"] = col2
            items = append(items, rec)
        default:
            if err != nil {
                panic(err)
            }
        }
    }

    result["time"] = time.Now().Format("2006-01-02 15:04:05")
    result["items"] = items

    rows.Close()

    printInfo("END selectData")

    return result
}

func main() {
    httpInvokerPort, exists := os.LookupEnv("FUNCTIONS_HTTPWORKER_PORT")
    if exists {
        printInfo("FUNCTIONS_HTTPWORKER_PORT: " + httpInvokerPort)
    }
    mux := http.NewServeMux()
    mux.HandleFunc("/SampleFunc", sampleFuncHandler)
    log.Println("Go server Listening...on httpInvokerPort:", httpInvokerPort)
    log.Fatal(http.ListenAndServe(":"+httpInvokerPort, mux))
}

functions/SampleFunc/function.json

{
  "bindings": [
    {
      "type": "httpTrigger",
      "direction": "in",
      "name": "req",
      "methods": [
        "get",
        "post"
      ]
    },
    {
      "type": "http",
      "direction": "out",
      "name": "res"
    }
  ]
}

functions/local.settings.json

{
    "IsEncrypted": false,
    "Values": {
      "AzureWebJobsStorage": "UseDevelopmentStorage=true",
      "DB_HOST": "xxxxxxxxxxxx.postgres.database.azure.com",
      "DB_PORT": "5432",
      "DB_NAME": "sampledb1",
      "DB_USER": "user1@xxxxxxxxxxxx",
      "DB_PW": "xxxxxxxxxxxx"
    }   
}