データベースは機密情報が格納される場所であり、その重要性からできるだけ限定されたユーザだけがアクセスできるようにしたいと考えるのは当然です。一歩進めて、理想的にはデータベースへのアクセスに必要なユーザを静的に設定するのではなく、都度必要に応じて動的に生成したいという考え方があります。

しかしながら、動的にユーザを払い出すためには、その都度ユーザに与える権限を設定するという手間が生じます。このプロセスを効率化するために、具体例として「スキーマ内の全テーブルに対して読み取り専用の権限をもつユーザ」を効率よく作成する方法を考えてみましょう。

実装

PostgreSQLには「ROLE」と「USER」という2つの概念が存在します。実質的にはこれら2つは機能的にほとんど一緒であり、ユーザー権限とアクセス制御を管理します。ただし、ここでは説明をわかりやすくするため、少し異なる意味合いでこれら2つの用語を使用します。「ROLE」は権限の定義に用い、一方「USER」はログイン権限を持つ具体的なユーザとして扱います。

権限としてのROLEの定義

CREATE USERはCREATE ROLEの別名になりました

CREATE USER

まずは、スキーマ内の全テーブルに対する読み取り権限をROLE roとして定義しましょう。それはおそらく次のような形になるでしょう。

/*
 * ro ロールは、どのテーブルの参照も可能な権限を持つ
 */
CREATE ROLE ro NOINHERIT NOBYPASSRLS NOLOGIN;

-- スキーマを利用する権限を付与
GRANT USAGE ON SCHEMA my_schema TO ro;
-- スキーマ配下の全テーブルに対する参照権限を付与
GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT SELECT ON TABLES TO ro;

このSQLスクリプトでは、まずroというROLEを作成し、次にこのROLEに特定のスキーマ(my_schema)を使用する権限と、そのスキーマ内の全テーブルの読み取り権限を付与しています。最後に、このスキーマ内で将来作成される全てのテーブルに対するデフォルトの読み取り権限をroに付与しています。

ここで作成したroというROLEはログイン権限を持たず、そのため直接データベースへはログインできません。それは、このROLEにはログインするためのパスワードが存在しないからです。

次に進むステップとしては、このroというROLEを持つユーザーを作成する機能を設定すれば良いです。

「ユーザを作成することだけできる」ユーザの作成

「ユーザを作成」するユーザ自身が任意の操作が可能だと、セキュリティリスクが高まります。そのため、ここではユーザ作成のみを許可する専用のユーザ、adminを設定します。

/* adminは、ユーザを作成する能力を持つが、自身はテーブルの参照等はできない */
CREATE USER admin CREATEROLE NOINHERIT NOBYPASSRLS PASSWORD 'postgres';
ALTER USER admin SET search_path TO my_schema;

ここで作成したadminユーザには、明示的にGRANTコマンドによる権限付与がなされていないため、データベースにログインしてもテーブル参照等の操作は実行できません。

$ psql -h localhost -U admin -d kiririmode
kiririmode=> select * from my_schema.parents;
ERROR:  permission denied for schema my_schema
LINE 1: select * from my_schema.parents;

ROLEを継承したユーザを有効期限付きで作成

次に、上で作成したadminユーザを使用して、`ro`` ROLEを継承する新しいユーザーを作成してみましょう。

kiririmode=> CREATE USER ro_kiririmode PASSWORD 'kiririmode' VALID UNTIL '2023-07-17 18:00:00 JST' IN ROLE ro;
CREATE ROLE
kiririmode=> ALTER USER ro_kiririmode SET search_path TO my_schema;
ALTER ROLE
kiririmode=>

ここではVALID UNTILオプションを使用して、パスワードが2023-07-17 18:00:00 JSTまで有効なユーザーを作成しています。これにより、その時刻を過ぎるとユーザーのアクセス権限は自動的に失効します。

VALID UNTIL 'timestamp'

VALID UNTIL句は、ロールのパスワードが無効になる日時を設定します。この句が省略された場合、パスワードは永遠に有効になります。

CREATE ROLE

さらに、IN ROLE rolenameを指定することで、作成するユーザーが指定したrolenameのROLEに所属します。結果として、新しく作成したユーザro_kiririmodeはroというROLEが持つ権限を継承できます。

このユーザro_kiririmodeを使ってみると、VALID UNTILで指定した18:00までにデータベースにログインすれば、テーブルの参照が可能になっていることが確認できます。

❱ ❱ date; psql -h localhost -U ro_kiririmode -d kiririmode
月  7 17 17:58:44 JST 2023
Password for user ro_kiririmode:
psql (14.8 (Homebrew), server 15.3 (Debian 15.3-1.pgdg110+1))
WARNING: psql major version 14, server major version 15.
         Some psql features might not work.
Type "help" for help.

kiririmode=> select * from parents;
 id |     name
----+--------------
  1 | ウルトラの父
  2 | ウルトラの母
(2 rows)

逆にinsertはエラーになりますね。想定通りです。

kiririmode=> insert into parents(id, name) values (3, 'hoge');
ERROR:  permission denied for table parents

一方で、新規レコードの挿入（INSERT）を試みるとエラーが発生します。これは我々が想定していた通りの挙動です。なぜなら、roというROLEには読み取り専用の権限（SELECT）しか付与していないため、データの挿入や更新などの書き込み操作は許可されていないからです。

$ ❱ date; psql -h localhost -U ro_kiririmode -d kiririmode
月  7 17 18:00:33 JST 2023
Password for user ro_kiririmode:
psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  password authentication failed for user "ro_kiririmode"

まとめ

上記の通り、事前に必要な権限を持つROLEを定義し、ユーザ作成時にそのROLEを継承するアプローチは効率的です。ただし、このままでは不要なユーザが蓄積されてしまい、セキュリティ上の懸念が生じます。したがって、定期的なユーザの棚卸しは必要でしょう。

有効期限が切れたユーザを特定するためには、以下のSQLクエリを使用できます。

kiririmode=# SELECT usename FROM pg_shadow WHERE valuntil < CURRENT_TIMESTAMP;
    usename
---------------
 ro_kiririmode

この結果をもとに、不要なユーザを削除できます。

kiririmode=# DROP USER ro_kiririmode;
DROP ROLE

このようにして不要なユーザを適宜削除することで、セキュリティを保ちながらデータベースを適切に管理できます。

理系学生日記

おまえはいつまで学生気分なのか

有効期限のあるPostgreSQLユーザを動的に作成する効率的な方法を考える

実装

権限としてのROLEの定義

「ユーザを作成することだけできる」ユーザの作成

ROLEを継承したユーザを有効期限付きで作成

まとめ