目次

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で行う。

pg_hda.conf

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

データベースインスタンス作成

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

$ psql
> \password [username]

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

psqlメモ

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

ユーザ管理

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

ユーザの確認

ユーザの確認は、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の権限は空になっている。

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

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

 postgres=UC/postgres, =UC/postgres

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

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

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

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

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

スキーマレベル

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

> \dn+

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

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

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

テーブル(オブジェクト)レベル

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

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

> \d

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

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

> \dp

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

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

メモ

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