Heroku Posgres へ pg8000 に接続しようとしてどハマリした話

序文

Heroku Postgres と pg8000 のイケていない部分が負のシナジー効果を生み出した結果大変苦労した話です。

本文

Heroku Postgres について

Heroku Postgres は ポートフォリオ作成用サーバ御用達となって久しい Heroku が公式に用意している PosgreSQL のアドオンサーバーです。
よく使用されるClearDB, JawsDB と同じく無料プランと、スケーラブルが容易な有料プランに分かれています。
今回は無料プランに関するお話です。（有料プランも同じかもしれない）

pg8000 について

pipでも配布されている PostgreSQL　用接続ドライバモジュールです。
pythonでPostgreSQLに接続するモジュールは "psycopg2" あたりがよく使用されている印象ですが、このモジュールはプレースホルダの文字列が %s 固定で、一般的な ? が使用できないという少なくとも私にとっては致命的な欠点があり、デフォルトのプレースホルダ文字がpsycopg2と同じ%sだけど設定で ? にも変更できるpg8000の方が他データベースとの互換性（特に標準添付のsqlite3）を考えると使い勝手が良いなと感じています。

Heroku Postgres のSSL接続

Heroku Postgres は接続にSSLの利用が強制されます。これはHeroku PostgresがHeroku内部でしか利用できないわけではなく、外部サービスからもインターネットを通して利用できるため、できるだけデータの盗聴を防ぐための普遍的な仕組みです。
（なのでherokuにデプロイする前にデータベースだけHeroku Postgresに切り替えてテストすることも可能です）

SSLを使用するにはサーバー側にサーバー証明書が必要です。この証明書でサーバーの存在証明と通信の暗号化をおこなっています。通常、サーバー証明書は権威あるルート認証局から有料で発行してもらいます（無料で発行してくれる機関もあります）。SSLを利用する各サーバーはルート認証局からそのサーバー（ドメイン）の存在証明という後ろ盾をもらってサービスを行なっています。
ただ、まだリリース前のシステムに有料で（有効期限もある）サーバー証明書を発行するのはコスト的にも厳しいので、自分自身がルート認証局になってサーバー証明書を発行することがシステム開発時には多いです。そのような証明書を自己署名証明書(self signed certificate)と呼びます。

で、Heroku Postgresに使用されているSSLのサーバー証明書はなぜかこの自己署名証明書です。なんでや。

自己署名証明書の問題点

言わずもがな、「そのサーバーが第３者によって存在証明されていない」ことです。後ろ盾がないのです。
ゆえに、オレオレ詐欺に準えて「オレオレ証明書」なんて呼ばれていたりします。
この記事を書いている2022年時点では、このような自己署名証明書でSSL通信しようとするWebサイトにブラウザで閲覧しようとすると、ブラウザ側は無効な証明書という扱いで接続を拒絶したりします。
警告を無視して無理やり閲覧できるブラウザと、そうでなく完全に拒絶するブラウザがあります。

ブラウザだけでなく、通り一遍のプログラミング言語でも現在はHTTP/HTTPSによる通信機能が組み込まれています。
こちらもブラウザと同様に自己署名証明書は通常無効な証明書として通信を拒絶する設定になっていることがほとんどだと思います。
ただし、事前に自己署名証明書でも接続可能にするようなオプションを各言語とも実装していると思います。pythonもそういったことが可能です。

実際に接続してみる

それでは、ここでpythonでよく利用されるpsycopg2とpg8000の両モジュールで実際にHeroku Postgresサーバに接続してみます。

以下が実際に実行するサンプルコードです。
双方とも公式ドキュメント等を参考に、SSL通信を有効にして接続してみます。

なお、検証にあたってはpsycopg2をM1 Macにインストールする作業が最も大変でした。M1 Mac対応はまだまだ道半ばです。

参考：
psycopg2側： https://devcenter.heroku.com/ja/articles/heroku-postgresql#connecting-in-python
pg8000側: https://github.com/tlocke/pg8000#connect-to-postgresql-over-ssl

環境：MacBook Air M1 MacOS / Monterey 12.2.1 / python 3.9.12

psycopg2検証コード

psycopg2

import psycopg2
import os
import ssl

databaseUrl = os.getenv('DATABASE_URL')
conn = psycopg2.connect(databaseUrl, sslmode='require')
cur = conn.cursor()
cur.execute("select 'ok' as status")
result = cur.fetchall()
for row in result:
    print(row)
cur.close()
conn.close()

psycopg2はherokuの環境変数をそのままconnect関数に投入してもちゃんと接続してくれるようです。とても楽ちん。公式ドキュメントにはどこにも書かれてない方法のようだったけど。
sslmodeパラメータでssl接続を強制します。

結果

('ok',)

正常に接続できました。

pg8000側検証コード

pg8000

import pg8000
import os
import re
import ssl

params = re.findall(r'postgres://(.*):(.*)@(.*):(.*)/(.*)',os.getenv("DATABASE_URL"))[0]
user = params[0]
password = params[1]
host = params[2]
port = params[3]
database = params[4]
    
conn = pg8000.Connection(
    user=user,
    password=password,
    host=host,
    port=port,
    database=database,
    ssl_context=True
)
cur = conn.cursor()
cur.execute("select 'ok' as status")
result = cur.fetchall()
for row in result:
    print(row)
cur.close()
conn.close()

pg8000はpsycopg2のようにDATABASE_URLをそのままconnectionに投入できないので、正規表現で各パラメータに分解しています。

参考：【heroku】環境変数から接続情報を取得しよう【MySQL】

実行結果

Traceback (most recent call last):
  File "/Users/haruyan/Desktop/git/pg8000-demo-heroku/connectiontest_pg8000.py", line 16, in <module>
    conn = pg8000.Connection(
  File "/Users/haruyan/Desktop/git/pg8000-demo/venv/lib/python3.9/site-packages/pg8000/legacy.py", line 442, in __init__
    super().__init__(*args, **kwargs)
  File "/Users/haruyan/Desktop/git/pg8000-demo/venv/lib/python3.9/site-packages/pg8000/core.py", line 255, in __init__
    self._usock = ssl_context.wrap_socket(self._usock, server_hostname=host)
  File "/opt/homebrew/Cellar/python@3.9/3.9.12/Frameworks/Python.framework/Versions/3.9/lib/python3.9/ssl.py", line 500, in wrap_socket
    return self.sslsocket_class._create(
  File "/opt/homebrew/Cellar/python@3.9/3.9.12/Frameworks/Python.framework/Versions/3.9/lib/python3.9/ssl.py", line 1040, in _create
    self.do_handshake()
  File "/opt/homebrew/Cellar/python@3.9/3.9.12/Frameworks/Python.framework/Versions/3.9/lib/python3.9/ssl.py", line 1309, in do_handshake
    self._sslobj.do_handshake()
ssl.SSLCertVerificationError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self signed certificate (_ssl.c:1129)

最終行のエラーメッセージが核心です。 certificate verify failed: self signed certificate　つまり自己署名証明書のため検証エラーが発生した、と書かれています。
psycopg2ではSSLを有効にするだけで接続できたのに。pg8000はそのあたりをよしなにしてくれないようです。

pg8000での自己署名証明書検証エラーの回避方法

公式ドキュメントにも方法が記載されていませんし、google検索しても全くと言っていいほどヒットしませんが、以下のようにssl_contextを設定することにより自己署名証明書検証エラーを回避できます。

pg8000改良版

import pg8000
import os
import re
import ssl

params = re.findall(r'postgres://(.*):(.*)@(.*):(.*)/(.*)',os.getenv("DATABASE_URL"))[0]
user = params[0]
password = params[1]
host = params[2]
port = params[3]
database = params[4]
+sslContext = ssl.create_default_context()  # SSL接続情報の作成
+sslContext.check_hostname = False          # ホスト名の検証をoffにする
+sslContext.verify_mode = ssl.CERT_NONE　    # 証明書の検証をoffにする。オレオレも期限切れも関係なし。
    
conn = pg8000.Connection(
    user=user,
    password=password,
    host=host,
    port=port,
    database=database,
-    ssl_context=True
+    ssl_context=sslContext　　　　# ssl_context引数に先ほど作成したSSL接続情報を代入
)
cur = conn.cursor()
cur.execute("select 'ok' as status")
result = cur.fetchall()
for row in result:
    print(row)
cur.close()
conn.close()

実行結果

['ok']

無事（？）接続することができました。

おわりに

pg8000でHeroku PostgresのようなSSL必須かつ証明書が自己署名されているようなサーバに対しての接続方法を確立することができました。
同じような件で困っている他の方の助力に少しでもなればと思います。

え、psycopg2で十分？　だからプレースホルダが（以下略）