4
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

PostgreSQL 14がやってくる(6) - pg_read_all_data/pg_write_all_data

Posted at

はじめに

にゃーん。
最近、ばたばたしていたPostgreSQL 14の調査をサボってましたが、また見てみようかなと。
今日は、PostgreSQL 14で追加された定義済みロール1について調べてみました。

定義済みロール

定義済みロールは、PostgreSQL 9.6から導入された権限分掌強化のための仕組みです。以降、各メジャーバージョンアップごとにさり気なく増えてます。
それぞれの定義済みロールがどのバージョンで増えたのかは以下の表を見てください。

ロール名 9.6 10 11 12 13 14
pg_signal_backend
pg_read_all_settings -
pg_read_all_stats -
pg_stat_scan_tables -
pg_monitor -
pg_read_server_files - -
pg_write_server_files - -
pg_execute_server_program - -
pg_read_all_data - - - - -
pg_write_all_data - - - - -
pg_database_owner - - - - -

PostgreSQL 14で増えたデフォルトロール

PostgreSQL 14では以下の3つのデフォルトロールが追加されました。

ロール名 概要
pg_read_all_data すべてのテーブル、ビュー、シーケンスに対する読み取り権限を与える。
pg_write_all_data すべてのテーブル、ビュー、シーケンスに対する書き込み権限を与える。
pg_database_owner データベース所有者に一致する定義済みロール。
テンプレートデータベースで有用。

これらのロールはログイン権限を持っていないので、それ単独でログイン用のユーザとしては使えません。他のロール(ユーザ)にこのロールをメンバとして持つようにGRANT文で設定が必要です。

今回の記事では、上記のうちpg_read_all_datapg_write_all_dataについてPostgreSQL 14 beta3で動作確認した内容を紹介します。

検証モデル

今回の検証では、最初に以下の3つの一般ユーザ(ログイン権限をもったロール)を作ります。

ユーザと付与するロール

ユーザ名 このユーザの役割 付与するロール
db_owner データベース所有者。そのデータベースに対してなんでもできる。ローカルな神 なし(データベース作成時に所有者に指定する)
data_admin 業務データの参照/更新を行うユーザ pg_read_all_data
pg_write_all_data
viewer 業務データの参照のみを行うユーザ pg_read_all_data

作成するデータベース/スキーマ/テーブル

  • testdb というデータベースを作成し、所有者を db_owner に設定する。
  • db_ownerがtestdb データベースの下にtestスキーマを作成する。
  • db_owverがtestスキーマ内にfooテーブルを作成する。

data_admin/viewerユーザの挙動

上記の設定により、data_admin/viewerユーザは以下のように動作する。

操作 data_admin viewer
testスキーマ上に任意のテーブルを作成する 権限エラー 権限エラー
fooテーブルのSELECT 成功 成功
fooテーブルのUPDATE 成功 権限エラー
fooテーブルのDELETE 成功 権限エラー
fooテーブルのTRUNCATE 権限エラー 権限エラー

ちょっと面白いことに、pg_write_all_dataを付与したdata_adminは、DELETEはできるけど、TRUNCATEはできない。
また、今回はdata_adminにはpg_write_all_datapg_read_all_dataを付与しているが、pg_write_all_dataしか付与しない場合は、INSERTはできるけどUPDATEもDELETEも権限エラーになる。
動きを考えればまあ、そうかとなるんだけど(UPDATEもDELETEもWHERE句での評価を行う以上、対象の行を読めなければいけない)、PostgreSQL 14 beta3文書のTable 22.1を見ると

Write all data (tables, views, sequences), as if having INSERT, UPDATE, and DELETE rights on those objects, and USAGE rights on all schemas, even without having it explicitly.

と書いてあって、一見するとpg_write_all_dataロールだけ付与すれば、INSERT/UPDATE/DELETEが出来るようにも読めちゃうのよなあ。にゃーん。

おわりに

ということで、PostgreSQL 14のpg_write_all_datapg_read_all_dataをうまく使うと、データ更新をさせたいユーザと参照のみしかさせないユーザの管理を簡単にできるかも?という話でした。

参考

以前のバージョンの定義済みロール(デフォルトロール)については、以前自分が調べた記事も参考にしてください。


  1. PostgreSQL 13まではデフォルトロール(Default Role)と呼ばれていました。呼び方自体、PostgreSQL 14で変えたみたい。 

4
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
4
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?