Sep - 12th
MySQLのカラム複製脆弱性
Posted at 3:45 pm | Filed Under MySQL, PHP, PostgreSQL, Security, WordPress
昨日書いたMySQLの脆弱性ですが、手元の環境で見事に再現しました。MySQLのバージョンはCentOSでyumからインストールしたmysql-4.1.20-3.RHEL4.1.el4_6.1です。
手順は以下の通り。まずデータベース、テーブルを作成します。
mysql> create database sec_test; Query OK, 1 row affected (0.02 sec) mysql> use sec_test Database changed mysql> create table test (username char(16)); Query OK, 0 rows affected (0.01 sec)
ご覧の通り、char(16)でusernameというカラムを持つテーブルを作成します。
まず「admin」というusernameを持つ行を作ります。
mysql> insert into test (username) values ('admin');
Query OK, 1 row affected (0.00 sec)
mysql> select count(*) from test where username = 'admin';
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
出来ています。
次に、問い合わせの文字列部分の後ろに空白を追加してみます。全部で16文字になるようにしました。
mysql> select count(*) from test where username = 'admin '; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec)
後ろの空白が無視されているのがわかります。update:正確にいえば、char型なので指定された長さ未満の文字列は空白文字で埋められるみたいです。null文字とかじゃないんですね。
次に、char(16)の範囲を超えたところで「x」を追加して検索してみます。
mysql> select count(*) from test where username = 'admin x'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)
さすがにヒットしませんでしたが、そもそもカラムの制限長を超えているのにエラーにはなりません。
では、この値をINSERTしてみます。
mysql> insert into test (username) values ('admin x');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select count(*) from test where username = 'admin';
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.01 sec)
「admin」としてINSERT出来てしまいました。
これで、（１）既存のデータに重複行があるかチェック（２）なければINSERT、という処理で重複登録を防ごうとしてもダメなことがわかります。
ちなみにPostgreSQL 7.4.19でテストすると
db_test=# create table security_test (username char(16));
CREATE TABLE
db_test=# INSERT INTO security_test (username) VALUES ('admin');
INSERT 0 1
db_test=# SELECT COUNT(*) FROM security_test WHERE username = 'admin';
count
-------
1
(1 row)
db_test=# SELECT COUNT(*) FROM security_test WHERE username = 'admin ';
count
-------
1
(1 row)
db_test=# SELECT COUNT(*) FROM security_test WHERE username = 'admin x';
count
-------
0
(1 row)
db_test=# SELECT COUNT(*) FROM security_test WHERE username = 'admin ';
count
-------
1
(1 row)
db_test=# INSERT INTO security_test (username) VALUES ('admin x');
ERROR: value too long for type character(16)
db_test=# SELECT COUNT(*) FROM security_test WHERE username = 'admin';
count
-------
1
(1 row)
db_test=#
INSERTはできません。8.3.3でも。
db_test=# create table test (username char(16));
CREATE TABLE
db_test=# INSERT INTO test (username) VALUES ('admin');
INSERT 0 1
db_test=# SELECT count(*) FROM test WHERE username = 'admin';
count
-------
1
(1 row)
db_test=# SELECT count(*) FROM test WHERE username = 'admin ';
count
-------
1
(1 row)
db_test=# SELECT count(*) FROM test WHERE username = 'admin x';
count
-------
0
(1 row)
db_test=# SELECT count(*) FROM test WHERE username = 'admin ';
count
-------
1
(1 row)
db_test=# INSERT INTO test (username) VALUES ('admin x');
ERROR: value too long for type character(16)
INSERT時にあふれた分が勝手にカットされることはありませんでした。
Aug - 19th
PostgreSQLの長期間運用
Posted at 2:09 pm | Filed Under PostgreSQL
PostgreSQLの運用では、システム停止をともなう定期的なメンテナンスが必要とされている。
が、じゃあいったいどれくらい、何のためにシステム停止とVACUUM処理をやらなければいけないのかは、いまいちはっきりしたところはわからなかった。たとえば、データ領域が大きくなりすぎてディスク容量を圧迫しているとか、なんとなくパフォーマンスが低下しているとか、そんなタイミングで実施するようなアバウトな運用をしている人も結構いるのではないか。
マニュアルにも明記されているが、上に挙げた理由の他に、PostgreSQLのVACUUMにはもうひとつ重要な機能がある。といっても、通常のVACUUMやVACUUM FULLでは実行されず、psqlから「VACUUM F」まで入力してtabを押しても出てこないのでやっかいなのだが、長期間運用されるPostgreSQLではVACUUM FREEZEを定期的に実行してやる必要がある。
VACUUMというのは、PostgreSQLが削除フラグを立てて未使用となったデータ領域を回収してあげるもので、ANALYZEはプランナが利用する統計情報を更新することで利用効率を向上させるものという理解で基本的には間違いないようなのだが、このFREEZEというのは正直なところこれまでその存在も全く知らなかった。今日、たまたま隣席で7.3系のPostgreSQLでVACUUM作業中の人がログに変なメッセージを見つけたのがきっかけで発見した。
同じような警告メッセージがPostgreSQL7.2のChangelogにある。
NOTICE: Some databases have not been vacuumed in 1613770184 transactions.
Better vacuum them within 533713463 transactions,
or you may have a wraparound failure.
PostgreSQLのトランザクションには一意のID（XID）が振られていて、そのIDは32ビットのシステムでは40億くらい（2の32乗）が上限になっている。この上限値を超えると、トランザクションのIDはリセットされて0に戻ってしまう。数字が循環して0に戻ると、突然自分より前のトランザクションが存在しない状態になってしまい、逆に過去のトランザクションが未来のものに見えるようになるため、データ破壊を引き起こす。上のメッセージは、メンテナンス忘れなどでトランザクション数の累計が15億を超えるとVACUUM時に出てくるものだ。
VACUUM FREEZEはこの循環を回避するための機能で、現在のトランザクション以外のトランザクションにFrozenXIDというのを追加して、周回違いのIDを見間違わずに過去のものと判別できるようにする。何がなんだかわからないので、イメージしかわかないのだが、きっとPostgreSQLのトランザクションデータは構造体みたいなもので、そこにXIDというメンバ変数があって、そいつを更新してくれる、みたいなものなのだろう。
ちなみにVACUUM FREEZEはPostgreSQL 7.2から導入された機能で、上の説明もだいたいそれに合わせたものになっている。というのも、8.3のマニュアルではVACUUM FREEZEは廃止予定となっており、VACUUMの実行はpostgres.confのvacuum_freeze_min_ageの値を0に設定したものと同じことになる。つまり、VACUUMで代用できるわけだ。どうりでpsqlからもちゃんと出てこないわけだ。
PostgreSQLの7系で無停止運転が出来ないのは、こういう理由もあったからだ。
Aug - 6th
pgpool-IIのオンラインリカバリ機能と格闘中
Posted at 9:57 pm | Filed Under PostgreSQL, pgpool
いろいろ気づいたことのメモ。
まずは目玉機能のオンラインリカバリについて。プロジェクトのページによれば
1. CHECKPOINT 実行
2. ファーストステージの実施
3. 接続がすべて切断されるまで待機
4. CHECKPOINT 実行
5. セカンドステージの実施
6. postmaster の起動(pgpool_remote_start の実行)
7. ノードの復帰
という手順でリカバリされるのだが、手元の環境でまず引っかかったのが３の「接続がすべて切断されるまで待機」のところ。というのも、pgpool-IIにpgpoolを経由して接続するという変な環境だったのだが、child_life_timeを0に設定していたために接続がいつまで経っても切れずにここで失敗してしまったのだ。
で、pgpool-IIのソース（現時点でrev.1.21）を調べると（これは会社の同僚がやっていた）、まだ接続が切れていなければ3秒待ってから最大30回ループすることになっている。
/*
* Wait all connections are closed.
*/
static int wait_connection_closed(void)
{
int i = 0;
do {
if (Req_info->conn_counter == 0)
return 0;
if (WAIT_RETRY_COUNT != 0)
sleep(3);
} while (i++ < WAIT_RETRY_COUNT);
pool_error("wait_connection_closed: existing connections did not close in %d sec.", pool_config->recovery_timeout);
return 1;
}
なぜかしびれた。これはプログラム内に意味深な数字を見つけたときのプログラマの健康な反応なのだろう。まあ、根拠はよくわからないが、まあそういう数字であると。ふむふむ。
で、接続元がpgpoolだとコネクションを張りっぱなしになるのでこのループで終了してしまう。負荷の高いサービスだと、pgpool経由でpgpoolにつないだ状態では、child_life_timeを下げてもなかなかチャンスがこないかもしれない。というわけで現在ここを試験中。
接続の問題が解消されると、あっさり動作した。PostgreSQLは7系なのでrsyncで動作している。
Jul - 17th
Postgres-R
Posted at 8:15 pm | Filed Under PostgreSQL
Twitter経由で知ったのだが、Postgres-Rというレプリケーション用の拡張がある。面白そうなので概要を読んだ。以下のように説明されている。
Postgres-R概要
Postgres-Rクラスタの構造
Postgres-Rは高速に相互接続された「何も共有しない」クラスタ群として設計されています。そのためギガビットイーサネットで接続されたAMD Opteronマシンのようなありふれたハードウェアで構築することができるクラスタになっています。Postgres-Rノード間の通信は全てレプリケーショングループを提供するグループコミュニケーションシステム（GCS）を介して行われます。ひとつのデータベースが各グループにレプリケーションされることになります。
グループに追加された最初のノードはまずレプリケーションを開始し、最初のデータを提供します。その後で他のノードがグループからのレプリケーションを受け付けることができるようになります。各ノードは稼動状態になる前にリカバリシステムにより初期化されます。リカバリ中にのみリカバリ提供側とリカバリを受ける側のノードとの違いを認識することができますが、通常は全てのノードは同じ内容です。
Postgres-Rノードのコンポーネント
Postgres-Rの主なコンポーネントはレプリケーションマネージャです。これはPostgres本体とは別に追加されたプロセスで、主にメッセージの調整を行い、グループコミュニケーションシステムやトランザクションを実行するバックエンドとの接続を調整します。
Postgresにはトランザクションを扱うバックエンドのプロセスがあります。それぞれのバックエンドは一度にひとつのトランザクションしか扱うことができません。リモートのノードからのトランザクションをリプレーするために、レプリケーションマネージャはレプリケーションマネージャの管理下にありクライアントとの接続はない「リモートバックエンド」というものを開始します。それに対して、ローカルのトランザクションを扱っているバックエンドのプロセスたちは「ローカルバックエンド」と呼ばれ、クライアントと直に接続されています。
レプリケーションされたトランザクションのライフサイクル
Read-onlyのトランザクションはローカルで扱われ、通常のPostgresの単一ノードの操作と何ら変わらないものとみなされます。トランザクションがデータを書き込むと（UPDATE、INSERTまたはDELETEなどのSQLコマンド）、新しいデータがすぐに書き込みセットとして集められます。ローカルバックエンドはトランザクションを処理し続け、クライアントからコミットリクエストを受け取るまで書き込みセットに変更を集め続けます。
クライアントにコミットを返す前に、ローカルバックエンドは書き込みセットをレプリケーションマネージャに送ります。レプリケーションマネージャは順に並べられたグループコミュニケーションシステムのチャンネルを使ってそれを順々に全ての他のノードに送信します。トランザクションを開始したローカルバックエンドは、書き込みセットが戻されたところでコミットできるようになります。自分でもリプレイするように書き込みセットを受け取った他のノードでは、レプリケーションマネージャがリモートバックエンドのプロセスを開始して書き込みセットをそれに引き渡します。するとリモートバックエンドは書き込みセットにあるデータからトランザクションを自分でもリプレイします。
衝突の扱い
Postgres-Rでは書き込みトランザクションはシリアライズされて元と同じ順序でレプリケーションクラスタを共有する各ノードに配信されます。これによりノード間の同期と一貫性が保証されます。トランザクションがあるノードに正常にコミットされたならば、他の全てのノードにも同じくコミットされます。そのため、他のノードの処理を待つことなくそれぞれのノードがフルスピードで稼動することが可能になります。ネットワークのトラヒックを可能な限り抑えるためにトランザクションの変更だけが転送されます。
この手法はシリアライズ可能なPostgresのトランザクションレベルで最大の効果を発揮します。READ COMMITTEDモードでは通常の単一ノードの運用時と同じトランザクションセマンティクスを提供するためにロックもレプリケーションされます。そのためレプリケーションするデータベースシステムのほとんどでボトルネックとなるであろうネットワークトラヒックの増大を引き起こします。
というわけで、実際の動作をさせてみないとなんともいえないが、面白そうな存在なのでちょくちょくチェックする。
Jul - 1st
これはよい - 「達人に学ぶSQL徹底指南書」
Posted at 2:47 am | Filed Under PostgreSQL
“達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)” (ミック)
面白い。役に立つ。というか、もう何年ウェブアプリケーション作ってきたかわからないけど、正直WHERE句にCASE文を書いたりしたことはなかった。DBエンジニアからみれば、こんなの初級者なんだろうな。集合論とか、TRUEとFALSEに加えて第３の真理値があるなんて、概念としては知っていても考えたこともなかった。
サポートページが、もはやそれ自体が読み物となっている。
Jun - 30th
PostgreSQL8.3系への移行は悩み多き道
Posted at 5:10 pm | Filed Under PostgreSQL
PostgreSQLの7.4系から一気に8.3系に移行すると大変なことになると知った。
例えばこんなテーブルとデータを用意する。
$ cat ./test_db.sql
CREATE TABLE test_table (
datetime timestamp without time zone NOT NULL DEFAULT '2008-01-01 00:00:00'
);
INSERT INTO test_table (datetime) VALUES ('2008-06-30 01:23:45');
これを7.4系（5432ポートで起動）と8.3系（5433ポートで起動）のPostgreSQLに流し込む。
$ psql -Upostgres -p 5433 -f ./test_db.sql db_test $ psql -Upostgres -p 5432 -f ./test_db.sql db_test
何が問題かというと、例えば次のようなSQLを発行した場合
$ psql -Upostgres -p 5432 -c "SELECT SUBSTR(datetime, 1, 4) FROM test_table" db_test substr -------- 2008 (1 row)
7.4系ではSUBSTRのような文字列型データを扱う関数を利用した場合、値が自動的にキャストされていたのだが、
psql -Upostgres -p 5433 -c "SELECT SUBSTR(datetime, 1, 4) FROM test_table" db_test ERROR: function substr(timestamp without time zone, integer, integer) does not exist at character 8 HINT: No function matches the given name and argument types. You might need to add explicit type casts. STATEMENT: SELECT substr(datetime, 1, 4) FROM test_table ERROR: function substr(timestamp without time zone, integer, integer) does not exist at character 8 HINT: No function matches the given name and argument types. You might need to add explicit type casts.
8.3系ではパフォーマンスを稼ぐためか、この自動キャストがなくなっている。もちろん
$ psql -Upostgres -p 5433 -c "SELECT SUBSTR(datetime::text, 1, 4) FROM test_table" db_test substr -------- 2008 (1 row)
明示的にキャストすれば問題ないのだが、移行する全てのアプリケーションのSQLをチェックしなおす必要がある。他にも、日付のカラムをLIKE検索している場合などにも同じようなことになる。
$ psql -Upostgres -p 5433 -c "SELECT * FROM test_table WHERE datetime LIKE '%0%'" db_test
ERROR: operator does not exist: timestamp without time zone ~~ unknown at character 42
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
STATEMENT: SELECT * FROM test_table WHERE datetime LIKE '%0%'
ERROR: operator does not exist: timestamp without time zone ~~ unknown at character 42
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
$ psql -Upostgres -p 5433 -c "SELECT * FROM test_table WHERE datetime::text LIKE '%0%'" db_test
datetime
---------------------
2008-06-30 01:23:45
(1 row)
たしかに、型を正しく理解してSQLを書くのが大事なわけだが、今さらこの挙動は困る。
Jun - 6th
PostgreSQLカンファレンス2008
Posted at 8:04 pm | Filed Under PGCluster, PostgreSQL, pgpool
というわけで六本木一丁目まで出かけた。
pgpoolのセッションは大盛況。PostgreSQLである程度の規模のシステムを構築するのにはみんな苦労しているという現実を思い知らされる。内容は、これまで何度か見てきたpgpoolの説明とほとんど変わらなかったが、HeartBeatを利用してpgpoolのところを冗長化する構成が出ていたのがこれまでになかったところ。でもその話題はあまり盛り上がっていなかった。
大規模システムを構築する場合は、パラレルクエリを利用するのはあまりよろしくないな。1個のクラスタが壊れたらどうやって復旧していいのかわからないし、そこをさらにpgpoolで冗長化するのも冗談みたいになって手間がかかってしまう。やはりpgpoolによる同期処理とロードバランシングか、あるいはpgpoolのロードバランシングとSlony-Iによる非同期処理の組み合わせになるだろうか。ただ、後者だと更新系クエリの結果を受けてSELECTを発行しているようなありがちな箇所を変更するなど既存のアプリケーションに手を入れるところが多そうなので、やはりpgpoolで全てまかなえるようになるのが理想的だ。
とはいえ、pgpoolのプロセスそのものを冗長化してSingle point of failureをなくすためにはもっと工夫しないといけない。pgpool自体は同じ設定であちこちに立ててあげれば使えるのだが、ウェブアプリケーション側から接続先を動的に切り替えるのは無理があるので、やはりHeartBeatで監視してアプリケーション側からは接続先の切り替えは見えないようにしてあげるのがいいと思われる。
PostGISは前々から興味津々だったのだが、デモが見にくくてこまった。それから、Functionとして利用する機能の説明が駆け足すぎてきちんと把握できず、聴衆がみんな置いてけぼりだったのが難点か。PostGISの単なる紹介なのか、導入事例の紹介なのか、もうちょっと焦点を絞ってあればよかったかも。個人的には面白かったのだが。
全文検索機能については会場の雰囲気からも高い関心がもたれているのが感じられた。しかし、tsearch2（や日本語を扱えるtsearch2の拡張）の実装には関わっていない上にテキスト検索に詳しいわけでもない人がざっと概要を説明するだけのもので、これじゃやっぱ使えないな、という失望感が会場に広がっていた。実際、全文検索はもはや形態素解析かN-Gramかという次元のようなものではなくて、利用局面に応じてどんな順位付けをするのか（できるのか）、どんな実装ができるのかといったもう少しデータ寄りに踏み込んだ内容が求められているはずだ。そのあたりは、LudiaやRAST、Hyper Estraierのような実装の方が相当先に進んでしまっていて、正直なところ今更な感じ。
PGClusterのセッションも、pgpoolと同様に高い関心を集めているようだった。とはいえ、作者の海外赴任にともない活動拠点がヨーロッパになったこと、管理機能を含めまだ未完成の部分があること、身近な導入実績のなさから今では完全にpgpoolに立ち遅れてしまっている感は否めない。だが、PostgreSQLの負荷分散だけでなくoidやLargeObject、Serial値、乱数、時間関数の結果まで同期してしまうという高機能さと、徹底してSingle point of failureをなくそうという姿勢には、従来から高い期待を寄せられていたのも事実だ。これはメインの開発者の気質にもよるのだろうが、実際的な利用を想定してとにかくリリースしてしまうより、実装や構成についてもっと深く突き詰めようとして完成に至らず、実際に利用されるよりも学究的なプロジェクトの様相を呈しているのも残念だ。しかし、開発自体は進んでおり、また開発者も非常に高い理想を掲げているため、今後大きな成果をあげることもあるかもしれない。まあ、カンファレンスの段階であれば、実運用されている環境への導入には躊躇せざるをえないが。
Dec - 22nd
ludia1.4
Posted at 11:14 pm | Filed Under PostgreSQL
Aug - 28th
PowerGres on Windows
Posted at 1:01 am | Filed Under PHP, PostgreSQL
SRAがPowerGresのセミナーを開催する。
ところで、ゼンド・ジャパンがSRAと「PowerGres on Windows」を52,500円で販売するそうな。曰く：
「本番環境はLinux、開発環境はWindows」というケースでは、開発者がWindows環境で開発環境を構築するのに煩雑な設定作業が多く、開発効率の低下を招く一因でもあった。本製品の導入によって、Windows環境でLAPP（Linux、Apache、PostgreSQL、PHP）開発を行う開発者のシステム構築の負担を軽減し、技術サポートにより開発効率を向上できる
とのことだが、ウェブアプリケーションの開発をWindows上でやる必然性はないし、いまいちよくわからない。開発会社なら、プログラマに無理矢理Windowsを使わせなくても、開発環境くらいLinuxでもMac OS Xでもいいから*nix上に勝手に構築させてあげればいいじゃないか。動作確認にWindowsが必要なら、Mac OS XでParallelsで十分だし、VMWareでもなんでも選択肢はあるだろうに。
Jun - 4th
PostgreSQLカンファレンス2007
Posted at 10:58 am | Filed Under PostgreSQL
会社の人に参加するように話しておいたのだが、忘れているようだ。そんなやる気のない奴に無理矢理参加させるなんてもったいない。是非自分で行きたいと思うのだが、今から間に合うのだろうか。
keep looking »