PostgreSQL に関するメモ

しばらくMySQLばかり触っていたが、久々にPostgreSQLを触る必要が出てきた。SQL関係は大体同じなのでよいが、ユーザ管理や権限設定などは製品によって概念や構造が異なるので、忘れないようにメモしておく。

チューニング等はそのうちまとめるとして、とりあえず接続設定のみメモ。

ネットワーク経由（TCP/IP)の接続、およびSSLの設定。postgresql.confに以下を設定。

listen_addresses = '*'
port = 5432

ssl_cert_file = 'server.cer'
ssl_key_file = 'server.key'

ネットワークからの接続を受け付けるためには、listen_addressesを設定する。「*」はすべてのインターフェイス。複数のネットワークインターフェイスがなければ単純に「*」で良いと思う。

SSL関係は、とりあえず証明書と秘密鍵を登録すればよい。証明書の検証をssl_ca_fileなどを設定しなければ、標準では証明書を検証しない。したがってオレオレ証明書でOK。基本的にデータベース接続はローカルなネットワーク内のみしか想定しないので、暗号化さえされればよい。

誰が、どこから、どのように接続できるのかを設定する。接続権限については、データベース内の権限でも設定できる。検証はしていないが、クライアント認証段階でチェックがあって、それをクリアして接続した後にさらに権限がチェックされるイメージだと思われる。つまり、ここでの設定はDBへの接続の第一関門ということか。

クライアント認証の設定はpg_hda.confで行う。

• https://www.postgresql.jp/document/9.5/html/client-authentication.html

主に、接続元と認証方法、接続方法を制御する。ユーザやデータベースも設定できる。ポイントを簡単にメモ。

• TYPE
  • hostはTCP/IP接続の可否。TCP/IP接続をするためにはpostgresql.confのlisten_addresses、portを設定する。
  • hostsslとするとSSL通信を強制。SSL通信のためには、サーバ証明書と秘密鍵をpostgresql.confに設定する必要がある。
  • localはソケット経由
• DATABASE / USER
  • 細かく設定することもできるが、基本はALLで運用か。当然細かく設定するのが好ましいが、設定を変えるときに気づかづにはまるので、ここは接続元や接続方法の制御にしてしまうのも良いかも。
• ADDRESS
  • 接続元を設定する。ネットマスクを指定しないとエラーになるので注意
• METHOD
  • 認証方法
  • trustは接続してきたものをすべて許可。ローカルからはこれで良いかもしれないが、本番前にはローカルも認証する方が良いと思う。phppgadminのようなツールを同一サーバに入れていると、素通しなる可能性があるため。
  • md5：ハッシュ化してパスワード認証。基本的にパスワード認証はこれ。
  • password：平文のパスワード認証。使わない。

最初の起動の前に、データベースインスタンスを作成する。MySQLと違い、管理ユーザpostgresのパスワードが設定されないので、psqlユーティリティで接続してパスワード設定。

$ psql
> \password [username]

最初のログイン前までは上記pga_hda.confのローカルからの接続をtrustにしておかないと入れない。

ちょっと便利なオプションをメモ

• \x
  • 拡張表示のon/offトグル。拡張表示をonにすると、1フィールドが1行に表示される。フィールド数が多いテーブルなどを参照するときには便利。一覧性は低くなる。

ユーザはロールという概念に統合されている。ロールは、ユーザでもありグループでもあるようである。ロールにロールを属させるとグループになり、権限が継承される仕組みのようだ。

ユーザの確認は、SQLとpsqlのメタコマンドのいずれかで行う。権限も表示される。

SQL

SELECT * FROM pg_roles;

psql

\du

ユーザの作成はSQLを使う。

CREATE ROLE {ユーザ名} WITH LOGIN PASSWORD '{パスワード}';

WITH LOGINは、ログインできるようにするオプション。グループにあたるロールの場合は、ログインできなくてもよいということか。

デフォルトでは、スーパーユーザではなく、データベースを作成できず、新たなロール（ユーザ）も作成できない。

psqlではなくシェルから直接ユーザを作成できるラッパコマンドもある。

createuser {ユーザ名}

オプションで、パスワードの設定などができる。–helpオプションを参照。

スーパーユーザは何でもできるが、新しく作ったユーザも実は結構いろいろできてしまう。

上記の手順でユーザを作成すると、最初の状態では基本的にはどのデータベースにも接続できるし、テーブルも作成できる。具体的に問題になるのは、標準でtemplate0、template1、postgresというデータベースがある。templateには権限が適切に設定されており、postgresユーザ以外は何もできなくなっているが、postgresデータベース上では新しいユーザでもテーブルが作成できる状態になっている。インストール直後はpostgresデータベースに接続制限がされてないので、ここにテーブルを作ることができる。なので、とりあえず権限の範囲と内容を確認しながら、設定を進める。

データベースの一覧と権限の確認は、psqlのメタコマンドを利用する。

> \l

データベースの一覧とともに権限も表示される。postgresの権限は空になっている。

データベースレベルの権限は

• CREATE
  • データベース内にスキーマを作成できる
• CONNECT
  • データベースに接続できる
• TEMP(TEMPORARY)
  • 一時テーブルの作成ができる

\lコマンドで権限を確認すると

 postgres=UC/postgres, =UC/postgres

のようになっている。「=」はPUBLIC（全員）なので、全員にスキーマ作成と接続権限がある。また、postgresユーザには、個別にその権限が割り振られている。

他のユーザが勝手にこのデータベースにテーブル作成できないように、REVOKEコマンドで権限を落とす。

postgresデータベースに接続した状態で

# 全員からpostgresデータベースに関する権限をすべて落とす
> REVOKE ALL ON DATABASE postgres FROM PUBLIC;

これで、postgresユーザ以外は接続もスキーマ作成もできなくなる。

スキーマの確認は、目的のデータベースに接続してpsqlのメタコマンドを使う。

> \dn+

「+」をつけると権限まで表示してくれる。

スキーマレベルの権限は、

• CREATE
  • オブジェクトの作成権限＝テーブルやビューの作成。オブジェクト名を変更するには、この権限＋所有権。
  • DBレベルのスキーマ作成権限と、スキーマレベルのオブジェクト作成権限は無関係。したがって、DBレベルでCREATE権限がなくても、スキーマレベルでCREATE権限があればスキーマ内にテーブルが作成できる。
• USAGE
  • 利用権限。オブジェクトにアクセスできる権限。

# 標準で設定されているスキーマpublicからすべての権限を落とす
> REVOKE ALL ON SCHEMA public FROM PUBLIC;

テーブルレベルの所有権と権限の確認は異なるメタコマンドを利用する。

テーブルの一覧と所有権の確認

> \d

このコマンドではテーブルのオーナーが確認できるが、権限は見えない。

テーブルの一覧と権限の確認

> \dp

このコマンドで権限が確認できる。

テーブルを作成した直後の権限は空になっている。つまり、所有者のみに全権限が与えられている状態。

ちょっと変則的な動きをするのは、色々権限を設定した後に、REVOKEでPUBLICおよび個別権限を全部落としていくと、\dpコマンドのアクセス権の欄が空になる。しかし、この状態はオーナーに全権限がある状態になる。オーナーに与えられた個別権限も含めて削除してしまうと、実はオーナーが何でもできる状態になる。したがって、テーブルオーナーであってもテーブルを削除できないようにするには、オーナーに必要な権限のみ明示的に与えなければならないということになる（と思う）。