PostgreSQL 特定のデータベースやユーザのみパラメータの設定値を変更する方法

PostgreSQLの設定は基本的にpostgresql.confに記入し、その効力は全体に及びます。そういった中で、特定のユーザのや特定のデータベースにのみ、postgresql.confで設定したパラメータの値を変更したい場合がまれにありますよね？最近、そういう事例があったので調べてみました。

1インスタンスに複数のデータベースが動いてる開発用DBサーバがありました。最近ver 8から9にアップグレードを行ったのだが、アプリケーションの挙動がどうもおかしい。よく調べてみると、standard_conforming_strings パラメータのデフォルト値が変わった事が原因だった。

standard_conforming_strings の詳細はマニュアルに譲るとして、このパラメータは 9.1以降標準で on に変更されました。エスケープに関する設定なので、画面に出力される文字列にモロに影響してしまいます。

アップグレード前と同じ動きにするなら、本来であればpostgresql.confを書き換えるのがベターでありますが、問題となっているのは一つのデータベースのみであり、ほかのデータベースは特に問題は出ていません。問題のあるデータベースのみ standard_conforming_strings を off にしたいのです。

結論を先に申しますと、ALTER DATABASE 文で可能です。

ALTER DATABASE testdb01 SET standard_conforming_strings = off;

これで、このtestdb01データベースの standard_conforming_strings はoffになります。データベースだけではなく、ユーザに対しても可能です。

ALTER USER testusr01 SET standard_conforming_strings = off;

設定は pg_db_role_setting に反映されるので、以下のSQLで確認出来ます。

select d.datname, a.rolname, s.setconfig
 from pg_db_role_setting s 
 left join pg_database d on s.setdatabase=d.oid 
 left join pg_authid a on s.setrole=a.oid;

結果

datname | rolname | setconfig
---------+----------+-----------------------------------
 testdb01|          | {standard_conforming_strings=off} 
         | testusr01| {standard_conforming_strings=off}
(2 rows)

photo by Mike Mozart