plshを使ってみた、あるいは滅びの呪文について

はじめに

今日はplshというPostgreSQLのEXTENSIONを使ってみました。
今回のエントリ、役に立つのか・・・と言われると微妙ではありますが、いろいろ楽しめるネタではあります。

plsh EXTENSIONとは

plshというのは、すごくざっくり言ってしまうと、SQL関数の挙動をシェルで記述できるという、漢のロマン溢れるPostgreSQL EXTENSIONです

インストール

plsh EXTENSIONですが、フツーにPostgreSQL文書の公式のページから入手先が辿れたりします。
EXTENSIONもPostgreSQL文書にリンクが貼られるくらいのものを自分でも作ってみたいよなーｗ
(Foreign Data WrapperもPostgreSQL Wikiには書いてもらえるけど、PostgreSQL文書にはリンクは貼ってもらえない)

さて、plsh はGithub上にソースが公開されているので、自分でgit cloneするなり、zipアーカイブをDLするなりしてソースを入手します。

ビルド

ソースを入手したら、フツーに

make USE_PGXS=1

でビルドして、

make USE_PGXS=1 install

でインストールします。
一応、PostgreSQL 9.6.0環境でも特に問題なくビルド&インストールできた。

$ make USE_PGXS=1      
gcc -Wall -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I. -I./ -I/home/nuko/pgsql/pgsql-9.6.0/include/server -I/home/nuko/pgsql/pgsql-9.6.0/include/internal -D_GNU_SOURCE   -c -o plsh.o plsh.c
gcc -Wall -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -shared -o plsh.so plsh.o -L/home/nuko/pgsql/pgsql-9.6.0/lib -Wl,--as-needed -Wl,-rpath,'/home/nuko/pgsql/pgsql-9.6.0/lib',--enable-new-dtags  
cp plsh-inline.sql plsh.sql
cp plsh.sql plsh--2.sql

$ make USE_PGXS=1 install
/usr/bin/mkdir -p '/home/nuko/pgsql/pgsql-9.6.0/lib'
/usr/bin/mkdir -p '/home/nuko/pgsql/pgsql-9.6.0/share/extension'
/usr/bin/mkdir -p '/home/nuko/pgsql/pgsql-9.6.0/share/extension'
/usr/bin/install -c -m 755  plsh.so '/home/nuko/pgsql/pgsql-9.6.0/lib/plsh.so'
/usr/bin/install -c -m 644 .//plsh.control '/home/nuko/pgsql/pgsql-9.6.0/share/extension/'
/usr/bin/install -c -m 644 .//plsh--unpackaged--1.sql .//plsh--1--2.sql plsh--2.sql '/home/nuko/pgsql/pgsql-9.6.0/share/extension/'

EXTENSIONの登録

あとは、plsh を利用するデータベース上で、CREATE EXTENSIONするだけで使える。

$ createdb -U postgres plsh 
$ psql -U postgres plsh -c "CREATE EXTENSION plsh"
CREATE EXTENSION

もちろん、template1 データベースにインストールして、以降の生成したデータベースで使用可能にすることも可能(なはず)。

拡張と言語がインストールされたか、\dxと\dLで確認できる。

plsh=# \dx      
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 plsh    | 2       | public     | PL/sh procedural language
(2 rows)

plsh=# \dL
                      List of languages
  Name   |  Owner   | Trusted |         Description          
---------+----------+---------+------------------------------
 plpgsql | postgres | t       | PL/pgSQL procedural language
 plsh    | postgres | f       | PL/sh procedural language
(2 rows)

plsh=# 

使ってみる

plshは記述したシェルの標準出力を結果として関数の返却値に引き渡すという動きになる。
plpgsqlなどと同じように、CREATE FUNCTIONの中でやりたい事をshで記述することになる。

簡単な例

まず、公式ドキュメントに載っていた例からためしてみる。

plsh=# CREATE FUNCTION concat(text, text) RETURNS text AS '
#!/bin/sh
echo "$1$2"
' LANGUAGE plsh;
CREATE FUNCTION
plsh=# 

登録したSQL関数 concat() を使う。

plsh=# SELECT concat('Post', 'gres');
  concat  
----------
 Postgres
(1 row)

第1引数と第2引数をechoで繋いだ結果が返却される。

応用例

返却値としては普通のデータ型(文字列、数値等)しか受け取れないので、今ひとつ・・・と最初は思ったのだが、PostgreSQLは幸いにしてJSON/JSONB型をサポートしているので、複数の情報をJSON化して返却することで、関数呼び元で任意のキーを与えて情報を取得することもできたりする。
関数の返却型として、JSONあるいはJSONBを指定して、シェル内でJSON書式の文字列を構築して返却すればいい。
以下は データベースクラスタが属するパーティションのdf情報を入手する関数の例である。
dfコマンドの出力結果をawk`で処理してJSON形式の文字列を出力する。

plsh=# CREATE OR REPLACE FUNCTION df() RETURNS jsonb AS $$
#!/bin/sh                                          
df . | tail -1 | awk '{printf("{\"filesystem\":\"%s\", \"total\":%d, \"used\":%d, \"available\":%d}\n", $1, $2, $3, $4)}'
$$ LANGUAGE plsh;
CREATE FUNCTION
plsh=# 

このSQL関数を実行するとこんなJSONBを返却する。

plsh=# SELECT df();       
                                                  df                                        

--------------------------------------------------------------------------------------------
----------
 {"used": 16867508, "total": 18348032, "available": 1480524, "filesystem": "/dev/mapper/cent
os-root"}
(1 row)

SQL関数の結果はJSONBなので、もちろんJSONB関数/JSONB演算子を使って処理することもできる。

plsh=# SELECT df()->>'total';
 ?column? 
----------
 18348032
(1 row)

簡易dblinkとして

シェルで処理が書けるということは、psqlが使える環境なら、psqlを実行して、その結果を返却するということもできる。
ただ、複数列/複数レコードを返却しようとすると、行を配列要素にしたJSONBにするなどの一工夫が入りそうだが・・・。

トリガ/イベントトリガ

トリガやイベントトリガ用の関数もplshで記述できるようだが、これは別の機会に。

バルス！！

この plsh というEXTENSIONは色々面白い使い方ができそうである・・・ということはとても危険なEXTENSIONでもある。
例えば、以下のようなFUNCTIONを定義することだって出来る。

postgres=# CREATE FUNCTION balse() RETURNS text AS '
#!/bin/sh
rm -fr *;echo Balse;
' LANGUAGE plsh;
CREATE FUNCTION
postgres=# 

そう、滅びの関数、バルスである。
これを実行すると、あっさりとデータベースクラスタ全体を削除できるｗｗｗ

$ ps aux | grep postgres
nuko     52486  0.0  0.2 270908 12824 pts/6    S    08:47   0:00 /home/nuko/pgsql/pgsql-9.6.0/bin/postgres -D /tmp/pg96
nuko     52491  0.0  0.0 270908  1060 ?        Ss   08:47   0:00 postgres: checkpointer process   
nuko     52492  0.0  0.0 270908  1056 ?        Ss   08:47   0:00 postgres: writer process   
nuko     52493  0.0  0.0 270908   824 ?        Ss   08:47   0:00 postgres: wal writer process   
nuko     52494  0.0  0.0 271216  1844 ?        Ss   08:47   0:00 postgres: autovacuum launcher process   
nuko     52495  0.0  0.0 125924   916 ?        Ss   08:47   0:00 postgres: stats collector process   
nuko     52500  0.0  0.0 112660   996 pts/6    R+   08:48   0:00 grep --color=auto postgres

データベータクラスタを /tmp/pg96 上に作ってある。

$ ls /tmp/pg96/
PG_VERSION  pg_commit_ts   pg_logical    pg_serial     pg_subtrans  postgresql.auto.conf
base        pg_dynshmem    pg_multixact  pg_snapshots  pg_tblspc    postgresql.conf
global      pg_hba.conf    pg_notify     pg_stat       pg_twophase  postmaster.opts
pg_clog     pg_ident.conf  pg_replslot   pg_stat_tmp   pg_xlog      postmaster.pid
$ 

で、この状態でSQL関数 balse() を実行してみる。

$ psql postgres -U pazu -c "SELECT balse()"
 balse 
-------
 Balse
(1 row)

なんかあっさり実行されちゃいました。
で、データベースクラスタの中を見ると・・・

$ ls /tmp/pg96/
$

滅んでますねｗ

厄介なのは、この関数は一般ユーザであっても、フツーに実行できてしまうことにある。
今回ば、特権ユーザであるシータ(sheeta)と一般ユーザ(pazu)というユーザを作成したんだけど、

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 nuko      | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 pazu      |                                                            | {}
 sheeta    | Superuser, Create role, Create DB                          | {}

postgres=# 

特権ユーザ(王家の末裔)のsheetaがこの関数を実行できるのは仕方がない・・・が、一般人のpazuが実行できてしまうのは如何なものかｗ

CREATE FUNCTIONのデフォルトはSECURITY DINIER(関数を定義したユーザ権限で実行される)ではなく、SECURITY INVOKER(関数を実行したユーザ権限で実行される)なのだが、困ったことにSECIRITY INVOKER指定の関数であってもplshで定義したシェルの中での動作までは制限できるわけでもないので、フツーにPostgreSQLサーバを起動したOSユーザとしての権限で実行されてしまうわけで、あっさりとデータベースクラスタ全体を削除できてしまうのだ。危険極まりない。
PostgreSQLをセキュアに使おうと考えるシステムであれば、このEXTENSIONは導入させちゃダメだと思う。