Ruby on Railsで，DBへの全接続を強制的に切断したい （Webアプリから，sudo経由で任意のコマンドを実行可能にする方法）

データベースへの接続を「強制的に切断」するには，どうしたらよいか。

環境は，Linux上で，Ruby on RailsからPostgreSQLにつないでいる場合を想定。

考えうる方式は３通り。

1. ActiveRecord側から，DB接続を切断する
2. PostgreSQL側から，DB接続を切断する
3. OS側から，DB接続を切断する

何をしたいか？

Webアプリの画面上から，データベースのリストアを行ないたい。

つまり，PostgreSQLで言うと

• dropdb

• createdb

• ダンプファイルを用いたリストア

の３段階を，Webアプリ上から実行したいのだ。

ここで，dropdbユーティリティを実行する時点で，下記のようなエラーに遭遇する：

dropdb: database removal failed: ERROR:
database "hoge_db" is being accessed by other users

誰かが接続中で利用中のDBは，dropできないのだ。

これはdropdbのかわりに，下記のようにSQLで試した場合も同じ結果になる。

psql postgres -c "DROP DATABASE hoge_db;"
 # => ERROR: database "hoge_db" is being accessed by other users

なので，データベースをDROPする前に，

まず「全ての接続を切断」する必要が生じる。

（１）ActiveRecord側から，DB接続を切断する

Ruby on RailsのAPIドキュメントを見ると，disconnect()メソッドが存在する。

Class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter#disconnect!()
http://ar.rubyonrails.org/classes/Act...

• Close the connection.

Railsのバージョンが古い場合は，自前でdisconnect()を定義する。

If you want to disconnect a database properly, there is the code:)!!!
http://osdir.com/ml/lang.ruby.rails/2...

• postgresql_adapter.rbにdisconnect()メソッドを定義する。
• connection_specification.rbにclear_connection()メソッドを定義する。
• ※この人は，この情報を投稿する前に，下記のフォーラムで質問している。
  • http://www.ruby-forum.com/topic/52012 (Helps!!!!! Rails database connection guru needed!!!!!!!)

remove_connection()というのもある。

しかし実際には，接続を完全に閉じることができていない。

ActiveRecord::Base#remove_connection()
http://apidock.com/rails/v1.2.6/Activ...
Remove the connection for this class. This will close the active connection and the defined connection (if they exist).

Ruby/ActiveRecordで動的に接続先を変更する
http://yakinikunotare.boo.jp/orebase/...

• remove_connection　ってメソッドが使えそうだなと思って establish_connectionと交互にやればできるんじゃないかと思ったんだが・・・なんかできない。

How to disconnect to a database????
http://www.ruby-forum.com/topic/51636
while we try to disconnect our database connection with
ActiveRecord::Base.remove_connection(), we find out
that the connection is not closed (the function seems
not to be working perfectly) and we are still
connected to the database and able to access to the
tables.

ところで，これらの方法は「現在のHTTPリクエスト」を中心に見た考え方。

「現在のリクエスト用の接続を切断する」だけ。

特定のDBに対する「すべての接続を切断する」ことは，これではできない。

（２）PostgreSQL側から，DB接続を切断する

特定のDB（の中の，特定のスキーマ）に対する「すべての接続を切断する」ためには，

Railsの視点ではなく，DB側の視点で考える必要がある。

まず，特定のデータベースへの全ての接続状況を確認することは，

下記のようなシステムカタログを参照するSQLによって可能。

SELECT
  *
FROM
  pg_stat_activity
;

PostgreSQL/8.x/データベースへの接続数を確認するSQL
http://www.lovebug.jp/index.php?Postg...

それらの接続を強制的に終了させることは，下記のDB管理用の関数によって可能。

• pg_cancel_backend()
  • PostgreSQL 8.3以前でも利用可能。
  • IDLEな接続を切断できない。

• pg_terminate_backend()
  • PostgreSQL 8.4以降で利用可能。
  • IDLEな接続でも切断できる。

pg_cancel_backend()の使い方：

SELECT
  pg_cancel_backend( procpid )
FROM
  pg_stat_activity
WHERE
  usename = '特定のユーザ名'
;

killing the query with the built in functions
http://postgis.refractions.net/piperm...

Is there a way to kill a connection from the pg_stat_activitly list?
http://www.issociate.de/board/post/46...

• "select pg_cancel_backend(procpid) " can end the current query for that user, but then this connection becomes IDLE, still connected.
• From the command line on the server you can issue a kill pid to do that.

9.20. System Administration Functions
http://www.postgresql.org/docs/8.1/st...
pg_cancel_backend sends a query cancel (SIGINT) signal to a backend process identified by process ID. The process ID of an active backend can be found from the procpid column in the pg_stat_activity view, or by listing the postgres processes on the server with ps.

Postgresql 8.0 and Cancel/Kill backend functions
http://www.mail-archive.com/pgsql-gen...

• 切断によって表示されるエラーメッセージ：
  • Session 1: ERROR: canceling query due to user request

pg_terminate_backend()について：

pg_terminate_backend() で強制切断
http://lets.postgresql.jp/documents/t...

• 以前から pg_cancel_backend() 関数は存在していたのですが、これは実行中のクエリを取り消すことしかできません。「IDLE in transaction」状態になっている意図しないロングトランザクションを取り消すには、SIGTERM シグナルを直接発行する必要がありました。
• pg_terminate_backend() 関数は切断を SQL から実行できるので、より安全で扱いが容易です

結局，PostgreSQL 8.3よりも以前のバージョンを利用している場合は，

DB側の関数によって「全ての接続を切断する」ことはできない。

（３）OS側から，DB接続を切断する

ここまでで

• Webアプリケーションの視点

• DBの視点

で考えて，方法を探索してきた。

それでもだめだったので，

次は，視野をDBよりも低いレイヤに移して考える必要がある。

つまり，OSの視点で考えるという事。

Linux上で，Postgresを起動スクリプトから利用している場合，

下記のコマンドによってDBを再起動できる。

/etc/init.d/pgsql restart

stopで停止するだけでも接続は全部リセットされるが，

あえてstopではなくrestartとしているのには理由がある。

なぜなら，dropdbやcreatedbなどのユーティリティー類を呼び出すためには，pgsqlが起動している必要があるのだ。

もしrestartの代わりにstopとすると，dropdb呼び出し時に下記のようなエラーになってしまう。

could not connect to server:
No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

なのでstopではなくrestartしている。

しかし，このrestart実行のためには，実行ユーザが，postgresユーザかrootユーザとしてログインしている必要がある。

Webアプリ（Ruby on Rails）が httpd などの専用起動ユーザで実行されている場合は，直接実行できない。

この問題を回避するためには，sudoによって一般ユーザに権限を付与すればよい。

sudoはLinux上でRPMからインストールできる。

sudo rpm build for : CentOS 5
http://rpm.pbone.net/index.php3/stat/...

rpm -ivh sudo-1.7.2p1-10.el5.i386.rpm

ただし，sudoを利用開始するためのステップを踏むためには，

まずはroot権限でのsudo導入作業が必要になる。

user 権限でのrpm --rebuild
http://search.luky.org/vine-users.0/m...

• rpm の「インストール」は root でやるのが原則

RPMパッケージをインストールする
http://itpro.nikkeibp.co.jp/article/C...

• パッケージのインストールには，ルート権限が必要
• アップデートの場合もルート権限が必要

ソースからのインストール手順
http://www.support-you.com/wiki/lpic/...

• make installは、/usr/local以下など書き込みにroot権限が必要な場所にインストールされることが多いので、一般的にroot権限が必要s

パスワード入力なしで sudo コマンドを実行する方法
http://blog.tofu-kun.org/070517085727...

• PHPã‚„Perlスクリプトなどで sudo を使いたい場合
• /etc/sudoersという設定ファイルを編集するには当然 root 権限が必要

なので，rootによる手作業を完全に避けることはできない。

（もしroot本人による介入を抜きにしてroot権限が取得できたら，セキュリティが成り立たなくなる。）

全てをWebアプリから自動化することは，不可能。

ここでは，Webアプリの運用開始前に，サーバ上でroot権限で作業することを前提とする。

sudoの設定ファイルである /etc/sudoers の編集のポイント：

• 下記行を追加
  • Webサーバの実行ユーザ名 ALL=(ALL) NOPASSWD:ALL

• Defaults requirettyをコメントアウト
  • Webサーバは「端末」という概念を持っていないから，そのままsudoしようとすると「standard in must be a tty」のエラーになる。

書き換えただけで次回のsudo実行からすぐに反映される。

sudoが「sudo: sorry, you must have a tty to run sudo」と文句を言うときは
http://blog.cles.jp/item/2919

• Webアプリからsudoが実行できない時，/etc/sudoersの　Defaults requirettyをコメントアウトせよ。httpdはtty持っていないので。

standard in must be a tty
http://x68000.q-e-d.net/~68user/cgi-b...

• su は端末 (tty) からのパスワード入力を求めているのに、端末がないよ、という意味です。
• 端末というのは、キーボードで入力ができて、文字が表示されるもの

●特定コマンドの利用制限
http://www.atmarkit.co.jp/fsecurity/r...

• セキュリティ上の理由、または操作ミスによる影響範囲を最小限に抑えるために、root権限によるコマンドの実行を特定コマンドのみに限定したい
• sudoではroot権限以外に，特定のコマンドのみ可能とか部分的な制限+特権付与ができる

これで，冒頭で述べた目的を果たすことができる。

DBの全接続を停止するために，root権限でDBを再起動する。

sudo /etc/init.d/pgsql restart

※なお，もし "/etc/init.d/pgsql restart" のように引用符でくくってしまうと，それ全体が１つのコマンドとみなされてしまい，sudoの実行に失敗する。

これで，心置きなくdropdbし，createdbし，ダンプファイルからDBを再構築できる。

つまり，Webアプリ上からDBをリストアできる。

なお，Railsでacts_as_authenticatedプラグインなんかを使っている場合，

ログインに必要なセッション情報はDBに格納されるので，

リストア直後は強制的にアプリからログアウトされることになる。

もしリストア直後に無理やりサーバにアクセスしようとすると，下記のエラーになる。

画面上：
  We’re sorry, but something went wrong.
  We’ve been notified about this issue and we’ll take a look at it shortly.


ログ上：
  PGSQL ERROR: FATAL: terminating connection due to administrator command
  server closed the connection unexpectedly
  This probably means the server terminated abnormally
  before or while processing the request.
  〜（処理しようとしたSQL文）

このあたりの問題は，リストア完了時にブラウザ上で，

サーバを介さずにうまく専用のページにリダイレクトして対処する。

結論

まとめとして，以下のような教訓を得た。

教訓：

• Webアプリが進化してゆくと，いろいろと高度なことをやらせたくなる。
  • OS上のファイルを直接操作　とか
  • 特定のミドルウェアを直接実行　とか。

• そのためには，「OS上のコマンドを直接実行」という道を避けられない。

• そして，そのためには，sudoの利用を避けられない。

サーバ上で特殊な操作を行ないたいのであれば，どうしてもsudoのお世話になる。ということ。

sudoを使い慣れていれば，Webアプリの可能性は大きく広がるだろう。

（※セキュリティ上のリスクも同時に広がることになるので，ご注意を・・・。）

補足

情報を探している間，Railsではない別のActiveRecordに関する情報が紛れ込んで困った。

.NETにもActiveRecordの実装が存在し，名を「Castle ActiveRecord」という。

これはRuby on Railsの情報ではないので，除外して考える必要がある。

Castle ActiveRecord
http://www.castleproject.org/activere...

• The Castle ActiveRecord project is an implementation of the ActiveRecord pattern for .NET.

How I can disconnect to one database, and open new connection with Castle ActiveRecord?
http://stackoverflow.com/questions/38...

• ActiveRecordStarter.ResetInitializationFlag();

Rails内部でのコネクションの使い回しについて：

ActiveRecordのソースコードを読む
http://blog.livedoor.jp/sasata299/arc...

• ActiveRecord では 2.2 からコネクションプーリングが導入されている

接続を一個一個つぶしてゆくというアプローチについて：

• killコマンドの実行にはroot権限が必要なので，結局sudoを使う点では変わりない。

postgres へのクライアントからの接続を強制切断する ruby スクリプト
http://blog.champierre.com/archives/809

• postgres への接続プロセスの pid を調べ、sudo kill -9

pg_ctl経由でPostgresを強制停止
http://akio0911.net/archives/2745

• データベースへの接続が残っているために失敗：
  • ERROR: DROP DATABASE: database “masters” is being accessed by other users / dropdb: database removal failed
• pg_ctl に -m f オプションを指定し、トランザクションも強制終了し再起動した後に、データベースの削除を行います。
• →この後で.pidファイルの削除に権限が必要になる。