GeoIP2のCSVデータでPostgresSQLデータベースを更新して国別拒否リストを生成

投稿日：

国単位でIPアドレス制限を行うためにPostgreSQLにインポートしたGeoIP2のデータを更新しようとしたときの覚書。

環境： CentOS Stream 8, PostgreSQL 14.6, nginx 1.22.1


GeoIP Updateは独自形式のデータベースバイナリ

公式が公開しているGeoIP Updateというプログラムが使えるか調査。

YUMリポジトリにもあったけどバージョンが低い。

# dnf search geoip
# dnf info geoipupdate

Name         : geoipupdate
Version      : 2.5.0

だけどGeoIP Updateは独自形式(mmdb)で保存される。
これをnginxから参照する方法もあるみたいだけど、nginx自体のbuildが必要との情報があったので、前と同じcsv形式でPostgreSQLのデータベースをアップデートすることにした。


国別IPアドレスのCSVファイルをダウンロード

最新のCSVダウンロード手順

  • MaxMindへログイン
  • 左メニューのGeoIP2/GeoLite2のDownload Files
  • 「GeoLite2 Country: CSV Format」のDownload ZIPをクリック

ZIPファイルの中身

  • GeoLite2-Country-Blocks-IPv6.csv： IPv6一覧
  • GeoLite2-Country-Blocks-IPv4.csv： IPv4一覧
  • GeoLite2-Country-Locations-ja.csv： IPアドレスIDと国コードの対応リスト（日本語版）


PostgreSQLデータベースへインポート

新規作成時は前の記事を参考に。
nginxで国単位のIPアドレス制限

postgresユーザーでSQLコマンド発行
# su - postgres
$ psql geo

確認のためテーブル一覧表示。
geo=# \d

データ削除
geo=# truncate table geoip2_location;
geo=# truncate table geoip2_network;

IPv4とIPv6をインポート
geo=# \copy geoip2_network(network, geoname_id, registered_country_geoname_id, represented_country_geoname_id,is_anonymous_proxy, is_satellite_provider) from '/opt/geo/GeoLite2-Country-Blocks-IPv4.csv' with (format csv, header);

geo=# \copy geoip2_network(network, geoname_id, registered_country_geoname_id, represented_country_geoname_id,is_anonymous_proxy, is_satellite_provider) from '/opt/geo/GeoLite2-Country-Blocks-IPv6.csv' with (format csv, header);

IPアドレスIDと国コードの対応リストをインポート
geo=# \copy geoip2_location(geoname_id, locale_code, continent_code, continent_name, country_iso_code, country_name, is_in_european_union) from '/opt/geo/GeoLite2-Country-Locations-ja.csv' with (format csv, header);

geo=# quit


nginxの拒否／許可リストを生成

nginxの設定ファイル用にdeny/allow文字を付けてSQL文を実行する。
（SQLの実行結果をファイルに書き出す）

ロシアからのIPアドレスを拒否するnginxのconfファイル。
$ psql geo -c "SELECT concat('deny ', network, ';')  FROM geoip2_network gn LEFT JOIN geoip2_location gl on gn.geoname_id = gl.geoname_id WHERE gl.country_iso_code = 'RU';" >> deny_RU.conf

中国からのIPアドレスを拒否するnginxのconfファイル。
$ psql geo -c "SELECT concat('deny ', network, ';')  FROM geoip2_network gn LEFT JOIN geoip2_location gl on gn.geoname_id = gl.geoname_id WHERE gl.country_iso_code = 'CN';" >> deny_CN.conf

北朝鮮からのIPアドレスを拒否するnginxのconfファイル。
$ psql geo -c "SELECT concat('deny ', network, ';')  FROM geoip2_network gn LEFT JOIN geoip2_location gl on gn.geoname_id = gl.geoname_id WHERE gl.country_iso_code = 'KP';" >> deny_KP.conf

日本からのIPアドレスを許可するnginxのconfファイル。
$ psql geo -c "SELECT concat('allow ', network, ';')  FROM geoip2_network gn LEFT JOIN geoip2_location gl on gn.geoname_id = gl.geoname_id WHERE gl.country_iso_code = 'JP';" >> allow_JP.conf

ファイルの先頭と末尾のいらない文を削除。
viコマンドは「1G」で先頭に、「G」でファイル末尾に移動できる。
「dd」で行削除。
# less deny_RU.conf

rootに戻ってnginxの設定ディレクトリに配置
# mv /var/lib/pgsql/deny_* /etc/nginx/conf.d/global/

拒否リストの確認
# less /etc/nginx/conf.d/global/wordpress_restrictions.conf

include conf.d/global/deny_RU.conf;
include conf.d/global/deny_CN.conf;
include conf.d/global/deny_KP.conf;

nginx再読み込み。
# nginx -t
# systemctl reload nginx

あとはgitで他のサーバーと共有する。


そのうち自動更新するスクリプトを作りたい。


【関連記事】