D.Horiyama's output

内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 ch3 各種設定ファイルと基本設定

OSS-DB勉強メモ

出典: 

設定ファイルの種類

  • postgresql.conf
  • pg_hba.conf
  • pg_ident.conf

  • recovery.conf

    • V12で消えた
  • pg_service.conf

postgresql.conf

  • 公式
  • PostgreSQL全体の動作を制御する

  • 後勝ち

    • cf. pg_hba.confは上から評価して最初にマッチしたもの

設定項目の書式

  • 略

設定の参照と変更

  • SHOW文で参照
  • SET文で変更
  • pg_settingsビューの参照・更新も可

設定項目の反映タイミング

  • pg_settingsのcontextカラム

    • user/superuser

      • SET文

    • sighup

      • reload時

    • postmaster

      • restart時

設定ファイルの分割と統合

#------------------------------------------------------------------------------
# CONFIG FILE INCLUDES
#------------------------------------------------------------------------------

# These options allow settings to be loaded from files other than the
# default postgresql.conf.  Note that these are directives, not variable
# assignments, so they can usefully be given more than once.

#include_dir = '...'			# include files ending in '.conf' from
					# a directory, e.g., 'conf.d'
#include_if_exists = '...'		# include file only if it exists
#include = '...'			# include file

Column: コマンドラインパラメータによる設定

ALTER SYSTEM文による変更

  • PostgreSQL version 9.4から

    • RESETサブコマンドは9.5から
Command:     ALTER SYSTEM
Description: change a server configuration parameter
Syntax:
ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }

ALTER SYSTEM RESET configuration_parameter
ALTER SYSTEM RESET ALL

URL: https://www.postgresql.org/docs/12/sql-altersystem.html
  • 試しに、autovacuum (sighup)の設定値をALTER SYSTEM SETで変更してみる
postgres=# SHOW autovacuum;
 autovacuum 
------------
 on
(1 row)

postgres=# ALTER SYSTEM SET autovacuum='off';
ALTER SYSTEM

postgres=# SHOW autovacuum;
 autovacuum 
------------
 on
(1 row)
  • SETのように即反映されることはない
  • postgres.auto.confファイルに追記されている
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
autovacuum = 'off'
  • HUPシグナル送って再度確認
pg_ctl kill HUP 1
postgres=# SHOW autovacuum;
 autovacuum 
------------
 off
(1 row)
  • 設定反映された
  • ALTER SYSTEM RESETでデフォルトに戻す
postgres=# ALTER SYSTEM RESET autovacuum;
ALTER SYSTEM

postgres=# SHOW autovacuum;
 autovacuum 
------------
 off
(1 row)

postgres=# SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

postgres=# SHOW autovacuum;
 autovacuum 
------------
 on
(1 row)
  • postmasterな設定はrestart必要

  • 制限

    • superuser権限があること

    • トランザクションブロック内では使えない

      • ファイルシステムに直接作用し、ロールバックできないから
postgres=# BEGIN;
BEGIN

postgres=# ALTER SYSTEM SET autovacuum='off';
ERROR:  ALTER SYSTEM cannot run inside a transaction block

postgres=# ROLLBACK;
ROLLBACK

  • postgres.conf直接編集と比較しての利点

    • 設定の誤りをALTER SYSTEMの実行時にチェックできる
    • リモートサーバから永続的な設定変更を行える
postgres=# ALTER SYSTEM SET autovacuum='piyo';
ERROR:  parameter "autovacuum" requires a Boolean value

pg_hba.confファイル

  • 公式
  • HBA: host-based authentication

記述形式

  • fall throughとかbackupとかない

    • 上から順番に評価する

    • 1行でもマッチしたらそこで終わり

      • cf. postgresql.confは後勝ち
    • 1行もマッチしなければfail
  • ので上の方にあまり広くマッチするものを書かない

接続方式

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'
					# comma-separated list of addresses;
					# defaults to 'localhost'; use '*' for all
					# (change requires restart)
...

  • listen_addressの値により認証方式が変わる

    • localhostだとlocalとか

SSL接続

  • 使用条件

    • クライアント、サーバ双方がSSLをインストールしてあること

      • サーバ: configure時に--with-opensslオプションをつけてビルドする
      • rpmで拾ってくるとSSLつき
    • sslパラメータがonであること
postgres=# select * from pg_settings where name = 'ssl';
-[ RECORD 1 ]---+-------------------------------------
name            | ssl
setting         | off
unit            | 
category        | Connections and Authentication / SSL
short_desc      | Enables SSL connections.
extra_desc      | 
context         | sighup
vartype         | bool
source          | default
min_val         | 
max_val         | 
enumvals        | 
boot_val        | off
reset_val       | off
sourcefile      | 
sourceline      | 
pending_restart | f
  • デフォルトオフ
  • SIGHUPで反映

接続データベース

  • ,区切り

  • データベース名のほか、特別な値を設定できる

    • all

    • sameuser

      • ユーザ名と同じデータベース

    • samerole

      • samegroup (obsolete)と同義
      • データベースと同じ名前のroleに属しているメンバ

    • replication

      • 物理レプリケーション接続

    • @<相対パス>

      • カンマ区切りを別ファイルに逃がせる
  • こういう感じのDBとユーザがあるとする
postgres@b612ca6ed5fc:/$ psql --pset=pager -l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
(4 rows)

postgres@b612ca6ed5fc:/$ psql --pset=pager -c "\du"
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test      |                                                            | {}

pg_hba.conf

# host    all             all             127.0.0.1/32            trust
host    sameuser             all             127.0.0.1/32            trust
host    all             all             127.0.0.1/32            reject
  • 127.0.0.1において、sameuserのみtrust、ほかはreject
  • (pg_ctl reloadで反映)
  • testユーザでtest DB、postgresユーザでpostgres DBには接続可能
postgres@b612ca6ed5fc:/$ psql -h 127.0.0.1 -U test test
psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

test=> \q


postgres@b612ca6ed5fc:/$ psql -h 127.0.0.1
psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=# \q
  • postgresユーザでtest DBには接続できない
postgres@b612ca6ed5fc:/$ psql -h 127.0.0.1 test
psql: error: could not connect to server: FATAL:  pg_hba.conf rejects connection for host "127.0.0.1", user "postgres", database "test", SSL off
  • localhostはMySQLのようにUnixドメインソケット通信と解釈されたりはしないのでループバック扱いなので、やはり接続できない
postgres@b612ca6ed5fc:/$ psql -h localhost test
psql: error: could not connect to server: FATAL:  pg_hba.conf rejects connection for host "127.0.0.1", user "postgres", database "test", SSL off
  • -h, --hostオプションを指定しなければUnixドメインソケット通信 = localルールにマッチする 
postgres@b612ca6ed5fc:/$ psql test
psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

test=#

Column: ログイン権限

postgres@b612ca6ed5fc:/$ psql -c 'CREATE USER chino'
CREATE ROLE


postgres@b612ca6ed5fc:/$ psql -c 'CREATE ROLE cocoa'
CREATE ROLE


postgres@b612ca6ed5fc:/$ psql -P pager -c '\du'
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 chino     |                                                            | {}
 cocoa     | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test      |                                                            | {}
  • CREATE USERはCREATE ROLEのエイリアス
  • CREATE ROLEはデフォルトでNOLOGIN権限

接続ユーザ

  • 先頭に+がある/ないの2パターン

    • ない: ユーザ名完全一致
    • ある: 指定のロールのメンバに一致
  • +を試してみる

pg_hba.conf

# host    all             all             127.0.0.1/32            trust
host    all             +rabbithouse             127.0.0.1/32            trust
host    all             all             127.0.0.1/32            reject
  • rabbithouseロールに3人くらいユーザをぶら下げる
postgres@b612ca6ed5fc:/$ psql -c 'CREATE ROLE rabbithouse'
CREATE ROLE


postgres@b612ca6ed5fc:/$ psql -c 'CREATE USER rize IN ROLE rabbithouse'
CREATE ROLE


postgres@b612ca6ed5fc:/$ psql -c 'GRANT rabbithouse TO chino;'
GRANT ROLE


postgres@b612ca6ed5fc:/$ psql -c 'GRANT rabbithouse TO cocoa;'
GRANT ROLE


postgres@b612ca6ed5fc:/$ psql -P pager -c '\du'
                                      List of roles
  Role name  |                         Attributes                         |   Member of   
-------------+------------------------------------------------------------+---------------
 chino       |                                                            | {rabbithouse}
 cocoa       | Cannot login                                               | {rabbithouse}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 rabbithouse | Cannot login                                               | {}
 rize        |                                                            | {rabbithouse}
 test        |                                                            | {}
  • rabbithouseに属するrizeちゃんでログインできる
postgres@b612ca6ed5fc:/$ psql -h localhost -U rize test
psql -h localhost -U rize test
psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

test=> \q

接続元のIPアドレス

  • CIDRとかサブネットマスクとか使える

  • 特殊な値

    • 0.0.0.0/0, ::/0

      • 任意のIPv4/IPv6から接続可能

    • samehost

      • サーバが持つ全IPアドレスに一致

    • samenet

      • サーバが接続しているサブネット内のIPアドレスに一致

認証方式

  • PostgreSQL 10からpg_hba.confファイルの内容をpg_hba_file_rulesビューで参照できるように
postgres=# SELECT * FROM pg_hba_file_rules;
 line_number | type  |   database    |   user_name    |  address  |                 netmask                 | auth_method | options | error 
-------------+-------+---------------+----------------+-----------+-----------------------------------------+-------------+---------+-------
          84 | local | {all}         | {all}          |           |                                         | trust       |         | 
          87 | host  | {all}         | {+rabbithouse} | 127.0.0.1 | 255.255.255.255                         | trust       |         | 
          88 | host  | {all}         | {all}          | 127.0.0.1 | 255.255.255.255                         | reject      |         | 
          90 | host  | {all}         | {all}          | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust       |         | 
          93 | local | {replication} | {all}          |           |                                         | trust       |         | 
          94 | host  | {replication} | {all}          | 127.0.0.1 | 255.255.255.255                         | trust       |         | 
          95 | host  | {replication} | {all}          | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust       |         | 
          97 | host  | {all}         | {all}          | all       |                                         | trust       |         | 
(8 rows)
  • ただしスーパーユーザのみ
test=> SELECT * from pg_hba_file_rules;
ERROR:  permission denied for view pg_hba_file_rules

pg_ident.confファイル

  • 公式/User Name Maps

  • PostgreSQLの認証では外部認証システムを使える

    • Ident
    • GSSAPI
  • OSユーザ名とデータベースユーザ(ロール)名とが一致しないとき、そのマッピングに用いる
# MAPNAME  SYSTEM-USERNAME  PG-USERNAME