PostgreSQLのユーザ管理

# ユーザとロール

標準SQL (SQL/Foundation) では, データベースユーザというのは決められていなくて,「ロール」という名前になっています。

例えば, SQLの次のような構文でロールを作ったりできます。

CREATE ROLE  [ WITH ADMIN  ]

PostgreSQLでは, 「(データベース) ユーザ」と「ロール」は同じものです。

TODO: ロールのネスト

# データベースユーザ

PostgreSQLでは、データベースに接続するためのユーザ名とOSのユーザ名は、リンクさせることもできるし、別にすることもできるようになっています。

コマンドラインでデータベースユーザ名を省略した場合は、OSのログインユーザ名でデータベースに接続しようとします。

通常は、アプリケーションからTCP/IPなどでデータベースサーバにアクセスし、これらのサーバを別のホスト（機械）にすることもありますから、このユーザ名は別にします。

データベースユーザは, PostgreSQLに登録しなければなりません。

$ /usr/local/pgsql/bin/createuser -P
Enter name of user to add: hori
Enter password for user "hori": パスワード
Enter it again: 上で入力したパスワード
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}
 hogeuser  |                                                | {}

$ dropuser ユーザ名

# パスワードの設定・変更

データベースユーザのパスワードを変更するには、次のように psql コマンドのオプションでデータベース template1 を指定するか、

# psql -U postgres template1

psql でPostgreSQLサーバに接続したのちに、template1 データベースに接続しなおします。

postgres=# \connect template1
You are now connected to database "template1" as user "postgres".

"ALTER USER" SQL文でパスワードを変更します。背後に注意。

template1=# ALTER USER ユーザ名 WITH PASSWORD '新しいパスワード';
ALTER ROLE

# データベースへのアクセス権限

データベースユーザがどのデータベースに接続できるか, という認証と, データベースユーザがデータベースをどのように操作できるか, というアクセス権限は, 別です。

pg_hba.conf ファイルで database列が all 以外になっているデータベースユーザは, そのpg_hba.conf で指定されたデータベースにしか接続できません。

逆に, pg_hba.conf ファイルで database列が all のユーザは, 認証さえ通れば, どのデータベースにも接続できます。

通常は, アプリケーションサーバからデータベースサーバへ接続するので, pg_hba.conf で行う認証の設定だけで十分です。

ただ, そういつでもあるわけではありませんが, 例えばエンドユーザコンピューティングのために, 特定のテーブルに限定し, かつSELECT だけはできるようにしたいことがあります。

GRANT文を使えば, あるユーザに特定のテーブルの特定の操作だけを許可できます。

GRANT文は, 次のようになります。

GRANT 権限, ... ON テーブル名, ... TO ロール名, ...;

権限は, SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER をカンマで繋げるか, ALL と書きます。

テーブルやカラムごとのアクセス権は, psql の\dp コマンドで確認できます。