さくらVPS設定その２：PostgreSQLの導入.md

さくらVPS設定その２：PostgreSQLの導入.md

さくらVPS設定手順書シリーズ

1. さくらVPS設定その1:Debian 9をインストールしてからsudo、root、SSHを設定するまで.md
2. さくらVPS設定その2:PostgreSQLの導入.md
3. さくらVPS設定その3:Nginxの導入.md

PostgreSQL

まえがき: なぜ$ apt install postgresqlで完了しないのか？

Debianのパッケージには、公式Debianディストリビューションに含まれているものと含まれていないものがある。 その場合、ユーザーが指定してリポジトリを登録しなければならない。 今回Debian 9に追加したPostgreSQL 11は作業時現在（2019年6月頃）標準でサポートされていなかったので、これに該当した。

Debian/パッケージ

PostgreSQL 11のAPT repository追加

リポジトリ署名キーインポート

$ sudo apt install -y vim wget

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

OK

Debianにリポジトリの内容をコピー

$ RELEASE=$(lsb_release -cs)
$ echo "deb http://apt.postgresql.org/pub/repos/apt/ ${RELEASE}"-pgdg main | sudo tee  /etc/apt/sources.list.d/pgdg.list

deb http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main

リポジトリファイルの確認

$ cat /etc/apt/sources.list.d/pgdg.list

deb http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main

DebianにPostgreSQL 11をインストール

• aptで作業する前は常にapt update

$ sudo apt update

$ sudo apt -y install postgresql-11

リモートアクセスを有効に

• ※5432はデフォルトポート番号

$ sudo ss -tunelp | grep 5432

tcp    LISTEN     0      128    127.0.0.1:5432                  *:*                   users:(("postgres",pid=11036,fd=6)) uid:107 ino:26086 sk:2 <->
tcp    LISTEN     0      128     ::1:5432                 :::*                   users:(("postgres",pid=11036,fd=3)) uid:107 ino:26085 sk:4 v6only:1 <->

リスニングアドレス変更

$ sudo vim /etc/postgresql/11/main/postgresql.conf

• CONNECTIONS AND AUTHENTICATIONの下にlisten_addresses = '*'追加

PostgreSQL再起動

$ sudo systemctl restart postgresql

Confirm the new PostgreSQL bind address:

$ sudo ss -tunelp | grep 5432

tcp    LISTEN     0      128       *:5432                  *:*                   users:(("postgres",pid=11108,fd=3)) uid:107 ino:27017 sk:5 <->
tcp    LISTEN     0      128      :::5432                 :::*                   users:(("postgres",pid=11108,fd=6)) uid:107 ino:27018 sk:6 v6only:1 <->

postgres のパスワード変更

$ psql -c "alter user postgres with password '[指定したいのパスワード]'"

ALTER ROLE

機能のテスト

• ユーザ作成

$ createuser [テスト作成のユーザ名]
$ createdb test_db -O [テスト作成のユーザ名]

$ psql -l  | grep test_db
 test_db   | [テスト作成のユーザ名] | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

• データベースに入る

$ psql test_db

• ユーザのパスワード設定

$ alter user [テスト作成のユーザ名] with password '[テスト作成のユーザパスワード]';

ALTER ROLE

$ create table test_table ( id int,first_name text, last_name text );
$ insert into test_table (id,first_name,last_name) values (1,'John','Doe');
$ select * from test_table;
$ DROP TABLE test_table;
$ ¥q
$ dropdb test_db;
$ dropuser [テスト作成のユーザ名]

# ユーザーの削除確認
# ※不要なユーザが残っていると脆弱性に繋がる
$ psql -c 'select usename from pg_user;'

参考

• PostgreSQL
  • https://computingforgeeks.com/how-to-install-postgresql-11-on-debian-9-debian-8/