Cmdlets for PostgreSQL

Build 24.0.9062

接続の確立

CData Cmdlets ユーザーは、データモジュールをインストールし、接続プロパティを設定してスクリプトを開始できます。このセクションでは、CSV インポートおよびエクスポートcmdlet などのネイティブPowerShell cmdlet でPostgreSQL Cmdlets を使用する例を示します。

インストールおよび接続

PSGet がある場合は、PowerShell Gallery から次のコマンドを使ってcmdlet をインストールできます。CData サイトからセットアップを取得することもできます。 

Install-Module PostgreSQLCmdlets

プロファイルに以下を追加すると、次のセッションでcmdlet がロードされます。 

Import-Module PostgreSQLCmdlets;

Connect-PostgreSQL cmdlet を使って、別のcmdlet に渡すことができる接続オブジェクトを作成します。 

$conn = Connect-PostgreSQL -User 'postgres' -Password 'admin' -Server '127.0.0.1' -Port '5432' -Database 'postgres'

PostgreSQL への接続

PostgreSQL に接続するには、通常次の接続プロパティが必要です。

  • Server：PostgreSQL データベースをホスティングしているサーバーのホスト名またはIP アドレス。
  • User：PostgreSQL サーバーに認証する際に使われるユーザー。

また、オプションで以下を設定することもできます。

  • Database：PostgreSQL サーバーに接続する場合のデータベース。設定されていない場合は、ユーザーのデフォルトデータベースが使用されます。
  • Port：PostgreSQL データベースをホスティングしているサーバーのポート。5432がデフォルトです。

標準

他のスキームを選択しない限り、本製品 がPostgreSQL サーバーに接続する際に使用するデフォルトの認証メカニズムはパスワードです。

標準認証を使用する場合、PostgreSQL にログイン資格情報で接続するにはAuthScheme をPassword に設定します。

そして、認証するために、認証するユーザに紐づいたPassword を設定します。

pg_hba.conf 認証スキーム

本製品 がサポートするパスワード認証スキームのサブタイプは、PostgreSQL サーバー上のpg_hba.conf ファイルで有効化する必要があります。

PostgreSQL サーバーでの認証の設定について、詳しくはPostgreSQL ドキュメントを参照してください。

MD5

本製品 は、MD5 でパスワードを検証することで認証できます。pg_hba.conf ファイルのauth-method をmd5 に設定し、この認証方法を有効にする必要があります。

SASL

本製品 は、SASL（特にSCRAM-SHA-256）でパスワードを検証することで認証できます。pg_hba.conf ファイルのauth-method をscram-sha-256 に設定し、この認証方法を有効にする必要があります。

Azure

Microsoft Azure でPostgreSQL への接続に利用できる方法は以下のとおりです。

  • Azure Active Directory OAuth
  • Azure Active Directory Password
  • Azure Active Directory MSI

Azure AD

Azure AD は、Microsoft のマルチテナント、クラウドベースのディレクトリおよびID 管理サービスです。これはユーザーベースの認証で、AuthScheme をAzureAD に設定する必要があります。

Web アプリケーションを介したAzure AD への認証には、必ずカスタムOAuth アプリケーションの作成が必要です。詳細はAzure AD アプリケーションの作成 を参照してください。

デスクトップアプリケーション

CData は、デスクトップアプリケーションからAzure AD への接続を簡略化する埋め込みOAuth アプリケーションを提供します。

カスタムOAuth アプリケーションを使用して、デスクトップアプリケーションで認証することもできます。（詳しくは、Azure AD アプリケーションの作成 を参照してください。） Azure AD 経由で認証するには、以下のパラメータを設定します。

  • AuthScheme：AzureAD。

  • カスタムアプリケーションのみ：

    • OAuthClientId：カスタムOAuth アプリケーションの登録時に割り当てられたクライアントId。
    • OAuthClientSecret：カスタムOAuth アプリケーションの登録時に割り当てられたクライアントシークレット。
    • CallbackURL：カスタムOAuth アプリケーションの登録時に定義したリダイレクトURI。

接続すると、本製品 はデフォルトブラウザでPostgreSQL のOAuth エンドポイントを開きます。ログインして、アプリケーションにアクセス許可を与えます。

本製品 はOAuth プロセスを完了し、PostgreSQL からアクセストークンを取得してそれを使ってデータをリクエストします。 OAuth 値はOAuthSettingsLocation で指定されたパスに保存されます。これらの値は接続間で永続化されます。

アクセストークンの期限が切れたときは、本製品 は自動でアクセストークンをリフレッシュします。

ヘッドレスマシン

ヘッドレスマシンのユーザーアカウントでドライバーを設定するには、インターネットブラウザに対応した別の端末で認証する必要があります。

以下のいずれかの方法で行います。

  • 後述のオプション1：Verifier code を取得および交換に従い、OAuthVerifier 値を取得します。
  • 後述のオプション2：OAuth 設定を転送で説明するように、本製品 を別のマシンにインストールします。通常のブラウザベースのフローで認証した後、OAuth 認証値を転送します。

オプション1：Verifier code を取得および交換

  1. 認可エンドポイントを見つけます。

    カスタムアプリケーションのみ： 次のプロパティを設定して、Authorization URL を作成します。

    • OAuthClientId：アプリケーションの登録時に割り当てられたクライアントId。
    • OAuthClientSecret：アプリケーションの登録時に割り当てられたクライアントシークレット。

    カスタムアプリケーションおよび埋め込みアプリケーション：GetOAuthAuthorizationUrl ストアドプロシージャを呼び出します。

    1. ストアドプロシージャによって返されたURL をブラウザで開きます。
    2. ログインして、本製品 にアクセス許可を与えます。verifier code を含むコールバックURL にリダイレクトされます。
    3. verifier code の値を保存します。この値は後でOAuthVerifier 接続プロパティを設定する際に使用します。

  2. OAuth verifier code をOAuth リフレッシュトークンおよびアクセストークンと交換します。

    ヘッドレスマシンでは、次のプロパティを設定します。

    • AuthScheme：AzureAD。
    • OAuthVerifier：verifier code。
    • OAuthSettingsLocation：接続間で維持されるOAuth トークンの値を保存するファイルの場所。

    • カスタムアプリケーションのみ：

      • OAuthClientId：カスタムOAuth アプリケーション設定のクライアントId。
      • OAuthClientSecret：カスタムOAuth アプリケーション設定のクライアントシークレット。

  3. OAuth 設定ファイルが生成されたら、以下のように接続プロパティをリセットします。

    • OAuthSettingsLocation：暗号化されたOAuth 認証値が保存される場所。アクセストークンの自動リフレッシュを有効にするために、この場所が本製品 に読み書きのアクセス許可を与えることを確認してください。

    • カスタムアプリケーションのみ：

      • OAuthClientId：アプリケーションの登録時に割り当てられたクライアントId。
      • OAuthClientSecret：アプリケーションの登録時に割り当てられたクライアントシークレット。

オプション2：OAuth 設定を転送

ヘッドレスマシン経由の接続に先立ち、インターネットブラウザに対応したデバイスでドライバーとの接続を作成し、インストールする必要があります。上述の「デスクトップアプリケーション」の説明に従って、接続プロパティを設定します。

「デスクトップアプリケーション」の手順を完了すると、生成された認証値は、OAuthSettingsLocation で指定された場所に暗号化されて書き込まれます。デフォルトのファイル名はOAuthSettings.txt です。

接続が正常にテストされたら、OAuth 設定ファイルをヘッドレスマシンにコピーします。

ヘッドレスマシンでは、次のプロパティを設定します。

  • AuthScheme：AzureAD。
  • OAuthSettingsLocation：OAuth 設定ファイルの場所。アクセストークンの自動リフレッシュを有効にするために、この場所が本製品 に読み書きのアクセス許可を与えることを確認してください。

  • カスタムアプリケーションのみ：

    • OAuthClientId：アプリケーションの登録時に割り当てられたクライアントId。
    • OAuthClientSecret：アプリケーションの登録時に割り当てられたクライアントシークレット。

Note： Azure PostgreSQL Flexible サーバーはサポート対象外です。Azure PostgreSQL Single Server インスタンスのみサポートされます。

Azure PostgreSQL インスタンスでActive Directory 管理者が設定されていることを確認します（Active Directory 管理者 -> 管理者の設定）。

次に、接続するには以下を設定します。

  • User：Azure PostgreSQL サーバーへのアクセス権を付与したAzure Active Directory ユーザーに設定。
  • AzureTenant：Azure 上のPostgreSQL への認証に使用するOAuth アプリの概要ページにある、Directory (tenant) ID に設定。
  • Server：Azure PostgreSQL インスタンスの概要ページにある、Azure PostgreSQL のサーバー名に設定。
  • Database：Azure PostgreSQL インスタンスで接続するデータベースに設定。
  • Port：PostgreSQL データベースをホスティングしているサーバーのポート。 5432がデフォルトです。
  • OAuthClientId：Azure 上のPostgreSQL への認証に使用するOAuth アプリの概要ページにある、アプリケーション（クライアント）ID に設定。
  • OAuthClientSecret：認証するOAuth アプリの証明書とシークレットページで生成されたクライアントシークレットの値に設定。
  • CallbackURL：OAuth アプリの作成時に指定したリダイレクトURI に設定。

EC2 Instances

AuthScheme をAwsEC2Roles に設定します。

EC2 インスタンスから本製品 を使用していて、そのインスタンスにIAM ロールが割り当てられている場合は、 認証にIAM ロールを使用できます。本製品 は自動的にIAM ロールの認証情報を取得し、それらを使って認証するため、AWSAccessKey およびAWSSecretKey を指定する必要はありません。

認証にIAM ロールも使用している場合は、さらに以下を指定する必要があります。

  • AWSRoleARN：認証したいロールのRole ARN を指定。これにより、本製品 は指定されたロールの認証情報を 取得しようと試みます。
  • AWSExternalId（オプション）：別のAWS アカウントでロールを引き受ける場合にのみ必要です。

IMDSv2 サポート

PostgreSQL 本製品 は、IMDSv2 をサポートしています。IMDSv1 とは異なり、新バージョンでは認証トークンが必須です。エンドポイントおよびレスポンスは、両バージョンで同じです。

IMDSv2 では、PostgreSQL 本製品 はまずIMDSv2 メタデータトークンの取得を試み、それを使用してAWS メタデータエンドポイントを呼び出します。トークンを取得できない場合、本製品 はIMDSv1 を使用します。

Azure Password

AuthScheme をAzurePassword に設定します。

Azure 資格情報を使用して直接接続するには、次の接続プロパティを指定します。

  • AuthScheme：AzurePassword に設定。
  • User：Azure への接続に使用するユーザーアカウントに設定。
  • Password：Azure への接続に使用するパスワードに設定。
  • AzureTenant：Azure 上のPostgreSQL への認証に使用するOAuth アプリの概要ページにある、Directory (tenant) ID に設定。
  • Server：Azure PostgreSQL インスタンスの概要ページにある、Azure PostgreSQL のサーバー名に設定。
  • Database：Azure PostgreSQL インスタンスで接続するデータベースに設定。
  • Port：PostgreSQL データベースをホスティングしているサーバーのポート。 5432がデフォルトです。

GCP サービスアカウント

サービスアカウントを使用してPostgreSQL Google SQL Cloud Instance に認証するには、新しいサービスアカウントを作成し、アカウント証明書のコピーを用意する必要があります。サービスアカウントを持っていない場合は、Azure AD アプリケーションの作成 の手順に従って作成できます。 JSON ファイルの場合は、以下のプロパティを設定します。

  • AuthScheme：GCPServiceAccount に設定。
  • InitiateOAuth：GETANDREFRESH に設定。
  • OAuthJWTCertType：GOOGLEJSON に設定。
  • OAuthJWTCert：Google が提供する.json ファイルへのパスに設定。
  • OAuthJWTSubject：（オプション）この値は、サービスアカウントがGSuite ドメインの一部で、委任を有効にする場合にのみ設定します。このプロパティの値は、データにアクセスしたいユーザーのE メールアドレスでなければなりません。

PFX ファイルの場合は、代わりに以下のプロパティを設定します。

  • AuthScheme：GCPServiceAccount に設定。
  • InitiateOAuth：GETANDREFRESH に設定。
  • OAuthJWTCertType：PFXFILE に設定。
  • OAuthJWTCert：Google が提供する.pfx ファイルへのパスに設定。
  • OAuthJWTCertPassword：（オプション）.pfx ファイルのパスワードに設定。Google はPFX 証明書を暗号化するため、ほとんどの場合、これを提供する必要があります。
  • OAuthJWTCertSubject：（オプション）複数の証明書を格納するOAuthJWTCertType を使用している場合にのみ設定。Google によって生成されたPFX 証明書には設定しないでください。
  • OAuthJWTIssuer：サービスアカウントのE メールアドレスに設定。このアドレスには、通常iam.gserviceaccount.com ドメインが含まれます。
  • OAuthJWTSubject：（オプション）この値は、サービスアカウントがGSuite ドメインの一部で、委任を有効にする場合にのみ設定します。このプロパティの値は、データにアクセスしたいユーザーのE メールアドレスでなければなりません。

Managed Service Identity (MSI)

Azure VM 上でPostgreSQL を実行しており、MSI を利用して接続したい場合は、AuthScheme をAzureMSI に設定します。

User-Managed Identities

マネージドID のトークンを取得するには、OAuthClientId プロパティを使用してマネージドID の"client_id" を指定します。

VM に複数のユーザーが割り当てられたマネージドID がある場合は、OAuthClientId も指定する必要があります。

Amazon Web Services

AWS キーを取得

IAM ユーザーの認証情報を取得するには：
  1. IAM コンソールにサインインします。
  2. ナビゲーションペインでユーザーを選択します。
  3. ユーザーのアクセスキーを作成または管理するには、ユーザーを選択してからセキュリティ認証情報タブを選択します。
AWS ルートアカウントの資格情報を取得するには：
  1. ルートアカウントの認証情報を使用してAWS 管理コンソールにサインインします。
  2. アカウント名または番号を選択します。
  3. 表示されたメニューでMy Security Credentials を選択します。
  4. ルートアカウントのアクセスキーを管理または作成するには、Continue to Security Credentials をクリックし、［Access Keys］セクションを展開します。

AWS IAM Roles

AuthScheme をAwsIAMRoles に設定します。

多くの場合、認証にはAWS ルートユーザーのダイレクトなセキュリティ認証情報ではなく、IAM ロールを使用することをお勧めします。AWS ルートユーザーのAWSAccessKey およびAWSSecretKey を指定している場合、ロールは使用できない場合があります。

AWS ロールとして認証するには、次のプロパティを設定します。

  • AuthScheme：AwsIAMRoles に設定。
  • User：aws_iam ロールを付与したAWS ホスティングPostgreSQL ユーザーに設定。このユーザーは、rds-db:connect 権限を含むポリシーを含むロールのAWS ユーザーにマッピングする必要があります。
  • AWSRoleARN：認証するIAM ユーザーに付属するロールのRole ARN を指定。これにより、本製品 は指定されたロールの認証情報を 取得しようと試みます。
  • AWSAccessKey：認証するIAM ユーザーのアクセスキー。
  • AWSSecretKey：認証するIAM ユーザーのシークレットキー。

多要素認証が必要な場合は、以下を指定します。

  • CredentialsLocation：MFA クレデンシャルが保存される設定ファイルの場所。詳しくは、接続文字列オプションのCredentials File Location のページを参照してください。
  • MFASerialNumber：MFA デバイスが使用されている場合は、そのシリアル番号。
  • MFAToken：MFA デバイスから利用できる一時トークン。
これにより、本製品 は一時的な認証情報を取得するために、リクエストでMFA 認証情報を送信します。

Note： 一時的な認証情報の有効期間（デフォルトは3600秒）を制御するには、TemporaryTokenDuration プロパティを設定します。

Kerberos

Kerberos 認証は、CData Cmdlets PowerShell Module for PostgreSQL が接続を試行している際にPostgreSQL サーバーで開始されます。この認証方法を有効化するには、PostgreSQL サーバーでKerberos を設定します。PostgreSQL サーバーでのKerberos 認証の設定を完了したら、本製品 からKerberos 認証を行う方法についてKerberos の使用 を参照してください。

データの取得

接続の作成が完了したら、リレーショナルデータベースに対して通常行える操作を 別のcmdlet を使って実行できます。 Select-PostgreSQL cmdlet はデータを取得するためのネイティブなPowerShell インターフェースを提供します。 

$results = Select-PostgreSQL -Connection $conn -Table ""postgres"."schema01".Orders" -Columns @("ShipName, ShipCity") -Where "ShipCountry='USA'"
Invoke-PostgreSQL cmdlet はSQL インターフェースを提供します。このcmdlet を使うと、Query パラメータを介してSQL クエリを実行できます。

cmdlet 出力のパイプ処理

cmdlet は行オブジェクトをパイプラインに一度に一行ずつ返します。以下は、結果をCSV ファイルにエクスポートします。 

Select-PostgreSQL -Connection $conn -Table "postgres"."schema01".Orders -Where "ShipCountry = 'USA'" | Select -Property * -ExcludeProperty Connection,Table,Columns | Export-Csv -Path c:\my"postgres"."schema01".OrdersData.csv -NoTypeInformation

Select-PostgreSQL からの結果をSelect-Object cmdlet にパイプして、Export-CSV cmdlet にパイプする前にいくつかのプロパティを実行していることがわかるでしょう。これをする理由は、CData Cmdlets は接続、テーブル、およびカラムの情報を結果セットの各行オブジェクトに追加しますが、必ずしもその情報がCSV ファイルに必要ではないからです。

ただし、これによってcmdlet の出力を別のcmdlet にパイプすることが容易になります。以下に、結果セットをJSON に変換する例を示します。 

 
PS C:\> $conn  = Connect-PostgreSQL -User 'postgres' -Password 'admin' -Server '127.0.0.1' -Port '5432' -Database 'postgres'
PS C:\> $row = Select-PostgreSQL -Connection $conn -Table ""postgres"."schema01".Orders" -Columns (ShipName, ShipCity) -Where "ShipCountry = 'USA'" | select -first 1
PS C:\> $row | ConvertTo-Json
{
  "Connection":  {

  },
  "Table":  ""postgres"."schema01".Orders",
  "Columns":  [

  ],
  "ShipName":  "MyShipName",
  "ShipCity":  "MyShipCity"
}

データの削除

以下は、抽出条件に合うあらゆるレコードを削除します。 

Select-PostgreSQL -Connection $conn -Table "postgres"."schema01".Orders -Where "ShipCountry = 'USA'" | Remove-PostgreSQL

データの変更

cmdlet はデータクレンジング同様、データの変換を容易にします。次の例は、レコードがすでに存在するかどうか、挿入する前に更新が必要かどうかをチェックしてから、CSV ファイルのデータをPostgreSQL にロードします。 

Import-Csv -Path C:\My"postgres"."schema01".OrdersUpdates.csv | %{
  $record = Select-PostgreSQL -Connection $conn -Table "postgres"."schema01".Orders -Where ("Id = `'"+$_.Id+"`'")
  if($record){
    Update-PostgreSQL -Connection $conn -Table "postgres"."schema01".Orders -Columns @("ShipName","ShipCity") -Values @($_.ShipName, $_.ShipCity) -Where "Id  = `'$_.Id`'"
  }else{
    Add-PostgreSQL -Connection $conn -Table "postgres"."schema01".Orders -Columns @("ShipName","ShipCity") -Values @($_.ShipName, $_.ShipCity)
  }
}

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9062