OSS-DB試験対策 緑本 ch5 設定ファイル

OSS-DBPostgreSQL勉強メモ

出典: 

postgresql.conf

postgres@00aa48792429:/$ cat $PGDATA/postgresql.conf
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
...
  • 最初からいろいろコメントアウトされてる
...
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

#data_directory = 'ConfigDir'		# use data in another directory
					# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf'	# host-based authentication file
					# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf'	# ident configuration file
					# (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = ''			# write an extra PID file
					# (change requires restart)
...
  • 設定反映タイミングには複数ある
  • 公式

    • internal

      • ユーザが直接変更不可能
      • SHOW文で設定値確認可能
    • postmaster

      • サーバー起動時にのみ設定可能
      • 設定変更の反映にpg_ctl restart必要

        • reloadではなく
    • sighup

      • サーバー再起動せずに設定反映可能
      • SIGHUPで反映

        • pg_ctl reload
        • pg_ctl kill HUP <pid>
    • superuser-backend

      • サーバー再起動せずに設定反映可能
      • スーパーユーザによるコネクション確立時に設定可能
    • backend

      • サーバー再起動せずに設定反映可能
      • 一般ユーザによるコネクション確立時に設定可能
    • superuser

      • サーバ再起動不要、スーパーユーザによるSET文でも反映可能
    • user

      • サーバ再起動不要、一般ユーザによるSET文でも反映可能
スーパーユーザ 一般ユーザも
コネクション確立時 superuser-backend backend
SET文 superuser user
postgres@8040c0c21fcb:/$ psql -p 5433 -c "SHOW max_connections"
 max_connections 
-----------------
 100
(1 row)
  • max_connectionsを書き換えてみる
- max_connections = 100			# (change requires restart)
- #max_connections = 100			# (change requires restart)
+ max_connections = 123			# (change requires restart)
  • そのまま再度読んでみる
postgres@8040c0c21fcb:/$ psql -p 5433 -c "SHOW max_connections"
 max_connections 
-----------------
 100
(1 row)
  • 変わらない
 max_connections 
-----------------
 100
(1 row)
  • SIGHUP送ってみる
postgres@8040c0c21fcb:/$ pg_ctl reload -D /home/postgres/data -o "-p 5433"
  • 反映されないものがあるぞ、とエラー出る
server signaled
2020-01-19 07:19:51.088 UTC [94] LOG:  received SIGHUP, reloading configuration files
2020-01-19 07:19:51.088 UTC [94] LOG:  parameter "max_connections" cannot be changed without restarting the server
2020-01-19 07:19:51.088 UTC [94] LOG:  configuration file "/home/postgres/data/postgresql.conf" contains errors; unaffected changes were applied
postgres@8040c0c21fcb:/$ psql -p 5433 -c "SHOW max_connections"
  • 変わらない

    • サーバー再起動を要するパラメータだから

      • (change requires restart)
 max_connections 
-----------------
 100
(1 row)
  • サーバ再起動
postgres@8040c0c21fcb:/$ pg_ctl restart -D /home/postgres/data -o "-p 5433"
2020-01-19 07:21:19.933 UTC [94] LOG:  received fast shutdown request
waiting for server to shut down....2020-01-19 07:21:19.937 UTC [94] LOG:  aborting any active transactions
2020-01-19 07:21:19.938 UTC [94] LOG:  background worker "logical replication launcher" (PID 101) exited with exit code 1
2020-01-19 07:21:19.939 UTC [96] LOG:  shutting down
2020-01-19 07:21:19.954 UTC [94] LOG:  database system is shut down
 done
server stopped
waiting for server to start....2020-01-19 07:21:20.051 UTC [438] LOG:  starting PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-01-19 07:21:20.052 UTC [438] LOG:  listening on IPv4 address "0.0.0.0", port 5433
2020-01-19 07:21:20.052 UTC [438] LOG:  listening on IPv6 address "::", port 5433
2020-01-19 07:21:20.057 UTC [438] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2020-01-19 07:21:20.071 UTC [439] LOG:  database system was shut down at 2020-01-19 07:21:19 UTC
2020-01-19 07:21:20.077 UTC [438] LOG:  database system is ready to accept connections
 done
server started
  • 再度読んでみる
postgres@8040c0c21fcb:/$ psql -p 5433 -c "SHOW max_connections"
 max_connections 
-----------------
 123
(1 row)
  • 反映された

接続と認証 (CONNECTIONS AND AUTHENTICATION)

listen_addresses

  • デフォルトlocalhost

    • TCPはローカルループバックのみ許可
    • リモートからは繋げない
  • 他の特殊値

    • 0:0:0:0: 任意のIPv4アドレスで接続許可
    • ::: 任意のIPv6アドレスで接続許可
    • *: 任意のIPアドレスで接続許可
    • 空: Unixドメインソケット通信でのみ接続可能
  • サーバ側のIPアドレスを指定することに注意

    • ネットワークインタフェースが複数あったりするので
    • クライアント側のIPアドレスではない
  • postmaster

port

  • デフォルト5432
  • 先述のlisten_addressesで指定される全IPアドレスに対して共通
  • postmaster

max_connections

  • 典型的にはデフォルト100
  • postmaster

クライアント接続デフォルト (CLIENT CONNECTION DEFAULTS)

search_path

  • スキーマ検索パスを設定する
  • 【補】スキーマ: データベース中の名前空間みたいなやつ

  • 【補】スキーマ一覧のメタコマンドが\dnなのはたぶんnamespaceから来ているのでは
postgres@8040c0c21fcb:/$  psql -c "\dn"
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)
  • デフォルトで"$user", public
postgres@8040c0c21fcb:/$  psql -c "SHOW search_path"
   search_path   
-----------------
 "$user", public
(1 row)
  • 設定可能タイミング: user

    • SETで上書き可能

defaulttransactionisolation

  • デフォルトread committed
  • 設定可能タイミング: user

client_encoding

  • 設定可能タイミング: user

エラー報告とログ取得 (ERROR REPORTING AND LOGGING)

log_destination

...
#------------------------------------------------------------------------------
# REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

#log_destination = 'stderr'		# Valid values are combinations of
					# stderr, csvlog, syslog, and eventlog,
					# depending on platform.  csvlog
					# requires logging_collector to be on.

# This is used when logging to stderr:
#logging_collector = off		# Enable capturing of stderr and csvlog
					# into log files. Required to be on for
					# csvlogs.
					# (change requires restart)
  • 設定値(enum)

    • stderr
    • csvlog

      • logging_collector (boolean)がonであること
    • syslog
    • eventlog

      • windows用
  • 設定可能タイミング: sighup

logging_collector

# This is used when logging to stderr:
#logging_collector = off		# Enable capturing of stderr and csvlog
					# into log files. Required to be on for
					# csvlogs.
					# (change requires restart)
  • ログをファイルに書き出すか否か
  • デフォルトoff
  • log_destinationcsvlogを設定する場合はonにする必要がある
  • 設定可能タイミング: postmaster

log_directory

#log_directory = 'log'			# directory where log files are written,
					# can be absolute or relative to PGDATA
  • デフォルト: $PGDATAに対する相対log/
  • $PGDATAに対する相対パス、または絶対パスを設定可能
  • 設定可能タイミング: sighup

log_filename

#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'	# log file name pattern,
					# can include strftime() escapes
  • strftimeのエスケープが使える
  • 設定可能タイミング: sighup

logminmessages

  • ログレベル
# - When to Log -

#log_min_messages = warning		# values in order of decreasing detail:
					#   debug5
					#   debug4
					#   debug3
					#   debug2
					#   debug1
					#   info
					#   notice
					#   warning
					#   error
					#   log
					#   fatal
					#   panic
  • デフォルトwarning
  • 下のものほど深刻

    • warning

      • 想定外動作
    • error

      • 特定のトランザクションで問題発生
      • そのトランザクションだけabortされる
      • 他のトランザクションに影響なし
    • log

      • DBAが着目すべき動作ログ
    • fatal

      • 特定のセッションで問題発生
      • そのセッションだけ切断される
      • 他のセッションに影響なし
    • panic

      • 致命的なエラー発生
      • 全セッション強制切断
      • PostgreSQLは停止する
  • 設定可能タイミング: superuser

loglineprefix

  • ログ行頭に出力する内容
# - What to Log -

...

#log_line_prefix = '%m [%p] '		# special values:
					#   %a = application name
					#   %u = user name
					#   %d = database name
					#   %r = remote host and port
					#   %h = remote host
					#   %p = process ID
					#   %t = timestamp without milliseconds
					#   %m = timestamp with milliseconds
					#   %n = timestamp with milliseconds (as a Unix epoch)
					#   %i = command tag
					#   %e = SQL state
					#   %c = session ID
					#   %l = session line number
					#   %s = session start timestamp
					#   %v = virtual transaction ID
					#   %x = transaction ID (0 if none)
					#   %q = stop here in non-session
					#        processes
					#   %% = '%'
					# e.g. '<%u%%%d> '
  • デフォルト: '%m [%p] '

    • タイムスタンプ(ミリ秒)とPID

      • 【補】Y-m-d H:i:s.uフォーマット
  • 設定可能タイミング: sighup

SET文/SHOW文の使い方

SET文

postgres@8040c0c21fcb:/$ psql -c "\h SET"
Command:     SET
Description: change a run-time parameter
Syntax:
SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }

URL: https://www.postgresql.org/docs/12/sql-set.html
  • 公式
  • SESSIONLOCALの2種類ある

    • 省略するとSET SESSION
SET SESSION SET LOCAL
トランザクション実行中 設定される 設定される
commit後 設定値残る 設定値残らない
rollback後 設定値残らない 設定値残らない

SHOW文

postgres@8040c0c21fcb:/$ psql -c "\h SHOW"
Command:     SHOW
Description: show the value of a run-time parameter
Syntax:
SHOW name
SHOW ALL

URL: https://www.postgresql.org/docs/12/sql-show.html
  • 公式
  • そのセッションの設定値が表示される

    • SET文で上書きされている場合、他のセッションでは異なる値が見えることがある
  • セッションデフォルト値の確認
postgres@8040c0c21fcb:/$ psql

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=# SHOW search_path;

   search_path   
-----------------
 "$user", public
(1 row)
  • スキーマを作ってSETでsearch_path上書きしてみる
postgres=# CREATE SCHEMA exam_schema;
CREATE SCHEMA

postgres=# \dn

    List of schemas
    Name     |  Owner   
-------------+----------
 exam_schema | postgres
 public      | postgres
(2 rows)

postgres=# SET search_path TO 'exam_schema';
SET
  • 設定反映を確認
postgres=# SHOW search_path;

 search_path 
-------------
 exam_schema
(1 row)
  • 別のターミナルを開いて、別のセッションで確認してみる
postgres@8040c0c21fcb:/$ psql -c "SHOW search_path"

   search_path   
-----------------
 "$user", public
(1 row)
  • このセッションでは上書きしていないので、セッションデフォルトの"$user", publicが設定されている

pg_hba.conf

  • クライアント認証の設定

    • hba: host-based authentication
  • 公式
  • 設定の書式
local      database  user  auth-method  [auth-options]
host       database  user  address  auth-method  [auth-options]
hostssl    database  user  address  auth-method  [auth-options]
hostnossl  database  user  address  auth-method  [auth-options]
host       database  user  IP-address  IP-mask  auth-method  [auth-options]
hostssl    database  user  IP-address  IP-mask  auth-method  [auth-options]
hostnossl  database  user  IP-address  IP-mask  auth-method  [auth-options]
hostgssenc database  user  IP-address  IP-mask  auth-method  [auth-options]
hostnogssenc database  user  IP-address  IP-mask  auth-method  [auth-options]
  • マッチするものが複数ある場合、ファイルの先頭側のものが評価される

接続方式(1列目)

  • local

    • Unixドメインソケット通信用の設定
    • この設定がないとUnixドメインソケット接続不可
  • host

    • TCP/IP通信用の設定
    • 後述のhostssl/hostnossl, hostgssenc/hostnogssencにもマッチする
    • これを設定してクライアントを許可しても、PostgreSQLサーバー設定のlisten_addressesを適切に設定しないと接続できないことに留意する

      • 無設定時のデフォルトはlocalhostなのでローカルループバックのみ
  • hostssl/hostnossl

    • 接続がSSLで暗号化されている/されていない場合にのみマッチ
  • hostgssenc/hostnogssenc

    • 【補】PostgreSQL version 12から追加されたみたい
    • 接続がGSSAPIで暗号化されている/されていない場合にのみマッチ
    • GSSAPI: Generic Security Standard Application Programming Interface

      • (usually a part of the Kerberos installation)
      • 利用するためにはGSSAPIサポートを有効化してPostgreSQLをビルドすること

アドレス

  • TCP/IP接続の場合に設定

    • CIDRアドレス

      • 192.168.100.0/24とか
    • アドレス + ネットマスク

      • アドレス + 255.255.255.0とか

認証方式

  • trust

    • 公式/Trust認証
    • ざる認証
    • 単一ユーザでローカル接続するとき有用
    • 複数ユーザの場合は適さない

      • Unixドメインソケット通信に制限するならアリ

        • ファイルシステムのパーミッションで守れる
      • その場合、host 127.0.0.1 ...を削除すること
  • reject

    • 全拒否
    • 特定のホストをブラックリスト入りさせるのに有用
  • パスワード認証系

    • 公式/パスワード認証
    • scram-sha-256

      • challenge-response

        • 平文パスワードが盗聴されたりサーバ上から盗まれる心配はない
      • 暗号学的ハッシュでパスワードを永続化する
      • いちばん安全
      • しかし古いクライアントでサポートされていない
    • md5

      • challenge-response
      • scram-sha-256よりはセキュアでない

        • ハッシュが盗まれる心配はある
        • MD5は今や安全でないので…
    • password

      • パスワードを平文で送信する
      • ので、SSLで通信経路を暗号化していないとやばい

        • 【補】そのためにhostsslとかを使うのかな