Synology DS-216Jのnginx+phpから標準インストール済PostgreSQLを使う

Synology DS-216Jには、「Hyper Backup」「Web station」「Synology Photo」（「Node.js v12」「PHP7.4」「PHP 7.0」）「Phyton2」を追加でインストールしている。PHP 7.4/7.0は、Synology Photoをインストールした時に依存パッケージとしてインストールされた記憶がある。Phyton2は、何かのスクリプトを動かすのにインストールした記憶。
Synology DS-220Jは、「Hyper Backup」「Hyper Backup Vault」「Web station」「PHP7.4」「Phyton2」を追加インストールしてある。PHP 7.4とPyton2は、DS-216Jに合わせた記憶がある。
両機種共に標準でHTTPサーバーにnginx、CGI用スクリプトにPHP、SQLデータベースにPostgreSQLがインストールされている。
磁気ディスク状態の警告を受けたため2021年11月にDS216JからDS220Jへ移行した。データ移行後磁気ディスクからの警告が出なくなったのでHTTPサーバーとして利用している。このHTTPサーバーは、Let's Encryptからサーバー証明書を得て、MyDNSのDynamicDNSにIPv4（Aレコード）とIPv6（AAAAレコード）を登録し、PPPoE IPv4アドレスとNGN IPv6 IPoE経由でアクセスが出来る。
同窓会の「出欠確認受付」をnginx+php+postgresqlを使いこのサーバーで受けるよう設定してみた。

（1）nginx/php/postgresqlのバージョン

DS-216J DSM 7.1.1-42962 Update 5
$ nginx -v
nginx version: nginx/1.20.1
----
$ php -v
PHP 7.3.3 (cli) (built: Oct  7 2021 06:18:21) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.3.3, Copyright (c) 1998-2018 Zend Technologies
--
$ php74 -v
PHP 7.4.30 (cli) (built: Oct 13 2022 15:45:32) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
----
$ psql -V
psql (PostgreSQL) 11.11

DS-220J DSM 7.2-64570 Update 1
$ nginx -v
nginx version: nginx/1.23.1
----
$ php -v
PHP 8.1.9 (cli) (built: Dec  6 2022 02:51:27) (NTS)
Copyright (c) The PHP Group
Zend Engine v4.1.9, Copyright (c) Zend Technologies
--
$ php74 -v
PHP 7.4.33 (cli) (built: May  3 2023 10:56:08) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
----
$ psql -V
psql (PostgreSQL) 11.11

（2）postgreSQLの設定
「Synology DS215jをWebサーバーにする」を参考にさせて頂いた。
ただ、ネットワーク経由でのアクセスなのとローカルネットワーク上のホストからのアクセスを信頼許可している。
Synologyのアプリは、peer認証を使っている。DS-216J内からだけpostgreSQLを使うのでpeer認証で設定してみた。

(A)ユーザアカウントでsshログインが出来るよう「コントロールパネル」「ユーザーとグループ」「グループ」「administrators」にユーザーアカウントを追加する（sshが利用できる前提）。

(B)データベースとデータベースアカウントを作る
DB名： YAMAZAKIJH
DBユーザ名：yamazaki

ssh user@ds216j
user@ds216j:~$ sudo -s
Password: -user's password-
root@ds216j:~# su - postgres

---- SQLデータベース用アカウントを作る
postgres@ds216j:~$createuser -P yamazaki
Enter password for new role: himitsunokotoba
Enter it again: himitsunokotoba

---- SQLデータベースを作る
postgres@ds216j:~$ createdb -O yamazaki -E UTF8 -T template0 --lc-collate=ja_JP.UTF8 --lc-ctype=ja_JP.UTF8 YAMAZAKIJH

---- SQLデータベースを確認
postgres@ds216j:~$psql
psql (11.11)
Type "help" for help.

postgres=# \du yamazaki
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------
 yamazaki  |            | {}

postgres=# \l "YAMAZAKIJH"
                               List of databases
    Name    |  Owner   | Encoding |  Collate   |   Ctype    | Access privileges 
------------+----------+----------+------------+------------+-------------------
 YAMAZAKIJH | yamazaki | UTF8     | ja_JP.UTF8 | ja_JP.UTF8 | 
(1 row)

（3）postgreSQLのpeer認証
peer認証用のmapをmap名pg_yamazakiと定義する。

/etc/postgresql/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   YAMAZAKIJH      yamazaki                                peer map=pg_yamazaki
local   all             postgres                                peer map=pg_root
local   all             all                                     peer

ident名とpostgreSQLユーザ名をmap名定義で一致させる

/etc/postgresql/pg_ident.conf
PNAME     IDENT-USERNAME    PG-USERNAME
pg_root       root              postgres
pg_root       postgres          postgres
pg_yamazaki   root              yamazaki
pg_yamazaki   user             yamazaki
pg_yamazaki   http              yamazaki

「http」は、phpスクリプトがnginx配下でpostgreSQLをアクセスする時のユーザ名/グループ名

（3）nginx+phpでpostgreSQLテスト
nginxからphpのcgiにアクセスし、postgreSQL接続とアクセスを確認する

testdb.php
<?php
 $con=pg_connect(" dbname=YAMAZAKIJH user=yamazaki");
	echo $con . '';
	$result=pg_query($con, "select * from pg_stat_activity");
	var_dump(pg_fetch_all($result));
	pg_close($con);
?>

（4）Table生成とデータ取込み

addressテーブルの生成
YAMAZAKIJH=> create table address(uid serial,no numeric,kana varchar(16),name varchar(16),maden varchar(4),class char(1),memo text,postal char(8),address varchar(64),phone varchar(13),facsimile varchar(13),mobile varchar(13),email varchar(48),email2 varchar(48));
CREATE TABLE

addressテーブルの確認
YAMAZAKIJH=> \d
               List of relations
 Schema |      Name       |   Type   |  Owner   
--------+-----------------+----------+----------
 public | address         | table    | yamazaki
 public | address_uid_seq | sequence | yamazaki
(2 rows)

YAMAZAKIJH=> \d address
                                     Table "public.address"
  Column   |         Type          | Collation | Nullable |               Default                
-----------+-----------------------+-----------+----------+--------------------------------------
 uid       | integer               |           | not null | nextval('address_uid_seq'::regclass)
 no        | numeric               |           |          | 
 kana      | character varying(16) |           |          | 
 name      | character varying(16) |           |          | 
 maden     | character varying(4)  |           |          | 
 class     | character(1)          |           |          | 
 memo      | text                  |           |          | 
 postal    | character(8)          |           |          | 
 address   | character varying(64) |           |          | 
 phone     | character varying(13) |           |          | 
 facsimile | character varying(13) |           |          | 
 mobile    | character varying(13) |           |          | 
 email     | character varying(48) |           |          | 
 email2    | character varying(48) |           |          | 

テーブルにデータをインポート
YAMAZAKIJH=> \copy address(no,kana,name,maden,class,memo,postal,address,phone,facsimile,mobile,email,email2) from '/volume1/homes/user/address.csv' with encoding 'UTF8' csv Header;
COPY 143

CSVファイルは、Excellで作成。保存時にファイルの種類を「CSV UTF-8（コンマ区切り）で書き出したファイルをインポートした。postgreSQLで書き出したUTF-8のcsvは、データ・外部データの取り込みで取り込む。

addressテーブルのuidにunique制約を付加する
YAMAZAKIJH=> alter table address add unique (uid);
ALTER TABLE
YAMAZAKIJH=> \d address
                                     Table "public.address"
  Column   |         Type          | Collation | Nullable |               Default                
-----------+-----------------------+-----------+----------+--------------------------------------
 uid       | integer               |           | not null | nextval('address_uid_
seq'::regclass)
 no        | numeric               |           |          | 
 kana      | character varying(16) |           |          | 
 name      | character varying(16) |           |          | 
 maden     | character varying(4)  |           |          | 
 class     | character(1)          |           |          | 
 memo      | text                  |           |          | 
 postal    | character(8)          |           |          | 
 address   | character varying(64) |           |          | 
 phone     | character varying(13) |           |          | 
 facsimile | character varying(13) |           |          | 
 mobile    | character varying(13) |           |          | 
 email     | character varying(48) |           |          | 
 email2    | character varying(48) |           |          | 
Indexes:
    "address_uid_key" UNIQUE CONSTRAINT, btree (uid)

upsertの確認
YAMAZAKIJH=> select uid,name,memo from address where uid=116;
 uid |    name    | memo 
-----+------------+------
 116 | 山崎　太郎 | 
(1 row)

YAMAZAKIJH=> insert into address (uid,memo) values (116,'連絡先非公開') on conflict (uid) do update set uid=116,memo='連絡先非公開';
INSERT 0 1
YAMAZAKIJH=> select uid,name,memo from address where uid=116;
 uid |    name    |     memo     
-----+------------+--------------
 116 | 山崎　太郎 | 連絡先非公開
(1 row)

YAMAZAKIJH=> insert into address (uid,memo) values (116,'') on conflict (uid) do update set uid=116,memo='';
INSERT 0 1
YAMAZAKIJH=> select uid,name,memo from address where uid=116;
 uid |    name    | memo 
-----+------------+------
 116 | 山崎　太郎 | 
(1 row)

（5）phpからPostgreSQLをアクセス