📦

PostgreSQL の権限管理をまとめる

pekepek

2024/12/22に公開

!この記事は 株式会社ログラス Product チーム Advent Calendar 2024 のシリーズ 2、22 日目 の記事です。

 はじめにこんにちは！ログラスで Web エンジニアをやっている石畑です。
今回は自分の PostgreSQL の権限周りの知識があいまいだったので、権限管理についてまとめてみました。インフラの方などが既に設定されていることが多く、中々いじる機会がない部分だと思うので、同じようにあまり触ったことが無い方の参考になるといいなと思います！

 権限の全体像まずは登場する概念とその関係について見ていきます。
権限管理に出てくる概念は基本的には「ロール」とそれに付与する「権限」のみです。普段 DB の接続で使用している「ユーザ」も別概念ではなく、「ログイン属性を持ったロール」です。「スーパーユーザ」や「グループ」なども同様です。そして、あらゆるオブジェクト（DB、テーブルなど）はロールに権限を付与して初めてアクション（作成・閲覧など）が可能になります。図にすると以下のようなイメージです。
このように「ロール」と「権限」を使って、様々な設定や DB などの権限管理を行うのですが、権限のみでは最小でテーブルレベルでのアクセス制御しか行うことができません。
そのため、例えば「マルチテナントで、テーブルを分けずに一つのテーブルでレコードごとにアクセス制限をしたい」というニーズがあっても実現することができません。そこで、その際に使うのが「RLS（Row Level Security）」です。RLS を有効にすると、テーブルごとにレコードのアクセスポリシーを定義することができ、可視できるレコードを制限することができます。
ざっくりと権限管理の全体像と役割のイメージがついたでしょうか。それでは、実際に「ロール」、「権限」、「RLS」について見ていきます。

 ロール前述した通りユーザなどはロールの一つであり、役割や属性の違いでしかありません。その証拠にユーザの作成は
CREATE USER name;
で行いますが、
CREATE ROLE name LOGIN;
と全く同じ意味になります（name という名前でロールを作成し、LOGIN 属性を付与している）。
ちなみにロールの属性としては他にも以下のようなものがあります。


属性
概要


スーパーユーザ
スーパーユーザかどうか

ロール作成
ロールの作成・変更の権限があるか（ただし、スーパーユーザ・レプリケーションの新規接続属性は付与できない）

権限の継承
ロールはデフォルトでは他のロールの権限を継承することができる。これを継承しない状態にも変更可能

行単位セキュリティのバイパス
RLS を有効にしてもポリシーを無視して全てのデータにアクセス可能にできる

その他にもいくつか設定できる属性があるので、全ての属性は PostgreSQL のドキュメントを参照して下さい。
また、いくつか特徴的なロールを説明していくと
スーパーユーザ
全ての操作が可能な最上位の権限を持ったロール
最初からデフォルトで一件作成されている
ロール作成時に SUPERUSER 属性を持たせることで新規作成も可能
ただし、スーパーユーザにしか作成権限はない


グループ
権限をまとめるために使用するロールをグループと呼んでいるだけで、現在は同じ概念

CREATE GROUP と CREATE ROLE は全く同じ動作をする

権限の継承
他のロールをメンバに加えることで権限を継承することができる
GRANT group_role TO role1, ... ;
group_role ロールのメンバに role1, … を加えている
これにより role1 は group_role に付与された権限を扱える
継承は数珠つなぎになっており、A → B → C と権限を継承させることができる
権限を継承したくない場合は NOINHERIT 属性を持たせることもできる


public ロール
デフォルトで存在し、削除できない特別なロール
全てのロールは public ロールの権限を継承している
NOINHERIT のロールも public は継承している
そのため、public ロールへの権限の付与は注意が必要


役割に応じてロールの作成・属性追加を行ったら、ロールに権限を付与していきます。

 権限前述の通り、あらゆるオブジェクトの操作には権限の付与が必要になります。しかし、一部例外もいます。
それが
スーパーユーザ
オブジェクト所有者
オブジェクト作成時のロール
テーブルなら CREATE TABLE したロール

です。
これらのロールは明示的に権限を付与しなくても、対象のオブジェクトに対して全ての操作が可能です。これらのロールを使って、他のロールに権限付与を行っていきます。また、「権限付与の権限」を渡すこともできます。

 権限の付与権限は GRANT コマンドを使って「権限の種類」、「オブジェクトタイプ」、「対象のロール」を指定して付与します。
例
GRANT SELECT, INSERT ON TABLE users TO app_user;
この例では users テーブルの SELECT, INSERT 権限を app_user ロールに付与しています。
付与できる権限の種類には以下のようなものがあります。


権限
短縮形
適用可能なオブジェクトタイプ


SELECT
r (「read」)
LARGE OBJECT, SEQUENCE, TABLE（およびテーブルのようなオブジェクト）、テーブルの列

INSERT
a (「append」)
TABLE、テーブルの列

UPDATE
w (「write」)
LARGE OBJECT, SEQUENCE, TABLE, テーブルの列

DELETE
d
TABLE

TRUNCATE
D
TABLE

REFERENCES
x
TABLE、テーブルの列

TRIGGER
t
TABLE

CREATE
C
DATABASE, SCHEMA, TABLESPACE

CONNECT
c
DATABASE

TEMPORARY
T
DATABASE

EXECUTE
X
FUNCTION, PROCEDURE

USAGE
U
DOMAIN, FOREIGN DATA WRAPPER, FOREIGN SERVER, LANGUAGE, SCHEMA, SEQUENCE, TYPE

SET
s
PARAMETER

ALTER SYSTEM
A
PARAMETER

出典 :（翻訳）日本 PostgreSQL ユーザ会, PostgreSQL 16.4 文書, 5.7. 権限, 表5.1 ACL短縮形
また、オブジェクトタイプは以下のようなものがあります。「すべての権限」には権限の短縮形、「psql コマンド」には対象のオブジェクトの権限設定を調べる際の psql コマンドが記載されています。


オブジェクトタイプ
すべての権限
デフォルト PUBLIC 権限
psql コマンド


DATABASE
CTc
Tc
\l

DOMAIN
U
U
\dD+

FUNCTION or PROCEDURE
X
X
\df+

FOREIGN DATA WRAPPER
U
none
\dew+

FOREIGN SERVER
U
none
\des+

LANGUAGE
U
U
\dL+

LARGE OBJECT
rw
none
\dl+

PARAMETER
sA
none
\dconfig+

SCHEMA
UC
none
\dn+

SEQUENCE
rwU
none
\dp

TABLE（およびテーブルのようなオブジェクト）
arwdDxt
none
\dp

テーブルの列
arwx
none
\dp

TABLESPACE
C
none
\db+

TYPE
U
U
\dT+

出典 :（翻訳）日本 PostgreSQL ユーザ会, PostgreSQL 16.4 文書, 5.7. 権限, 表5.2 アクセス権限のまとめ
これらを組み合わせてロールの権限管理をしていきます。

 権限の確認付与した権限は次の形式で表示されます。
grantee=privilege-abbreviation[*].../grantor
grantee : 権限を付与されたロール名
privilege-abbreviation : 権限の短縮形のリスト
許可オプション（権限付与の権限）がある場合は * が追加される

grantor : 権限を付与したロール名
実際にテーブルに付与した権限を見てみましょう。権限の確認は先程の表の「psql コマンド」で行うことができます。
例 : taro_table テーブルの権限の確認
 \dp taro_table
                               Access privileges
 Schema |    Name    | Type  | Access privileges | Column privileges | Policies
--------+------------+-------+-------------------+-------------------+----------
 public | taro_table | table | taro=arwdDxt/taro+|                   |
        |            |       | jiro=rw/taro     +|                   |
        |            |       | =r/taro           |                   |
dp コマンドでテーブルに紐づく権限を表示
テーブル名を省略すると全てのテーブルの権限が表示される

Access privileges 列
1 行目 : taro=arwdDxt/taro
taro はテーブル所有者なので全ての権限が付与されている

taro=.../taro というように自分自身で権限を付与した表記になる

2 行目 : jiro=rw/taro
jiro は r（SELECT）, w（UPDATE）権限を taro から付与されたことがわかる

3 行目 : =r/taro
この例のように grantee が表示されていないものは public ロールに対する権限
全てのロールは taro_table に対して SELECT が許可されている


以上がロールと権限の管理になります。

 RLS（Row Level Security）ここまでで基本的には PostgreSQL での権限管理は完了です。ただ、前述の通り 1 つのテーブルでロールごとに「アクセスできるレコードを分けたい」とか、「自身に関係のあるレコードしか更新できないようにしたい」などのニーズがあるかもしれません。そういった場合に使用できるのが RLS です。
一旦 RLS を有効にすると行セキュリティポリシーが true となるレコードしか閲覧できなくなります。
ポリシーには
PERMISSIVE（許容）ポリシー : アクセス可能なレコードを定義
RESTRICTIVE（制限）ポリシー : アクセス不可なレコードを定義
があり、これらを組み合わせてアクセス条件を定義していきます。また、デフォルトでは全てのレコードがアクセス不可なので、RLS を有効にした場合、少なくとも 1 つの PERMISSIVE ポリシーが必要になります。

 RLS の例例を見るのがわかりやすいので、実際に RLS を有効にして、テーブルの SELECT をしてみます。
RLS の有効化は
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ポリシーの作成は
CREATE POLICY policy_name ON table_name TO role_name USING (...);
で行うことができます。

 例 : users テーブルの可視データを制限するusers テーブルの RLS を有効にして PERMISSIVE ポリシーを作成
-- users テーブルと sample データを用意
CREATE TABLE users (id integer, name varchar(20));
INSERT INTO users VALUES (1, 'taro'), (2, 'jiro');

-- 全ロールに対して users テーブルに SELECT 可能にする
GRANT SELECT ON users to public;

-- users テーブルの RLS を有効にする
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- current_user（SQL の実行ユーザ）と name カラムの値が一致するレコードのみにアクセス可能なポリシーを作成
CREATE POLICY users_policy ON users TO public USING (name = current_user);
実際に taro ロールでデータを取得してみる
-- taro ロールに切り替え
SET ROLE taro;

SELECT * FROM users;

 id | name
----+------
  1 | taro
(1 row)
users テーブルには taro, jiro のレコードがありますが、SELECT では taro のレコードしか取得できないことがわかります。条件を指定し忘れても初めからアクセス不可能なので、安全ですね。

なんとなく、RLS のイメージがつかめましたでしょうか。

 条件の指定方法例の通りポリシーは CREATE POLICY で作成していきます。その際に
付与する権限
対象のテーブルとロール
ポリシーの条件
を指定します。付与する権限は「SELECT、INSERT、UPDATE、DELETE」です。また、条件は USING, WITH CHECK 句で指定し、条件が true になったものにアクセスができます。
USING と WITH CHECK 句の違いは
USING
既存の行に対する検査
SELECT、INSERT、DELETE で使われる
条件を満たさない行はアクセスできない（エラーにはならず無視される）

WITH CHECK
新規に挿入・更新される行に対する検査
INSERT、UPDATE で使われる
条件を満たさない行は挿入・更新できず、エラーになる

です。また、WITH CHECK は省略可能で、その場合 USING が使われます。
さらに条件には「サブクエリ」や「関数」を使うことも可能で、多様なアクセス制限をかけることが可能です。ただし、データ競合などで情報漏洩を起こす場合がないか慎重な設計が必要です。

 その他の特徴その他、いくつか RLS を使う上で重要な情報を列挙したいと思います。

1 つのポリシーを複数のロールに割り当てることが可能
他の権限と同様に継承されるので、グループでまとめることも可能


1 つのテーブルに複数のポリシーが定義可能
テーブルごとにポリシー名が一意である必要はある
PERMISSIVE ポリシーは OR、RESTRICTIVE ポリシーは AND で条件が結合される


次のロールは常に RLS を無視する
スーパーユーザ、BYPASSRLS 属性を持つロール、テーブル所有者
ただし、テーブル所有者は FORCE ROW LEVEL SECURITY を設定すると強制できる


参照整合性確認はデータ整合性を維持するため、常に RLS を無視する
一意性制約、主キー制約、外部キー制約など
これを悪用して情報漏洩が起こらないように注意が必要
レコードは見えないが、エラーで存在を知ることができる可能性



クエリがポリシーによって影響を受ける場合にエラーを発生させることが可能
デフォルトでは不可視なレコードは無視されるだけで、エラーにはならない
設定パラメータの row_security を off にするとエラーになる
例えば「バックアップのための取得をする際に、RLS の影響を受けていないことを保証したい」場合などに有効

以上が RLS の説明です。これで各種設定から DB、テーブル、レコードまで全ての権限管理ができるようになりました！

 さいごに最後まで読んでいただき、ありがとうございました！やや長くなってしまったのですが、これで権限の概要が理解できるといいなと思っています。また、普段は触らない部分なので、またわからなくなったときに読み返してもらえると嬉しいです！
また、先日の技術書典でも「SQL パフォーマンス改善のためのインデックス・結合の知識とクエリの調査」というタイトルで DB の話を書いた本を出したので、読んでもらえると嬉しいです。以下の URL から無料でダウンロードできます。それでは！
https://techbookfest.org/product/wCDWvUCj8m07U6vuvv3YSu?productVariantID=cjZ9wf7zmExGysp6bZL6Nw

 参考文献The PostgreSQL Global Development Group, （翻訳）日本 PostgreSQL ユーザ会, PostgreSQL 16.4 文書, 閲覧日 : 2024/12/20, https://www.postgresql.jp/document/16/html/index.html
上原 一樹, 勝俣 智成, 佐伯 昌樹, 原田 登志, 内部構造から学ぶ PostgreSQL, 技術評論社, 2022/11/24

属性	概要
スーパーユーザ	スーパーユーザかどうか
ロール作成	ロールの作成・変更の権限があるか（ただし、スーパーユーザ・レプリケーションの新規接続属性は付与できない）
権限の継承	ロールはデフォルトでは他のロールの権限を継承することができる。これを継承しない状態にも変更可能
行単位セキュリティのバイパス	RLS を有効にしてもポリシーを無視して全てのデータにアクセス可能にできる

権限	短縮形	適用可能なオブジェクトタイプ
SELECT	r (「read」)	LARGE OBJECT, SEQUENCE, TABLE（およびテーブルのようなオブジェクト）、テーブルの列
INSERT	a (「append」)	TABLE、テーブルの列
UPDATE	w (「write」)	LARGE OBJECT, SEQUENCE, TABLE, テーブルの列
DELETE	d	TABLE
TRUNCATE	D	TABLE
REFERENCES	x	TABLE、テーブルの列
TRIGGER	t	TABLE
CREATE	C	DATABASE, SCHEMA, TABLESPACE
CONNECT	c	DATABASE
TEMPORARY	T	DATABASE
EXECUTE	X	FUNCTION, PROCEDURE
USAGE	U	DOMAIN, FOREIGN DATA WRAPPER, FOREIGN SERVER, LANGUAGE, SCHEMA, SEQUENCE, TYPE
SET	s	PARAMETER
ALTER SYSTEM	A	PARAMETER

オブジェクトタイプ	すべての権限	デフォルト PUBLIC 権限	psql コマンド
DATABASE	CTc	Tc	\l
DOMAIN	U	U	\dD+
FUNCTION or PROCEDURE	X	X	\df+
FOREIGN DATA WRAPPER	U	none	\dew+
FOREIGN SERVER	U	none	\des+
LANGUAGE	U	U	\dL+
LARGE OBJECT	rw	none	\dl+
PARAMETER	sA	none	\dconfig+
SCHEMA	UC	none	\dn+
SEQUENCE	rwU	none	\dp
TABLE（およびテーブルのようなオブジェクト）	arwdDxt	none	\dp
テーブルの列	arwx	none	\dp
TABLESPACE	C	none	\db+
TYPE	U	U	\dT+

株式会社ログラステックブログPublication

株式会社ログラスは「良い景気を作ろう。」をミッションに、企業経営を推進する次世代型経営管理クラウド「Loglass 経営管理」を開発し提供しています。

はじめに

権限の全体像

ロール

権限

権限の付与

権限の確認

RLS（Row Level Security）

RLS の例

例 : users テーブルの可視データを制限する

条件の指定方法

その他の特徴

さいごに

参考文献

Discussion