8
0

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 1 year has passed since last update.

PG16:allow granting VACUUM and ANALYZE privileges on relations

Last updated at Posted at 2022-11-30

はじめに

にゃーん。趣味でポスグレをやっている者だ。

この記事はPostgreSQL 16 全部ぬこ Advent Calendar 2022 1日目の記事です。
今回はVACUUM/ANALYZEを実行可能な権限をもつ定義済みロールの追加について書いてみます。

今日、別件で、12/19に最新化したソースでpg_rolesを調べたら、定義済みロールpg_vacuum_all_tablesと、pg_analyze_all_tablesが消えてました・・・。
どうやらpg_maintainに統合されたようです。(2022-12-21)

概要

項目 内容
タイトル allow granting VACUUM and ANALYZE privileges on relations
Topic Miscellaneous
ステータス commited
Last modified 2022-11-28
概要 VACUUM/ANALYZE用の定義済みロールの追加

変更内容

PostgreSQL 16では、VACUUMやANALYZEを行う権限を持つ定義済みロールが追加されました。

定義済みロールとは

PostgreSQLには9.6から導入された定義済みロールと呼ばれるロールがあります。これは、特権ユーザ(postgresユーザ)でないとできなかった操作を、別のロールに割当て、そのロールを非特権ユーザに割り当てることで、特権を持たないユーザでも様々な運用を可能にします。

PostgreSQL 9.6~16(devel)までに対応した定義済みロールを以下を示します。

ロール名 対応ver ロールの機能
pg_signal_backend 9.6 他のバックエンドを終了可能
pg_read_all_settings 10 全ての設定変数を読み取る
pg_read_all_stats 10 全てのpg_stat_*ビューを読み取る。
pg_stat_scan_tables 10 各種の統計関連のエクステンションを使用できる。
pg_monitor 10 pg_read_all_settings, pg_read_all_stats, pg_stat_scan_tablesを兼ねる。
pg_read_server_files 11 サーバ上のファイルを読める。
pg_write_server_files 11 サーバ上のファイルに書ける。
pg_execute_server_program 11 サーバ上でのプログラムを実行できる。
pg_read_all_data 14 すべてのデータ(テーブル、ビュー、シーケンス)を読み取る。
pg_write_all_data 14 すべてのデータ(テーブル、ビュー、シーケンス)に書き込む。
pg_database_owner 14 データベース所有者に一致する定義済みロール。
pg_checkpoint 15 CHECKPOINTを実行可能なロール
pg_vacuum_all_tables 16 VACUUMを実行可能なロール
pg_analyze_all_tables 16 ANALYZEを実行可能なロール

PostgreSQL 16では上記の表の最後の2行にある、pg_vacuum_all_tablespg_analyze_all_tablesが追加されました。
この追加されたロールは、名前のとおり全てのテーブルに対するVACUUMやANALYZEを実行する権限を持ちます。

実行例

検証環境

createuserユーティリティを使って、postgresユーザの権限で以下の非特権ロールを作成します。

ロール名 ロール内容
testdb_owner testdbデータベースの所有者
user_a user_aスキーマのデータにアクセス可能なロール
user_b user_bスキーマののデータにアクセス可能ロール
user_m VACUUM/ANALYZEのみ可能なロール
(PostgreSQL 16のみ使用)
$ createuser -U postgres testdb_owner
$ createuser -U postgres user_a
$ createuser -U postgres user_b
$ createuser -U postgres user_m
$ psql -U postgres postgres -c "\du"
                                     List of roles
  Role name   |                         Attributes                         | Member of
--------------+------------------------------------------------------------+-----------
 postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testdb_owner |                                                            | {}
 user_a       |                                                            | {}
 user_b       |                                                            | {}
 user_m       |                                                            | {}

$

postgresユーザの権限で、検証用のデータベースtest_dbを所有者db_ownerとして作成します。

$ createdb  -U postgres -O testdb_owner testdb
$ psql -l
                                              List of databases
   Name    |    Owner     | Encoding | Collate | Ctype | ICU Locale | Locale Provider |   Access privileges
-----------+--------------+----------+---------+-------+------------+-----------------+-----------------------
 postgres  | postgres     | UTF8     | C       | C     |            | libc            |
 template0 | postgres     | UTF8     | C       | C     |            | libc            | =c/postgres          +
           |              |          |         |       |            |                 | postgres=CTc/postgres
 template1 | postgres     | UTF8     | C       | C     |            | libc            | =c/postgres          +
           |              |          |         |       |            |                 | postgres=CTc/postgres
 testdb    | testdb_owner | UTF8     | C       | C     |            | libc            |
(4 rows)

$

psqlを使ってtestdbにログインし、db_owner権限で、user_a用、user_b用のスキーマを作成し、それぞれのスキーマと同名のユーザにALL権限(USAGEとCREATE相当)を与えます。

=> CREATE SCHEMA user_a;
CREATE SCHEMA
=> CREATE SCHEMA user_b;
CREATE SCHEMA
=> GRANT ALL ON SCHEMA user_a TO user_a ;
GRANT
=> GRANT ALL ON SCHEMA user_b TO user_b ;
GRANT
=>

スキーマの権限を確認します。

=> \dnp+
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |
 user_a | testdb_owner      | testdb_owner=UC/testdb_owner          +|
        |                   | user_a=UC/testdb_owner                 |
 user_b | testdb_owner      | testdb_owner=UC/testdb_owner          +|
        |                   | user_b=UC/testdb_owner                 |
(3 rows)

=>

user_aユーザ、user_bユーザでログインし、それぞれアクセス可能なスキーマ上にtestテーブルを作成します。

$ psql testdb -U user_a -c "CREATE TABLE user_a.test_a (id int primary key, data text)"
CREATE TABLE
$ psql testdb -U user_b -c "CREATE TABLE user_b.test_b (id int primary key, data text)"
CREATE TABLE
$

PostgreSQL 15

PostgreSQL 15上で、このtestdbに対してVACUUMを実行すると以下のようになります。
以下はuser_aロールでVACUUMを実行する例になります。

$ psql testdb -U user_a -c "VACUUM"
実行ロール user_aスキーマ user_bスキーマ システムカタログ
user_a VACUUMされる スキップされる スキップ
user_b スキップ VACUUMされる スキップ
testdb_owner VACUUMされる VACUUMされる データベース内のカタログはVACUUMされる。
データベースにまたがるカタログはスキップされる。
postgres VACUUMされる VACUUMされる VACUUMされる

PostgreSQL 16

postgresユーザでログインし、GRANTコマンドで、PostgreSQL 16に追加された定義済みロールpg_vacuum_all_tablespg_analyze_all_tablesの役割をuser_mに与えます。

=# GRANT pg_vacuum_all_tables TO user_m;
GRANT ROLE
=# GRANT pg_analyze_all_tables TO user_m;
GRANT ROLE
=#

duコマンドで、user_mがpg_vacuum_all_tablespg_analyze_all_tablesのメンバに属していることが確認できます。

=# \du
                                                      List of roles
  Role name   |                         Attributes                         |                  Member of
--------------+------------------------------------------------------------+----------------------------------------------
 postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testdb_owner |                                                            | {}
 user_a       |                                                            | {}
 user_b       |                                                            | {}
 user_m       |                                                            | {pg_vacuum_all_tables,pg_analyze_all_tables}

=#

これで準備完了です。PostgreSQL 15と同様に、各ロールでtestdbをVACUUMします。

VACUUMの結果は以下のようになります。

実行ロール user_aスキーマ user_bスキーマ システムカタログ
user_a VACUUMされる スキップされる スキップ
user_b スキップ VACUUMされる スキップ
testdb_owner VACUUMされる VACUUMされる データベース内のカタログはVACUUMされる。
データベースにまたがるカタログはスキップされる。
postgres VACUUMされる VACUUMされる VACUUMされる
user_m VACUUMされる VACUUMされる VACUUMされる

user_mにVACUUM権限が与えられたことを確認できました。

user_mの各スキーマへのアクセス

user_mはVACUUM専用のロールになっているので、user_a, user_bスキーマにアクセスしようとしても権限エラーになります。

$ psql testdb -U user_m -c "TABLE user_a.test_a"
ERROR:  permission denied for schema user_a
LINE 1: TABLE user_a.test_a
              ^
$ psql testdb -U user_m -c "TABLE user_b.test_b"
ERROR:  permission denied for schema user_b
LINE 1: TABLE user_b.test_b
              ^
$

このように、VACUUM/ANALYZE専用のメンテナンスロールを作成することで、

  • メンテナンスコマンドは実行できるけど
  • データそのものにはアクセスできない

という権限分掌が実現できるようになりました。
素晴らしい!

他のメンテナンスコマンドは?

PostgreSQLのメンテナンスはVACUUMの他にもANALYZEREINDEXCLUSTERなどがありますが、それらについても、user_mで動作させたときの結果を確認してみました。

コマンド名 結果
ANALZYE 実行可能
REINDEX SCHEMA 権限エラーになる
REINDEX 権限エラーになる
VACUUM FULL 実行可能
CLUSTER 権限エラーになる

今回与えられたロールはANALYZEVACUUM用なので、REINDEX,CLUSTERの実行権限はないようです。実質的にほぼ同じ動作のVACUUM FULLCLUSTERで挙動が異なってくるのがちょい罠っぽいですね。

psqlタブ補完の改善(2022-12-03追記)

別の改善項目(psqlのタブ補完関連)を調べているときに、以下の改善項目もこの記事に追記すべきと判断して、追記しました。

PostgreSQL 16からは、psqlのタブ補完でGRANT/REVOKEコマンドの直後にVACUUM/ANALYZEキーワードが補完されるようになりました。

PostgreSQL 15

=# GRANT
ALL                        pg_checkpoint              pg_signal_backend          TEMPORARY
ALTER SYSTEM               pg_database_owner          pg_stat_scan_tables        testdb_owner
CONNECT                    pg_execute_server_program  pg_write_all_data          TRIGGER
CREATE                     pg_monitor                 pg_write_server_files      TRUNCATE
DELETE                     pg_read_all_data           postgres                   UPDATE
EXECUTE                    pg_read_all_settings       REFERENCES                 USAGE
GRANT                      pg_read_all_stats          SELECT
INSERT                     pg_read_server_files       SET
=# GRANT

PostgreSQL 16

=# GRANT
ALL                        pg_analyze_all_tables      pg_read_server_files       SELECT
ALTER SYSTEM               pg_checkpoint              pg_signal_backend          SET
ANALYZE                    pg_database_owner          pg_stat_scan_tables        TEMPORARY
CONNECT                    pg_execute_server_program  pg_vacuum_all_tables       TRIGGER
CREATE                     pg_monitor                 pg_write_all_data          TRUNCATE
DELETE                     pg_read_all_data           pg_write_server_files      UPDATE
EXECUTE                    pg_read_all_settings       postgres                   USAGE
INSERT                     pg_read_all_stats          REFERENCES                 VACUUM
=# GRANT

おわりに

今回はPostgreSQLの権限分掌を強化する定義済みロールの追加について紹介しました。
今後も他のメンテナンスコマンド対応のロールや、これらのロールをまとめたメンテナンス用ロールなどが追加されていくのかもしれませんね。

8
0
3

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
8
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?