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で上書き可能
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_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
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
- 公式
-
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
とかを使うのかな
- 【補】そのために