未分類

概要

マニュアル解説シリーズです。今回のお題はこちら。

PostgreSQL: Documentation: 12: 20.1. The pg_hba.conf File

認証について

はじめに

PostgreSQLサービスを使うには、まず

  • サーバーにアクセスできること(ネットワーク的に到達できること)
  • サーバーに接続できること(サービスが稼働していて、ポートなどが開いていること)
  • サービスがクライアントを認証し、PostgreSQLのユーザーとマッピングできること

が必要です。3つ目の項目がココで説明する内容です。最初の2つに問題がある場合は、ここで説明する以前の話になります。

※細かく言うと、他にOSが何らかのセキュリティ(SELinux/AppArmorなど)で弾くケースがありますが、今回は考慮しません。

クライアント認証とはサーバー側から見て、接続してきたモノがどこの誰かを識別することです。この人に違いないと思ったらユーザーとマッピングされますが、そうでない場合はエラーで弾かれるということです。

PostgreSQLの認証の種類

認証方法説明
trust認証接続できただけで特定のユーザーとして認証する
パスワード認証指定されたユーザーであることをパスワードで確認して認証する
GSSAPI認証ActiveDirectoryなどで使用されるKerberos認証で認証されたユーザーとして認証する。
GSSAPIはそこで使用されるAPIで、所謂シングルサインオン。
SSPI認証Windows認証で認証されたユーザーとして認証する(Kerberos含む)。
サーバー/クライアントともにWindowsのときだけ使用できる、所謂シングルサインオン。
IDENT認証identサーバーでクライアントが実行中のユーザーを取得して認証する。
大昔に利用されてたサーバーで現在は使われない
Peer認証ローカル接続のときだけ、クライアントの実行ユーザーを取得して認証する。Windowsでは使用できない。
OSのユーザー名とPostgreSQLのユーザー名が一致する必要がある
LDAP認証LDAPのデータを使って指定されたユーザーであることを確認して認証する。中身はパスワード認証と同じ。
RADIUS認証RADIUSのデータを使って指定されたユーザーであることを確認して認証する。中身はパスワード認証と同じ。
平文ではないものの通信が暗号化されない
証明書認証SSLで受け取ったクライアント証明書のCommon Nameで認証する。
PAM認証LinuxのPAMのデータを使って指定されたユーザーであることを確認して認証する。中身はパスワード認証と同じ。
BSD認証OpenBSDで使える認証方式。よく知らない。

かなり方式が多いので注意です。ただ、よく使用されるのは太字の3つ。他はセキュリティに難があったり、ユーザーの管理がPostgreSQL内のユーザーとのマッピング維持が大変だったりして、あまり使用されません。証明書認証は通信経路そのものが暗号化されており、今後利用の増加が見込まれるという意味での太字です。

Ubuntu18.04のデフォルト設定

Ubuntu18.04のpg_hba.confのデフォルト設定はこんな感じ。

# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# 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
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

# で始まる行はコメントです。それ以外の行は、どんなIPから、どういう接続で誰がどのデータベースに接続しようとしていた場合、どういう認証方式を使用するかを表しています。あとは1行ずつ見ていきます。

local   all             postgres                                peer

これは

  • local=ローカル接続=Unixドメインソケットの接続
  • all=対象データベースは何でもいい
  • postgres=ユーザーがpostgres(管理者)

という条件を全て満たす場合、peer=Peer認証するという設定です。そういえば最初にpostgresユーザーでpsqlで繋いだときパスワード聞かれませんでしたよね。そのときはココの設定が効いていたということです。

local   all             all                                     peer

次の行は先程のユーザー設定列がall、誰でもいいになっただけです。つまり、ローカル接続ならどのデータベースでも誰でもPeer認証にする、という設定です。一般ユーザーを作った後、そのユーザーでpsqlを実行したときにパスワード聞かれませんでしたよね。そのときに使われた設定です。

host    all             all             127.0.0.1/32            md5

次の行はhostなので、ローカル接続ではないTCP/IPの接続であることを意味しています。データベースとユーザーはallなので何でもOKです。次のIPアドレスっぽいのが、クライアントのIPです。127.0.0.1はローカルホストを意味する特別なIPで、つまりTCP/IPで同じホストから繋いできたときは、誰でもどんなデータベースでも、md5=パスワード認証を行う、という意味になります。ただし、同じホストならローカル接続でPeer認証できるので、普通はこの設定を使うことはありません。

ただし、Peer認証はLinux上のユーザー名と一致しないと使えないので、Linuxのユーザー名と異なるユーザー名をPostgreSQL側に作成した場合には使用されます。

host    all             all             ::1/128                 md5

次の行はさっきの設定のIPv6版です。説明は省略します。

残りの行はレプリケーション用の接続設定なので、1つしかない今回は割愛します。

つまりどういう設定か…

ローカルホストからしか使えない設定になっています。
なので別のマシンから接続する場合は、認証設定を追加する必要があります。

ローカルホスト以外からのパスワード認証の追加

特定のIPv4ホストからの接続でパスワード認証を追加する

host    all             all             10.0.2.2/32            md5

こんな感じで、10.0.2.2(VirtualBoxのNATの仮想ルーターのIP)からTCP/IPで接続された場合、パスワード認証をかけることが出来ます。

特定のIPv4 LAN内の複数ホストからの接続でパスワード認証を追加する

host    all             all             192.168.0.0/24            md5

こんな感じで、192.168.0.1~192.168.0.254までのIPからTCP/IPで接続された場合、パスワード認証をかけることが出来ます。

設定ファイルを編集したら…

OSに用意された方法でサービスの再起動をしてください。

ローカルホスト以外からのSSLクライアント認証の追加

hostssl all all all cert map=cnmap

こんな感じで追加出来ます。SSLで接続する場合は、他のTCP/IP接続手段は消した方がいいでしょう。
cnmapというのは、Common Nameとユーザーをどうマッピングするかという設定を他のファイルに記述するのですが、そこで使用されるマッピングの名前です。

具体例を↓に書いたので、良かったら参考にしてください。あまり丁寧には書いてないですけど…。

まとめ

PostgreSQLの認証設定は種類が多く、デフォルトではローカルからの認証設定しか存在しない。
今回はそこにリモート接続からの認証設定を追加する方法を解説した。

未分類

前書き

前回はWindows上のVirtualBoxで入れたUbuntuにPostgreSQLをインストールし、簡単なテーブルを作成・表示させてみました。

今回はそのテーブルをWindowsから見てみようという記事です。

Ubuntu側の設定

PostgreSQLの設定

前回はUbuntu上のpsqlコマンドから同じマシン上のPostgreSQLサーバーに接続してSQLを叩いていました。現在のPostgreSQLの設定はこのタイプの接続しか許可していません。別のマシン上のアクセスを許可する設定をしていきます。

この設定は設定ファイルに書かれているので、ターミナル/sshで接続したコマンドプロンプトから、viコマンドを使ってファイルを編集する必要があります。ただ、viはとても癖のあるエディタで機能も少ないので、それを改良したvimを先にインストールします。操作感は同じですが、ハイライトされるなどの機能が違います。

$ sudo apt install -y vim

インストールが終わると、同じviコマンドでも、動きが変わってきます。ではまず、/etc/postgresql/10/main/pg_hba.confを編集して、別のマシンからの接続設定を追加します。

$ sudo vi /etc/postgresql/10/main/pg_hba.conf

viの操作方法(最小限)

キー操作意味
上に移動
下に移動
左に移動
右に移動
i入力モードに入り、以降[ESC]で戻るまで打ち込んだ文字を挿入します。
xカーソル位置の文字を削除します。
dd1行削除します。
[ESC]入力モードから戻ります。
ZZファイルを保存して終了します。
:q!ファイルを保存しないで終了します。

上を参考にどうにかして、ファイル末尾付近に移動して「ココ追加」の1行を追加してみてください。vimでググるとよりいろいろな操作方法を知る事ができますが、覚えることが多いので今は最小限でいいです。

# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# 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
# ↓ココ追加
host    all             all             10.0.2.2/32             md5
# ↑ココ追加
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
"/etc/postgresql/10/main/pg_hba.conf" 102L, 4790C             90,10        末尾

追加したのは、「(ホストから転送されてきた)ホスト自身からの接続の場合、パスワードで認証する」という設定です。10.0.2.2はVirtualBoxがホストに割り当てている仮想ネットワーク上のアドレスです。編集しているpg_hba.confファイルのフォーマットについての詳細は、

20.1. pg_hba.confファイル (PostgreSQLのマニュアル)

を御覧ください。よく分からないと思いますが、丁寧に説明しているとそれぞれ1つの記事になってしまうので、今回は割愛します。

参考: VirtualBoxのネットワークについて

参考: pg_hba.confファイルの認証設定

次にpostgresql.confを編集し、TCP/IPで待ち受ける(listenする)IPアドレスを追加します(デフォルトではTCP/IPで待ち受けていない=IPアドレスでの接続を待ち受けていない)。

$ sudo vi /etc/postgresql/10/main/postgresql.conf

ここでも「ココ追加」と書かれている1行を、どうにかして追加してください。

# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
# ↓ここ追加
listen_addresses = '*'                  # what IP address(es) to listen on;
# ↑ここ追加
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
                                        # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation
                                        # (change requires restart)
#bonjour = off                          # advertise server via Bonjour
<c/postgresql/10/main/postgresql.conf" 664L, 23154C           64,15-10       8%

完了すると、Ubuntuが持っているネットワークI/F全てで、ポート5432の接続待ちをする設定が追加されます。後はPostgreSQLサービスを再起動して新しい設定を読み込ませるだけです。

$ sudo service postgresql restart

VirtualBoxの設定

sshのときと同様にポートフォワードの設定が必要です。Ubuntuを動かしているVirtualBox上のメニューから、[デバイス]>[ネットワーク]>[ネットワーク設定]を開きます。

[高度]>[ポートフォワーディング]を押します。

上記のようにゲストIPを10.0.2.15でホスト/ゲストポートを5432に設定し、名前を適当につけて[OK]を押します。

すると、ホスト側の5432ポートに来た接続は、ゲスト側のIP10.0.2.15のポート5432に転送されます。この設定がない場合、ファイヤーウォール(NAT)に弾かれて、ホストからゲストにPostgreSQLの標準通信ができません。

Windows側の設定

PostgreSQLクライアントを導入

PostgreSQLクライアントの選定

PostgreSQLの無料クライアントはいくつか種類があります。

最も軽いのはUbuntuで使用したpsql(サーバー同梱)です。これはバッチ実行などをする際にはよく使いますが、コマンドから直に何かしたいとき以外はあまり使いません。

PostgreSQL専用のGUIクライアント兼管理ツールpgadminがあります。これも管理をする際には使いやすいツールです。

今回は、他のDBでも汎用に使用できるGUIのSQLエディタである、DBeaverをご紹介します。これは元々主にJava開発のIDEとして使用されるeclipseのプラグインとして実装されたものです。日本ではこのような種類ではA-5が有名ですが、世界的には現在このDBeaverが無料汎用SQLエディタでNo.1です。

DBeaverのインストール方法

DBeaverのインストールにはいくつか方法があります。

  1. インストーラを使用する
  2. zipを展開してjdkの設定を自分でする
  3. chocolateyを使用してインストールする

今回はchocolateyを使用して、インストールしますが、いずれの方法でも構いません。

Chocolateyとは

以下のリンク先のソフトです。

Chocolatey Software | Chocolatey – The package manager for Windows

Windows用のコマンドラインベースのパッケージ管理ツールに該当します。Ubuntuのaptのような使い勝手のツールです。便利なので、私も可能なときはchocolateyでアプリをインストールしています。便利な点はいくつかありますが、

  • まとめてソフトのアップデートを出来ること
  • インストールしたアプリの一覧をエクスポートし、他のPCにもまとめてインストールすることが出来ること
  • 共通の依存パッケージを複数入れなくて良くなったりすること

くらいでしょうか…

逆にイケてない点は

  • アプリ独自で更新機能を持っているソフトはどっちでアップデートする迷う(DBeaverもそうですが)
  • ユーザーの少ないパッケージは作りが適当で、うまくインストール/アンインストールできない場合がある

辺りです。

Chocolateyのインストール

インストール方法はあらゆるケースを網羅した資料が英語だけど↓にあります。

Chocolatey Software | Installation

私は以下の手順でインストールしています。

  1. Windowsキーを押す
  2. cmdと打ち込んでCtrl+Shift+[Enter]
  3. ユーザーアカウント制御(UAC)の問い合わせに[OK]
  4. 立ち上がった管理者権限のコマンドプロンプトで以下を実行
@"%SystemRoot%\System32\WindowsPowerShell\v1.0\powershell.exe" -NoProfile -InputFormat None -ExecutionPolicy Bypass -Command " [System.Net.ServicePointManager]::SecurityProtocol = 3072; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))" && SET "PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin"

終わるとchocolateyが使えるようになります。コマンドラインが不得意な人は、続けてchoco install chocolateyguiでGUI版もインストールできます。

DBeaverのインストール

管理者権限のコマンドプロンプトから

cinst dbeaver -y

するだけでインストールは終了します。

Windowsから接続

DBeaverの起動

初回起動時に出てくるポップアップは全部キャンセルします。一度しか見れないメッセージだと間違えるかもしれないので。

左上の新しい接続ボタンを押します。

PostgreSQLのゾウさんを選択して[次へ]

DatabaseとUserとPasswordを記述して、[テスト接続]

まだドライバがないため、ダウンロードするか聞いてくれるので[ダウンロード]

ダウンロードが完了すると、正しく設定されていれば上のようなテスト接続成功画面が出てきます。
満足して[OK]→[終了]してください。

元の画面に戻ると、データベースナビゲータで選べるデータベースが追加されてます。開いてみてください。

ポチポチ押していくと、前回作成したデータベースやテーブルがグラフィカルに表示されてることが分かります。ここでexample_tableをダブルクリックすると…

何やら分かりやすく整理されてテーブルの定義が表示されています。上部中央のデータタブをクリックすると…

当然のようにExcel表のような画面が表示されています。SQLを覚えてpsqlでコマンド叩かなくてもExcelが使える人なら簡単な編集は出来てしまいます。もちろんSQLも便利に使えるソフトです。でも、これでデータベースも怖くなくなりそうじゃないですか?

まとめ

WindowsのVirtualBox上で動くUbuntuに入れたPostgreSQLサーバーを外部からTCP/IPで接続できるように設定し、WindowsからDBeaverを使ってアクセスできるようになった。

次回は、Ubuntu上でVisual Studio codeをインストールし、pythonの開発環境を整えたいと思います(↓)。

未分類

前回まではVirtualBox上のUbuntu18.04にsshサーバーをインストールして、Windowsからsshで繋げられるところまで実施しました。

今回はUbuntu18.04にHerokuでよく使用されるPostgreSQLをインストールして、SQLなDatabaseを使えるようにします。

インストールの前に

Dockerじゃダメなの?

最近の環境構築では、こういうケースで、便利なのでDockerを使うことも多いです。ただし、それはUbuntuやLinuxに慣れてれば、の話なので、今回はまずは基本のローカルインストールを実施します。

Databaseとはどんなもの?

みんなが使用する大規模データを入れておくサーバーです。ファイルサーバーよりもより細かく、大量のデータを検索したり集計したりできます。検索したり集計したりするのに、データの持ち方や、検索・集計に使用するI/Fや言語などの区別により、いろいろな種類のデータベースがあります。

今回インストールするPostgreSQLは古式ゆかしいSQLを使用するRDBMSという分類のデータベースです。Excelのお化けみたいなタイプで、OracleやMySQLなどと同様古くからある、最もオーソドックスなタイプのデータベースになります。

インストール

$ sudo apt install postgresql

いつもどおり、パスワードを入力して、容量確認して"y"でOKです。2020年2月4日現在PostgreSQLの最新バージョンは12ですが、ここでインストールされるバージョンは10になります。これはUbuntu18.04が約2年前のOSであることが原因です。最新バージョンを入れる方法もありますが、そこまで凝ったこともしないので、今回はバージョン10のまま使います。

初期設定など

素のPostgreSQLはpostgresという管理ユーザーが一人だけいるパスワード設定のない管理用のデータしか入っていないデータベースになっています。

管理ユーザーで接続

まずはsudoで PostgreSQLの管理ユーザー postgres になります。

user@ubuntu1804:~$ sudo -i -u postgres bash --login
[sudo] user のパスワード: 
postgres@ubuntu1804:~$ 

PostgreSQLに接続します。ここではPostgreSQLサーバーに同梱されているpsqlコマンドを使います。

postgres@ubuntu1804:~$ psql
psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
Type "help" for help.

postgres=# 

この状態でPostgreSQLに接続しています。例えば現在操作しようとしているデータベースの状態を調べるには…

postgres-# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres-# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | 
 template0 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

postgres-# 

psqlコマンドの内部コマンドである、\coninfoで、現在接続しているデータベースを調べています。"postgres"という名前のデータベースに接続しています。ややこしいのですが、PostgreSQLに限らずデーターベースサーバー内には通常複数のデータベースが格納されていて、それぞれに名前がついています。この"postgres"という名前のデータベースは、管理用のデータが入っているデータベースです。

その次に実行している\l内部コマンドが、現在接続しているPostgreSQLサーバー内のデータベース一覧です。全て管理用のデータベースなので、普通のデータは格納しませんし、これらのデータベースは基本的に直接いじりません。

管理ユーザーのパスワード設定

まずはpostgresユーザーのパスワードを設定します。 (パスワード)部分にはご自分の覚えやすいパスワードを設定してください。

postgres-# alter user postgres with password '(パスワード)'
postgres-# 

一般ユーザーとデータベース作成

このままPostgreSQL内のユーザー追加と、データベース追加を行います。
(ユーザー)(パスワード)(ユーザーデータベース名)には自分で名前を入れてください(ユーザー名はUbuntuのユーザー名と一致させてください)。ここではそれぞれuser, password, userdbで実施しています。

postgres=# create user "(ユーザー)" password '(パスワード)';
CREATE ROLE
postgres=# create database "(ユーザーデータベース名)" owner "(ユーザー)";
CREATE DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | 
 template0 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 userdb    | user     | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | 
(4 rows)

postgres=# 

psqlの内部コマンド\lで新しいデータベースuserdbが所有者userで作成されているのが分かります。

なお、今回設定されたパスワード、実は使用しません。ローカル接続のpeer認証を使っているからなのですが、ネットワーク経由で接続する場合に初めて必要になるのですが、今回はネットワーク経由で使用しないということです(SSLクライアント認証などを使用する場合はネットワーク経由でも使いませんけども…)。

これで一旦管理ユーザーでの作業が終わるので、抜けます。psqlからは\qで、sudo bashからはexitで抜けます。

postgres=# \q
postgres@ubuntu1804:~$ exit
ログアウト
user@ubuntu1804:~$ 

一般ユーザーで接続

改めてpsqlコマンドで繋ぎ直します。(ユーザーデータベース名)(ユーザー)には先程作成したものを使用してください。ここではそれぞれuserdb, userとしています。

user@ubuntu1804:~$ psql -d (ユーザーデータベース名) -U (ユーザー)
psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
Type "help" for help.

userdb=>

テーブル作成とデータ追加

何もしないのも寂しいのでサンプル程度のデータを作成します。以下を貼り付けて実行してください。create tableでExcel表のようなテーブルを作成し、insertでデータをテーブルに挿入しています。英語圏の人なら何をしているかこれだけで分かるような文法です。

create table example_table (item_id varchar(14) primary key, item_name varchar(100), kakaku numeric(10,0));
insert into example_table values('4514603365511','アサヒ 十六茶 PET500ml',124);
insert into example_table values('4514603343519','アサヒ 十六茶 (LL)紙パックスリム250ml',80);
insert into example_table values('4514603377019','アサヒ 十六茶 PET2L',350);
insert into example_table values('4514603240719','アサヒ 十六茶 PET275ml',115);
insert into example_table values('4514603377217','アサヒ 十六茶 PET630ml',140);
insert into example_table values('4514603240818','アサヒ 十六茶 ボトル缶275g',115);
insert into example_table values('4514603241112','アサヒ 十六茶 缶245g',86);
insert into example_table values('4514603386110','アサヒ 十六茶ほうじ茶 PET630ml',140);
commit;

実行するとexample_tableが作成されて、中にアサヒ飲料様の尊いお茶のデータが数件作成されます。元データは↓のホームページからお借りしました。

JANコード一覧|商品情報|アサヒ飲料

では作成したテーブルのデータをselectで見てみましょう。

userdb=> select * from example_table;
    item_id    |               item_name               | kakaku 
---------------+---------------------------------------+--------
 4514603365511 | アサヒ 十六茶 PET500ml                |    124
 4514603343519 | アサヒ 十六茶 (LL)紙パックスリム250ml |     80
 4514603377019 | アサヒ 十六茶 PET2L                   |    350
 4514603240719 | アサヒ 十六茶 PET275ml                |    115
 4514603377217 | アサヒ 十六茶 PET630ml                |    140
 4514603240818 | アサヒ 十六茶 ボトル缶275g            |    115
 4514603241112 | アサヒ 十六茶 缶245g                  |     86
 4514603386110 | アサヒ 十六茶ほうじ茶 PET630ml        |    140
(8 rows)

userdb=> 

それっぽいデータが表示されました。十六茶、皆さんも大好きですよね。

まとめ

WindowsのVirtualBox上にインストールしたUbuntu18.04にPostgreSQLをインストールし、簡単なテーブルを作成、表示してみました。

次回はもう一発PostgreSQLをやります。ホスト側から、DBeaverというSQLエディタを使って操作してみます。

未分類

何かと面倒なdockerでpostgresを動かし、SSLで接続した上に、せっかくSSLなのでクライアント認証する、という企画です。

CAと鍵と証明書の作成

privateなnetworkなのでオレオレCAを作成します。

create_cert.sh

#!/bin/bash
set -euo pipefail

# create root CA
openssl req -new -nodes -text -subj "/CN=ca.local" -out ca.csr -keyout ca.key
chmod og-rwx ca.key
openssl x509 -req -in ca.csr -text -days 3650 -extfile /etc/ssl/openssl.cnf -extensions v3_ca -signkey ca.key -out ca.crt
rm ca.csr

# create server cert
openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=server.local" 
chmod og-rwx server.key
openssl x509 -req -in server.csr -text -days 365 -CA ca.crt -CAkey ca.key -CAcreateserial -out server.crt
rm server.csr

# create client certs
openssl req -new -nodes -text -out client1.csr -keyout client1.key -subj "/CN=client1.local" 
chmod og-rwx client1.key
openssl x509 -req -in client1.csr -text -days 365 -CA ca.crt -CAkey ca.key -CAcreateserial -out client1.crt
rm client1.csr

# change owner for alpine postgres
sudo chown 70 server.key
  • .certが証明書、.keyが秘密鍵
  • caがオレオレルートCAで、serverがpostgresが動くサーバ、client1がクライアント用
  • domainはとりあえず.local
  • CA自己署名証明書は10年、他は1年で作成
  • サーバの秘密鍵はalpine上のpostgresイメージのUIDに合わせて70に

postgresをdockerで起動(初回)

docker-compose.yml

version: "3"

services:
  db:
    image: postgres:12-alpine
    container_name: postgres
    command: -c ssl=on -c ssl_ca_file=/var/lib/postgresql/ca.crt -c ssl_cert_file=/var/lib/postgresql/server.crt -c ssl_key_file=/var/lib/postgresql/server.key
    ports: 
      - "5432:5432"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_DB=postgres
      - POSTGRES_PASSWORD=secret
    volumes:
      - ./ca.crt:/var/lib/postgresql/ca.crt:ro
      - ./server.crt:/var/lib/postgresql/server.crt:ro
      - ./server.key:/var/lib/postgresql/server.key:ro
      - ./data:/var/lib/postgresql/data
  • 公式イメージではありません!

起動!

$ docker-compose up

SSLで接続(パスワード認証)

別のコンテナからホストに繋いで確認します。

$ docker run -it --rm postgres:12-alpine psql "host=(ホストIP) user=postgres password=secret"

結果

psql (12.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \q

外部からの認証をSSLクライアント認証のみに設定

サーバーを一度落とす

$ docker-compose down

./data/pg_hba.confの最後を以下に書き換える

#host all all all md5
hostssl all all all cert map=cnmap
  • (ローカルを除く)全IPからのTCPホスト接続をパスワード認証
    → (ローカルを除く)全IPからのTCPホスト接続をSSLクライアント認証(ユーザーマッピングはcnmap)

./data/pg_ident.confに以下を加える

cnmap   /^(.*)\.local$  postgres
  • cnmapという名前で、.localで終わるCNにユーザーpostgresをマッピングするルールを定義

設定確認

起動!

$ docker-compose up

パスワード認証で接続

$ docker run -it --rm postgres:12-alpine psql "host=(ホストIP) user=postgres password=secret"

結果

psql: error: could not connect to server: FATAL:  connection requires a valid client certificate
FATAL:  no pg_hba.conf entry for host "172.20.0.1", user "postgres", database "postgres", SSL off

SSLクライアント認証で接続

$ docker run -it --rm \
-v $PWD/client1.crt:/root/.postgresql/postgresql.crt \
-v $PWD/client1.key:/root/.postgresql/postgresql.key \
-v $PWD/ca.crt:/root/.postgresql/root.crt \
postgres:12-alpine psql "host=(ホストIP) user=postgres"
  • ややこしそうに見えるが、~/.postgresql/にクライアント証明書とクライアント鍵と信頼してるCAの証明書を置いて、ホスト+ユーザー指定で繋いでるだけ

結果

psql (12.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \q

参考リンク