このエントリーをはてなブックマークに追加


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 行)

関連記事・関連資料

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=以降の文字列について説明します。
各文字は以下の意味があります。

文字説明
aINSERT可能(apend)
rSELECT可能(read)
wUPDATE可能(write)
dDELETE可能(delete)
DTRUNCATE
xREFERENCES
tTRIGGER
/yyyyこの権限を付与したロール

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 ]
    
    <省略>

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


トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2015-03-20 (金) 22:08:00