プログラマーのメモ書き

伊勢在住のプログラマーが気になることを気ままにメモったブログです

NVR510 の不正アクセス検知のパケット破棄とタイルサーバー公開

こちらの記事で書いたように OpenStreetMap のタイルサーバーを運用しています。

先日、なんの気なしに、このタイルサーバーを使っているWebサイトを見に行くと、地図が表示されていません。 タイルサーバー側で特に変更もしていないので、何か不具合でも起こったかな?と思って調べてみたら、いろいろとはまったので、解決するまでの顛末をメモっときます。

問題の切り分け

まずはタイルサーバー自体に問題ないか調べました。sshで接続して、プロセスやサーバー上のログ等を見ても特に問題点は見当たりません。

でも、さきほどのWebサイトを見るとやっぱり地図は表示されません。念のため、同じLAN内のPCからローカルIPアドレスで接続すると、問題なく表示されます。

あれ?どういうことだ?

こうなるということは、サーバーの問題ではなく、外部からのアクセス(DNSかルータ)が怪しい。 ということで、もうちょっと調べると、ルータが原因でした。

ルータの設定内容の確認

最初に結論を書いておくと、

  • LAN内のWebサーバーを公開(今回は80以外のポートでした)
  • 不正アクセス検知のパケット破棄を有効

とした時に問題が起きました。

どうも、この設定だと、外部からのアクセスができなくなるようです。 実際、不正アクセス検知のパケット破棄を無効にすると、外部からもアクセス可能になりました。

そういえば、少し前にルータから不正アクセス検知があったと通知が来ていて、その際になにも考えずに、『パケット破棄』を有効にしたことを思い出しました。 これが原因だったんですね。

なお、設定していた内容は次の通りです。

Web サーバーの公開方法

Webサーバーの公開設定はこちらの記事にあるようにしています。具体的には次の通りです。

IPマスカレード設定 f:id:junichim:20190320141415p:plain

NATディスクプリタのインターフェースへの適用 f:id:junichim:20190320141526p:plain

受信方向のIPフィルタ設定 f:id:junichim:20190320141702p:plain

NATディスクプリタでIPマスカレードを設定し、LAN内のWebサーバーに転送しています。同時に、受信方向のIPフィルタを定義して、ルータを通過させています。 IPフィルタは、NATディスクプリタを設定すると、自動で定義してくれるやつをそのまま使っています。

不正アクセス検知とパケットの破棄

パケット破棄は、ルータの管理画面の『詳細設定』より、『セキュリティ』->『不正アクセス検知』と進み、『設定』ボタンを押して表示される画面で指定することができます。

f:id:junichim:20190320142243p:plain

画面上部の『検知したパケットの破棄』を選択すると、すべての場合でパケット破棄を指定することができます。

設定後はこんな感じになります(すべての場合でパケットを破棄にしました)。

f:id:junichim:20190320141929p:plain

詳細な原因

さて、画面上の機能の説明からすると、直感的にはこれは不思議な挙動です。 ということで、よくわからなかったこともあり、ヤマハのサポートさんに聞いてみたところ、いろいろと懇切丁寧に教えていただけました。

それは、上記の不正アクセス検知とパケット破棄の設定の一つとして、WinnyとShareのパケットを破棄する設定があるのですが、それを設定したことにより、タイルサーバーの通信も破棄していた、というものでした。

どういうことか理解するために、Winnyのパケット破棄がどのように行われているか調べてみます。

ちなみに、あとからルータのヘルプページを見てみると、不正アクセス検知のパケット破棄を有効にするとIPフィルタが変更されるとちゃんと書いてました。最初からこれに気づいていれば、もうちょっと解決が早かったかもしれません。

f:id:junichim:20190320153609p:plain

Winnyのパケット破棄について

Winnyのパケット破棄を設定した際には、静的フィルタで外部への通信をすべて遮断します。 そのうえで、動的フィルタにて内側->外側の通信を許可するようにしているようです。

静的フィルタの設定はこんな感じです(ルータの管理画面から『IPフィルタ』の『送信方向のフィルタ』を選択すると見ることができます)。

f:id:junichim:20190320145610p:plain

通信開始で使われる ACK を持った外部へのパケットを破棄していることがわかります。一方、通信開始時一番最初の SYN は通しています。

動的フィルタは、tcpを対象に外向き方向に設定しています。最初に内側から外側に通信が開始されて、それが静的フィルタで通過されると、次からは動的フィルタが先に適用されて、通信が許可される、という流れだそうです。Winnyの検知はこの動的フィルタの動作のタイミングで行われるようです。

(参考:IPフィルタについて)

動作確認

この動作を確認するためにログを見てみました。

まず、送信時のIPフィルタ設定(上記の 200098)を変更して、 reject-nolog から reject に変更しログに記録させます。

次に、syslog がデフォルト設定のままだと、破棄されたパケットを見ることができないので、ルータの管理画面より『管理』->『保守』->『コマンドの実行』を開いて、

syslog notice on

と実行し、 notice レベルのログを吐くようにします(コンソールでもできます)。

f:id:junichim:20190320144941p:plain

これで、タイルサーバーにアクセスすると

2019/03/20 11:24:17: Configuration saved in "CONFIG0" by HTTPD
2019/03/20 11:24:24: [INSPECT] PP[01][out][動的フィルタID] UDP 192.168.xxx.xxx:aaaaa > 送信先アドレス・ポート (2019/03/20 11:23:54)
2019/03/20 11:24:26: PP[01] Rejected at OUT(200098) filter: TCP 192.168.yyy.yyy:bbbbb > 送信先アドレス・ポート
2019/03/20 11:24:28: PP[01] Rejected at OUT(200098) filter: TCP 192.168.zzz.zzz:cccccc > 送信先アドレス・ポート
2019/03/20 11:24:29: PP[01] Rejected at OUT(200098) filter: TCP 192.168.zzz.zzz:cccccc > 送信先アドレス・ポート

のように、静的フィルタ(ID番号 200098)により、パケットが破棄されていることがわかります。

なお、この際、動的フィルタがあるんだから、通信できないのだろうか?と考えたのですが、よく考えると、この設定の場合、

  • 内から外へ通信を開始る際に、パケットを通過させる

ということなので、最初に外から来たタイルサーバーへのアクセス(に対応する応答のパケット)はこのIPフィルタで落とされてしまうんじゃないかと思ってます(合ってんのかな?)。

なお、ログの内容については、下記などを参考にしてください。

(参考:ログ設定など)

(参考:ログの見方)

回避策

ということで、一応原因がわかりましたので、回避策を取ります。とえらそうに書いてますが、これも、ヤマハのサポートさんに教えていただきました。重ね重ねありがとうございます。

やることは、送信時のフィルタ設定で、当該タイルサーバーから外に戻るパケットを明示的に許可してあげればよい、というものです。

実際の設定方法は、

f:id:junichim:20190320150437p:plain

のように、パケットを破棄するフィルタ設定の手前に、タイルサーバーからの通信(IPアドレス、ポート番号を指定)を通すためのフィルタを追加してやります。

テスト

ということで、この状態でテストすると、無事表示されました。 一方、ルータの設定で不正アクセス検知のパケット破棄も生きたままになっています。

いや、なかなか難しいですね。

OpenStreetMap タイルサーバーで pbf ファイル生成に挑戦 (失敗)

最初に結論を書いておきます。

こちらの記事に書いた osm2pgsql を利用して立ち上げたタイルサーバーでは、pbfファイルを作るのは非常に難しいです。

以下は、この結論になるに至るまでに調べたこととかを備忘録代わりに書いておきます。

動機

さて、まず、タイルサーバーでpbfファイルを作りたいなと思った動機です。

避難所検索@伊勢というオフラインマップを利用したAndroidアプリをリリースしているのですが、このアプリで使うオフライン用の地図データが、 pbf ファイルをもとにして、map ファイルという形で作成されます( なお、作り方については、こちらの記事などを参照してください)。

この、pbfファイルは、通常のOpenStreetMap のデータファイル(.osmファイル、xml形式)をバイナリでまとめたものになります。 タイルサーバーは常に最新の地図データになるように更新しているので、これをもとにpbfファイルを作れれば、簡単に最新のオフライン用地図データを作れるんじゃないかな?と思ったのが動機です。

最初のトライ

OpenStrretMap の osmosis のドキュメントに --read-apidb ってオプションを使って xml ファイルに書き出す例が載ってるので、それに従って試してみます。 この時、pbf で出力したいなら、 --write-pbf オプションを指定すればいいみたい。

Creating a Custom PBF File

試してみると

osm@map:~$ osmosis --read-apidb database="gis" user="osm"  --write-pbf file="jm.osm.pbf"
1 25, 2019 11:22:54 午前 org.openstreetmap.osmosis.core.Osmosis run
情報: Osmosis Version 0.44.1
1 25, 2019 11:22:55 午前 org.openstreetmap.osmosis.core.Osmosis run
情報: Preparing pipeline.
1 25, 2019 11:22:55 午前 org.openstreetmap.osmosis.core.Osmosis run
情報: Launching pipeline execution.
1 25, 2019 11:22:55 午前 org.openstreetmap.osmosis.core.Osmosis run
情報: Pipeline executing, waiting for completion.
1 25, 2019 11:22:55 午前 org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager waitForCompletion
重大: Thread for task 1-read-apidb failed
org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (サーバーは、パスワード・ベースの認証を要求しました、しかし、いかなるパスワードも提供されませんでした。)
        at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:243)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:372)
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128)
        at org.openstreetmap.osmosis.apidb.common.DatabaseContext2.executeWithinTransaction(DatabaseContext2.java:89)
        at org.openstreetmap.osmosis.apidb.v0_6.ApidbReader.run(ApidbReader.java:105)
        at java.lang.Thread.run(Thread.java:748)
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (サーバーは、パスワード・ベースの認証を要求しました、しかし、いかなるパスワードも提供されませんでした。)
        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1551)
        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1390)
        at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1046)
        at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:202)
        ... 5 more
Caused by: org.postgresql.util.PSQLException: サーバーは、パスワード・ベースの認証を要求しました、しかし、いかなるパスワードも提供されませんでした。
        at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:444)
        at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:173)
        at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
        at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:136)
        at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:29)
        at org.postgresql.jdbc3.Jdbc3Connection.<init>(Jdbc3Connection.java:24)
        at org.postgresql.Driver.makeConnection(Driver.java:393)
        at org.postgresql.Driver.connect(Driver.java:267)
        at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
        at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
        at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1558)
        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1547)
        ... 8 more

1 25, 2019 11:22:55 午前 org.openstreetmap.osmosis.core.Osmosis main
重大: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: One or more tasks failed.
        at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.waitForCompletion(Pipeline.java:146)
        at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:92)
        at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:37)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.codehaus.plexus.classworlds.launcher.Launcher.launchStandard(Launcher.java:328)
        at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:238)
        at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:408)
        at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:351)
        at org.codehaus.classworlds.Launcher.main(Launcher.java:31)

osm@map:~$ 

Exceptionの山。

何が悪いんだろうか?と思って、ネットであれこれ調べると read-pgsql ってオプションもあることがわかりました。

xml - Using osmosis to convert POSTGIS Table to .OSM - Stack Overflow

なので、これに切り替えて試すも、結果は同じ、Exceptionの山。 その後、host, user, password とかを変えていろいろと試してみたけど、一向にできません。

原因を調べる

Exception をよく見ると、認証で引っかかっている模様です。 そういえば、こちらの記事でセットアップしたのだけど、Postgres のユーザーにはパスワードを付けた覚えがありません。

でも、下記のリファレンスを見ても、--read-apidb で特段のオプション指定がないと、パスワードなしで接続する模様です。

https://wiki.openstreetmap.org/wiki/Osmosis/Detailed_Usage_0.47#--read-apidb_.28--rd.29

さて、何が起こっているんだろうか?

考えてみる

ちょっと立ち止まって、少し考えてみました。

psql でデータベースに接続するときは、osmユーザー(OSのユーザー)でログインしていれば、

psql -d gis

のようにデータベース名だけ指定すれば問題なく接続できます。これって、なんででしょうか?

これは、PostgreSQL の認証周りを調べないとよくわからないぞ。 ということで、PostgreSQL の認証周りを調べてみることにしました。

PostgreSQL の認証

どこから見つけた情報かはもう忘れてしまいましたが、 pg_hba.conf というファイルでユーザー認証を制御しているらしいということがわかりました。

19.1. pg_hba.confファイル

今のタイルサーバーだと、 /etc/postgresql/9.5/main に pg_hba.conf というファイルがありました。

で、デフォルトの設定を確認すると

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

となっていました。

この設定内容がどういうことなんだろうかと、わからないながらマニュアルと見比べていくと、 ネットワーク経由の場合(hostの行が該当)は、md5認証(パスワード認証の一種)になっており、 パスワードが未設定の場合は、常にPostgreSQLへの接続に失敗する、と書いてありました。

19.3.2. パスワード認証

これが原因でしょう、きっと。

一方、psqlでの接続は、localが使われてるっぽい。なので、Unixユーザーがosmに切り替わっていれば、peerで認証されるので、そのままPostgresqlのユーザーとしてosmが使われ、 パスワードなしだから、接続できる、ってことのようです。

19.3.6. Peer認証

認証方法の変更(pg_hba.conf の編集)

さて、osmosis での接続時は -h でホストを指定するので常にネットワーク経由での接続じゃないかと推測されます。なので、この設定ファイルを変更してやればよさげです。 ちなみに、dbユーザーにパスワードを付けるという選択肢もあるけど、タイルサーバーの更新スクリプト周りも変えることになるとはまりそうなので、こっちは採用しませんでした。

ということで、pg_hba.conf を編集します。

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    gis             osm             127.0.0.1/32            trust
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

host 接続の対象データベースが gis でユーザーが osm の場合のみ信頼するように trust を付けました。

変更内容を有効にするため、postgreSQL を再起動します。

mor@map:/etc/postgresql/9.5/main$ sudo /etc/init.d/postgresql restart
[ ok ] Restarting postgresql (via systemctl): postgresql.service.
mor@map:/etc/postgresql/9.5/main$ 

これで再度試してみます。

二回目のトライ

さて、再度試してみましたが、結果は変わらず Exception の山。

osm@map:~$ osmosis --read-apidb database="gis" user="osm"  --write-pbf file="jm.osm.pbf"
1 25, 2019 11:48:21 午前 org.openstreetmap.osmosis.core.Osmosis run
情報: Osmosis Version 0.44.1
1 25, 2019 11:48:22 午前 org.openstreetmap.osmosis.core.Osmosis run
情報: Preparing pipeline.
1 25, 2019 11:48:22 午前 org.openstreetmap.osmosis.core.Osmosis run
情報: Launching pipeline execution.
1 25, 2019 11:48:22 午前 org.openstreetmap.osmosis.core.Osmosis run
情報: Pipeline executing, waiting for completion.
1 25, 2019 11:48:22 午前 org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager waitForCompletion
重大: Thread for task 1-read-apidb failed
org.openstreetmap.osmosis.core.OsmosisRuntimeException: Unable to create resultset.
        at org.openstreetmap.osmosis.apidb.common.DatabaseContext.executeQuery(DatabaseContext.java:429)
        at org.openstreetmap.osmosis.apidb.v0_6.impl.SchemaVersionValidator.validateDBVersion(SchemaVersionValidator.java:82)
        at org.openstreetmap.osmosis.apidb.v0_6.impl.SchemaVersionValidator.validateVersion(SchemaVersionValidator.java:55)
        at org.openstreetmap.osmosis.apidb.v0_6.ApidbReader.runImpl(ApidbReader.java:74)
        at org.openstreetmap.osmosis.apidb.v0_6.ApidbReader$1.doInTransactionWithoutResult(ApidbReader.java:110)
        at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:33)
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:131)
        at org.openstreetmap.osmosis.apidb.common.DatabaseContext2.executeWithinTransaction(DatabaseContext2.java:89)
        at org.openstreetmap.osmosis.apidb.v0_6.ApidbReader.run(ApidbReader.java:105)
        at java.lang.Thread.run(Thread.java:748)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "schema_migrations" does not exist
  ポジション: 21
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:283)
        at org.openstreetmap.osmosis.apidb.common.DatabaseContext.executeQuery(DatabaseContext.java:424)
        ... 9 more

1 25, 2019 11:48:22 午前 org.openstreetmap.osmosis.core.Osmosis main
重大: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: One or more tasks failed.
        at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.waitForCompletion(Pipeline.java:146)
        at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:92)
        at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:37)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.codehaus.plexus.classworlds.launcher.Launcher.launchStandard(Launcher.java:328)
        at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:238)
        at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:408)
        at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:351)
        at org.codehaus.classworlds.Launcher.main(Launcher.java:31)

osm@map:~$ 

ただ、接続はできたっぽくて、Exception の中身が違ってきてます。

一応、--read-pgsqlも試してみると、

osm@map:~$ osmosis --read-pgsql database="gis" user="osm" --dataset-dump  --write-pbf file="jm.osm.pbf"
1 25, 2019 11:49:28 午前 org.openstreetmap.osmosis.core.Osmosis run
情報: Osmosis Version 0.44.1
1 25, 2019 11:49:28 午前 org.openstreetmap.osmosis.core.Osmosis run
情報: Preparing pipeline.
1 25, 2019 11:49:28 午前 org.openstreetmap.osmosis.core.Osmosis run
情報: Launching pipeline execution.
1 25, 2019 11:49:28 午前 org.openstreetmap.osmosis.core.Osmosis run
情報: Pipeline executing, waiting for completion.
1 25, 2019 11:49:28 午前 org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager waitForCompletion
重大: Thread for task 1-read-pgsql failed
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT version FROM schema_info]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "schema_info" does not exist
  ポジション: 21
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:237)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:456)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:464)
        at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:472)
        at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:477)
        at org.openstreetmap.osmosis.pgsnapshot.common.SchemaVersionValidator.validateDBVersion(SchemaVersionValidator.java:64)
        at org.openstreetmap.osmosis.pgsnapshot.common.SchemaVersionValidator.validateVersion(SchemaVersionValidator.java:47)
        at org.openstreetmap.osmosis.pgsnapshot.v0_6.impl.PostgreSqlDatasetContext.initialize(PostgreSqlDatasetContext.java:99)
        at org.openstreetmap.osmosis.pgsnapshot.v0_6.impl.PostgreSqlDatasetContext.iterate(PostgreSqlDatasetContext.java:197)
        at org.openstreetmap.osmosis.dataset.v0_6.DumpDataset.process(DumpDataset.java:48)
        at org.openstreetmap.osmosis.pgsnapshot.v0_6.PostgreSqlDatasetReader.run(PostgreSqlDatasetReader.java:53)
        at java.lang.Thread.run(Thread.java:748)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "schema_info" does not exist
  ポジション: 21
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:283)
        at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:209)
        at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:209)
        at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:441)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
        ... 11 more

1 25, 2019 11:49:29 午前 org.openstreetmap.osmosis.core.Osmosis main
重大: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: One or more tasks failed.
        at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.waitForCompletion(Pipeline.java:146)
        at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:92)
        at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:37)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.codehaus.plexus.classworlds.launcher.Launcher.launchStandard(Launcher.java:328)
        at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:238)
        at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:408)
        at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:351)
        at org.codehaus.classworlds.Launcher.main(Launcher.java:31)

osm@map:~$ 

微妙にExceptionの中身が違ってきてますね。

いずれにせよ、schemaがどうこうってメッセージがでてます。なんだこれ?

調査

調べてみると、そのももずばりで困ってる人がいました。

Postgis DB to osm pbf file - OSM Help

でも、回答なしです。

もうちょっと調べると、インポート時の話ですが、read-pgsql の時の Exception と似たことで困ってるのがありました。

Osmosis: import .osm to postgresql fails - OSM Help

この回答を見ると、schemaを作れ、とあります。

ん? なんだその話。

最初にタイルサーバーを立ち上げたときの手順にはそんなの載ってなかったはずです。

ということで、上記コメント内のリンクをたどると、ありました。

Osmosis/PostGIS Setup - OpenStreetMap Wiki

でも、この手順って、まんま OpenStreetMap のデータベースを作成する手順なんで、今のものと互換性あるんでしょうか?

で、気になったので、上記のドキュメントに載っている osmosis_dir/script/pgsnapshot_schema_0.6.sql なんてのの中身を見てみようと思い、 実際に今のタイルサーバーにインストール済みの osmosis について調べると

mor@map:~$ dpkg -L osmosis
/.
/etc
/etc/osmosis
/etc/osmosis/log4j.properties
/etc/osmosis/plexus.conf
/usr
/usr/bin
/usr/bin/osmosis
/usr/share
/usr/share/doc
/usr/share/doc/osmosis
/usr/share/doc/osmosis/examples
/usr/share/doc/osmosis/examples/pgsnapshot_schema_0.6.sql
/usr/share/doc/osmosis/examples/contrib
/usr/share/doc/osmosis/examples/contrib/apidb_0.6_osmosis_xid_indexing.sql
/usr/share/doc/osmosis/examples/contrib/apidb_0.6.sql
/usr/share/doc/osmosis/examples/contrib/dump_apidb.sh
/usr/share/doc/osmosis/examples/contrib/replicate_osm_file.sh
/usr/share/doc/osmosis/examples/contrib/CreateGeometryForWays.sql
/usr/share/doc/osmosis/examples/pgsnapshot_schema_0.6_upgrade_4-5.sql
/usr/share/doc/osmosis/examples/pgsnapshot_schema_0.6_bbox.sql
/usr/share/doc/osmosis/examples/pgsimple_schema_0.6_bbox.sql
/usr/share/doc/osmosis/examples/pgsnapshot_and_pgsimple.txt
/usr/share/doc/osmosis/examples/pgsnapshot_schema_0.6_upgrade_5-6.sql
/usr/share/doc/osmosis/examples/pgsimple_schema_0.6_linestring.sql
/usr/share/doc/osmosis/examples/pgsimple_load_0.6.sql
/usr/share/doc/osmosis/examples/munin
/usr/share/doc/osmosis/examples/munin/osm_replication_lag
/usr/share/doc/osmosis/examples/munin/README
/usr/share/doc/osmosis/examples/munin/osm_replication.conf
/usr/share/doc/osmosis/examples/pgsnapshot_load_0.6.sql
/usr/share/doc/osmosis/examples/pgsnapshot_schema_0.6_action.sql
/usr/share/doc/osmosis/examples/pgsimple_schema_0.6.sql
/usr/share/doc/osmosis/examples/pgsimple_schema_0.6_action.sql
/usr/share/doc/osmosis/examples/pgsnapshot_schema_0.6_linestring.sql
/usr/share/doc/osmosis/examples/fix_line_endings.sh
/usr/share/doc/osmosis/copyright
/usr/share/doc/osmosis/changelog.Debian.gz
/usr/share/doc/osmosis/README
/usr/share/man
/usr/share/man/man1
/usr/share/man/man1/osmosis.1.gz
/usr/share/osmosis
/usr/share/osmosis/osmosis-apidb-0.44.1.jar
/usr/share/osmosis/osmosis-hstore-jdbc-0.44.1.jar
/usr/share/osmosis/osmosis-core-0.44.1.jar
/usr/share/osmosis/osmosis-dataset-0.44.1.jar
/usr/share/osmosis/osmosis-xml-0.44.1.jar
/usr/share/osmosis/osmosis-pbf-0.44.1.jar
/usr/share/osmosis/osmosis-replication-http-0.44.1.jar
/usr/share/osmosis/osmosis-areafilter-0.44.1.jar
/usr/share/osmosis/osmosis-extract-0.44.1.jar
/usr/share/osmosis/osmosis-set-0.44.1.jar
/usr/share/osmosis/osmosis-pgsnapshot-0.44.1.jar
/usr/share/osmosis/osmosis-pgsimple-0.44.1.jar
/usr/share/osmosis/osmosis-pbf2-0.44.1.jar
/usr/share/osmosis/osmosis-tagfilter-0.44.1.jar
/usr/share/osmosis/osmosis-replication-0.44.1.jar
/usr/share/osmosis/osmosis-osm-binary-0.44.1.jar
/usr/share/osmosis/osmosis-tagtransform-0.44.1.jar
mor@map:~$ 

/usr/share/doc/osmosis/examples にインストールされていますね。 で、実際に中を見てみると、下記のような今のテーブル構成と全然異なっています。

osm@map:~$ psql -d gis
psql (9.5.14)
Type "help" for help.

gis=> \d
               List of relations
 Schema |        Name        | Type  |  Owner   
--------+--------------------+-------+----------
 public | geography_columns  | view  | postgres
 public | geometry_columns   | view  | osm
 public | planet_osm_line    | table | osm
 public | planet_osm_nodes   | table | osm
 public | planet_osm_point   | table | osm
 public | planet_osm_polygon | table | osm
 public | planet_osm_rels    | table | osm
 public | planet_osm_roads   | table | osm
 public | planet_osm_ways    | table | osm
 public | raster_columns     | view  | postgres
 public | raster_overviews   | view  | postgres
 public | spatial_ref_sys    | table | osm
(12 rows)

gis=> 

ということで、単純に examples 以下の sql を実行してもいろいろと問題になりそうな予感ばかりが立ちます。 さて、どうしたものかな。

スキーマ

ということで、もうちょっと調べてみると、OpenStretMap のデータベースって1種類じゃなくて、用途に応じて何種類もスキーマがあるということがわかりました。

Databases and data access APIs - OpenStreetMap Wiki

なるほど、そういうことか。

でもって、osm2pgsql で使ってるスキーマは、レンダリングに特化したもので、osm データ(xmlやpbf)の取得には適さないそうです。

ということで、今使っているタイルサーバーのスキーマと osmosis でpbf出力を行う際に想定しているスキーマが異なるので、簡単には pbf ファイルが作れないということがわかりました。 ついでに、 --read-apidb と --read-pgsql オプションでは想定しているスキーマも異なっていたようです。

まとめ

たぶん OpenStreetMap 界隈でサーバー扱ってる人には、このようなことは当たり前なんでしょうけど、初心者にはハードルが高いです。どこかに情報がまとまって落ちてるとありがたいです(が、そこを見つけるのもまだ一苦労なんだろうな、きっと)。 さすがに、調べに調べた挙句、できなかったのでちょっと疲れました。

とはいえ、オフラインマップ用のデータを更新していかないといけないので、めげずに次の方法を探ることにします。

Overpass API を触ってみた

OpenStreetMap で作成した要素を調べてみたいと思いたって Overpass API (Overpass turbo) を使ってみましたので、その際のメモです。

Overpass API って?

OpenStreetMap のデータを抽出してくれる便利なサービスとのことです。XMLまたは Overpass QL という形式のクエリを投げて、結果を取得するそうです。詳しくは、下記URLを参照してください。

https://wiki.openstreetmap.org/wiki/JA:Overpass_API

実際に使えるサービスを提供してくれてるのがこちら。

https://overpass-api.de/

これを Web から使えるようにしてくれてるサービスが Overpass turbo になります。詳しい説明は下記を参照してください。

https://wiki.openstreetmap.org/wiki/JA:Overpass_turbo

Overpass turbo を触ってみる

まずは地図上で結果を確認できる Overpass turbo をさっそく触ってみました。

Overpass turbo のサイトにアクセスすると、下記のような入力画面と地図が表示されます(最初、地図の中心はイタリアかどこかですが、下記は伊勢を中心に表示するように移動後のものになります)。

f:id:junichim:20190222210522p:plain

で、このまま、画面上部の『実行』ボタンを押すと、

f:id:junichim:20190222205805p:plain

のように右画面の地図で表示している範囲内の水飲み場を示してくれます。

これは便利そうです。 ということで、左画面に書く Overpass QL について調べてみることにします。

Overpass QL クエリの書き方

中途半端な説明をするよりも、言語ガイドOverpass QL のページを見てもらうほうがいいのですが、 それだと、あまりに内容があれなんで、ちょっと戸惑った点とかとも合わせて、自分なりの理解の元、基本的な感じを書いておきます。間違ってる可能性も大いにありますので、変なところ見つけた方ぜひご指摘ください。

最初のサンプルとして、下記のものを考えます。

node
  (34.1316, 136.3006,34.6774, 136.9621)
  (user:"junichim");
out;

抽出結果はこちら。

f:id:junichim:20190222221514p:plain

伊勢市を中心に、私(ユーザー名 junichim)が編集したノードを表示しています。

クエリ(フィルタ)ステートメント

取得する要素を表す種類指定子(node, way ,relation (rel) , area) と、それらに続くフィルタ条件から成ります。

node で始まる行が、OpenStreetMapのノード(緯度経度を持つ地点)を取得するステートメントです。 続く (34.1316, 136.3006,34.6774, 136.9621) が対象となる領域を指定するフィルタ条件になります。ここでは、(南の緯度, 西の経度, 北の緯度, 東の経度)という形で指定します。 最後の (user:"junichim") がそのノードを作成したユーザーを指定するフィルタになります。

これで1つの命令(ステートメント)になり、最後はセミコロンになります。 表記は、1行で書いても、空白なしでもありでもいけるようです。

フィルタの種類はいろいろありますが、tag のキーバリューを指定する

node
  (34.1316, 136.3006,34.6774, 136.9621)
  ["name" ~ "商店$"];
out;

などもよく使うと思います。この場合は name キー が正規表現 ( ~ で指定) で『xx商店』で終わる ($) ものを指定しています。

アクション

上記の例の最後の out がアクションになります。 out アクションにより、クエリ結果を出力することになるようなので、ほぼ書くことになると思います。

out に引き続いて、出力の詳細さなどを記述できます。

out; // out body と同じ
out body; // デフォルト
out ids; // id のみ    
out skel; // body より簡潔
out meta; // 一番詳しい
結果の結合

複数のフィルタ結果を統合(和集合)を取りたいときに使います。

(
  node
    (34.1316, 136.3006,34.6774, 136.9621)
    ["name" ~ "商店$"];
  node
    (34.1316, 136.3006,34.6774, 136.9621)
    ["name" ~ "駅$"];
);
out;

()内に書かれた個々のフィルタを or でつなげたものになります。 この例だと、『xx商店』のノードと『〇〇駅』を取得します。

結果の絞り込み

実は一番動作がよくわからなかったのが、この結果の絞り込みです。

例えば、フィルタステートメントを連続して書くと、『伊勢』で始まり、『郵便局』で終わるものを指定できるような印象がありますが、

node
  (34.1316, 136.3006,34.6774, 136.9621)
  ["name" ~ "^伊勢"];
node
  (34.1316, 136.3006,34.6774, 136.9621)
  ["name" ~ "郵便局$"];
out;

のように書くと、

f:id:junichim:20190222223603p:plain

あれ?167か所も選ばれています。明らかに伊勢とは関係なさそうなところにもプロットがあります。

この例だと、一つのステートメントで書けば、両方の条件を and で指定できます。

node
  (34.1316, 136.3006,34.6774, 136.9621)
  ["name" ~ "^伊勢"]
  ["name" ~ "郵便局$"];
out;

結果も

f:id:junichim:20190222224805p:plain

となり、16か所、伊勢市近辺の郵便局だけになりました。

いやいや、言語ガイドにある例だと、指定したnameの近くにある別のnameを持つもの なんてのが堂々と載ってます。

node["name"="Bonn"];
node
  (around:1000)
  ["name"="Gielgen"];
out body;

引用元はこちら

これって、さっきだめだった奴と同じ書き方なのに and 条件になってんじゃないの?

・・・これ調べてみると、実に曲者でして、リファレンスをよく読むと、フィルタステートメントのいくつかは、前のステートメントの結果を受け取らない、とあります。

でも、aroundフィルタでは受け取るんですね。なので、上記の言語ガイドの例は間違いなく and 条件として動いています。

他に、結果セットを明示的に示すフィルタ(入力集合によるフィルタ) も受け取ることができます。

なので、この場合は、

node
  (34.1316, 136.3006,34.6774, 136.9621)
  ["name" ~ "^伊勢"];
node._
  (34.1316, 136.3006,34.6774, 136.9621)
  ["name" ~ "郵便局$"];
out;

のようにデフォルトの結果セット (_) を指定してやれば、問題なく and 条件を作れます。

f:id:junichim:20190222230807p:plain

これは、はまりそうです。

なお、結果セットについては、 Overpass QL のページの概要と集合の部分が詳しいです。

Overpass turbo 使用上の注意

Overpass turbo はクエリを書けば、すぐに地図上で結果を確認できるので非常に便利なのですが、取得したデータ量が過大だった場合、下記のような警告が表示されます。

f:id:junichim:20190222205618p:plain

あまりにデータ量が多い時は条件を考え直したほうがいいかもしれませんね。

Overpass API として使ってみる

最後に、Overpass turbo 経由ではなく、スクリプト等で Overpass API を使いたい場合も試しました。 この場合は、下記のエンドポイントに対して data= パラメータで Overpass QL を URL エンコードした文字列を渡せばOKです。

https://overpass-api.de/api/interpreter

POSTでも投げられるそうです。詳しくは下記のページをご覧ください。

http://www.overpass-api.de/command_line.html

ただ、取得する結果のフォーマットをjsonとかにしたいときは、

[out:json];
node
  (34.1316, 136.3006, 34.6774, 136.9621);
out meta;

のように、先頭に出力形式を指定する必要があります(デフォルトはXML)。

試しに、指定範囲内で過去に編集したユーザー一覧を作ってみようと思いやってみました。

指定範囲内のノードをすべて取得する Overpass QL を作成します。

[out:json];
node
  (34.1316, 136.3006, 34.6774, 136.9621);
out meta;

この時、出力は meta を指定して、作成ユーザーの情報が載るようにします。

これを先ほどのエンドポイントに向かって投げます。

curl https://overpass-api.de/api/interpreter?data=%5bout%3ajson%5d%3b%0d%0anode%0d%0a%20%20%2834%2e1316%2c%20136%2e3006%2c%2034%2e6774%2c%20136%2e9621%29%3b%0d%0aout%20meta%3b > test.json

こんな感じで、Overpass API でデータ(約180Mあったのでご注意を)を取得後、

jq ' .elements[] | select(.timestamp >= "2018-01-01T00:00:00Z") | .user ' test.json | sort | uniq

のようにして、 jqで処理すれば簡単に取れますね。

こっちはこっちでいろいろと使えそうです。

参考

qiita.com

medium.com

www.nofuture.tv