PostgreSQL/テーブルに対してのアクセス権限を設定する・GRANT,REVOKE

• Prev
• Next
• PostgreSQL

PostgreSQLでテーブルに対してのアクセス権限を設定する・GRANT,REVOKE †

テーブルに対してアクセス権限の設定を行うGRANT, REVOKEの使用例を以下に記します。
データベース毎のなどのアクセス権限については、GRANTの構文を確認して実施してみてください。
基本的に多少の構文が変わるだけなので、本資料は役立つと思います。

使用したOSはCentOS6になります。
また、使用したPostgreSQLのバージョンは以下の通りです。

postgres=# select version();
                                                     version                                                      
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.4.13 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit
(1 行)

関連記事・関連資料 †

• PostgreSQLでユーザを追加する方法
• PostgreSQLでユーザを削除する方法
• PostgreSQLでユーザのロールを変更する・ALTER ROLE, ALTER USER

GRANT, REVOKEを使ってユーザにアクセス権限の設定を行う †

以下にユーザ、データベースを新規に作成しGRANT,REVOKEを使ってアクセス権限を設定しテーブルを操作してみます。

GRANT, REVOKE実施前準備 †

以下のようにpostgresユーザでPostgreSQLに接続し、データベースfooの作成、テーブルt1の作成、ユーザsakuraを作成しました。

• データベースfooの作成

  -bash-4.1$ psql
  psql (8.4.13)
  "help" でヘルプを表示します.
  
  postgres=# create database foo;
  CREATE DATABASE
  postgres=# \l
                                           データベース一覧
     名前    |  所有者  | エンコーディング |  照合順序   | Ctype(変換演算子) |      アクセス権       
  -----------+----------+------------------+-------------+-------------------+-----------------------
   foo       | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | 
   postgres  | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | 
   template0 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres
                                                                             : postgres=CTc/postgres
   template1 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres
                                                                             : postgres=CTc/postgres
  (4 行)

• fooデータベースに切替

  postgres=# \c foo
  psql (8.4.13)
  データベース "foo" に接続しました。.

• テーブルt1を作成

  foo=# create table t1 (c1 int, c2 int);
  CREATE TABLE
  foo=# \d t1
     テーブル "public.t1"
   カラム |   型    | 修飾語 
  --------+---------+--------
   c1     | integer | 
   c2     | integer | 

• ユーザsakuraの作成

  foo=# create user sakura with password 'sakurapass';
  CREATE ROLE
  foo=# SELECT usename FROM pg_user;
   usename  
  ----------
   postgres
   sakura
  (2 行)

GRANTでアクセス権限を付与せずt1テーブルを操作してみる †

新しく作成したsakuraでデータベースfooに接続しinsertをしてみると権限がないためエラーとなります。

• sakuraでデータベースfooに接続
  (pg_hba.confの設定を変更し、以下のコマンドで接続できるようにしています。)

  -bash-4.1$ psql -U sakura -h 127.0.0.1 foo
  ユーザ sakura のパスワード: 
  psql (8.4.13)
  "help" でヘルプを表示します.
  

• t1テーブルの確認

  foo=> \d t1
     テーブル "public.t1"
   カラム |   型    | 修飾語 
  --------+---------+--------
   c1     | integer | 
   c2     | integer | 
  

• INSERT文を発行すると権限がないためエラーとなる。

  foo=> insert into t1 values(1,1);
  ERROR:  リレーション t1 への権限がありません
  foo=> 

GRANTでINSERT, SELECT権限を付与してみる †

スーパーユーザpostgresにで再度fooデータベースに接続し、GRANTでINSERT, SELECTを付与してみます。

• postgresユーザでfooデータベースに接続

  -bash-4.1$ psql foo
  psql (8.4.13)
  "help" でヘルプを表示します.

• GRANTでt1テーブルに対し、ユーザsakuraにSELECTとINSERTのアクセス権限を付与します。

  foo=# GRANT SELECT, INSERT ON t1 TO sakura;
  GRANT

• アクセス権限を確認するとt1テーブルに対し、sakuraにSELECTとINSERTの権限が付与されているのが確認できます。

  foo=# \z
                                    アクセス権
  スキーマ | 名前 |    型    |        アクセス権         | アクセス権限を剥奪する 
  ----------+------+----------+---------------------------+------------------------
  public   | t1   | テーブル | postgres=arwdDxt/postgres | 
                             : sakura=ar/postgres          

  (1 行)

• INSERT, SELECT, UPDATEを実行してみるとUPDATEがエラーになります。
• sakuraでデータベースfooに接続

  -bash-4.1$ psql -U sakura -h 127.0.0.1 foo
  ユーザ sakura のパスワード: 
  psql (8.4.13)
  "help" でヘルプを表示します.

• INSERT, SELECTを発行

  foo=> INSERT INTO t1 VALUES(1,1),(2,2);
  INSERT 0 2
  foo=> SELECT * FROM t1;
   c1 | c2 
  ----+----
    1 |  1
    2 |  2
  (2 行)

• UPDATEを発行

  foo=> UPDATE t1 SET c1=10, c2=10 WHERE c1=1;
  ERROR:  リレーション t1 への権限がありません
  foo=> 

GRANTで全てのアクセス権限を付与する †

上記の例では、SELECTとINSERTのみテーブルt1に対しアクセス権限をあたえました。
以下の例は、ALLを使って全ての権限を付与します。

• スーパーユーザpostgresでfooデータベースに接続。

  -bash-4.1$ psql foo
  psql (8.4.13)
  "help" でヘルプを表示します.

• t1テーブルに対しsakuraにALLの権限を付与しています。

  foo=# GRANT ALL ON t1 TO sakura;
  GRANT

• \zにてアクセス権限を確認。

  foo=# \z
                                     アクセス権
   スキーマ | 名前 |    型    |        アクセス権         | アクセス権限を剥奪する 
  ----------+------+----------+---------------------------+------------------------
   public   | t1   | テーブル | postgres=arwdDxt/postgres | 
                              : sakura=arwdDxt/postgres     
  (1 行)

sakura=arwdDxt/postgres の意味 †

sakura=以降の文字列について説明します。
各文字は以下の意味があります。

REVOKEでアクセス権限を剥奪してみる †

上記では、ユーザsakuraに対しアクセス権限を付与しました。
以下、付与した権限を剥奪してみます。

REVOKEでTRUNCATE権限を剥奪する †

• スーパーユーザpostgresでデータベースfooに接続する。
• bash-4.1$ psql foo

  psql (8.4.13)
  "help" でヘルプを表示します.

• REVOKEでTRUNCATE権限を剥奪する

  foo=# REVOKE TRUNCATE ON t1 FROM sakura;
  REVOKE

• \zでアクセス権限を確認する

  foo=# \z
                                     アクセス権
   スキーマ | 名前 |    型    |        アクセス権         | アクセス権限を剥奪する 
  ----------+------+----------+---------------------------+------------------------
   public   | t1   | テーブル | postgres=arwdDxt/postgres | 
                              : sakura=arwdxt/postgres      
  (1 行)

   
  sakura=arwdxt/postgresのDが無くなっており、TRUNCATEのアクセス権限が無くなっているのが確認できます。

REVOKEで全てのアクセス権限を剥奪する †

• ALLで全てのアクセス権限を剥奪します。

  foo=# REVOKE ALL ON t1 FROM sakura;
  REVOKE

• \zでアクセス権限を確認する

  foo=# \z
                                     アクセス権
   スキーマ | 名前 |    型    |        アクセス権         | アクセス権限を剥奪する 
  ----------+------+----------+---------------------------+------------------------
   public   | t1   | テーブル | postgres=arwdDxt/postgres | 
  (1 行)

• ユーザsakuraは削除されません。

  foo=# SELECT usename FROM pg_user;
   usename  
  ----------
   postgres
   sakura
  (2 行)

GRANT, REVOKEの構文確認 †

psqlで接続し\hを使うと簡単に構文を確認することができます。

\h コマンド

• GRANTの構文確認

  postgres=# \h GRANT
  コマンド:     GRANT
  説明:         アクセス権限を定義する
  書式:
  GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
      [,...] | ALL [ PRIVILEGES ] }
      ON [ TABLE ] テーブル名 [, ...]
      TO { [ GROUP ] ロール名 | PUBLIC } [, ...] [ WITH GRANT OPTION ]
  
  GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( カラム名 [, ...] )
      [,...] | ALL [ PRIVILEGES ] ( カラム名 [, ...] ) }
      ON [ TABLE ] テーブル名 [, ...]
      TO { [ GROUP ] ロール名 | PUBLIC } [, ...] [ WITH GRANT OPTION ]
  
  <省略>

• REVOKEの構文確認

  postgres=# \h REVOKE
  コマンド:     REVOKE
  説明:         アクセス権限を剥奪する
  書式:
  REVOKE [ GRANT OPTION FOR ]
      { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
      [,...] | ALL [ PRIVILEGES ] }
      ON [ TABLE ] テーブル名 [, ...]
      FROM { [ GROUP ] ロール名 | PUBLIC } [, ...]
      [ CASCADE | RESTRICT ]
  
  REVOKE [ GRANT OPTION FOR ]
      { { SELECT | INSERT | UPDATE | REFERENCES } ( カラム名 [, ...] )
      [,...] | ALL [ PRIVILEGES ] ( カラム名 [, ...] ) }
      ON [ TABLE ] テーブル名 [, ...]
      FROM { [ GROUP ] ロール名 | PUBLIC } [, ...]
      [ CASCADE | RESTRICT ]
  
  <省略>

オプションが多数ありますので、必要に応じて設定してください。