ロールにテーブルやビューなどに対する権限を追加する(GRANT)

GRANT コマンドを使ってロールに対して権限を追加することができます。権限はテーブルやスキーマの作成する権限や、テーブルやカラムからデータを取得したりデータを追加する権限などです。ここでは PostgreSQL で GANT コマンドを使って権限を追加する方法について解説します。

(Last modified: 2023年01月07日)

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;

-- --

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

\dp

テーブルに対する権限を追加する(1)

それではスーパーユーザーではない momo ロールに対して SELECT 権限を追加してみます。権限を追加する前にデータを取得したらどうなるのかを確認するため momo で PostgreSQL に接続後に staff テーブルからデータを取得してみます。

select * from staff;

テーブルに対する権限を追加する(2)

staff テーブルに対する SELECT の権限がありませんので ERROR: テーブル staff へのアクセスが拒否されましたと表示されてデータの取得に失敗します。

それでは momo ロールに対して staff テーブルおよび myshema.book テーブルへの SELECT の権限を追加します。スーパーユーザーで接続したあと次のように実行してください。

grant select on staff to momo;
grant select on myschema.book to momo;

テーブルに対する権限を追加する(3)

権限が追加されました。それでは確認のためにあらためて \dp を実行してみます。

\dp

テーブルに対する権限を追加する(4)

それぞれのテーブルのアクセス権限のところに次のような値が設定されています。

postgres=arwdDxt/postgres+
momo=r/postgres

値の見方は次の通りです。 momo ロールに対しては 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 -- この権限を付与したロール

それでは再度一般ロールである momo で PostgreSQL に接続後、 staff テーブルからデータを取得してみます。

select * from staff;

テーブルに対する権限を追加する(5)

staff テーブルからデータを取得することができました。

同じように mysheme.book テーブルからデータを取得してみます。

select * from mysheme.book;

テーブルに対する権限を追加する(6)

今度は ERROR: スキーマ myschema へのアクセスが拒否されましたと表示されてデータを取得することができませんでした。これは public スキーマであればデフォルトでスキーマ内のテーブルなどのオブジェクトに対するアクセスが許可されていますが、 public 以外のスキーマの場合は明示的に USAGE 権限を追加しないとスキーマ内のオブジェクトにアクセスすることができません。

スキーマ内のオブジェクトに対する権限の追加方法の詳細はあとからでてきますのでここでは省略します。スーパーユーザーで接続したあと次のように実行してください。

grant usage on schema myschema to momo;

テーブルに対する権限を追加する(7)

myshema スキーマ内のオブジェクトに対するアクセスが許可されました。

それではあらためて mysheme.book テーブルからデータを取得してみます。

select * from mysheme.book;

テーブルに対する権限を追加する(8)

mysheme.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 を使用してテーブルとカラムに対する権限を確認してみると、 momo には何も権限が追加されていない状態です。

\dp

テーブルのカラムに対する権限を追加する(1)

それでは momo ロールに対して staff テーブルの name カラムへの SELECT の権限を追加します。スーパーユーザーで接続したあと次のように実行してください。

grant select (name) on staff to momo;

テーブルのカラムに対する権限を追加する(2)

権限が追加されました。それでは確認のためにあらためて \dp を実行してみます。

テーブルのカラムに対する権限を追加する(3)

列の権限のところに次のような値が設定されています。

name:            +
momo=r/postgres

設定されている値の見方はアクセス権限の場合と同じです。 momo ロールに対しては postgres ロールによって r(SELECT) の権限が追加されているという意味になります。

それでは再度一般ロールである momo で PostgreSQL に接続後、 staff テーブルからデータを取得してみます。

select * from staff;

テーブルのカラムに対する権限を追加する(4)

ERROR: テーブル friend へのアクセスが拒否されましたと表示されてデータの取得に失敗しました。これは staff テーブルには id カラムと name カラムの 2 つのカラムがあり、 momo ロールには name カラムの SELECT 権限した追加されていないためです。

では staff テーブルの name カラムだけデータを取得してみます。

select name from staff;

テーブルのカラムに対する権限を追加する(5)

今度はデータの取得に成功しました。

なお 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 を使用して mydb データベースに対する権限を確認してみると、 momo には mydb データベースに対する権限が何もありません。

\l mydb

スキーマを作成する権限を追加する(1)

それでは momo ロールに対して mydb データベースでスキーマを作成する権限を追加します。スーパーユーザーで接続したあと次のように実行してください。

grant create on database mydb to momo;

スキーマを作成する権限を追加する(2)

権限が追加されました。それでは確認のためにあらためて \l を実行してみます。

スキーマを作成する権限を追加する(3)

アクセス権限のところに次のような値が設定されています。

=Tc/postgres         +
postgres=CTc/postgres+
momo=C/postgres

設定されている値の見方はアクセス権限の場合と同じです。 momo ロールに対しては postgres ロールによって C(CREATE) の権限が追加されているという意味になります。

それでは一般ロールである momo で PostgreSQL に接続後、 mydb データベースに momo スキーマを作成してみます。

create schema momo;

スキーマを作成する権限を追加する(4)

mydb データベースの中に momo スキーマを作成することができました。確認のために psql メタコマンドの \dn を実行してみます。

¥ dn

スキーマを作成する権限を追加する(5)

momo スキーマが作成されていることが確認できました。

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

最後に指定のスキーマ内にテーブルなどのオブジェクトを作成する権限を追加する場合です。

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 スキーマに対する権限を確認してみると、 momo には myschema スキーマに対する権限が何もありません。

\dn+

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

それでは momo ロールに対して myschema スキーマでオブジェクトを作成する権限を追加します。スーパーユーザーで接続したあと次のように実行してください。

grant create on schema myschema to momo;

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

権限が追加されました。それでは確認のためにあらためて \dn+ を実行してみます。

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

アクセス権限のところに次のような値が設定されています。

postgres=UC/postgres+
momo=C/postgres

それでは一般ロールである momo で PostgreSQL に接続後、 mydb データベースの momo スキーマの中に product テーブルを作成してみます。

create table myschema.product (id integer, name varchar(10));

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

momo スキーマの中に product テーブルを作成することができました。確認のために psql メタコマンドの \dp を実行してみます。

\dp

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

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

-- --

GANT コマンドを使って権限を追加する方法について解説しました。

( Written by Tatsuo Ikura )

著者 / TATSUO IKURA

これから IT 関連の知識を学ばれる方を対象に、色々な言語でのプログラミング方法や関連する技術、開発環境構築などに関する解説サイトを運営しています。