内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 ch3 各種設定ファイルと基本設定
設定ファイルの種類
- 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時
- user/superuser
設定ファイルの分割と統合
#------------------------------------------------------------------------------ # 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)
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接続
- 使用条件
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
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
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とかサブネットマスクとか使える
- 特殊な値
認証方式
- 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