HOME > 受験対策 > OSS-DB道場> オススメ!OSS-DB情報 > 第22回 PostgreSQL 9.5について(後編)

OSS-DB道場

第22回 PostgreSQL 9.5について(後編)

2016年1月7日(米国時間)にリリースされたPostgreSQLバージョン9.5の機能紹介の後編です。

(3)行単位セキュリティの導入

GRANTによりSELECT、INSERT、UPDATE、DELETEのアクセス権限をテーブルごと、あるいはテーブル内の列ごとに付与することができますが、GRANTではテーブル内の行ごとにアクセス権限を管理することはできません。つまり、テーブル内の特定の行にアクセスできるユーザは、テーブル内のすべての行にアクセスできました。
目的に合わせてビューを使うことで、間接的にテーブル内の一部の行にだけアクセスを許可することも可能でしたが、9.5で導入された行単位のセキュリティポリシーを使うと、特定の条件に合った行だけSELECT/INSERT/UPDATE/DELETEの操作を可能にすることを、テーブル単位でロール毎に設定できます。
行単位セキュリティが有効なテーブルでは、セキュリティポリシーに反する行は、例えばSELECTしても行が返されません(ユーザからは行が存在しないように見えます)し、UPDATEしても更新されません。ポリシーに反する行をINSERTやUPDATEで作成しようとするとエラーになります。
セキュリティポリシーはCREATE POLICYで作成し、対象のテーブル、対象のユーザ(ロール)、対象の操作(SELECT/INSERT/UPDATE/DELETE/ALL)、各操作が可能となる条件式などを指定します。
なお、行単位セキュリティの機能はPostgreSQLの独自拡張です。

(4)UPDATEのSET句でSELECTリストが使用可能に

従来、UPDATE文で複数列を同時に更新するときの構文は
UPDATE table_name
SET column_name1 = expression1, column_name2=expression2...
あるいは
SET (column_name1, column_name2...) = (expression1, expression2...)
のいずれかでしたが、expressionとして複数列を返すSELECT文を記述することができませんでした。
他のテーブルを参照してデータを更新するときはFROM句(PostgreSQLの独自拡張)を使って表結合することで回避していましたが、9.5ではSQL標準に従ったSELECTリストを代入する構文が追加され、
SET (column_name1, column_name2...) = (SELECT col1, col2... FROM ...)
というような記述が可能になりました。
以下に具体的な例を示します。

:psqlのプロンプトからの入力、:psqlからの出力)

2つのテーブルmasterとsubがあります。

select * from master;
 id | a  | b
----+----+----
  1 | a1 | b1
  2 | a2 | b2
  3 | a3 | b3
(3 rows)
select * from sub;
 id |   a   |   b
----+-------+-------
  1 | a1sub | b1sub
  2 | a2sub | b2sub
(2 rows)

masterテーブルのa列、b列の内容を、subテーブルのもので置き換えたいとき、PostgreSQL 9.4まででは、FROM句による表結合を使って、

update master m
set (a, b) = (s.a, s.b)
from sub s
where m.id = s.id;

のように書いていましたが、PostgreSQL 9.5では、これに加えて

update master m
set (a, b) = (select a, b from sub s where m.id = s.id);

のように書くこともできます。
ただし、これら2つのUPDATEは結果が必ずしも同じではないことに注意してください。
この例ではsubテーブルにid=3の列が存在しないため、FROMを使う場合は、結合できる2行だけが更新されて

select * from master;
 id |   a   |   b
----+-------+-------
  3 | a3    | b3
  1 | a1sub | b1sub
  2 | a2sub | b2sub
(3 rows)

となりますが、SELECTを使う場合は、id=3に対してNULLが返されるため3行とも更新され、

select * from master;
 id |   a   |   b
----+-------+-------
  1 | a1sub | b1sub
  2 | a2sub | b2sub
  3 |       |
(3 rows)

という結果になります。
また、逆に以下のようにsubテーブルにid=3の行が複数ある(subテーブルのid列には一意制約がないものとします)場合、

select * from sub;
 id |   a   |   b
----+-------+-------
  1 | a1sub | b1sub
  2 | a2sub | b2sub
  3 | a3sub |
  3 |       | b3sub
(4 rows)

FROMを使うとUPDATE自体はエラーになりませんが、その結果は

select * from master;
 id |   a   |   b
----+-------+-------
  1 | a1sub | b1sub
  2 | a2sub | b2sub
  3 | a3sub |
(3 rows)

あるいは

 id |   a   |   b
----+-------+-------
  1 | a1sub | b1sub
  2 | a2sub | b2sub
  3 |       | b3sub
(3 rows)

で、id=3の行の更新結果がどうなるかは予測できません。
SELECTを使う場合は、1行の更新にSELECTが複数行を返すことが認められず、

ERROR:  more than one row returned by a subquery used as an expression

というエラーになります。

別の例を採り上げます。
以下のdetailsテーブルをgid列の値が同じ行ごとにグループ化して集計し、summaryテーブルに格納することを考えます。

select * from details ;
 did | gid | x
-----+-----+---
   1 |   1 | 1
   2 |   1 | 2
   3 |   1 | 3
   4 |   2 | 2
   5 |   2 | 4
   6 |   2 | 6
   7 |   3 | 4
   8 |   3 | 5
(8 rows)
select * from summary;
 id | sumx | avgx | countx
----+------+------+--------
  1 |      |      |
  2 |      |      |
  3 |      |      |
(3 rows)

FROM句を使って結合する場合、集約関数が使えないという制約があるため、PostgreSQL 9.4まででは、次のようにするしかありませんでした。

update summary set
sumx=(select sum(x) from details where summary.id = details.gid),
avgx=(select avg(x) from details where summary.id = details.gid),
countx=(select count(x) from details where summary.id = details.gid);
UPDATE 3

PostgreSQL 9.5では、次のように記述できます。

update summary
set (sumx, avgx, countx)
=(select sum(x), avg(x), count(x) from details where summary.id = details.gid);
UPDATE 3

いずれも結果は同じで

select * from summary;
 id | sumx | avgx | countx
----+------+------+--------
  1 |    6 |    2 |      3
  2 |   12 |    4 |      3
  3 |    9 |  4.5 |      2
(3 rows)

となりました。

解説:松田神一

ページトップへ