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, 入門
-,

執筆者:

関連記事

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

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

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

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

Omnibus版のGitLab、Mattermostデータベースに外部ホストからJDBCで接続

Omnibusで入れたPostgreSQLは、 Unix Domainソケットで接続待機Peer認証で認証 していて外部からの接続は出来ないことが前回分かりました。One IT Thing  …

ERROR: Several keys given – pgcrypto does not handle keyring、またはERROR: Corrupt ascii-armor

gpgキーストアに同じUIDで複数の鍵ペアを登録してしまうと、ファイルにexportした際に1ファイルに複数の鍵情報が入ってしまい、1ファイル1鍵を期待しているpgcryptoに怒られます。 目次1 …

スタンドアロンモードのPgAdmin4が開くデフォルトブラウザを変更

PostgreSQL公式GUIクライアントツールの「PgAdmin4」。 PostGISのgeometry型をWeb地図上に表示出来たり、サーバモードで起動すればチーム内で共通的に使えるPostgre …

 

shingo.nakanishi
 

東京在勤、1977年生まれ、IT職歴2n年、生涯現役技術者を目指しています。健康第一。