Postfix から MySQL を使う

これは Postfix Advent Calendar 2014 の15日めの記事です。

ルックアップテーブル

Postfix のルックアップテーブルは Linux だと通常は hash 形式のファイルですが、ファイルの代わりに MySQL, PostgreSQL, LDAP 等を参照することができます。

どの形式が使えるかは postconf -m コマンドで使用できる形式の一覧を見ることができます。Ubuntu だと次のようになってます。

% postconf -m
btree
cidr
environ
fail
hash
internal
memcache
nis
proxy
regexp
sdbm
socketmap
sqlite
static
tcp
texthash
unix

Ubuntu では deb で対応形式を追加できるようになっています。

% sudo apt-get install postfix-cdb postfix-ldap postfix-mysql postfix-pcre postfix-pgsql
...
% postconf -m
btree
cdb
cidr
environ
fail
hash
internal
ldap
memcache
mysql
nis
pcre
pgsql
proxy
regexp
sdbm
socketmap
sqlite
static
tcp
texthash
unix

あとから追加できるのは Ubuntu(Debian?)の独自拡張で、通常はコンパイル時に指定したものしか使えません。 CentOS の場合は mysql はありますが、pgsql(PostgreSQL) はありません。Postfix をコンパイルし直さない限り、あとで追加することもできません。

% postconf -m
btree
cidr
environ
fail
hash
internal
ldap
memcache
mysql
nis
pcre
proxy
regexp
socketmap
static
tcp
texthash
unix

MySQL

ルックアップテーブルは、ある値(キー)を与えるとそれに対応する値を返すテーブルで、*_maps という名前のパラメータや、smtpd_*_restrictions に指定するアクセス制御で使用されます。

たとえば alias_maps パラメータで MySQL を参照したい場合は次のように指定します。

[main.cf]

alias_maps = mysql:/etc/postfix/alias.mysql

/etc/postfix/alias.mysql ファイルには MySQL 接続用の情報とクエリを記述します。

[alias.mysql]

hosts = mysql_server
user = mysql_user
password = user_password
dbname = some_db
query = select result from alias_table where name='%s'

この例では、検索キーが alias_table の name カラムに一致したら result カラムの値を返します。

%s 内の '" はちゃんとエスケープされるので、SQL インジェクションについては気にしなくても大丈夫です。

クエリが複数カラムや複数レコードを返す場合は、, で結合されて返ります。

my.cnf

Postfix 2.11 から my.cnf や他のファイルを読むことができるようになりました。

option_group = group
option_file = /etc/postfix/mysql.cnf

option_file は my.cnf ファイルの代わりに読み込むファイル名を指定します。 option_group は cnf ファイル中のグループを指定します。

option_fileoption_group も指定されない場合は、my.cnf も読み込まれません。

これを使用することで、default-character-setinit-command 等の MySQL の色んなオプションが指定できるようになります。

charset

Postfix から MySQL への接続の charset は Ubuntu ではデフォルトは latin1 になっています。

試してみます。

[/tmp/hoge.mysql]

hosts = localhost
user = test
password = abcdefg
dbname = test
query = show variables like 'character_set_connection'
% postmap -q hoge mysql:/tmp/hoge.mysql
character_set_connection,latin1

上述したように Postfix 2.11 では my.cnf を読ませることができるので、my.cnf で default-character-set を設定すればそれに従います。

[/tmp/hoge.mysql]

hosts = localhost
user = test
password = abcdefg
dbname = test
query = show variables like 'character_set_connection'
option_group = hoge

[/etc/mysql/my.cnf]

[hoge]
default-character-set = utf8mb4
% postmap -q hoge mysql:/tmp/hoge.mysql
character_set_connection,utf8mb4

Postfix 2.11 未満では my.cnf を読むことができないので、charset を指定することができません。

接続用の charset がカラムの charset と異なる場合、クエリがエラーになることがあります。

たとえば、ascii のカラムにメールアドレスが格納されている場合、接続が latin1 だと 8bit 文字をクエリに渡すとエラーになります。

[/tmp/virtual_alias.mysql] *1

hosts = 127.0.0.1
user = test
password = abcdefg
dbname = test
query = select address from alias where alias='%s'

[main.cf]

virtual_alias_maps = mysql:/tmp/virtual_alias.mysql

[SMTP]

MAIL FROM:<hoge>
250 2.1.0 Ok
RCPT TO:<ほげ>
rcpt to:<ほげ@example.com>
451 4.3.0 <      @example.com>: Temporary lookup failure

[mail.log]

Dec 15 02:51:38 x220 postfix/smtpd[9860]: connect from localhost[127.0.0.1]
Dec 15 02:51:48 x220 postfix/smtpd[9860]: warning: mysql query failed: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='
Dec 15 02:51:48 x220 postfix/smtpd[9860]: warning: mysql:/tmp/virtual_alias.mysql lookup error for "??????@example.com"
Dec 15 02:51:48 x220 postfix/smtpd[9860]: NOQUEUE: reject: RCPT from localhost[127.0.0.1]: 451 4.3.0 <      @example.com>: Temporary lookup failure; from=<hoge> to=<??????@example.com> proto=SMTP
Dec 15 02:51:52 x220 postfix/smtpd[9860]: disconnect from localhost[127.0.0.1]

Postfix はメールアドレスのローカルパートに 8bit 文字があってもエラーにはせず、そのまま処理を行おうとします*2。そのため ascii カラムと latin1 リテラルを比較しようとして MySQL エラーになってしまいます。

クエリを次のように変更すると、リテラルを強制的に ascii とみなすようになるため、MySQL エラーにはなりません。

[/tmp/virtual_alias.mysql]

query = select address from alias where alias=_ascii'%s'

[SMTP]

RCPT TO:<ほげ@example.com>
550 5.1.1 <      @example.com>: Recipient address rejected: example.com

[mail.log]

Dec 15 02:53:13 x220 postfix/smtpd[10080]: connect from localhost[127.0.0.1]
Dec 15 02:53:21 x220 postfix/smtpd[10080]: NOQUEUE: reject: RCPT from localhost[127.0.0.1]: 550 5.1.1 <      @example.com>: Recipient address rejected: example.com; from=<hoge> to=<??????@example.com> proto=SMTP
Dec 15 02:53:23 x220 postfix/smtpd[10080]: disconnect from localhost[127.0.0.1]

一旦 Temporary lookup failure のエラーになると、しばらくエラーの状態が記憶されてしまうため、その後のクエリもエラーになってしまいます。

MySQL を使用する場合には注意しましょう。

*1:Ubuntu では Postfix のデーモンは chroot 下で動作するため、MySQL の socket ファイルが見れないので、localhost ではなく 127.0.0.1 を指定して TCP 接続を使用するようにしています。

*2:ドメインパートに 8bit 文字があると SMTP エラーになります。

7bit と 8bit の狭間で

これは Postfix Advent Calendar 2014 の6日目の記事です。

その昔、電子メールは 7bit データでした。

日本語は ASCII の範囲におさまらないのですが、ISO-2022-JP*1にエンコードすることで 7bit になるので、日本語でメールする人たちはそのようにしてました。今でも日本語を扱うメールアプリのデフォルトのエンコーディングは ISO-2022-JP になってることが多いと思います。

ただしヘッダの From や To フィールドには規格上 ISO-2022-JP は書けないので、メールアドレスの表示名には日本語は使えませんでした。

余談ですが、メール本文の冒頭で自分の名前を名乗る日本の風習は From に日本語で名前が書けなかったためじゃないかと、個人的に妄想してます。

バイナリデータは uuencode 等でテキストに変換して、メール本文に貼り付けて送ったものでした。

その後 MIME という規格ができて、メールヘッダにも ASCII 以外の文字列を記述できたり、メールにファイルを添付することができるようになり、便利に使えるようになりました。*2

MIME では本文中の 8bit 文字を ISO-2022-JP のような 7bit に変換しなくてもそのまま記述できます。ただし、ヘッダに Content-Transfer-Encoding: 8bit の記述が必要です。*3

UTF-8 のテキストをそのまま埋め込んだメールデータは次のようになります。あいかわらずヘッダ中では 8bit 文字は書けないので、Subject はエンコーディングしています(この例では「テスト」)。

Subject: =?utf-8?b?44OG44K544OI?=
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit

本日は晴天なり

8bit メールは新しい規格なので(と言っても20年くらい前ですが)、相手がちゃんと処理できるかどうかわかりません。

SMTP の EHLO 命令に対する応答に 8BITMIME が含まれてれば対応しています。Postfix は対応しています。

EHLO hoge
250-x220
250-PIPELINING
250-SIZE 10240000
250-VRFY
250-ETRN
250-STARTTLS
250-ENHANCEDSTATUSCODES
250-8BITMIME              ★
250 DSN

Postfix が 8bit メールを配送する時に相手が対応していない場合はどのようになるのか試してみます。

master.cf を次のようにします。

smtp      inet  n       -       -       -       -       smtpd
 -o content_filter=smtp:localhost:10025
10025     inet  n       -       -       -       -       smtpd
 -o smtpd_discard_ehlo_keywords=8BITMIME

25番ポートでうけつけたメールを 10025番ポートに中継しますが、10025番ポートの Postfix は EHLO に 8BITMIME を返しません。

この状態で 25番ポートに先ほどのメールを送信すると、最終的に届いた内容は次のようになりました。

Subject: =?utf-8?b?44OG44K544OI?=
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable                         ★

=E6=9C=AC=E6=97=A5=E3=81=AF=E6=99=B4=E5=A4=A9=E3=81=AA=E3=82=8A     ★

10025番ポートで動いている MTA が 8BITMIME を返さなかったので、25番ポートの Postfix が本文部のエンコーディングを 8bit から quoted-printable に変換して送信しました。

テキストを添付した次のメールを送ると、

Subject: txt attached
Content-Type: multipart/mixed; boundary=123456789

--123456789
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

ASCII BODY
--123456789
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit

本日は晴天なり
--123456789--

該当パートだけ quoted-printable になります。

Subject: txt attached
Content-Type: multipart/mixed; boundary=123456789

--123456789
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

ASCII BODY
--123456789
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable                         ★

=E6=9C=AC=E6=97=A5=E3=81=AF=E6=99=B4=E5=A4=A9=E3=81=AA=E3=82=8A     ★
--123456789--

メールを添付した次のメールを送ると、

Subject: eml attached
Content-Type: multipart/mixed; boundary=123456789

--123456789
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

ASCII BODY
--123456789
Content-Type: message/rfc822
Content-Transfer-Encoding: 8bit

Subject: =?utf-8?b?44OG44K544OI?=
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit

本日は晴天なり
--123456789--

添付パートの Content-Transport-Type と、添付されたメールの本文が変換されます。

Subject: eml attached
Content-Type: multipart/mixed; boundary=123456789

--123456789
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

ASCII BODY
--123456789
Content-Type: message/rfc822
Content-Transfer-Encoding: 7bit                                       ★

Subject: =?utf-8?b?44OG44K544OI?=
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable                           ★

=E6=9C=AC=E6=97=A5=E3=81=AF=E6=99=B4=E5=A4=A9=E3=81=AA=E3=82=8A       ★
--123456789--

添付されたメールを丸ごと quoted-printable にしないのは、Content-Type: message/rfc822 に指定できる Content-Transfer-Encoding は 7bit, 8bit,binary のいずれかだと決められているためです。

このように Postfix は配送先が 8bit メールを受け取れるかどうかによってメールの中身を変換することがありますが、main.cf に disable_mime_output_conversion = yes を設定するとこの 8bit → quoted-printable 変換を行いません。 相手が EHLO に 8BITMIME を返さなくても無理やり 8bit のまま送りつけます。

また、メールを受け取るときに Content-Transfer-Encoding: 8bit がないのに 8bit 文字が入っていた時にエラーにすることもできます。mian.cf に strict_8bitmime_body = yes を設定します。

この場合、DATA 命令の応答として次のようなエラーを返します。

550 5.6.0 improper use of 8-bit data in message body

この時ログには次のように記録されます。

postfix/cleanup[5085]: 7F3813A6: reject: mime-error improper use of 8-bit data in message body: ????????????????????? from localhost[127.0.0.1]; from=<sender@example.com> to=<rcpt@example.com>

*1:昔は「いわゆるJISコード」とか言われてました。

*2:便利になった分複雑になり、SMTP(Simple Mail Transfer Protocol)のどこがシンプルやねん!と言いたくなることもしばしばですが…。

*3:8bit をそのまま書けると言ってもテキストである必要があります。SMTP は行指向なので、行の区切りがないバイナリデータはやはりそのまま書くことはできません。

Postfix の拡張メールアドレス

これは Postfix Advent Calendar 2014 の4日目の記事です。

Postfix では拡張メールアドレスを使うことができます。

tmtms@eample.com というメールアドレスがあった場合、tmtms+ext@example.com というメールアドレスも自動的に有効になります。ext 部分はなんでも構いません。

サービス毎に異なるメールアドレスを登録したい場合にいちいち aliases 等でメールアドレスを新たに作成する必要はありません。

Gmail でも使えるようですが自分は Gmail 使ってないので詳しくは知りません。元々の発祥は qmail だと思います。

[追記] どうやら qmail 以前に Sendmail でも使えたようです。

拡張メールアドレスを使うには、recipient_delimiter パラメータに区切り文字を設定します。 上のように tmtms+ext@example.com としたい場合は、recipient_delimiter = + とします。

この前 Postfix の最近のパラメータについて調べたときに気がついたのですが、Postfix 2.11 から recipient_delimiter に複数文字を登録できるようになってました。

recipient_delimiter = +- と設定すると、tmtms+ext@example.comtmtms-ext@example.com の両方が拡張アドレスとして使えるようになります。

ときどきメールアドレスに + 文字が使えないダメなサイトがあるので、その時に代わりに - を使ったりすることができて便利ですね。

RFC5322 的にローカルパートで普通に使える文字は次のとおりです。これらの文字以外は recipient_delimiter には指定しない方がいいと思います。

ASCII英数字と ! # $ % & ' * + - / = ? ^ _ ` { | } ~

特定の拡張メールアドレス宛にきたメールだけ特別な処理をしたい場合は、$HOME/.forward+ext として forward ファイルを書いておくとそれが使用されます。

たとえば、あるサービスに tmtms+hoge@example.com として登録した後、そのメールアドレスに迷惑メールが送られてきてしまうという場合は、$HOME/.forward+hoge ファイルに /dev/null と書いておくとメールを見なくてすみます。

Postfix の main.cf のフォーマットについて

これは Postfix Advent Calendar 2014 の2日目の記事です。

Postfix が登場する以前、MTA と言えば Sendmail でした。Sendmail の設定ファイル sendmail.cf は人間が読むことも書くことも難しくて、設定ファイルを簡単に書くためのツールがいくつかあるくらいでした。それに比べたら Postfix の設定ファイルはかなり簡単です。

Postfix の重要な設定ファイルは主に2つあります。master.cf と main.cf です。

今回は main.cf のフォーマットについて詳しく書いてみます。

基本形式

基本は次の形式です。簡単です。

パラメータ名 = 値

「=」の前後の空白はあってもなくても構いません。また行末の空白文字は無視されます。

コメント

#」で始まる行はコメントです。

# コメント

#」は必ず行頭になければなりません。次のように書いてもパラメータの値の一部として扱われます。

パラメータ名 = 値 # コメント

空白

空白は書いたとおりにパラメータの値となります。ただし、先頭と末尾の空白は除去されます。

値に連続した空白を含む場合、postconf コマンドの出力では一つの空白として見えますが、実際には複数の空白がそのまま維持されています。

# grep smtpd_banner /etc/postfix/main.cf
smtpd_banner = a  b   c                    ← 連続した空白を含む値を指定
# postconf smtpd_banner
smtpd_banner = a b c                       ← postconf の出力では1個になっているが
# telnet localhost 25
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
220 a  b   c                               ← 設定した通りの値が使用される

複数の値を取るパラメータの場合は空白はカンマ(,)と共に区切り記号として扱われます。 値の間の空白はいくつ連続していてもひとつの区切りとして扱われます。

継続行

空白で始まる行は直前の行からの継続行として扱われます。

パラメータ名 =
 値1
  値2
   値3

改行は無視されますが、空白はそのまま残ります。ですので上の例だとパラメータ値は「値1 値2 値3」となります。

また、継続行の途中にコメント行を入れることもできます。

パラメータ名 =
  値1
# 値2
  値3

これは、「値1 値3」として扱われます。

パラメータ値の展開

パラメータの値の中で他のパラメータの値を展開することができます。

param1 = value
param2 = $param1

main.cf 中の記述順には影響しません。次のように書いても同じ結果になります。

param2 = $param1
param1 = value

パラメータ値を展開するための記述方法は「$param」、「${param}」、「$(param)」です。直後に別の文字が続くような場合は括弧つきの表記を使うのがよいでしょう。

$」をそのまま「$」として扱いたい場合は「$$」と記述します。

${param?value}」は param の値が空でない場合に value の値になります。param が空の場合は空のままです。

${param:value}」は param の値が空の場合に value の値になります。param が空でない場合は空になります。

括弧は {} でも () でも構いません。

value 部分でさらに他のパラメータを展開することができます。

param = ${param1?${param2:hoge}}

これは param1 が空でなく param2 が空の場合に param の値が「hoge」になり、それ以外の場合は空の値になります。

param に値がある時と空の時でそれぞれ異なる値を設定したい場合は次のようにするのがよいでしょう。

パラメータ名 = ${param?abc}${param:xyz}

ユーザー定義パラメータ

パラメータは Postfix であらかじめ定義されたもの以外に、自分で定義することもできます。

当然ですが直接 Postfix の動きに作用することはできません。できるのは、他のパラメータの値の中に展開することくらいです。

パラメータ名は ASCII 英数字と「_」です。数字だけのパラメータ名でも特に問題ないようです。

ユーザー定義パラメータを main.cf に記述して、そのパラメータがどこからも参照されない場合は、warning が出力されます。

# postconf -e 'hoge = abc'
# postfix reload
/usr/sbin/postconf: warning: /etc/postfix/main.cf: unused parameter: hoge=abc
postfix/postfix-script: refreshing the Postfix mail system

Postfix の設定ファイルの記述は簡単なのですけど、詳しく見てみたら何か新しい発見があるかと思って調べてみました。自分にとっては連続した空白の扱いが新たな発見でした。みなさんも何か新しい発見があったでしょうか。

Postfixのパラメータ

Postfix 2.9 がリリースされたころに「Postfixのパラメータ」という記事を書きましたが、それ以降の変更について書いてみます。

新しいパラメータ、全然しらない…。

Postfix 2.9.11

パラメータ数 701

Postfix 2.10.5

パラメータ数 709

追加

postscreen_upstream_proxy_protocol
postscreen_upstream_proxy_timeout
process_id
process_name
smtpd_log_access_permit_actions
smtpd_relay_restrictions
smtpd_upstream_proxy_protocol
smtpd_upstream_proxy_timeout

変更

proxy_read_maps               デフォルト値に $smtpd_sender_login_maps 追加
smtpd_recipient_restrictions  デフォルト値が空に変更

Postfix 2.11.3

パラメータ数 725

追加

lmdb_map_size
lmtp_connection_reuse_count_limit
lmtp_dns_support_level
lmtp_tls_force_insecure_host_tlsa_lookup
lmtp_tls_trust_anchor_file
postscreen_dnsbl_whitelist_threshold
smtp_connection_reuse_count_limit
smtp_dns_support_level
smtp_tls_force_insecure_host_tlsa_lookup
smtp_tls_trust_anchor_file
smtpd_sasl_service
tls_dane_digest_agility
tls_dane_digests
tls_dane_trust_anchor_digest_enable
tls_ssl_options
tls_wildcard_matches_multiple_labels
tlsmgr_service_name

削除

tlsproxy_tls_session_cache_timeout

変更

lmtp_tls_session_cache_timeout     最大値が無制限から 8640000 に変更
recipient_delimiter                最大長が1から無制限に変更
smtp_tls_session_cache_timeout     最大値が無制限から 8640000 に変更
smtpd_tls_session_cache_timeout    最大値が無制限から 8640000 に変更

Postfix 辞典 (DESKTOP REFERENCE)

Postfix 辞典 (DESKTOP REFERENCE)

MySQLユーザーがPostgreSQLを触ってみたメモ

最近なぜか MySQL を使う Ruby アプリを PostgreSQL に対応する羽目になっているのですが、今までほとんど MySQL 以外の RDBMS を触ってなかったので、色々ハマったりしたのでメモっときます。

なお PostgreSQL 歴が浅いので間違ってること書いてるかもしれません。

API

プログラムから MySQL にアクセスするには Ruby/MySQL を使っていたのですが、PostgreSQL 用の API を新たに覚えるのは面倒だったので、Sequel を使って書き直しました。

mysql.query("select col1, col2 from table where col3='xxx'")
↓
db[:table].where(col3: 'xxx').select(:col1, :col2)

…みたいな感じです。

今までプログラム中に突然 SQL が現れていて読みにくかったのが、Ruby プログラムとして読みやすくなるという効果もありました。

Sequel については前に記事を書いたので興味があれば見てください。

ユーザーとデータベース

MySQL はデータベースを指定せずにサーバーに接続することができます。その場合データベース未選択状態になります。接続後に use を使用して自由にデータベースを切り替えることができます。また、カレントのデータベースとは異なるデータベースのテーブルを指定することもできます。

PostgreSQL の場合はデータベースを指定しないで接続することはできません。psql コマンドはデータベースを省略すると、ユーザー名と同じ名前のデータベースに接続しようとします。また、接続後はデータベースを変更することはできません。psql の \c を使えばデータベースを変更できますが、これは内部的には新しいデータベースに接続して元のデータベースとの接続を切断しています。

Ubuntu の場合は postgres という名前のデータベースが最初から用意されています。 誰でも使用できるので MySQL の test データベースのようなものでしょうか。

ユーザー名と認証

MySQL では UNIX ドメインソケット経由での接続であってもパスワードは必要です。パスワードを設定しないこともできますが、その場合は誰でもそのユーザーでログインできてしまいます。また OS のユーザー名と MySQL 上のユーザー名はほとんど関係ありません。mysql コマンドでユーザー名省略時に OS のユーザー名が使用される程度です。

PostgreSQL の場合は UNIX ドメインソケット経由の場合は、OS のユーザー名と PostgreSQL 上のユーザー名が一致していればパスワードを聞かれません。また OS のユーザー名と異なるユーザー名を指定しても接続できません。ネットワーク経由の場合はユーザー名とパスワードが必要です。

Ubuntu の場合はあらかじめ postgres というユーザーがスーパーユーザーとして用意されています。MySQL の root と同じようなもんだと思います。

認証まわりの設定は pg_hba.conf ファイルで行います。どのクライアントからの接続で、どの認証方式を使用するかを指定することができます。

UNIX ドメインソケットの場合にパスワードを聞かれないのは、pg_hba.conf ファイル中に次の行があるためです。

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer

ユーザー作成

PostgreSQL に接続するユーザーを作るには、PostgreSQL のスーパーユーザーで createuser コマンドを実行します。

Ubuntu の場合は postgres ユーザーがスーパーユーザーです。

% sudo -u postgres -i
[sudo] password for tommy: 
postgres$ createuser -P hoge
Enter password for new role: 
Enter it again: 

この例では hoge ユーザーをパスワードつきで作成しています。 localhost からしか使用しない場合はパスワードをつけなくてもいいと思います。

データベース作成

PostgreSQL のスーパーユーザーで createdb コマンドを実行します。

postgres$ createdb hoge

接続

クライアントからの接続は MySQL に比べると PostgreSQL は重いです。

MySQL でコネクションプールを使わずに接続切断を繰り返してもなんとかなっていた場合でも、PostgreSQL ではコネクションプールを使わないといけなくなるかもしれません。

MySQL は1プロセスで動作し接続毎にスレッドを生成するのに対し、PostgreSQL は接続毎にプロセスを生成するためだと思います。

調べてませんが、もしかすると認証プロトコル自体の処理も関係あるのかもしれません。

自動変換等

MySQL は型が一致しなくてもテキトーに変換して処理してくれるのですが(かなり余計なお世話)、PostgreSQL は厳密に型をチェックするので、MySQL でエラーにならなかったクエリがエラーになることがあります。

以下は MySQL ではエラーになりませんが、PostgreSQL でエラーになる例です。

  • 数値カラムへの文字列の登録
hoge=> insert into t (i) values ('hoge');
ERROR:  invalid input syntax for integer: "hoge"
LINE 1: insert into t (i) values ('hoge');
                                  ^
  • 数値カラムと文字列の比較
hoge=> select * from t where i='hoge';
ERROR:  invalid input syntax for integer: "hoge"
LINE 1: select * from t where i='hoge';
                                ^
  • 文字列カラムと数値の比較
hoge=> select * from t where s=123;
ERROR:  operator does not exist: character = integer
LINE 1: select * from t where s=123;
                               ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
  • INSERT 時にデフォルト値を持たない NOT NULL カラムを省略
hoge=> insert into t (col1) values (123);
ERROR:  null value in column "col2" violates not-null constraint
DETAIL:  Failing row contains (123, null).
  • SELECT に指定していないカラムを GROUP BY に指定
hoge=> select col1 from t group by col2;
ERROR:  column "t.col1" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select col1 from t group by col2;
               ^
  • CHAR カラムにサイズ超過文字列を登録
hoge=> insert into t (col1) values ('0123456789a');
ERROR:  value too long for type character(10)
  • DATE カラムに 0000-00-00 を登録
hoge=> insert into t (d) values ('0000-00-00');
ERROR:  date/time field value out of range: "0000-00-00"
LINE 1: insert into t (d) values ('0000-00-00');
                                  ^
  • DATE カラムに不正な日付を登録
hoge=> insert into t (d) values ('2014-10-32');
ERROR:  date/time field value out of range: "2014-10-32"
LINE 1: insert into t (d) values ('2014-10-32');
                                  ^
  • 0 除算
hoge=> select 1/0;
ERROR:  division by zero

MySQL でも sql_mode を設定することで、これらのうちのいくつかをエラーにすることができます。

mysql> set sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY';

なお、ERROR_FOR_DIVISION_BY_ZERO を指定することで、UPDATE,INSERT 時のゼロ除算はエラーになりますが、SELECT の時はエラーではなくワーニングになります。

UNSIGNED

MySQL の整数型には UNSIGNED を指定することができますが、PostgreSQL には UNSIGNED 型はありません。

大文字小文字

MySQL の文字列型はデフォルトでは大文字小文字を区別しませんが、PostgreSQL は大文字小文字を区別します。

PostgreSQL で大文字小文字を区別しないようにするには、citext 拡張をデータベースに導入して、文字列型カラム作成時に char, varchar ではなく citext を使うのが良いようです。

hoge=> create table t (s varchar);
CREATE TABLE
hoge=> insert into t (s) values ('abc');
INSERT 0 1
hoge=> select * from t where s='ABC';
 s 
---
(0 rows)

citext 拡張を導入するにはスーパーユーザーでないとできません。

hoge=# create extension citext;
CREATE EXTENSION
hoge=> create table t (s citext);
CREATE TABLE
hoge=> insert into t (s) values ('abc');
INSERT 0 1
hoge=> select * from t where s='ABC';
  s  
-----
 abc
(1 row)

LIKE も大文字小文字を区別します。大文字小文字を区別したくない場合は ILIKE を使います。

hoge=> select 'abc' like 'ABC';
 ?column? 
----------
 f
(1 row)

hoge=> select 'abc' ilike 'ABC';
 ?column? 
----------
 t
(1 row)

スキーマ

MySQL には無いものですが、PostgreSQL にはデータベースとテーブルの間にスキーマというものがあります。 データベース内でテーブルの名前空間をわけられるようです。 特に指定しない場合はデフォルトのスキーマが使われるので意識しなくても問題ないです。

MySQL のデータベースは PostgreSQL のデータベースとスキーマの両方が混ざったものなのかもしれません。

トランザクション

PostgreSQL はトランザクション中で何かエラーが発生した場合は、以降のクエリはロールバックするまですべてエラーになります。

この状態でコミットするとエラーにはなりませんが、実際にはロールバックされます。これはちょっと罠っぽいです。

hoge=> begin;
BEGIN
hoge=> insert into t (i) values (123);
INSERT 0 1
hoge=> hoge;
ERROR:  syntax error at or near "hoge"
LINE 1: hoge;
        ^
hoge=> select * from t;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
hoge=> commit;
ROLLBACK
hoge=> select * from t;
 i 
---
(0 rows)

MySQL と違い、PostgreSQL はトランザクション中で CREATE TABLE や DROP TABLE しても、ロールバックすると無かったことになります。これは結構嬉しいです。MySQL はトランザクション中で CREATE TABLE, DROP TABLE すると、その時点で勝手にコミットされてしまうという罠があるので…。

その他の構文

MySQL は文字列リテラルの表記は「'」でも「"」でも良いですが、PostgreSQL では「'」だけです。「"」はテーブルやカラムの識別子リテラルを表します(MySQL での「`」に相当)。

MySQL の「||」は論理和を表しますが、PostgreSQL では「||」は文字列結合です。

これらも MySQL 側で sql_mode を指定することで PostgreSQL に合わせることができますが、Sequel を使ってれば構文の差異はある程度吸収してくれるので、あまり気にしませんでした。

おわりに

はじめは「MySQLユーザーのためのPostgreSQLガイド」というタイトルにしようと思ったのですが、そんな大層なことは書けなかったので、「MySQLユーザーがPostgreSQLを触ってみたメモ」にしました。

MySQL と PostgreSQL は色々違いがありますが、構文まわりは MySQL の方が特殊だと思いました。勝手に余計な変換はしない方がみんな幸せになれると思います。

MySQL から PostgreSQL に移行するには、まず sql_mode を設定して、その状態で MySQL でエラーにならないようにしてから、PostgreSQL に移行するのがいいと思います。

自分の場合は、数値と文字列の比較とか、数値カラムに数字文字列入れようとしてたりとか、SELECT で指定してないカラムを GROUP BY に指定してたりとか色々ありました。

あと、最近 MySQL も sql_mode のデフォルト値を厳し目にするようになってるので、PostgreSQL とは関係なく、可能であれば厳し目に設定しておいた方が何かといいと思います。

RSpec をやめて Test::Unit に戻る

最近の RSpec は、それまで

obj.stub(hoge: value)

と書けたものが、

allow(obj).to receive(:hoge).and_return value

と書かないといけなくなったりとか、正気の沙汰とは思えないような変更をしたりするので、何年かぶりに Test::Unit を使ってみようとリハビリ中です。

RSpec は、テストケースを入れ子にできたり、テストケースや example がクラスやメソッドではなく、文字列で自由に書くことができたりしたのが良かったのですが、最近の Test::Unit ではそれもできるようになっています。

[ruby-list:48926] [ANN] test-unit 2.5.2

このリリースはとみたさんに使ってもらえるように改良したリリー スです。新しく追加した--locationはRSpecの--line_numberと似た 機能です。sub_test_caseはshoulda-contextとRSpecのcontextと似 た機能です。これらの機能があればtest-unit 2に乗り換えてくれ ると聞いたので実装しました。

2年もたってやっと使い始めました。すいません…。

準備

% gem install test-unit

テスト

RSpec で次のように書いてたテストは、

describe Array do
  context '空の場合' do
    before do
      @array = []
    end
    it 'size が 0' do
      expect(@array.size).to eq 0
    end
  end
  context '要素が1つの場合' do
    before do
      @array = ['hoge']
    end
    it 'size が 1' do
      expect(@array.size).to eq 1
    end
  end
end

Test::Unit だと次のように書けます。

require 'test/unit'

class TestArray < Test::Unit::TestCase
  sub_test_case '空の場合' do
    setup do
      @array = []
    end
    test 'size が 0' do
      assert_equal 0, @array.size
    end
  end
  sub_test_case '要素が1つの場合' do
    setup do
      @array = ['hoge']
    end
    test 'size が 1' do
      assert_equal 1, @array.size
    end
  end
end

一番外側は Test::Unit::TestCase を継承してクラスを作成する必要がありますが、そのクラスの中は sub_test_case を使って任意の文字列でテストケースを記述できます。

RSpec の describe, context と同様に sub_test_case は入れ子にもできます。 ただし、test に指定する文字列がまったく同じだと後で定義した方は無視されてしまうようなので注意しましょう。

デスクトップ通知

gem で test-unit-notify をインストールして、スクリプトの先頭で

require 'test/unit/notify'

と書いておくと、デスクトップ通知で結果を知らせてくれて便利です。

f:id:tmtms:20141012233534p:plain

stub

stub は、test-unit-rr をインストールして、スクリプトの先頭で

require 'test/unit/rr'

としておき、次のように記述することができます。

stub(obj).hoge{value}   # obj の hoge メソッドが value を返す

RSpec よりもシンプルに書けて良いです。