34. PostgreSQL セキュリティ

PostgreSQL は、特定の権限を特定のロール にまとめて、豊富で柔軟なパーミッションシステムを持っています、また、これらのロールの一つ以上をユーザに提供します。

さらに PostgreSQL サーバは、複数の別のシステムを使ってユーザ認証ができます。データベースは他のアーキテクチャのコンポーネント同じ認証基盤を使うことができ、パスワード管理の簡素化が可能です。

34.1. ユーザとロール

本省では、二つの便利な運用用ユーザを作ります:

  • アプリケーション発行で使用する読み取り専用ユーザ。

  • ソフトウェア構築又はデータ解析での開発者によって使われる読み書き可能ユーザ。

ユーザ作成とユーザへの権限付与をせずに、適正な権限を持つ二つのロールを作り、その後に、二つのユーザを作って適切なロールを追加します。この方法で、ユーザよりもロールの方が簡単に再利用ができます。

34.1.1. ロールの作成

ロールはユーザでユーザはロールです。ユーザは "login" 権限を持つロールであると言えることとだけが違います。

機能的には、次に示す二つの SQL ステートメントは同じで、どちらも「ログイン権限を持つロール」つまり「ユーザ」を作ります。

CREATE ROLE mrbean LOGIN;
CREATE USER mrbean;

34.1.2. 読み取り専用ユーザ

読み取り専用ユーザはウェブアプリケーションが nyc_streets テーブルに対するクエリを使うためのユーザです。

ウェブアプリケーションは nyc_streets テーブルへの特定のアクセス権を持ちますが、postgis_reader ロールから PostGIS 操作に必要なシステムアクセス権を継承します。

-- A user account for the web app
CREATE USER app1;
-- Web app needs access to specific data tables
GRANT SELECT ON nyc_streets TO app1;

-- A generic role for access to PostGIS functionality
CREATE ROLE postgis_reader INHERIT;
-- Give that role to the web app
GRANT postgis_reader TO app1;

ここで app1 としてログインすると、nyc_streets テーブルの行を抽出できます。しかしながら、 ST_Transform が実行できません! なぜでしょう?

-- This works!
SELECT * FROM nyc_streets LIMIT 1;

-- This doesn't work!
SELECT ST_AsText(ST_Transform(geom, 4326))
  FROM nyc_streets LIMIT 1;

ERROR:  permission denied for relation spatial_ref_sys
CONTEXT:  SQL statement "SELECT proj4text FROM spatial_ref_sys WHERE srid = 4326 LIMIT 1"

答は、エラーメッセージに示されています。app1 ユーザが nyc_streets テーブルの内容を正常に表示できますが、spatial_ref_sys の内容を表示できないため、ST_Transform の実行に四敗します。

このため、postgis_reader ロールに対して全ての PostGIS メタデータテーブルへの読み込み権利を付与する必要があります:

GRANT SELECT ON geometry_columns TO postgis_reader;
GRANT SELECT ON geography_columns TO postgis_reader;
GRANT SELECT ON spatial_ref_sys TO postgis_reader;

これで、PostGIS テーブルからデータを読む必要のあるユーザに適用できる汎用的な postgis_reader ロールができました。

-- This works now!
SELECT ST_AsText(ST_Transform(geom, 4326))
  FROM nyc_streets LIMIT 1;

34.1.3. 読み書き可能ユーザ

考慮する必要のある読み/書きシナリオが二種類あります:

  • 既存のデータテーブルへの書き込みが必要なウェブアプリケーションなど。

  • 作業の一環として新しいテーブルとジオメトリカラムを作成する必要がある開発者と分析者。

データテーブルへの書込みアクセスが必要なウェブアプリケーションについては、テーブル自体への追加パーミッションの付与が必要なだけで、postgis_reader ロールの使用を継続できます。

-- Add insert/update/delete abilities to our web application
GRANT INSERT,UPDATE,DELETE ON nyc_streets TO app1;

こららの種類のパーミッションは、読み書きを行う WFS サービスなどで必要です。

開発者や分析者にとっては、PostGIS の主要なメタデータテーブルに対して、もう少し多いアクセスが必要です。PostGIS メタデータテーブルを編集できる postgis_writer ロールが必要です!

-- Make a postgis writer role
CREATE ROLE postgis_writer;

-- Start by giving it the postgis_reader powers
GRANT postgis_reader TO postgis_writer;

-- Add insert/update/delete powers for the PostGIS tables
GRANT INSERT,UPDATE,DELETE ON spatial_ref_sys TO postgis_writer;

-- Make app1 a PostGIS writer to see if it works!
GRANT postgis_writer TO app1;

では、app1 ユーザとして上のテーブル生成 SQL を試してみて、どうなるかを見て下さい!

34.2. 暗号化

PostgreSQL は多数の 安藤化機能 を提供しています。多数が任意指定であり、一部はデフォルトでオンになっています。

  • デフォルトでは、全てのパスワードは MD5 暗号化されます。クライアント/サーバ のハンドシェイクは、MD5 パスワードを二重に暗号化します。パスワードを盗聴する者によるハッシュの再利用を防ぎます。

  • SSL 接続 はクライアントとサーバの間で任意で使用可能です。全てのデータとログイン情報の暗号化のために、SSL 接続を使う際には、SSL 証明書認証も使用可能です。

  • データベース内のカラムは pgcryptoモジュール で暗号化できます。このモジュールにはハッシュアルゴリズム、直接暗号化 (blowfish, aes) および公開鍵や対称鍵の PGP 暗号化があります。

34.2.1. SSL 接続

SSL接続を使うには、クライアントとサーバの両方が SSL に対応している必要があります。

  • まず、SSL の有効化に再起動が必要になるので、PostgreSQL を終了させます。

  • 次に SSL 証明書とキーを取得または生成します。証明書はパスフレーズなしにするのに必要で、無い場合にはデータベースサーバは起動しません。自己署名したキーの生成ができます。次の通り行います:

    # Create a new certificate, filling out the certification info as prompted
openssl req -new -text -out server.req

# Strip the passphrase from the certificate
openssl rsa -in privkey.pem -out server.key

# Convert the certificate into a self-signed cert
openssl req -x509 -in server.req -text -key server.key -out server.crt

# Set the permission of the key to private read/write
chmod og-rwx server.key

  • server.crt と server.key を PostgreSQL データディレクトリに複製します。

  • postgresql.conf ファイル内で、"ssl" パラメータを "on" にして SSL を有効化します。

  • 次に、PostgreSQL を再起動すると、サーバが SSL 処理の準備ができます。

SSL が有効になったサーバでは、暗号化接続の生成が簡単です。PgAdmin では、新しいサーバ接続 (File > Add Server...) を生成し、SSLパラメータを "require" にします。

_images/ssl_create.jpg

新しい接続で接続すると、そのプロパティの中で SSL 接続を使っていることが分かります。

_images/ssl_props.jpg

デフォルトの SSL 接続モードは "prefer" ですので、接続時に SSL 設定を指定する必要さえありません。コマンドラインの psql ターミナルは、デフォルトで SSL のオプションを使用します:

psql (8.4.9)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=#

端末がどのように SSL 接続の状態を報告するかに注意して下さい。

34.2.2. データ暗号化

pgcrypto モジュールでは暗号化オプションの範囲が巨大です。そのため、共通鍵暗号方式を使ったデータのカラムの暗号化という、最も単純な使用場面だけを示します。

  • 最初に、PgAdmin または psql のどちらかで、contrib SQL ファイルをロードして、pgcryot を有効化します。

    pgsql/8.4/share/postgresql/contrib/pgcrypto.sql

  • その後、暗号化関数をテストします。

    -- encrypt a string using blowfish (bf)
SELECT encrypt('this is a test phrase', 'mykey', 'bf');

  • 復号化も可能であることも確認して下さい!

    -- round-trip a string using blowfish (bf)
SELECT decrypt(encrypt('this is a test phrase', 'mykey', 'bf'), 'mykey', 'bf');

34.3. 認証

PostgreSQL は、既存のエンタープライズアーキテクチャに簡単に統合できるよう、多数の 認証方式 に対応しています。運用目的としては、次のメソッドが一般に使われます:

  • Password は、パスワードがデータベースに格納され、MD5 暗号化を使用する、基本システムです。

  • Kerberos は、標準的なエンタープライズ認証メソッドで、PostgreSQLでは GSSAPI スキームと SSPI スキームの両方で使用されます。SSPI スキームを使うと、PostgreSQL は Windows サーバに対して認証できます。

  • LDAP は、もうひとつの一般的なエンタープライズ認証メソッドです。ほとんどの Linux ビスとリビューションに同梱されている OpenLDAP サーバは、LDAP のオープンソース実装を提供します。

  • Certificate (証明書) 認証は、全てのクライアント接続が SSL からになると期待できて、キーの配布を管理できる場合の選択肢です。

  • PAM 認証は、Linux または Solaris 上で透過的な認証のユーザプロビジョニングに PAM スキームを使用している場合の選択肢です。

認証メソッドは pg_hba.conf ファイルで制御します。ファイル名の "HBA" は "host based access" (ホストを基にしたアクセス) の略です。データベースごとに使用する認証メソッドを指定できるようにするだけでなく、ネットワークアドレスを使ってホストへのアクセスを制限できるため、こういう名前になっています。

pg_hba.conf ファイルの例を示します:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
host    all         all         ::1/128               trust
# remote connections for nyc database only
host    nyc         all         192.168.1.0/2         ldap

ファイルは五つのカラムでできています

  • TYPE アクセスのタイプを指定します。同じサーバからのアクセスでは "local" を、リモート接続では "host" とします。

  • DATABASE この行のコンフィギュレーションが参照するデータベースを指定します。"all" で全てのデータベースを指定します

  • USER この行が参照するユーザを指定します。"all" とすると全てのユーザを指定します

  • CIDR-ADDRESS リモート接続の際のネットワーク制限を指定します。ネットワーク/ネットマスク という書式です

  • METHOD 使用する認証プロトコルを指定します。"trust" で認証全体を行わず、チャレンジなしであらゆる有効なユーザ名を受け入れます。

ローカル接続の場合は、サーバへのアクセス自体が特権的ですので、trust にするのが一般的です。リモート接続は、PostgreSQL インストール時のデフォルトでは無効になっていて、リモート機からの接続をするには、エントリを追加しなければなりません。

上の例の nyc のラインはリモートアクセスのエントリの例です。 nyc の例では LDAP 認証による、ローカルネットワーク (この場合は 192.168.1. ネットワーク) 上の機械の nyc データベースへのアクセスだけを許しています。運用の設定ではこれらの規則の厳密版を、ネットワークのセキュリティ応じて多かれ少なかれ使用します。