One IT Thing

IT業界を楽しむ為の学習系雑記

PostgreSQL 入門

開発用PostgreSQLをインストールした後の外部接続、ユーザ作成、認証周りの設定

投稿日:2019年9月6日 更新日:

ローカル開発用にPostgreSQLを入れた後、開発や分析をし易くする為に外部ホストからパスワード付き接続出来るようにしていきます。

また、postgresスーパーユーザは権限が強すぎて開発アプリからの接続ユーザとして使うのは危険です。最低限の権限の一般ユーザも一緒に作っていきます。

環境

  • CentOS 7.6
  • PostgreSQL 11

PostgreSQLは以下の記事で入れてある状態です。未インストールの場合はご参照ください。

1.PostgreSQLサーバを外部公開

インストール直後はインストールしたホストからしか接続出来ないので、外部からTCP:5432ポートで接続出来るようにしておきます。
(#プロンプトはrootユーザで作業します)

# vi /var/lib/pgsql/11/data/postgresql.conf

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses = '0.0.0.0'            # what IP address(es) to listen on;

firewall-cmdで5432ポートを開け、

# firewall-cmd --add-port=5432/tcp --zone=public --permanent
success
# firewall-cmd --reload
success

サーバを再起動して外部へ公開。

# systemctl restart postgresql-11

A5 Mk2、jailer、Metabaseのようなツールで外部から接続出来るようにしておくことで開発作業を円滑に進められます。

サーバは外部公開出来ましたが、まだ肝心の外部から接続出来るユーザが居ません。以下で作っていきます。

2.一般作業ユーザを作成

最初から居るpostgresスーパーユーザは権力が強すぎるので、開発プログラムやコンソール、外部ホストから接続する為の作業用ユーザを一人作ります。(名前は任意で。ここでは仮にdevelユーザとします)

一般作業ユーザの認証方法は以下の2通りにします。

  1. PostgreSQLが入ったローカルホストからはOSユーザなら接続出来るpeer認証
  2. 外部ホストからはパスワードが必要なmd5認証

まずはローカルホストからpeer認証で接続出来るようにdevelというOSユーザをrootユーザで作成。ローカル開発ユーザなのでパスワードも適当にユーザ名と同じにしておきます。

(怒られますが作れます。複雑なパスワードは本番環境で設定すればOKです)

# adduser devel
# passwd devel
ユーザー devel のパスワードを変更。
新しいパスワード: [develと入力]
よくないパスワード: このパスワードは 8 未満の文字列です。
新しいパスワードを再入力してください:
passwd: すべての認証トークンが正しく更新できました。

sshやコンソールからdevelユーザ、develパスワードでCentOSにログイン出来るようになりました。

続いてPostgreSQL内にもdevelユーザを作ります。パスワードは気持ちセキュリティ強めでdevel#123にしてみます。

-bash-4.2$ /usr/pgsql-11/bin/psql

postgres=# CREATE ROLE devel LOGIN PASSWORD 'devel#123';
CREATE ROLE

postgres=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO devel;
GRANT

psqlコマンドに-dでデータベースを指定しなかった場合、ユーザ名と同じデータベースに接続されるので、develデータベースを作っておきます。

postgres=# CREATE DATABASE devel OWNER devel;
CREATE DATABASE

他にも一般作業ユーザに管理させたいデータベースがあれば作成しておきます。

postgres=# CREATE DATABASE dvdrental OWNER devel;
CREATE DATABASE

3.外部からのdevelユーザ接続をmd5認証に変更

インストール直後はOSユーザしかPostgreSQLにログイン出来ないpeer認証のみ。外部からpsqlコマンドやJDBCでログイン出来るようにパスワード認証にしておきます。

/var/lib/pgsql/11/data/pg_hba.confを編集してdevelユーザの外部ホスト認証方法をmd5認証に変更。

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all            all                                    peer
# ↓↓↓↓↓ 追加 ↓↓↓↓↓
host    all            devel         192.168.0.0/24           md5

192.168.0セグメントの外部ホストからdevelユーザならパスワード有りで接続可能、ローカルホストならpeer認証でデフォルト通りパスワード無し接続が可能な設定です。

設定をPostgreSQLに読み込ませて反映します。
restartではなくreloadで再起動せずに設定ファイルを再読み込みできます。
(restartでも問題無いです)

# systemctl reload postgresql-11

外部から接続する為の設定完了です。

接続確認

ローカルホストから接続

PostgreSQLが入ったローカルホストからならpostgres、devel両ユーザはパスワード無しで接続可能。

[root@spock ~]# su - postgres
-bash-4.2$ /usr/pgsql-11/bin/psql
psql (11.4)
"help" でヘルプを表示します。

postgres=#
[root@spock ~]# su - devel
[devel@spock ~]$ /usr/pgsql-11/bin/psql
psql (11.4)
"help" でヘルプを表示します。

devel=>

リモートホストから接続

Windows10からDBクライアント神ツール「A5 Mk2」で接続してみます。

サーバ―名:PostgreSQLが入っているホストIP
データベース名:devel
ユーザーID:devel
パスワード:devel#123

接続が出来れば外部ホストで動くプログラムから接続が出来る開発用PostgreSQL環境の完成です。

まとめ

本番環境のPostgreSQLにはAPサーバのような特定ホストIPからしか繋げず、接続ユーザのパスワードも複雑なものを設定しますが、開発環境が

  1. development(開発用)
  2. staging(本番想定テスト用)
  3. production(本番)

と分かれているなら、development環境はこれくらいの緩め設定にしておき、各開発者PCからの接続や、BIツールでの分析を色々やれるようにした方が開発が円滑に進みます。

-PostgreSQL, 入門
-,

執筆者:

関連記事

ER図自動生成ツール「SchemaSpy」でdvdrentalを可視化

SchemaSpyを使うとDBからデータを吸い上げてテーブルの相関をビジュアライズしてくれます。 本来ER図からDDLを生成出来るようにしておくべきで、出来れば使わない方がいいチート系のツールですがC …

無料で自由に使える日本全国の企業データベースをPostgreSQLで作る

開発するシステムよっては一覧から企業を選択する機能があるかも知れません。「企業データベース」でググると沢山の商用企業データベースが出てきます。 「でも・・・お高いんでしょう?」 はい。下手をすると家が …

PostgreSQL11に一般ユーザ権限でサンプルDB「dvdrental」をpg_restore

データベースインストール後にサンプルデータを投入することで、DBをバックエンドにして動作する他ミドルの検証が楽になったりすることがあります。 それぞれのデータベースで代表的なサンプルデータセットの存在 …

開発用PostgreSQLをCentOSにインストールしてSQLを流すまで

開発や勉強に使う為のPostgreSQLをCentOSにインストールします。 インストールから基本操作を体験して、0からデータベースを構築する経験を積んでおきます。 目次1 環境2 インストール手順を …

CentOS8をVirtualBoxにインストールしてXからdnfを打つまで

2019年9月24日(米時間)CentOS8がリリースされました。 RHEL8リリースから4か月、まだかまだかとリリース進捗を眺めていた方も多かったのではないでしょうか。 「時間が出来たら入れてみよう …


shingo nakanishi。東京で消耗中の職歴20年越え中年ITエンジニアです。「生涯現役プログラマを楽しむ」ことができる働き方探しをライフワークにしています。

19歳(1996年)から書き始めた個人日記が5,000日を超え、残りの人生は発信をして行きたいと思い、令和元日からこのサイトを開始しました。勉強と試行錯誤をしながら、自分が経験したIT関連情報を投稿しています。