概要
PostgreSQLにおいて、複数のデータベース、複数のスキーマ、複数のユーザーを作って適切なスコープや権限管理をしたくてもよくわからず、結局ユーザーにスーパーユーザー権限を与えて全部見れるようにしてしまったり、システム用ユーザーとしてデフォルトのpostgresユーザーを使ってしまっている人もいるのではないでしょうか。
この記事では、PostgreSQLに複数のスキーマを作り、ユーザーAはスキーマAのみアクセスできるように、ユーザーBはスキーマBとスキーマAの一部のテーブルにのみアクセスできるように設定する方法をまとめます。
あくまでも自分用にまとめたもので、間違っている箇所もあるかもしれませんので、コメントでご指摘頂ければ幸いです。
また、ロールを階層化してユーザー権限を管理する方法についてはこの記事では触れていません。直接ユーザーに対して権限を付与しています。
要件
- システムAがあるところへサブシステムとしてシステムBを新たに構築したい。
- システムAは、SystemAというスキーマの中に必要なテーブル群を持っている。
- システムAは、postgresユーザーを使ってDBにアクセスしている（あまりよくないが手を出せない部分）。
- システムBのテーブル群はSystemBというスキーマに格納し、SystemAと分けて管理したい。
- システムBから、SystemAの一部のテーブルも参照したい。
- システムBの為にuserbというユーザーを作りたい。
- userbは、SystemBへの全ての権限と、SystemAの一部のテーブル（usersテーブル）への参照権限を持たせたい。
PostgreSQLのデータベース・スキーマ・ユーザーの関係
-
データベース
-
db1（本番環境）
- スキーマ
-
SystemA
- テーブル
- users
- tableA1
- tableA2
- テーブル
-
SystemB
- テーブル
- tableB1
- tableB2
- tableB3
- テーブル
-
- スキーマ
-
db1dev（開発環境）
- スキーマ
-
SystemA
- テーブル
- users
- tableA1
- tableA2
- テーブル
-
SystemB
- テーブル
- tableB1
- tableB2
- tableB3
- テーブル
-
- スキーマ
-
-
ユーザー/ロール
- postgres
- userb（システムBの本番用ユーザー）
- userbdev（システムBの開発用ユーザー）
上記からわかるように、テーブルはスキーマの配下にあり、スキーマはデータベースの配下にあります。
しかし、ユーザー/ロールはデータベースとは独立して存在します。
この事からわかるのは、データベース－スキーマ－テーブルの関係は階層構造の一対多の関係にありますが、ユーザーとデータベースの関係は多対多だということです。
必要な設定
PostgreSQLがuserbdevからのdb1devデータベースのSystemB.tableB1へのSELECTが求められた時、以下のチェックが順に行われます。
- userbdevはPostgreSQLへのLOGIN権限を持つか？
- userbdevはdb1devデータベースへのCONNECT権限を持つか？
- userbdevはSystemBスキーマのUSAGE権限を持つか？
- userbdevはtableB1のSELECT権限を持つか？
ですので、上記の設定を全て行えば良いわけです。
それでは、順にやっていきましょう。
以下の操作は全て、postgresユーザー（スーパーユーザー）で行います。
ログイン先データベースはdb1devにします。
PostgreSQLへの LOGIN権限の付与
一般的には、ユーザーの作成時にCREATE文の中でLOGIN権限を与えます。
PostgreSQLでのユーザーはロールと同一です。CREATE USER文は、CREATE ROLEの別名であり、唯一の違いはCREATE USERで作ったロールにはデフォルトでLOGIN権限が与えられるという点のみです。
SQL標準に従い、CREATE ROLEでユーザーを作成します。
CREATE ROLE userbdev WITH LOGIN PASSWORD 'yourpassword';
これで、このユーザーでPostgreSQLへログイン可能になりました。
データベースへのCONNECT権限の付与
データベースへの権限付与は、GRANT文を用います。
GRANT CONNECT ON DATABASE db1dev TO userbdev;
しかし、実は初期状態で、userbdevユーザーは全てのデータベースにCONNECT可能になっています。
なので、現時点で上記の意味はありません。
どちらかというと、本番環境であるdb1データベースへのCONNECT権限を無効にしたいところです。
権限の無効化は、REVOKE文を使います。
REVOKE CONNECT ON DATABASE db1 FROM userbdev;
REVOKEの場合は、TOがFROMになる点に注意してください。
ところが、上記を実行しても、相変わらずuserbdevユーザーはdb1データベースに接続が可能です。
これは、全てのデータベースは「PUBLIC」に対して初期状態でCONNECT権限を与えており、PUBLICとは全てのロールを表す為です。
これを無効にするには、以下のようにします。
REVOKE CONNECT ON DATABASE db1 FROM PUBLIC;
これでuserbdevユーザーからは接続不可になりました。なお、こうしても、スーパーユーザーである postgres からは相変わらず接続が可能です。
総合すると、userbdevユーザーが「db1へは接続不可、db1devへは接続可」としたい場合、以下のようにします。
REVOKE CONNECT ON DATABASE db1 FROM PUBLIC;
REVOKE CONNECT ON DATABASE db1dev FROM PUBLIC;
GRANT CONNECT ON DATABASE db1dev TO userbdev;
スキーマへのUSAGE権限の付与
userbdevはシステムBが用いるアカウントであり、スキーマに新しくテーブルを作ったりする権限は不要です。
スキーマを使ってテーブルにアクセスできるようになっていればよく、その為の権限がUSAGE権限です。
スキーマのUSAGE権限付与は、GRANT文を用います。
GRANT USAGE ON SCHEMA "SystemB" TO userbdev;
ここでSystemBではなく"SystemB"とダブルコーテーションを付けている理由は、PostgreSQLの識別子は基本的に大文字小文字の区別がなく、SystemBと指定するとsystembとして認識される為です。PostgreSQLに大文字小文字の区別をするように指定する場合、上記のようにダブルコーテーションで囲います。"SystemB"スキーマは、大文字小文字の区別をする為に、ダブルコーテーション付きで作成されたものです。
今回、この話の説明の為にこうしていますが、PostgreSQLでは基本的には全て小文字で作っておいた方が何かとトラブルを回避できるでしょう。
userbdevは、今回SystemAスキーマのusersテーブルにもアクセスしたい為、SystemAのUSAGE権限も付与します。
GRANT USAGE ON SCHEMA "SystemA" TO userbdev;
テーブルへのアクセス権限の付与
db1devユーザーに、SystemBスキーマ中の全テーブルに対して全ての操作ができるようにします。
これは、以下のように指定します。
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA "SystemB" TO userbdev;
SystemA.usersテーブルに対しては、SELECT文のみを許可します。
GRANT SELECT ON TABLE "SystemA".users to userbdev;
もし、SELECTとUPDATEを許可したい場合はカンマで区切ります。
GRANT SELECT, UPDATE ON TABLE "SystemA".users TO userbdev;
その他のオブジェクトへのアクセス権限の付与
テーブル以外にも、シーケンスや関数への権限付与も必要な場合、以下のようにします。
-- シーケンスへの権限付与
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA "SystemB" TO userbdev;
-- 関数の実行権限の付与
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA "SystemB" TO userbdev;
まとめ
まとめると以下のようになります。
-- ユーザーの作成
CREATE ROLE userbdev WITH LOGIN PASSWORD 'yourpassword';
-- データベースCONNECT権限
REVOKE CONNECT ON DATABASE db1 FROM PUBLIC;
REVOKE CONNECT ON DATABASE db1dev FROM PUBLIC;
GRANT CONNECT ON DATABASE db1dev TO userbdev;
-- スキーマUSAGE権限
GRANT USAGE ON SCHEMA "SystemB" TO userbdev;
GRANT USAGE ON SCHEMA "SystemA" TO userbdev;
-- テーブル操作権限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA "SystemB" TO userbdev;
GRANT SELECT ON TABLE "SystemA".users TO userbdev;
尚、もしこの状態でuserbdevを消すとします。
DROP ROLE userbdev;
しかし、上記は「他のオブジェクトが依存していますのでロール"userbdev"を削除できません」というエラーになります。
なので、userbdevを消すには、GRANTの逆を事前に行う必要があります。
REVOKE CONNECT ON DATABASE db1dev FROM userbdev;
REVOKE USAGE ON SCHEMA "SystemB" FROM userbdev;
REVOKE USAGE ON SCHEMA "SystemA" FROM userbdev;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA "SystemB" FROM userbdev;
REVOKE SELECT ON TABLE "SystemA".users FROM userbdev;
DROP ROLE userbdev;
おまけ
スキーマ名によるテーブル名の修飾を省略する（search_path）
スキーマ内のテーブルを参照するには、テーブル名をスキーマ名で修飾する必要があります。
SELECT * FROM "SystemA".users;
しかし、これを省略する方法としてsearch_pathの設定があります。
search_pathにスキーマを設定することで、「あるユーザーでログインしている際にスキーマ名が指定されていなかった場合、そのテーブルを探しに行くスキーマの順」を決めることができます。
例えば、userbdevが"SystemB"スキーマと"SystemA"スキーマをを省略可能にするには、以下のようにALTER文を指定します。
ALTER ROLE userbdev SET search_path TO "SystemB", "SystemA";
上記のコマンドは現在接続中のデータベースに対して適用されますので、db1devデータベースに接続している時に行って下さい。
これで、以下のように書けます。
SELECT * FROM users;
もし、SystemBとSystemAに同名のテーブルがあった場合には、SystemBのテーブルが優先されます。
SystemAについては修飾させ、SystemBのみ省略可能にしたい場合には、SystemBのみを指定します。
ALTER ROLE userbdev SET search_path TO "SystemB";
尚、search_pathはデフォルトでは "$user", public となっています。
これは、「ユーザー名と同じ名前のスキーマをデフォルトスキーマとし、次にpublicスキーマを検索しにいく」という意味になります。
もしあなたのシステムで、「スキーマ名でのテーブル名修飾なんてしてないけど？」という事があるなら、それはログインユーザー名と同じスキーマだったり、publicスキーマにあるテーブルだったりするかもしれません。