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

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

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

　ロールはユーザーやグループを意味するもので詳しくはPostgreSQL ユーザー（ロール）管理を参照してください。

　まずは、データベースやテーブルのロール設定の確認方法についてです。

- ad -

データベースやテーブルのロールの確認

　テーブルごとのロール設定の確認は、psql の \dp コマンドで行います。

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

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

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

　上記の例ではアクセス権限はデフォルトの状態で、 Access privileges から分かるように何もロールの設定がされていません。

テーブルごとのロール設定

　テーブルごとにロールの設定を確認するには、\dp コマンドに続いてテーブル名を指定します。

=> \dp table_name
...

　テーブルごとのロールの設定は以下のテーブルのロール設定を参照してください。

システムオブジェクトのロールの確認

　システムオブジェクトとは、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
...

　上記の例ではデフォルトのスーパーユーザーであるpostgresがシステムオブジェクトの操作権限をもつことが分かります。

　システムオプジェクトから、以下のように操作権限を確認することができます。ただし、ログインできるロールだけが対象になります。

操作権限の一覧 
select
  usename
  , schemaname
  , tablename
  , has_table_privilege(usename, schemaname || '.' || tablename, 'select') as select
  , has_table_privilege(usename, schemaname || '.' || tablename, 'insert') as insert
  , has_table_privilege(usename, schemaname || '.' || tablename, 'update') as update
  , has_table_privilege(usename, schemaname || '.' || tablename, 'delete') as delete
  , has_table_privilege(usename, schemaname || '.' || tablename, 'references') as references
  , has_schema_privilege(usename, schemaname, 'usage') as usage
from
  pg_tables, pg_user
where
  schemaname in ('schema_name', 'public')  -- スキーマの絞り込み条件
and
  usename in ('rolename', 'postgres')  -- ユーザの絞り込み条件
order by
  1, 2, 3
;

　上記のコードは以下のページを参考にしています。

参考：AWS Redshift ユーザ単位で各テーブルに対する権限の有無を確認してみた／DevelopersIO

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

　ロール設定は、データベース、スキーマ、テーブル、ビュー、カラムごとに操作権限が設定できます。ロール設定には 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;

　データベースへのアクセスを拒否するには REVOKE 句を使用します。

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

　スキーマ、テーブル、ビューごとの操作設定をロールに与えることができます。データベースのオーナーとスーパーユーザー以外のロールでデータ操作するには以下の設定が必要になります。

スキーマのロール設定

　スキーマ以下のテーブルの操作を許可するには、スキーマへのアクセスを許可しなければなりません。

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利用可能な権限を全て許可
スキーマのアクセスを可能に
=> 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 ]
テーブルのデータを閲覧だけ可能に
=> GRANT SELECT ON table_name To role_name;					/* テーブルの閲覧を許可 */

=> GRANT USAGE ON SCHEMA schema_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へのログインを許可する必要があります。
　ログイン認証については「ログイン認証とロールの設定」を参照して下さい。