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

スキーマ検索パスを設定する
【補】スキーマ: データベース中の名前空間みたいなやつ
- 公式/Schemas
- テーブル、型、関数、演算子等を含む
【補】スキーマ一覧のメタコマンドが\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で上書き可能

default_transaction_isolation

デフォルト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_destinationにcsvlogを設定する場合は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

log_min_messages

ログレベル

# - 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

log_line_prefix

ログ行頭に出力する内容

# - 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

公式
SESSIONとLOCALの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をビルドすること
    - 公式/インストール手順
    - ./configureの--with-gssapiオプションでGSSAPIサポートの有無を設定できる

アドレス

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とかを使うのかな

勉強日記

チラ裏

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

postgresql.conf

接続と認証 (CONNECTIONS AND AUTHENTICATION)

listen_addresses

port

max_connections

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

search_path

default_transaction_isolation

client_encoding

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

log_destination

logging_collector

log_directory

log_filename

log_min_messages

log_line_prefix

SET文/SHOW文の使い方

SET文

SHOW文

pg_hba.conf

接続方式(1列目)

アドレス

認証方式