Pythonライブラリpsycopg2を使ってPostgreSQLにSSL接続

2024-05-01

PostgreSQLにpsqlを使用してSSLモードで接続する方法

PostgreSQLは、SSL/TLS暗号化を使用してクライアントとサーバー間の通信を保護することができます。これは、機密性の高いデータを扱うデータベース接続にとって重要なセキュリティ対策です。

前提条件

このチュートリアルを実行するには、以下のものが必要です。

PostgreSQLサーバーが実行されている
SSL証明書と秘密キーが設定されている
psqlコマンドラインクライアント

手順

SSLモードを指定する

psqlコマンドを実行する際に、sslmodeパラメータを使用してSSLモードを指定する必要があります。このパラメータには、以下の3つの値を設定できます。
- require: SSL接続のみを許可します。
- verify-ca: SSL接続を許可し、クライアント証明書を検証します。
例えば、以下のコマンドは、クライアント証明書検証を含むSSL接続を確立します。
```
psql -h hostname -d database -U username --sslmode=verify-ca
```
証明書ファイルを指定する

SSL証明書と秘密キーの場所をpsqlに伝える必要があります。これは、以下の方法で行うことができます。
- 環境変数を使用する:
  
  以下の環境変数を設定することで、証明書ファイルと秘密キーの場所を指定できます。
  - SSL_CERTFILE: SSL証明書ファイルのパス
- - --sslcert: SSL証明書ファイルのパス
例えば、以下のコマンドは、~/.postgresqlディレクトリにあるpostgresql.crtファイルを証明書ファイルとして、postgresql.keyファイルを秘密キーファイルとして使用します。
```
psql -h hostname -d database -U username --sslmode=verify-ca --sslcert=~/.postgresql/postgresql.crt --sslkey=~/.postgresql/postgresql.key
```
認証局(CA)ファイルを指定する (verify-caおよびverify-fullモードの場合)

クライアント証明書検証を行う場合は、認証局(CA)ファイルも指定する必要があります。これは、--sslcaコマンドラインオプションを使用して行います。

例:
```
psql -h hostname -d database -U username --sslmode=verify-ca --sslcert=~/.postgresql/postgresql.crt --sslkey=~/.postgresql/postgresql.key --sslca=/etc/ssl/certs/ca-certificates.crt
```

注意事項

SSLモードで接続するには、PostgreSQLサーバー側でSSLが有効化されている必要があります。
使用するSSL証明書と秘密キーは、有効で安全なものであることを確認してください。
認証局(CA)ファイルを指定する場合は、それが信頼できるソースからのものであることを確認してください。

PostgreSQLにpsqlを使用してSSLモードで接続する際のサンプルコード

以下の例は、psqlコマンドを使用して、SSLモードでPostgreSQLサーバーに接続する方法を示しています。

例1：クライアント証明書検証を含むSSL接続

psql -h hostname -d database -U username --sslmode=verify-ca --sslcert=~/.postgresql/postgresql.crt --sslkey=~/.postgresql/postgresql.key --sslca=/etc/ssl/certs/ca-certificates.crt

例2：SSL証明書検証なしのSSL接続

psql -h hostname -d database -U username --sslmode=require --sslcert=~/.postgresql/postgresql.crt --sslkey=~/.postgresql/postgresql.key

説明:

-h hostname: 接続先のPostgreSQLサーバーのホスト名
-d database: 接続先のデータベース名
-U username: 接続に使用するユーザー名
--sslmode: SSLモードを指定します。

補足:

上記の例は、bashシェルを使用していることを前提としています。他のシェルを使用している場合は、コマンドの構文が異なる場合があります。
パスワードを使用して認証を行う場合は、-pオプションを使用してパスワードをプロンプトで指定する必要があります。
環境変数を使用して証明書ファイルと秘密キーの場所を指定することもできます。詳細は、上記のチュートリアルを参照してください。

PostgreSQLにSSLで接続するには、psqlコマンド以外にもいくつかの方法があります。

PgAdminは、PostgreSQLデータベースを管理するためのグラフィカルツールです。SSL接続を含む様々な接続オプションを設定することができます。

手順:

PgAdminで、接続したいデータベースサーバーに接続します。
サーバーを右クリックし、「新規接続」を選択します。
接続ダイアログで、以下の情報を入力します。
- データベース: 接続先のデータベース名
- ユーザー名: 接続に使用するユーザー名
- パスワード: 接続に使用するパスワード
「SSL」タブをクリックします。
SSLモードを選択します。
証明書ファイルと秘密キーの場所を指定します。

Python:

psycopg2などのPythonライブラリを使用して、PostgreSQLにSSLで接続することができます。

import psycopg2

# 接続パラメータ
hostname = "localhost"
database = "mydatabase"
username = "myuser"
password = "mypassword"

# SSLモード
sslmode = "require"
sslcert = "~/.postgresql/postgresql.crt"
sslkey = "~/.postgresql/postgresql.key"
sslca = "/etc/ssl/certs/ca-certificates.crt"

try:
    # SSL接続を確立
    conn = psycopg2.connect(
        host=hostname,
        dbname=database,
        user=username,
        password=password,
        sslmode=sslmode,
        sslcert=sslcert,
        sslkey=sslkey,
        sslca=sslca,
    )

    # データベース操作を実行
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM mytable")
    results = cursor.fetchall()
    for row in results:
        print(row)

    # コミットとクローズ
    conn.commit()
    conn.close()

except Exception as e:
    print(e)

Go:

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/lib/pq" // PostgreSQLドライバー
)

func main() {
    // 接続パラメータ
    hostname := "localhost"
    database := "mydatabase"
    username := "myuser"
    password := "mypassword"

    // SSLモード
    sslmode := "require"
    sslcert := "/path/to/client.crt"
    sslkey := "/path/to/client.key"
    sslca := "/path/to/ca.crt"

    // SSL接続を確立
    db, err := sql.Open("postgres", fmt.Sprintf("host=%s dbname=%s user=%s password=%s sslmode=%s sslcert=%s sslkey=%s sslca=%s", hostname, database, username, password, sslmode, sslcert, sslkey, sslca))
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // データベース操作を実行
    rows, err := db.Query("SELECT * FROM mytable")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var id int
        var name string
        err := rows.Scan(&id, &name)
        if err != nil {
            log.Fatal(err)
        }
        fmt.Printf("ID: %d, Name: %s\n", id, name)
    }

    err = rows.Err()
    if err != nil {
        log.Fatal(err)
    }
}