【VirtualBox】開発環境を作る – PostgreSQL編 【CentOS】
こんにちは、なかだ（@newNakata）です。
前回（【VirtualBox】開発環境を作る – MySQL編 【CentOS】）の投稿と同じリレーショナルデータベース管理システム（RDBMS）のPostgresについての投稿です。
1つのサーバに対して1つ以上のDBが必要と言うわけではなく、あくまでもmysqlと違う、同じ扱いのシステムを入れると言う趣旨になっています。
本稿ではPostgres11をパッケージインストールしていきます。
構成
Virtual Box 6.1.16CentOS（Linux）Apache（HTTPD)MySQL（MariaDB）- PostgreSQL ← 本稿はここ
- PHP
PostgreSQLとは
オープンソースのリレーショナルデータベースシステムの1つです。
「ん？」って思いますよね。僕もです。
これは前の記事でも書きましたが、同じ考え方なので再度載せておきます。
データベースとは、SQL（構造化クエリ言語）を使ってデータを容易に操作できるようにまとめて構造化したものです。
大雑把に言うと、データを管理できれる入れ物です。
リレーショナルとは、全てのデータを1つの大きな入れ物に纏めるのではなく、データを複数のテーブルと呼ばれる個別の入れ物に分割して管理する方法を言います。
なので、データベースと言う大きな入れ物にテーブルと言う小さい入れ物が沢山あって、各小さな入れ物に対してクSQL（クエリ）を発行してデータを出し入れする事ができるシステムがデータベースです。
PostgreSQLについて
以下postgres（ポストグレス）と言います。
PostgreSQLのバージョンですが日本PostgreSQLユーザ会ニュースページに8月16日付けで投稿がされています。
| 13.4 |
| 12.8 |
| 11.13 |
| 10.18 |
| 9.6.23 |
各系統でマイナーバージョンリリースがされているようです。
本稿では僕が直近で扱ったPostgres11.13を採用したいと思います。
準備
インストール済postgresがあれば削除
本ブログ通りの作成であればインストール済のpostgresは無いため不要です。
そうで無い場合は以下のコマンドで削除してください。
rpm -qa | grep postgres
yum remove -y postgresql postgresql-libs postgresql-serverPostgresの開発パッケージの為のリポジトリ追加
この度はpostgres開発パッケージ（postgres11-devel）も一緒にインストールしようと考えています。
僕の周りだけかもしれませんが、postgresをインストールする時に一緒にdevel（postgres開発パッケージ）もインストールされているのですが、本体は11系を入れているのに、develは既存（9系）のパッケージが入っていたりします。
コマンドを叩く時など面倒なので、develも本体と同じバージョンを入れたいです。
ただし、2020年5月リリースの11.8または12.3から devel が OS 標準の yum レポジトリに含まれていない llvm関連のパッケージに依存する様になりました。
その為、develを入れる前にEPEL、SCL レポジトリを追加してこの問題に備えます。
本ブログ通りの作成であれ EPEL、SCL レポジトリ共に未インストールですが、既に入っている場合は再度インストールする必要はありません。
以下のコマンドを入力してインストールします。
yum install -y epel-release centos-release-scl以下のような感じで完了します。
/etc/yum.repos.d/ 以下にファイルが追加されています。
これで準備は完了です。
パッケージインストール
Postgres公式yumレポジトリ追加
※リポジトリについては【VirtualBox】開発環境を作る – Apache編 【CentOS】のパッケージインストールとはに関連情報があります。
まず最初にyum searchでpostgresパッケージの検索を行います。
パッケージがヒットするのでバージョンを調べてみましょう。
yum search postgres
yum info postgresql
古過ぎない？
このままでは11.13はパッケージインストールできないため、postgres の yum レポジトリ（公式）を以下のコマンドで追加します。
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm以下のような感じで完了します。
/etc/yum.repos.d/ 以下にファイルが追加されています。
追加されたファイルを cat コマンドなどで見てみると、common、9.6～13までのレポジトリ情報が記載されているのがわかります。
この度はpostgres11を対象としているので、postgres11の詳細なバージョンを以下のコマンドで確認してみましょう。
yum info postgresql11
postgres11は現時点で最新ですね。
インストールと起動設定
早速インストールします。
yum install -y postgresql11-server postgresql11-contrib postgresql11-devel以下の画像のような表示が出ると完了です。
ではパッケージがインストールされているか確認してみましょう。
rpm -qa | grep postgres
ちゃんとインストールされていますのでインストールはこれで完了です。
加え、パッケージインストールなので以下も追加されています。
- /etc/group に psotgresグループ
- /etc/passwd に postgres ユーザー
- /var/lib/pgsql にDB情報などの関連ファイル
次に、次回 vm 起動時にpostgresが自動で起動するよう設定します。
httpdでは行い、mysqlではパッケージインストール時に登録されていましたが、postgresではhttpd同様に設定する必要があります。
systemctl enable postgresql-11.service
systemctl list-unit-files | grep postgres1行目で自動起動登録、2行目でその確認のコマンドになります。
enabled になったので自動起動設定完了です。
データベースクラスタの作成
初期化と言う表現で記載しているサイトもあり、データベースクラスタはpostgres独自の用語です。
簡単に言うとデータベースの格納領域を作成します。
以下のコマンドを入力します。
export PGSETUP_INITDB_OPTIONS="--encoding=UTF-8 --no-locale"
/usr/pgsql-11/bin/postgresql-11-setup initdb1行目で initdb のオプションを設定します。
文字コードを utf-8 にし、locale（ロケール）機能は使用しません。
–no-locale は –locale=C でも同じ動きになります。
ロケールは各地域言語に応じた処理をしてくれるOSの仕組みで、日付や通貨の表示処理、文字列の並び替えの順序などで使用するのですが、これを使用すると期待通りの動きにならない事があります。
2行目でDBクラスタを作成しています。
上画像の様に OK が出れば完了です。
/var/lib/pgsql/11/data の下に情報が追加されています。
余談ですが、psql の直下にできるバージョンフォルダ（上記であれば11）の下ではなく /var/lib/pgsql 直下にdataフォルダを作成するため、起動ファイル（/usr/lib/systemd/system/postgresql-11.service）を編集しデータベースクラスタを作成している例も見られますが、それが絶対ではないので違いに注意が必要です。
慣れている方であればいいのですが、初期状態で煩わしさを感じないのであれば本稿の作成方法でよいと思います。
では早速起動してみましょう。
systemctl start postgresql-11このコマンド入力よるレスポンスの表示はありませんので以下のコマンドで起動確認します。
systemctl status
起動が確認できました。
最後にデータベースクラスタ（つまり初期DB）を確認してみましょう。
su - postgres
psql -l
結論からお伝えすると、初期DBは実際にDBとして使用する事はなく、削除もできません。
なので気にせずほっときましょう。
以下に詳しく解説します。
postgres
ユーティリティやユーザーなどの初期DBとして用意されていますが、実際にDBとして使用する事はありません。
psql コマンドで接続した場合はこのDBに接続されます。
postgresに接続して作業を行いたい場合はこのDBに接続する事になります。
言い換えると接続したい場合はユーザーに紐づいたDBが必要になります。
templete0
データベースのテンプレートのことで、新しくDBを作る時のコピー元DBになります。
template0はシステム標準のオブジェクトのみが含まれるテーブルで、更新などはできません。
template0からデータベースを作る場合は作成時に指定する必要があります。
template1
これもtemplate0同様にデータベースのテンプレートです。
template0とは違いDB作成時にtemplateを指定しない場合はこちらのtemplate1から作成（コピー）されます。
ですので、新しいDBに常に入れておきたい関数などをtemplate1に作っておけば、以降に作成されるDBに反映されるため便利です。
初期設定
postgres ユーザー（linuxユーザー）の初期パスワードを設定
これは Linux（OS）ユーザーの設定です。
本ブログの通りの作業の場合 root ユーザー 以外のユーザー作成は解説していないため、絶えず root ユーザーで作業を行っていると思います。
「ローカル環境だから」と言う理由で root ユーザー以外は作成していませんが、本来は権限があるユーザーしか root ユーザーになれません。
インストール時に自動で作成され、データベースの管理ユーザーになっている postgres ユーザーですが、パスワードは未設定になっています。
このままでは root ユーザー以外 postgres ユーザーになれません。
今後 root ユーザー以外での作業時、アカウントロック状態でユーザーをスイッチできな事を防ぐため postgres ユーザーのパスワードを設定しておきます。
ローカル環境ですので、パスワードを postgres （ユーザーと一緒）にします。
passwd postgres
含まれていると言うか、一緒なんですけどね。
コマンド後は入力を求められるのでパスワードを入力してください。
これでパスワードの設定は完了です。
postgres ユーザー（PostgreSQLユーザー）の初期パスワードを設定
これは PostgreSQL（データベース）ユーザーの設定です。
postgresに接続はもちろん、クライアントツールや php などのサーバサイドスクリプトから接続するためのパスワードになります。
前述のOSユーザーのパスワードではないので、注意が必要です。
と言いながらこちらも root ユーザーなので、パスワードは同じ postgres にします。
su - postgres
psql
alter role postgres with password 'postgres';
\q
exitいつも通りのコマンド表記ですが su (substitute user) コマンドを使う事で以降は exit で終了するまで postgres ユーザー としての作業になります。
コピペし易いようにいつもはコマンドより左側を記載していませんが、su でユーザーが変わると左側も変化します。
余談ですが、root ユーザーの時のコマンドは #（シャープ）、それ以外のユーザーの時のコマンドは $（ドル）表記で説明を省略している場合もあります。
1行目のコマンドで linux の root ユーザーから postgres ユーザーになりました。
前述通り [root@localhost ~]# から -bash-4.2 $ になっていると思います。
2行目のコマンドで PostgreSQLターミナルに対話的に問い合わせ（コマンド）を入力し結果を確認したり、設定の変更などを行う事ができます。
簡単に言うと、postgresにログインするイメージです。
前述でデータベースクラスタ作成時、postgresデータベースが作成されているとお伝えしましたが、このログイン時にDB名を省略した場合、ユーザーと同じデータベースに接続しに行きます。
3行目のコマンドで PostgreSQLユーザーの postgresユーザーに対し postgres と言うパスワードを設定しています。
簡単に言うと、今のところパスワードは全部 postgres です。
4行目の \（円マーク）q でPostgreSQLターミナルを終了（postgresからログアウト）しています。
5行目は1行目で postgres ユーザーになっているため、postgres ユーザーを終了（ログアウト）して元のユーザー（今回はroot ユーザー）に戻るためのコマンドになります。
これでpostgresユーザーのパスワード設定が完了しました。
最後にpostgresを再起動し設定を反映させます。
systemctl restart postgresql-11追加設定
接続ユーザーとDBの追加
ローカルの開発環境ですので全権限を持っている postgres ユーザーだけで十分ですが、postgresはロールと言う概念があるためユーザー作成をもって少しだけ触れておきます。
簡単にはロール＝ユーザーのようなものなので「ロールの追加」と誰かが言っていれば「ユーザーの追加」と理解しても大丈夫です。
大丈夫なのですが正しくは違います。
ロールにはユーザーとグループと言う概念が含まれており、ユーザーもグループもロールとして作成されます。
ですので、正しくはロール＝ユーザーではなく、ロールと言うくくりの中にユーザーとグループが存在すると言うほが正しいです。
と言う事で、そんな事もあるのですが直近の作業に必要な部分だけを抜き出しながら1つずつ進めていきます。
su - postgres
psql
\du1行目でまずpostgres ユーザーになります。
2行目のpsql コマンドで postgres データベースに接続します。
3行目の \（円マーク）du でロールを確認します。
現在は postgres ユーザーしかいないため postgres ユーザーのみ表示されます。
ロールも種類が多いのでここでは簡単に、スーパーユーザーなので何でもできると思って貰って大丈夫です。
次に新しくユーザーを作成します。
create role user1 login password 'user1';
\du1行目のcreate コマンドで新しくpostgresにログインできる user1 ユーザー（パスワードuser1）を作成します。
2行目で再び \du コマンドでロール一覧を表示させます。
user1 が追加されていますが、何も入っていません（ログインはできます）ね。
ロールは必要に応じて追加削除を行います。
ここでは user1 ユーザーは postgres ユーザーとの違いの為に作成したようなものなので、ロールの設定作業は行いません。
次に user1 ユーザーの接続DBを作成します。
前述した通り、postgresにログインするためにはデータベースが必要です。
create database userdb owner=user1;
\l1行目でDB名 userdb に対してDBの所有者を user1 で作成しています。
2行目でDB一覧を表示します。
データベースが所有者 user1 で作成されています。
所有者と言うくらいなので、このデータベースの所有者には各種SQLを実行する権限が自動的に付与されています。
ロールの事を権限と言っているわけではありません。
詳しくは後述します。
追加ユーザーおよび外部からの接続許可設定
前述の user1 ユーザー所有の userdb を作成したところで user1 ユーザーで postgres に接続したいところですが、今のままではできません。
そこで表題通り、接続許可設定を行っていきます。
\q
vi 11/data/postgresql.conf
(変更前)
#listen_addresses = 'localhost'
(変更後）
listen_addresses = '*'1行目は現在 postgres に接続中だと思いますので1度ログアウトします。
2行目の viコマンドで postgresql.conf ファイルを編集します。
コメント（#）を削除して localhost をアスタリスク（＊）に変更します。
ちなみにフルパスは /var/lib/pgsql/11/data/postgresql.conf です。
この様な感じ。
余談ですが、vi で開いたファイルに対して画像の様に行番号を出したい場合は入力モードではない状態で :set number と入力してエンターを押下すると画像の様になります。
もう1つ変更が必要なファイルがあるので続いて設定していきます。
vi 11/data/pg_hba.conf
# "local" is for Unix domain socket connections only
local userdb user1 md5 ← この行を追加
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
host all all 192.168.56.0/24 md5 ← この行を追加上記の「この行を追加」以外は最初から記載されている設定になります。
localとhostの違いは以下の通りです。
localの場合の接続方法
psql -U user1 -d userdb
hostの場合の接続方法
psql -U user1 -h 192.168.56.102 -d userdb
ですのでどちらも接続設定ですが、localの方はサーバ内（本稿の操作の様なものも含む）でhostの方は外部からの接続設定と思って貰えれば大丈夫です。
local設定は最初から記載されている peer よりも優先させるために peer よりも上に記述してください。
host設定の 192.168.56.0/24 については過去の記事に関連情報がありますのでそちらも是非見て頂きたいのですが、お使いのvmに設定されているIPで .0 以前の数字を変更してください。
こんな感じです。
ちなみに以下の設定もあります。
# "local" is for Unix domain socket connections only
local all all peer
↑を↓に変更
local all all trust
または↓に変更
local all all md5各設定を書かずに、既存の設定のMETHODの項目である peer 設定を trust または md5 に変更する方法です。
METHODについての設定は沢山ありますが、本稿で出た3つを簡単に説明します。
peer
初期設定がこれです。
これはOS側のユーザー名（インストール時に追加されるLinux ユーザーの postgresユーザー）とPostgreSQL側のユーザー名（これもインストール時に追加されて、前述でalterコマンドからパスワード設定を行ったpostgresユーザー）が一致していないと認証されません。
インストール時、この2つは自動で設定されています。
更にデータベースクラスタ作成時、postgresデータベースも作成されています。
つまり、これらの設定が整っているので、最初から psql コマンドだけでpostgresに接続する事ができていたと言うわけです。
trust
無条件で接続が許可されます。
接続時パスワードが不要になるため、新しいユーザーを作成する度に個別の設定は不要になります。
md5
本稿で設定したパスワード形式を使ってログインするための設定です。
既存の設定を md5 にした場合、postgresユーザー（psqlだけで気軽にログインしていた）もパスワードが求められるようになります。
これが一番一般的かもしれませんね。
本稿ではわざわざ既存設定の上に個別設定を記載していますが、設定の違いが理解できていれば状況に応じて md5 でも trust でも構わないと思います。
最後に変更した設定を反映させるために1度linuxの postgres ユーザーからログアウトし root ユーザーに戻って postgres を再起動しましょう。
exit
systemctl restart postgresql-11それでは新しく作った user1 ユーザーで postgres に接続してみましょう。
su - postgres
psql -U user1 -d userdb1行目は Linux の postgres ユーザーにスイッチ（ログイン）。
2行目で user1 が userdb データベースに接続。
無事接続できました。
接続とテーブルに対する権限（設定、ユーザー作成）
本項目は所有者以外のユーザーからのデータベースへの接続を考えないのであれば不要です。
前述の userdb データベースを作成した箇所で自動で割り振られるのはロールの権限ではないとお伝えしました。
データベース作成時、所有者が自動で割り振られるのはテーブル操作について各種権限のことになります。
ですので所有者以外のユーザーがテーブルを操作する事はできません。
ただし、データベース所有者が他ユーザーへ権限を付与する事で操作が可能になります。
ここでは設定、比較対象ユーザーとDBの作成についてまで解説します。
最初に、比較対象の新しいユーザーとデータベースを作成します。
su - postgres
psql
create role user2 login password 'user2';
create database user2 owner=user2;前述の user1 の時と同じで、この度は user2 にしています。
user1 の時と違うのはユーザー名とデータベース名が一緒と言うことです。
ですので user2 でログインする場合、データベース名は省略する事ができます。
こんな感じ。
次に、pg_hba.conf を設定します。
vi 11/data/pg_hba.conf
# "local" is for Unix domain socket connections only
local all user2 md5 ← この行を追加
local all user1 md5 ← db名をallに変更
local all all peerしつこいようですが本稿では設定を1つずつ追加していきます。
前回と違うのはDB名を all に変更しています。
これで各ユーザーは全てのDBに権限さえあればアクセスできます。
こんな感じ。
これでスーパーユーザーの postgres 以外の一般ユーザー user1、user2 が出来ました。
ここでも変更した設定を反映させるために1度linuxの postgres ユーザーからログアウトし root ユーザーに戻って postgres を再起動しましょう。
接続とテーブルに対する権限（テーブル作成）
まず最初に user1 ユーザーの userdb データベースにテーブルを追加します。
前項は postgres ユーザーだったため、最初から記載します。
su - postgres
psql -U user1 userdb
CREATE TABLE sample (
id SERIAL NOT NULL,
contents VARCHAR(255),
PRIMARY KEY (id)
);
INSERT INTO sample (id, contents) VALUES
(1, 'hello world!'),
(2, 'これはuser1ユーザーのuserdbデータベースのsampleテーブルです。');2行目で user1 ユーザーから userdb へ接続します。
CREATE 文で sample テーブルを作成。
INSERT 文でデータを2件ほど登録しています。
続いて user2 ユーザーの user2 データベースにテーブルを追加します。
操作は上記 user1 ユーザーからの続きです。
\q
psql -U user2
CREATE TABLE test (
id SERIAL NOT NULL,
message VARCHAR(255),
PRIMARY KEY (id)
);
INSERT INTO test (id, message) VALUES
(1, 'hello hogehoge.'),
(2, 'これはuser2ユーザーのuser2データベースのtestテーブルです。');1行目の \（円マーク）q で userdb からログアウトします。
2行目の psql で user2 ユーザーで user2 データベース（ユーザー名と同名なため省略）に接続します。
CREATE 文で test テーブルを作成。
こちらもINSERT 文でデータを2件ほど登録しています。
こんな感じ。
これで userdb データベースには sampleテーブル、user02 データベースには test テーブルができました。
接続とテーブルに対する権限（権限付与）
最初に各ユーザーになって作成したテーブルの権限を確認します。
権限確認には各データベースに接続時に \（円マーク）dp コマンドで確認する事ができます。
こんな感じ。
どちらのテーブルのアクセス権も何も表示していませんね。
つまり、オーナー以外のアクセスは何も許されていない状態です。
今の状態ではどうなるかやってみます。
\c userdb
select * from sample;1行目の \（円マーク）c はデータベースを変更する時に使用するコマンドです。
ここでは user02 ユーザーが userdb に接続しに行っています。
user02 ユーザーが他DB（自DBはuser02データベース）に接続可能なのは前述の pg_hba.conf ファイルのDB名を all に変更しているからです。
2行目で user02 ユーザーが userdb データベースの sampleテーブルに対して select 文を発行しました。
安定の permission denied でエラーになりました。
権限が無いため select 文だけでなく、何をやっても結果は同じです。
ではこれから設定していきます。
権限については簡単に以下の通りです。
a（INSERT）
テーブルに行（レコード）を追加（append）する権限。
r（SELECT）
テーブルの列、行を参照（読み取り（read））する権限。
ちなにみ、updateやdeleteでも列の値を参照するため、この権限が必要。
w（UPDATE）
テーブルの更新（書き込み（write））を行うための権限。
d（DELETE）
テーブルに対して削除を行うための権限。
D（TRUNCATE）
テーブルに対してtruncate（全行DELETE）を行うための権限。
x（REFERENCES）
テーブルの外部キー制約の作成を行うための権限。
t（TRIGGER）
テーブル上のトリガ作成を許可する権限。
X（EXECUTE）
U（USAGE）
C（CREATE）
c（CONNECT）
T（TEMPORARY）
arwdDxt
全ての権限。
細かく設定してもいいですが、明確な運用が決まってない場合、障害の原因にもなります。
本稿では最後の全ての権限を付与する方法を記載します。
後から追加された user02 ユーザーが、userdbも操作したいため、user1 ユーザーが権限を付与します。
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user02;GRANT に続けて付与したい権限を指定します。
ALL PRIVILEGES を指定する事で全ての権限が付与されます。
各権限を付与したい場合は , （カンマ）区切りで SELECT,INSERT,UPDATE の様に付与します。
ON に続けて対象テーブルを指定しますが ALL TABLES IN SCHEMA public とすると、public スキーマの全てのテーブルが対象になります。
現時点で userdb には sample テーブルしかありません。
後から作成されたテーブルに自動的にこの権限が付与される事はありませんので注意が必要です。
最後に TO に続けて権限を付与したいユーザーを指定します。
権限を付与する前と後でアクセス権限の表記が違うのがわかると思います。
権限が付与されている user2=arwdDxt/user1 が、user2 ユーザー（ロール）に対して user1 ユーザー（ロール）によって arwdDxt （全ての権限）が付与されています。
それでは権限付与前は permission denied でエラーになった user2 ユーザーで userdb データベースに接続し select文を発行してみましょう。
無事 select 文の発行に成功し、テーブルを参照する事ができました。
最後に付与した権限を削除するコマンドを紹介します。
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM user2;GRANT が REVOKE になっただけですので、詳細は割愛します。
userdb データベースの sample テーブルから user2 のアクセス権限が消えました。
これでまた user2 はテーブル操作を行おうとすると permission denied でエラーとなります。
まとめ
本稿はここまでとなります。
mysqlに続きパッケージインストールのみの投稿ですが、こちらも周辺情報を交えてもそこまで難しくない内容になっていると思って頂ければ嬉しいです。
最後の追加情報が少し冗長になってしまいました。
追加情報で一番必要な情報は外部からの接続許可設定になります。
ユーザーの追加については、ローカル開発環境ですし慣れるまではpostgresユーザーのみでの作業で大丈夫だと思います。
僕は今でもpostgresを使う時はpostgresユーザーですｗ
次回は本シリーズ最後のPHPのインストールについて記載して行きたいと思います。