設定ファイルの種類
- 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つき
- サーバ: configure時に
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