ラズパイNAPT経由でsubnetのPostgreSQLサーバーにアクセスする

## この記事について

図のようなNetworkを作ることになり、ラズパイNAPT(NATサーバー)とラズパイPostgreSQLサーバーを構築しました。
 
構築手順の備忘録として記事を作成します。  

(なお、Pi4BでNATサーバー、Pi3B(+)でPostgreSQLサーバーを立てたのは利用者の都合です)

<img width= 750 src = https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/565396/86e67609-a929-165a-db26-663438b9d9f7.png>


## 環境・設定  

OSイメージ作成時に、NATサーバーとPostgreSQLサーバーに同一の公開鍵を埋め込みます。  

### NATサーバー  

**Raspberry Pi：** Raspberry Pi 4 Model B Rev 1.2  
**OS：** Raspberry Pi OS Lite 64-bit (Debian GNU/Linux 12 (bookworm))  
**hostname：** MyNAT  
**static ip public subnet：** 192.168.1.100  
**static route / dns public subnet：** 192.168.1.1  
**static ip private subnet：** 10.1.1.1
**static route / dns private subnet：** 10.1.1.1  
**NAPT Port：** 11111  

### PostgreSQLサーバー  

**Raspberry Pi：** Raspberry Pi 3 Model B Plus Rev 1.3    
**OS：** Raspberry Pi OS Lite 64-bit (Debian GNU/Linux 11 (bullseye))  
**hostname：** MyPsql  
**DB：** PostgreSQL-15  
**static ip private subnet：** 10.1.1.11  
**PostgreSQL Port：** 5432  


## NATサーバー構築  

### インストール  

```sh
#アップデート
sudo apt update  
sudo apt -y upgrade  

#python関連インストール
sudo apt install -y python3-dev python3-pip 　

#dns、ntp、iptables他インストール
sudo apt install -y dnsmasq ntp iptables iptables-persistent postfix　git

sudo apt update  
sudo reboot
```

### Private subnetのdhcp設定

```sh:/etc/dnsmasq.conf
domain-needed
# Never forward addresses in the non-routed address spaces.
bogus-priv
no-dhcp-interface=wlan0
interface=eth0
dhcp-range=10.1.1.11,10.1.1.20,255.255.255.0,24h
```

自動起動有効化してスタートします。  

```sh
sudo systemctl enable dnsmasq  
sudo systemctl start dnsmasq  
```

### ntp設定

```sh: /etc/ntpsec/ntp.conf
#追加
pool 192.168.1.1 iburst
pool ntp.nict.jp iburst
・
・
・
#アンコメント・追加
restrict default kod nomodify nopeer noquery limited
restrict 10.1.1.0 mask 255.255.255.0 kod nomodify nopeer notrap
```

自動起動有効化してスタートします。  

```sh
sudo systemctl enable ntp  
sudo systemctl start ntp  
```

### eth0 ip固定

Debian12版はNetworkManagerが標準なのでそのまま使用します。  
NetworkManagerが初期設定する"Wired connection 1"が煩わしいので"eth0"に変更します。  

```sh
sudo nmcli connection add type ethernet ifname eth0 con-name eth0 autoconnect yes
sudo nmcli c delete Wired\ connection\ 1
```

Private subnetに接続するeth0のipを固定しipv6は無効化します。  

```sh
sudo nmcli connection modify eth0 ipv4.method manual ipv4.addresses 10.1.1.1/24 ipv6.method disabled
```

### filetr設定  

```sh: 初期化
sudo iptables -F
sudo iptables -X
sudo iptables -P INPUT DROP
sudo iptables -P FORWARD DROP
sudo iptables -P OUTPUT ACCEPT
```
```sh: INPUT
sudo iptables -A INPUT -i lo -j ACCEPT
sudo iptables -A INPUT -d 127.0.0.0/8 ! -i lo -j REJECT --reject-with icmp-port-unreachable
sudo iptables -A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
sudo iptables -A INPUT -s 192.168.1.0/24 -i wlan0 -p udp -m udp --dport 53 -j ACCEPT
sudo iptables -A INPUT -s 10.1.1.0/24 -i eth0 -p udp -m udp --dport 53 -j ACCEPT
sudo iptables -A INPUT -s 192.168.1.1/32 -p udp -m udp --dport 123 -j ACCEPT
sudo iptables -A INPUT -s 10.1.1.0/24 -i eth0 -p udp -m udp --dport 123 -j ACCEPT
sudo iptables -A INPUT -s 192.168.1.0/24 -p tcp -m state --state NEW -m tcp --dport 22 --tcp-flags FIN,SYN,RST,ACK SYN -m hashlimit --hashlimit-upto 1/min --hashlimit-burst 10 --hashlimit-mode srcip --hashlimit-name t_sshd --hashlimit-htable-expire 120000 -j ACCEPT
sudo iptables -A INPUT -s 10.1.1.0/24 -p tcp -m state --state NEW -m tcp --dport 22 --tcp-flags FIN,SYN,RST,ACK SYN -m hashlimit --hashlimit-upto 1/min --hashlimit-burst 10 --hashlimit-mode srcip --hashlimit-name t_sshd --hashlimit-htable-expire 120000 -j ACCEPT
sudo iptables -A INPUT -s 192.168.1.0/24 -p icmp -m icmp --icmp-type 8 -j ACCEPT
sudo iptables -A INPUT -s 10.1.1.0/24 -p icmp -m icmp --icmp-type 8 -j ACCEPT
sudo iptables -A INPUT -m limit --limit 5/min -j LOG --log-prefix "iptables denied: " --log-level 7
sudo iptables -A INPUT -j REJECT --reject-with icmp-port-unreachable
```

```sh: FORWARD
sudo iptables -A FORWARD -i eth0 -o wlan0 -j ACCEPT
sudo iptables -A FORWARD -i wlan0 -o eth0 -m state --state RELATED,ESTABLISHED -j ACCEPT
sudo iptables -A FORWARD -d 10.1.1.11/32 -p tcp -m tcp --dport 5432 -j ACCEPT
sudo iptables -A FORWARD -m state --state RELATED,ESTABLISHED -j ACCEPT
```
ipv4のforward設定を永続化します。  

```sh:/etc/sysctl.conf修正
sudo sed -i "s/#net.ipv4.ip_forward=1/net.ipv4.ip_forward=1/g" /etc/sysctl.conf
sudo sysctl -p
```

### NAT/NAPT設定  

Public subnetでPort 11111にアクセスするとPrivate subnetのPort 5432に転送するNAPTを設定します。

```sh: PREROUTING
sudo iptables -t nat -A PREROUTING -d 192.168.1.100/32 -p tcp -m tcp --dport 11111 -j DNAT --to-destination 10.1.1.11:5432
```

```sh: POSTROUTING
sudo iptables -t nat -A POSTROUTING -o wlan0 -j MASQUERADE
sudo iptables -t nat -A POSTROUTING -d 10.1.1.11/32 -p tcp -m tcp --dport 5432 -j SNAT --to-source 10.1.1.1
```

### filter/NAT/NAPT反映

```sh
sudo service netfilter-persistent reload
sudo service netfilter-persistent save
sudo sh -c "iptables-save >/etc/iptables.ipv4.filter"
sudo sh -c "iptables-save >/etc/iptables.ipv4.nat"
```

### ssh config設定  

NATからsubnetのPostgreSQLがインストールされているhostへのssh接続を簡略化します。  

```conf:./.ssh/config
#login先のhostname
Host MyPsql
  HostName 10.1.1.11
  #自分のusername
  User user
  Port 22
  #自分の秘密鍵のPATH
  IdentityFile ~/.ssh/My_pem.pem
  TCPKeepAlive yes
  IdentitiesOnly yes
```

/.sshに秘密鍵を設置し権限変更します。

```sh
sudo chmod 600 ~/.ssh/My_pem.pem
```

## PostgreSQLサーバー構築  

ラズパイNATサーバーとethernet接続する前にインターネット接続して以下を設定します。

### インストール  

```sh
#アップデート
sudo apt update
sudo apt -y upgrade

#python関連インストール
sudo apt install -y python3-dev python3-pip

#iptables他インストール
sudo apt install -y iptables iptables-persistent postfix git

sudo apt update
sudo reboot
```

### PostgreSQL-16インストール  

~~構築時点(2023.11.17)の~~latest版~~15~~(2024.04.15時点でpgdg.listのlatestは16になります)をインストールしました。Debian11のapt/source.listは最新がPostgreSQL-13なのでlistにリポジトリを追加します。  

```sh
#依存関係インストール
sudo apt install wget curl gnupg2 -y

#ファイルリポジトリ構成作成
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

#リポジトリ署名keyインポート
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

#インストール
sudo apt update
sudo apt -y install postgresql
```

### postgresql.conf設定  

listen addressを開放します。(ip範囲固定の方法が分からなかったので要確認)
```sh
sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '\*'/g" /etc/postgresql/16/main/postgresql.conf
```

### postgres user password変更  

ラズパイのpostgres userのpasswordを変更します。  
```sh
sudo passwd postgres
```

> New password: **your password**  
Retype new password: **your password**  
passwd: password updated successfully  

postgresの中のpostgres userのpassword変更  
"alter role postgres with password 'your password';"でyour passwordを認証するalter roleを作成します。  
```
su - postgres
postgres@MyPsql:~$ psql
・
・
postgres=# alter role postgres with password 'your password';
```

postgresサーバーから出てuserに戻ります。  

```
postgres=# \q
postgres@MyPsql:~$ exit
logout
```

### pg_hba.conf設定  

NATサーバーのsubnetからのアクセスを許可します  。

```sh
echo "host all all 10.1.1.0/24 md5" | sudo tee -a /etc/postgresql/16/main/pg_hba.conf
```

### 設定の反映  

```sh
/etc/init.d/postgresql restart
```
passwordを求められるので変更したパスワードを入力します。

> Restarting postgresql (via systemctl): postgresql.service==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to restart 'postgresql.service'.
Authenticating as: ,,, (user)
Password: your password
==== AUTHENTICATION COMPLETE ===

### eth0 ip固定

Debian11版はdhcpcdが標準なのでそのまま使用します。  

```sh:/etc/dhcpcd.conf
#追加
interface eth0
static ip_address=10.1.1.11/24
static routers=10.1.1.1
static domain_name_servers=10.1.1.1 8.8.8.8
```

ipv6は無効化します。  

```sh
sudo sed -i "s/rootwait/ipv6.disable=1 rootwait/g" /boot/cmdline.txt
```

### filetr設定  

listen addressがanyなのでfilterを設定します。  

```sh: 初期化
sudo iptables -F
sudo iptables -X
sudo iptables -P INPUT DROP
sudo iptables -P FORWARD DROP
sudo iptables -P OUTPUT ACCEPT
```
```sh: INPUT
sudo iptables -A INPUT -i lo -j ACCEPT
sudo iptables -A INPUT -d 127.0.0.0/8 ! -i lo -j REJECT --reject-with icmp-port-unreachable
sudo iptables -A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
sudo iptables -A INPUT -s 10.1.1.1/32 -i eth0 -p udp -m udp --dport 53 -j ACCEPT
sudo iptables -A INPUT -s 10.1.1.1/32 -i eth0 -p udp -m udp --dport 123 -j ACCEPT
sudo iptables -A INPUT -s 10.1.1.1/32 -p tcp -m state --state NEW -m tcp --dport 22 --tcp-flags FIN,SYN,RST,ACK SYN -m hashlimit --hashlimit-upto 1/min --hashlimit-burst 10 --hashlimit-mode srcip --hashlimit-name t_sshd --hashlimit-htable-expire 120000 -j ACCEPT
sudo iptables -A INPUT -s 10.1.1.1/32 -p icmp -m icmp --icmp-type 8 -j ACCEPT
sudo iptables -A INPUT -m limit --limit 5/min -j LOG --log-prefix "iptables denied: " --log-level 7
sudo iptables -A INPUT -j REJECT --reject-with icmp-port-unreachable
```
```sh:設定を反映
sudo service netfilter-persistent reload
sudo service netfilter-persistent save
sudo sh -c "iptables-save >/etc/iptables.ipv4.filter"
```

### timesyncd設定・再起動  

```sh
sudo sed -i "s/#NTP=/NTP=10.1.1.1/g" /etc/systemd/timesyncd.conf
sudo systemctl restart systemd-timesyncd
sudo reboot
```

## 動作確認  

NATサーバーとPostgreSQLサーバーを起動してethernetケーブルで接続し、NATサーバーからPostgreSQLサーバーにsshログインできることを確認します。  

```sh
user@MyNAT:~ $ ssh MyPsql  
```

> Linux MyPsql 6.1.21-v8+ #1642 SMP PREEMPT Mon Apr  3 17:24:16 BST 2023 aarch64  
>  
>The programs included with the Debian GNU/Linux system are free software;  
the exact distribution terms for each program are described in the  
individual files in /usr/share/doc/*/copyright.  
>  
> Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent  
permitted by applicable law.  
Last login: Thu Nov 23 00:22:51 2023 from 10.1.1.1  
user@MyPsql:~ $  

PostgreSQLサーバーでpostgresql.serviceが起動していることを確認します。  

```sh
user@MyPsql:~ $ sudo systemctl status postgresql
```

> ● postgresql.service - PostgreSQL RDBMS  
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)  
     Active: active (exited) since Thu 2023-11-23 00:28:31 JST; 53min ago  
    Process: 25879 ExecStart=/bin/true (code=exited, status=0/SUCCESS)  
   Main PID: 25879 (code=exited, status=0/SUCCESS)  
        CPU: 5ms  
>  
> Nov 23 00:28:31 MyPsql systemd[1]: Starting PostgreSQL RDBMS...  
Nov 23 00:28:31 MyPsql systemd[1]: Finished PostgreSQL RDBMS.   

NATサーバーからpostgresql.serviceへアクセス可能なことも確認します。  

```sh
user@MyNAT:~ $ nc -v -w 1 10.1.1.11 -z 5432 
```

> 10.1.1.11: inverse host lookup failed: Unknown host  
(UNKNOWN) [10.1.1.11] 5432 (postgresql) open  

最後にNATサーバーとPostgreSQLサーバと同一セグメントに接続したWindowsPCからNAPTでPostgreSQLへアクセス可能なことを確認します。  

```Powershell:
PS C:\Users\MyAccount> Test-NetConnection  192.168.1.100  -port 11111
```

> ComputerName     : 192.168.1.100  
RemoteAddress    : 192.168.1.100  
RemotePort       : 11111  
InterfaceAlias   : Wi-Fi 2  
SourceAddress    : 192.168.1.5  
TcpTestSucceeded : True  

ラズパイPrivate subnetに構築したPostgreSQLサーバーにNAPT経由で指定セグメントからアクセスできることが確認できました。  

## 参考文献  

*  [https://www.postgresql.org/](https://www.postgresql.org/)  
*  [Debian11にPostgres15をインストールし、nfsしてマウントしたHDDにバックアップをとる方法](https://techlive.tokyo/archives/13304)  
*  [IoTセンサーデータをPostgreSQLに保存してPower BIで見る](https://www.system-exe.co.jp/iot04/)  
*  [PostgreSQL クライアント接続ツール pgAdmin 4 をインストールする (Windows 版)](https://sorceryforce.net/ja/tips/postgresql-install-pgadmin4-windows)