PostgreSQL | ロール(ユーザー) ROLE 作成 | テーブル、ビューなどへの権限をロールに追加(GRANT)

Tags:

• PostgreSQL
• Database

GRANT コマンドを使用すると、ロールに権限を追加できる。権限には、スキーマ作成権限、テーブルやカラムからデータを検索する権限、データを追加する権限などがある。ここでは GRANT コマンドを使用して権限を追加する方法について説明する。

GRANT コマンドを使用して権限を追加する

GRANT コマンドを使用すると、ロールに権限を追加できる。対象に応じて複数の形式が用意されている。

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

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

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER server_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { { FUNCTION | PROCEDURE | ROUTINE }
       routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
       | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE lang_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
    ON LARGE OBJECT loid [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

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

GRANT { CREATE | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespace_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

複数の形式があるが、基本は次のとおりである。

GRANT 権限 ON 対象 TO 誰

権限は対象によって異なる。まとめると次のようになる。

権限	説明
SELECT	テーブルやビューのデータを参照できる。UPDATE や DELETE を行う場合にも必要。
INSERT	テーブルに新しいデータを追加できる。
UPDATE	テーブルのデータを更新できる。
DELETE	テーブルからデータを削除できる。
TRUNCATE	テーブルのデータを空にできる。
REFERENCES	テーブルやカラムに外部キー制約を作成できる。
TRIGGER	テーブルにトリガーを作成できる。
CREATE	対象がデータベースの場合はスキーマを作成できる。対象がスキーマの場合はテーブルなどのオブジェクトを作成できる。
CONNECT	指定したデータベースに接続できる。
TEMPORARY	データベースに一時テーブルを作成できる。
EXECUTE	関数、プロシージャ、演算子の使用を許可する。
USAGE	対象がスキーマの場合はオブジェクトへのアクセスを許可する。スキーマ以外の場合は対象に応じた権限が付与される。
ALL PRIVILEGES	使用可能な権限をまとめて許可する。

対象は、テーブル、テーブルのカラム、データベース、スキーマなどである。

誰にはロール名を指定する。現在のユーザーを表す CURRENT_USER と、現在のセッションユーザーを表す SESSION_USER も指定できる。また PUBLIC を指定した場合は、すべてのロール、つまり現在および今後追加されるすべてのロールに権限が追加される。

他のロールへ権限を追加できるのは、対象オブジェクトの所有者およびスーパーユーザーである。また GRANT コマンド実行時に WITH GRANT OPTION を指定すると、権限を付与されたロールは同じ権限を別のロールに追加できる。ただし PUBLIC を指定した場合は指定できない。

それでは、より具体的に見てみる。

テーブルへの権限を追加する

まず、テーブルへの権限を追加する方法について説明する。

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

たとえば、role_name ロールに table_name テーブルを対象として SELECT と INSERT 権限を追加するには、次のように実行する。

GRANT SELECT, INSERT ON table_name To role_name;

また、role_name ロールに table_name テーブルを対象としてすべての権限を追加するには、次のように実行する。この形式で指定できるすべての権限とは、SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER である。

GRANT ALL PRIVILEGES ON table_name To role_name;

role_name ロールに、指定したスキーマ schema_name に含まれるすべてのテーブルを対象として SELECT 権限を追加するには、次のように実行する。

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name To role_name;

role_name ロールに table_name テーブルを対象として SELECT と INSERT 権限を追加し、さらに他のユーザーにも同じ権限を付与できるようにするには、次のように実行する。

GRANT SELECT, INSERT ON table_name To role_name WITH GRANT OPTION;

–

それでは実際に試してみる。現在 devkuma データベースには、public スキーマに memo テーブル、myschema スキーマに book テーブルが作成されている。テーブルとカラムの権限情報を取得するには、psql メタコマンド \dp を使用する。\dp はテーブル、ビュー、シーケンス一覧をアクセス権限とともに表示するコマンドである。

devkuma=# \dp
                         액세스 권한
 스키마 | 이름 |  종류  | 액세스 권한 | 칼럼 접근권한 | 정책
--------+------+--------+-------------+---------------+------
 public | memo | 테이블 |             |               |
(1개 행)


devkuma=# \dp myschema.*
                          액세스 권한
  스키마  | 이름 |  종류  | 액세스 권한 | 칼럼 접근권한 | 정책
----------+------+--------+-------------+---------------+------
 myschema | book | 테이블 |             |               |
(1개 행)


devkuma=#

ここで、スーパーユーザーではない mykuma ロールに SELECT 権限を追加する。権限を追加する前にデータを取得するとどうなるか確認するため、mykuma で PostgreSQL に接続し、memo テーブルからデータを取得してみる。

devkuma=> select * from memo;
오류:  memo 테이블에 대한 접근 권한 없음
devkuma=>

memo テーブルに対する SELECT 権限がないため、「오류: memo 테이블에 대한 접근 권한 없음」と表示され、データ取得に失敗する。

では mykuma ロールに memo テーブルおよび myschema.book テーブルへの SELECT 権限を追加する。スーパーユーザーで接続し、次のように実行する。

devkuma=# grant select on memo to mykuma;
GRANT
devkuma=# grant select on myschema.book to mykuma;
GRANT
devkuma=#

権限が追加された。確認のため、もう一度 \dp を実行する。

devkuma=# \dp
                                액세스 권한
 스키마 | 이름 |  종류  |        액세스 권한        | 칼럼 접근권한 | 정책
--------+------+--------+---------------------------+---------------+------
 public | memo | 테이블 | postgres=arwdDxt/postgres+|               |
        |      |        | mykuma=r/postgres         |               |
(1개 행)


devkuma=# \dp myschema.*
                                 액세스 권한
  스키마  | 이름 |  종류  |        액세스 권한        | 칼럼 접근권한 | 정책
----------+------+--------+---------------------------+---------------+------
 myschema | book | 테이블 | postgres=arwdDxt/postgres+|               |
          |      |        | mykuma=r/postgres         |               |
(1개 행)


devkuma=#

各テーブルのアクセス権限には次のような値が設定されている。

postgres=arwdDxt/postgres+
 mykuma=r/postgres

値の意味は次のとおりである。mykuma ロールについては、postgres ロールによって r、つまり SELECT 権限が追加されていることを意味する。

rolename=xxxx -- ロールに付与された権限
=xxxx -- PUBLIC に付与された権限

            r -- SELECT (read)
            w -- UPDATE (write)
            a -- INSERT (append)
            d -- DELETE
            D -- TRUNCATE
            x -- REFERENCES
            t -- TRIGGER
            X -- EXECUTE
            U -- USAGE
            C -- CREATE
            c -- CONNECT
            T -- TEMPORARY
      arwdDxt -- すべての権限。テーブル用であり、他のオブジェクトでは異なる。
            * -- 直前の権限に関するグラントオプション

        /yyyy -- この権限を付与したロール

再び一般ロール mykuma で PostgreSQL に接続し、memo テーブルからデータを取得してみる。

devkuma=> select * from memo;
 id | memo
----+------
(0개 행)


devkuma=>

memo テーブルからデータを検索できるようになった。

同様に myschema.book テーブルのデータを取得してみる。

devkuma=> select * from myschema.book;
오류:  myschema 스키마(schema) 접근 권한 없음
줄 1: select * from myschema.book;
                    ^
devkuma=>

今回は「오류: myschema 스키마(schema) 접근 권한 없음」と表示され、データを取得できない。public スキーマではデフォルトでスキーマ内のテーブルなどのオブジェクトへのアクセスが許可されているが、public 以外のスキーマでは、明示的に USAGE 権限を追加しないとスキーマ内のオブジェクトにアクセスできないためである。

スキーマオブジェクトへの権限を追加する方法の詳細は後で説明する。まずはスーパーユーザーで接続し、次のように実行する。

devkuma=# grant usage on schema myschema to mykuma;
GRANT
devkuma=#

myschema スキーマのオブジェクトへのアクセスが許可された。

再び myschema.book テーブルからデータを取得してみる。

devkuma=> select * from myschema.book;
 id
----
(0개 행)


devkuma=>

myschema.book テーブルからデータを取得できるようになった。

テーブルのカラムへの権限を追加する

次に、テーブルのカラムへの権限を追加する方法について説明する。

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

たとえば、role_name ロールに table_name テーブルの column_name カラムを対象として SELECT 権限を追加するには、次のように実行する。

GRANT SELECT (column_name) ON table_name To role_name;

それでは実際に試してみる。psql メタコマンド \dp を使用してテーブルとカラムの権限を確認すると、mykuma にはまだ何も権限が追加されていない状態である。

devkuma=# \dp
                                액세스 권한
 스키마 | 이름 |  종류  |        액세스 권한        | 칼럼 접근권한 | 정책
--------+------+--------+---------------------------+---------------+------
 public | memo | 테이블 | postgres=arwdDxt/postgres |               |
(1개 행)


devkuma=#

mykuma ロールに、memo テーブルの memo カラムへの SELECT 権限を追加する。スーパーユーザーで接続し、次のように実行する。

devkuma=# grant select (memo) on memo to mykuma;
GRANT
devkuma=#

権限が追加された。確認のため、再度 \dp を実行する。

devkuma=# \dp
                                   액세스 권한
 스키마 | 이름 |  종류  |        액세스 권한        |    칼럼 접근권한    | 정책
--------+------+--------+---------------------------+---------------------+------
 public | memo | 테이블 | postgres=arwdDxt/postgres | memo:              +|
        |      |        |                           |   mykuma=r/postgres |
(1개 행)


devkuma=#

カラム権限には次の値が設定されている。

memo:              +
 mykuma=r/postgres 

設定値の意味はアクセス権限の場合と同じである。mykuma ロールに対して、postgres ロールにより r、つまり SELECT 権限が追加されたことを意味する。

再び一般ロール mykuma で PostgreSQL に接続し、memo テーブルからデータを取得する。

devkuma=> select * from memo;
오류:  memo 테이블에 대한 접근 권한 없음
devkuma=>

「오류: memo 테이블에 대한 접근 권한 없음」と表示され、データ取得に失敗する。これは memo テーブルには id と memo の 2 つのカラムがあり、mykuma ロールには memo カラムの SELECT 権限だけが追加されているためである。

では memo テーブルの memo カラムだけを取得してみる。

devkuma=> select memo from memo;
 memo
------
(0개 행)


devkuma=>

今回はデータ取得に成功した。

なお、public スキーマ以外のスキーマに作成されたテーブルのカラムからデータを検索するには、対象スキーマの USAGE 権限も必要である。

スキーマを作成できる権限を追加する

次に、指定したデータベースにスキーマを作成できる権限を追加する方法について説明する。

GRANT {{CREATE | CONNECT | TEMPORARY | TEMP} [...] | ALL [PRIVILEGES]}
    ON DATABASE database_name [...]
    TO role_specification [...] [WITH GRANT OPTION]

たとえば、role_name ロールに database_name データベースでスキーマを作成できる権限を追加するには、次のように実行する。

GRANT CREATE ON DATABASE database_name To role_name;

それでは実際に試してみる。psql メタコマンド \l を使用して devkuma データベースへの権限を確認すると、mykuma は devkuma データベースに対して何の権限も持っていない。

devkuma=# \l devkuma
                                데이터베이스 목록
  이름   |  소유주  | 인코딩 |     Collate      |      Ctype       | 액세스 권한
---------+----------+--------+------------------+------------------+-------------
 devkuma | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 |
(1개 행)


devkuma=#

mykuma ロールに、devkuma データベースでスキーマを作成できる権限を追加する。スーパーユーザーで接続し、次のように実行する。

devkuma=# grant create on database devkuma to mykuma;
GRANT
devkuma=#

権限が追加された。確認のため、もう一度 \l を実行する。

devkuma=# \l devkuma
                                     데이터베이스 목록
  이름   |  소유주  | 인코딩 |     Collate      |      Ctype       |      액세스 권한
---------+----------+--------+------------------+------------------+-----------------------
 devkuma | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 | =Tc/postgres         +
         |          |        |                  |                  | postgres=CTc/postgres+
         |          |        |                  |                  | mykuma=C/postgres
(1개 행)


devkuma=#

アクセス権限には次のような値が設定されている。

=Tc/postgres         +
postgres=CTc/postgres+
mykuma=C/postgres

設定値の意味はアクセス権限の場合と同じである。mykuma ロールについては、postgres ロールにより C、つまり CREATE 権限が追加されたことを意味する。

では一般ロール mykuma で PostgreSQL に接続し、devkuma データベースに mykuma スキーマを作成してみる。

devkuma=> create schema mykuma;
CREATE SCHEMA
devkuma=>

devkuma データベースに mykuma スキーマを作成できるようになった。確認のため、psql メタコマンド \dn を実行する。

devkuma=> \dn
 스키마(schema) 목록
   이름   |  소유주
----------+----------
 mykuma   | mykuma
 myschema | postgres
 public   | postgres
(3개 행)


devkuma=>

mykuma スキーマが作成されていることを確認できる。

スキーマにテーブルなどのオブジェクトを作成できる権限を追加する

最後に、指定したスキーマにテーブルなどのオブジェクトを作成できる権限を追加する方法について説明する。

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

たとえば、role_name ロールに schema_name スキーマでテーブルなどを作成できる権限を追加するには、次のように実行する。

GRANT CREATE ON SCHEMA schema_name To role_name;

また、role_name ロールに schema_name スキーマ内のテーブルなどへのアクセス権限を追加するには、次のように実行する。

GRANT USAGE ON SCHEMA schema_name To role_name;

それでは実際に試してみる。psql メタコマンド \dn+ を使用して myschema スキーマへの権限を確認すると、mykuma には myschema スキーマへの権限が何もない。

devkuma=# \dn+
                         스키마(schema) 목록
   이름   |  소유주  |     액세스 권한      |          설명
----------+----------+----------------------+------------------------
 mykuma   | mykuma   |                      |
 myschema | postgres | postgres=UC/postgres+|
          |          | mykuma=UC/postgres   |
 public   | postgres | postgres=UC/postgres+| standard public schema
          |          | =UC/postgres         |
(3개 행)


devkuma=#

mykuma ロールに、myschema スキーマへオブジェクトを作成できる権限を追加する。スーパーユーザーで接続し、次のように実行する。

devkuma=# grant create on schema myschema to mykuma;
GRANT
devkuma=#

権限が追加された。確認のため、再び \dn+ を実行する。

devkuma=# \dn+
                         스키마(schema) 목록
   이름   |  소유주  |     액세스 권한      |          설명
----------+----------+----------------------+------------------------
 mykuma   | mykuma   |                      |
 myschema | postgres | postgres=UC/postgres+|
          |          | mykuma=UC/postgres   |
 public   | postgres | postgres=UC/postgres+| standard public schema
          |          | =UC/postgres         |
(3개 행)


devkuma=#

アクセス権限には次のような値が設定されている。

postgres=UC/postgres+
mykuma=C/postgres

設定値の意味はアクセス権限の場合と同じである。mykuma ロールについては、postgres ロールにより C、つまり CREATE 権限が追加されたことを意味する。

では一般ロール mykuma で PostgreSQL に接続し、devkuma データベースの myschema スキーマに product テーブルを作成してみる。

devkuma=> create table myschema.product (id integer, name varchar (10));
CREATE TABLE
devkuma=>

myschema スキーマに product テーブルを作成した。確認のため、psql メタコマンド \dp を実行する。

devkuma=> \dp myschema.*
                                  액세스 권한
  스키마  |  이름   |  종류  |        액세스 권한        | 칼럼 접근권한 | 정책
----------+---------+--------+---------------------------+---------------+------
 myschema | book    | 테이블 | postgres=arwdDxt/postgres+|               |
          |         |        | mykuma=r/postgres         |               |
 myschema | product | 테이블 |                           |               |
(2개 행)


devkuma=>

myschema.product テーブルが作成されていることを確認できる。

–

ここまで、GRANT コマンドを使用して権限を追加する方法について説明した。