備忘録的プログラミングリファレンス

データベース、テーブルの操作権限とロール

 データベース、テーブル、ビューなどにロールによる閲覧、挿入、削除などの操作権限を設定する方法についてです。

 まずはテーブルごとのロール設定の確認方法と、システムオブジェクト(PostgreSQLがデータベースなどを管理するためのテーブルなど)のロール設定の確認方法です。

データベースやテーブルごとのロール設定の確認

 テーブルごとのロール設定の確認は、データベースで\dpコマンドで実行します。

テーブルごとのロール設定
\dp
...

		Access privileges
Schema |     Name     |   Type   |    Access privileges    | Column privileges | Policies
--------+--------------+----------+-------------------------+-------------------+----------
public | test         | table    |                         |                   |

 テーブルごとのロール設定は、access privileges項目をみます。

システムオブジェクトのロール設定

 システムオブジェクトとはPostgreSQLデータベースが管理するテーブルなどのオブジェクトです。 このシステムオブジェクトにもロールを設定することができます。
 PostgreSQLのシステムオブジェクトを確認するには、\dS[+]コマンドを実行します。

システムオブジェクトのロール設定の確認
\dS
...

		List of relations
Schema   |              Name               |   Type   |  Owner
------------+---------------------------------+----------+----------
pg_catalog | pg_aggregate                    | table    | postgres
pg_catalog | pg_am                           | table    | postgres
pg_catalog | pg_amop                         | table    | postgres
pg_catalog | pg_amproc                       | table    | postgres
pg_catalog | pg_attrdef                      | table    | postgres
pg_catalog | pg_attribute                    | table    | postgres
pg_catalog | pg_auth_members                 | table    | postgres
pg_catalog | pg_authid                       | table    | postgres
pg_catalog | pg_available_extension_versions | view     | postgres
pg_catalog | pg_available_extensions         | view     | postgres
...

 ロール設定は、データベース、スキーマ、テーブル、ビュー、カラムごとに操作権限が設定できます。ロール設定にはGRANTコマンドを使います。

データベースのロール設定

 データベースに関するロールの設定です。データベースごとにロールを設定することができます。

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
	ON DATABASE database_name [, ...]
	TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

 権限は以下があります。

権限名概要
CREATEデータベースの場合は、新規スキーマの作成を許可
CONNECTデータベースへのアクセスを許可。閲覧?新規追加、削除などは?この権限は、アクセス開始時にpg_hba.confの設定と共に検査される。
TEMPORARY一時テーブルの作成を許可
TEMPTEMPORARYに同じ
ALL上記のすべてを許可

 例えば、あるロールにデータベースへのアクセスを許可する場合は以下のようにします。

データベースへのアクセス許可

=> GRANT CONNECT ON DATABASE database_name TO role_name;

スキーマ、テーブル、ビューごとのロール設定

 スキーマ、テーブル、ビューごとにロールを設定することができます。

スキーマのロール設定

 スキーマーの操作を許諾させるためには、データベースへのログイン許可が必要です。 もしもスキーマ以下のテーブルの操作を許可するには、スキーマへのアクセスを許可しなければなりません。

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
  ON SCHEMA schema_name [, ...]
  TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

 権限は以下のようになっています。

SELECTレコードを閲覧
INSERTレコードを新規追加
UPDATEレコードの更新
DELETEレコードの削除
TRUNCATEテーブルの全レコードを削除
REFERENCES外部キー制約の作成
TRIGGERトリガの作成
CREATEスキーマやテーブルなどのオブジェクトの作成
CONNECT接続許可
TEMPORARY一時テーブルの作成
EXECUTE関数やプロシージャ、演算子の使用を許可
USAGE対象がスキーマの場合、オブジェクトへのアクセスを許可する 対象がそれ以外の場合、対象に応じた権限が付与される
ALL PRIVILEGES利用可能な権限を全て許可
スキーマのアクセスを可能に

=> ALTER ROLE new_name WITH LOGIN;									/* ロールのログインの許可 */

=> GRANT USAGE ON SCHEMA schema_name To role_name;	/* スキーマのアクセス許可 */

テーブルのロール設定

 テーブルへの認証を許可するには、データベースへのログイン、スキーマのアクセスを許可する必要があります。

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
	[, ...] | ALL [ PRIVILEGES ] }
  ON { [ TABLE ] table_name [, ...]
       | ALL TABLES IN SCHEMA schema_name [, ...] }
  TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
テーブルのデータを閲覧だけ可能に

=> ALTER ROLE new_name WITH LOGIN;									/* ロールのログインの許可 */

=> GRANT USAGE ON SCHEMA schema_name To role_name;	/* スキーマのアクセス許可 */

=> GRANT SELECT ON table_name To role_name;					/* テーブルの閲覧を許可 */

カラムごとのロール設定

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
特定のカラムの閲覧だけ可能に

GRANT SELECT (column_name) ON table_name To role_name;
		

 外部からPostgreSQLのデータ操作をするには、そのユーザーでPostgreSQLへのログインを許可する必要があります。
 ログイン認証については「ログイン認証とロールの設定」を参照して下さい。