PostgreSQL設定などいろいろと

[JDBC] [PostgreSQL SSL]

PostgreSQLな情報をちびちびとメモするところ。

JDBCについては公式を。SSLについては再度書き直すかな?

インストール

パッケージ化されているものもありますが、各種いろいろ。罠もあるかも。まとまったら別ページに移行予定。

Raspbian (Raspberry Pi)での設定をBlogに書いてみた。
初期設定の変更点
データベースの初期化(自動でされたが照合順序がまずいので再作成がおすすめかも)
SSLの設定

サーバ認証
クライアント認証

JDBCの設定

こんな感じのが必要かな。

Raspberry Piの場合

うろおぼえ RaspberryPi Zero Wで試したものの気がする(blogからの転載)

# apt-get install postgresql

ユーザ postgres が作られるので事前に作っていたりした場合は先に消す。パスワードは設定しない方がいい。

ルートな初期設定

実行ファイル系 /usr/lib/postgresql/13/bin
初期設定?ファイル系 /usr/share/postgresql/13
設定ファイル系 /etc/postgresql/13/main
データ系 /var/lib/postgresql/13/main

PGDATA で指定可能なのはデータ系。環境変数で指定しない場合は intdb とサーバ起動の時に指定する。

# systemctl stop postgresql

とりあえずサーバが動いていたら止める。

# su - postgres

postgres ユーザで作業。

postgres$ rm -rf /var/lib/postgresql/13/main

そのままだと文字コード(照合順序、Ctype(変換演算子))がよろしくない日本語系(ja_JP.UTF-8)になるので、ふらっとなもの(C)に変えたい。既にデータがある場合は先に削除。

いろいろオプションがあるのよ。

postgres$ /usr/lib/postgresql/11/bin/initdb -D /var/lib/postgresql/11/main -A md5 -E utf8 --lc-collate=C --lc-ctype=C -W

-A 認証方式
--auth-host=md5 Javaから使うのでscram-sha-256、md5かcertくらいにする。scram-sha-256が使えるのかどうかは謎。あとから変更可。
-D データ位置
-E デフォルト符号化方式 UTF8
--locale=C
--lc-collate=C
--lc-ctype=C
--lc-messages=ja_JP.UTF-8
--lc-monetary=ja_JP.UTF-8 または C
--lc-numeric=ja_JP.UTF-8 または C
--lc-time=ja_JP.UTF-8 または C
-W パスワード手入力

認証方式もついでに指定しておく。lc-collateとlc-ctypeをCにしておくのが重要。通貨(lc-monetary)や時間(lc-time)の方はお好みで。

$ vi /etc/postgresql/13/main/pg_hba.conf

今どきはIPv6にも対応しているのでIPv4、IPv6それぞれ適切なアドレス(グローバルではなくリンクローカルアドレス(fe80::XXXXがいい)を許可する。昔のように-iみたいなオプションはないらしい。ident とか付いているとリモート接続ができなかったり面倒なので全部はずしてmd5推奨かな。md5はsha-256にも対応していて、scram-sha-256がsha-256専用らしい。
詳細は略。

md5からscram-sha-256への移行

postgresql.conf の password_encryption を md5 から scram-sha-256 に変更するコメントアウトされていたら外す
パスワードを変更したときの保存方法が変わる
systemctl restart postgresql
psql でまだログインできるはず
パスワードを変更する ALTER USER current_user WITH PASSWORD 'newpassword'; または \password
全アカウント変更したら pg_hba.conf の md5 を scram-sha-256 に変更する (変更しなくても自動的に判別はしてくれる)
systemctl restart postgresql か systemctl reload postgresql
ログインできることを確認するできなければ pg_hba.conf の設定をmd5に戻してみるなど

つづき

# ifconfig

ifconfig でIPv6アドレスなどを確認する

$ vi /etc/postgresql/13/main/postgresql.conf

こちらは listen_address、portなどを適切に設定する。ここでIPv6はリンクローカルアドレスの方を指定したいが何故か繋がらないのでグローバルので設定する。SSLな例は気が向いたらしてもいいかな。

listen_address = 'localhost,2xxx::xxxx:xxxx:xxx:xxxx'

サーバ用の鍵ペア(秘密鍵、公開鍵証明書)、クライアントの鍵ペア(秘密鍵、公開鍵証明書)を作れば簡単かもしれない。

postgres$ exit

root# systemctl start postgresql

起動して完了かな。

# systemctl enable postgresql

などとすると自動実行可能な状態になるようで手動の起動と停止もsystemctlで可能

# systemctl start postgresql
# systemctl stop postgresql

service を使っている頃は次のような感じだった

# service postgresql start
# service postgresql stop

ここから先はpostgresユーザで初期設定など

# su - postgres (一般ユーザでもいい)
postgres@raspi:~ $ psql -version
psql (PostgreSQL) 13.x (Respbian 13.x-x)

postgres@raspi:~ $ psql -U postgres -h localhost
postgres=# \l

パスワードログインする場合はホスト名指定するか、pg_hba.confの最後の方を削るか、かな。

でデータベースの一覧が出てきます。

template0、template1の照合順序、Ctype(変換演算子)がCになっているのを確認します。qで一覧から抜けます。

使う場合はcreate database か $ createdb か、適度にデータベースを作りましょう。

postgres=# \q

[JDBC] [SSL]