PostgreSQL은 아무것도 설정하지 않아도 기동 해서 사용할 수 있습니다. 그러나, 어디까지나 최소한의 동작을 하기 위한 기본 설정으로 기동 하는 것이므로, 실제 시스템에서 사용할 때는, 요건에 맞는 적절한 설정을 할 필요가 있습니다.
「 설정 파일의 종류 」
PostgreSQL에는, 아래 표에 열거된 5개의 설정 파일이 있습니다.
먼저, 위의 3가지(postgresql.conf, pg_hba.conf, pg_ident.conf)에 대해 확인해 보겠습니다.
「 postgresql.conf 파일 」
PostgreSQL 전체의 동작을 제어하는 설정 파일로, 설정 항목은 크게 아래와 같은 카테고리로 분류됩니다.
각 카테고리에는 다수의 설정 항목이 있습니다만, 운용하는 경우 검토나 설정이 필요한 파라미터는 한정됩니다.
【 설정 항목의 서식 】
설정 항목은, 항목명과 항목 값을 '='로 연결한 서식으로 기술합니다. '#'(해시 기호)는 코멘트할 때 이용하며, # 이후의 줄 끝까지의 기술은 무시됩니다.
< 설정 항목 기술의 예 >
max_connections = 100 # (change requires restart)설정하는 값은 논리형, 부동 소수점형, 정수형, 문자형, 열거형의 5종류가 있습니다.
메모리 사이즈나 시간을 지정하는 파라미터의 경우, 숫자 뒤에 단위를 나타내는 문자를 계속해서 기술함으로써, 간단하고 읽기 쉬운 값을 설정할 수 있습니다.
shared_buffers 등 큰 값을 설정할 경우, 단위를 부여하여 설정 실수를 방지할 수 있습니다.
< 값에 단위를 부여하는 예 >
shared_buffer = 128MB 또한, 소문자 k(킬로)는 1000이 아닌 1024를 나타냅니다. 마찬가지로, M(메가)은 1024의 제곱, G(기가)는 1024의 3승, T(테라)는 1024의 4승입니다.
또한, 같은 설정 항목을 여러 개 기술한 경우, 기동 시에는 에러나 경고는 출력되지 않고, 아래쪽에 기술되어 있는 값을 유효한 값으로 봅니다.
< 같은 설정을 복수 기술했을 때의 예 >
[postgres@postgresql ~]$ egrep "^shared_buffers" $PGDATA/postgresql.conf
shared_buffers = 128MB # min 128kB
shared_buffers = 256MB # min 128kB
shared_buffers = 512MB # min 128kB
[postgres@postgresql ~]$ psql postgres -c "SHOW shared_buffers"
shared_buffers
----------------
512MB
(1 row)
【 설정 참조와 변경 】
설정 항목과 설정 값은 SHOW 명령으로 확인할 수 있습니다.
< SHOW 명령의 실행 예 >
[postgres@postgresql ~]$ psql
psql (14.9)
Type "help" for help.
postgres=# SHOW shared_buffers;
shared_buffers
----------------
128MB
(1 row)SHOW 명령으로 ALL을 지정하면, 모든 설정 항목의 항목명(name)과 값(setting)과 설명(description)이 표시됩니다.
< SHOW ALL 실행의 예 >
[postgres@postgresql ~]$ psql
psql (14.9)
Type "help" for help.
postgres=# SHOW ALL;
name | setting | description
----------------------------------------+----------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------
allow_in_place_tablespaces | off | Allows tablespaces directly inside pg_tblspc, for testing.
allow_system_table_mods | off | Allows modifications of the structure of system tables.
application_name | psql | Sets the application name to be reported in statistics and logs.
archive_cleanup_command | | Sets the shell command that will be executed at every restart point.
archive_command | (disabled) | Sets the shell command that will be called to archive a WAL file.
archive_mode | off | Allows archiving of WAL files using archive_command.
archive_timeout | 0 | Forces a switch to the next WAL file if a new file has not been started within N seconds.
array_nulls | on | Enable input of NULL elements in arrays.
authentication_timeout | 1min | Sets the maximum allowed time to complete client authentication.
autovacuum | on | Starts the autovacuum subprocess.
autovacuum_analyze_scale_factor | 0.1 | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples.
autovacuum_analyze_threshold | 50 | Minimum number of tuple inserts, updates, or deletes prior to analyze.
autovacuum_freeze_max_age | 200000000 | Age at which to autovacuum a table to prevent transaction ID wraparound.
autovacuum_max_workers | 3 | Sets the maximum number of simultaneously running autovacuum worker processes.
autovacuum_multixact_freeze_max_age | 400000000 | Multixact age at which to autovacuum a table to prevent multixact wraparound.
autovacuum_naptime | 1min | Time to sleep between autovacuum runs.
autovacuum_vacuum_cost_delay | 2ms | Vacuum cost delay in milliseconds, for autovacuum.
autovacuum_vacuum_cost_limit | -1 | Vacuum cost amount available before napping, for autovacuum.
autovacuum_vacuum_insert_scale_factor | 0.2 | Number of tuple inserts prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_insert_threshold | 1000 | Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums.
autovacuum_vacuum_scale_factor | 0.2 | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_threshold | 50 | Minimum number of tuple updates or deletes prior to vacuum.
autovacuum_work_mem | -1 | Sets the maximum memory to be used by each autovacuum worker process.
backend_flush_after | 0 | Number of pages after which previously performed writes are flushed to disk.
backslash_quote | safe_encoding | Sets whether "\'" is allowed in string literals.
backtrace_functions | | Log backtrace for errors in these functions.
bgwriter_delay | 200ms | Background writer sleep time between rounds.
bgwriter_flush_after | 512kB | Number of pages after which previously performed writes are flushed to disk.
bgwriter_lru_maxpages | 100 | Background writer maximum number of LRU pages to flush per round.
bgwriter_lru_multiplier | 2 | Multiple of the average buffer usage to free per round.
block_size | 8192 | Shows the size of a disk block.
bonjour | off | Enables advertising the server via Bonjour.
bonjour_name | | Sets the Bonjour service name.
--More--더 자세한 정보는, pg_settings 시스템 뷰를 참조하여 확인할 수 있습니다. 예를 들어, 아래의 명령과 같이 pg_settings 시스템 뷰의 unit 열을 보면, 어떤 단위로 설정되는지 알 수 있습니다.
< pg_settings 시스템 뷰 확인 >
[postgres@postgresql ~]$ psql
psql (14.9)
Type "help" for help.
postgres=# SELECT name, setting, unit FROM pg_settings WHERE name LIKE 'wal%';
name | setting | unit
-------------------------------+-----------+------
wal_block_size | 8192 |
wal_buffers | 512 | 8kB
wal_compression | off |
wal_consistency_checking | |
wal_init_zero | on |
wal_keep_size | 0 | MB
wal_level | replica |
wal_log_hints | off |
wal_receiver_create_temp_slot | off |
wal_receiver_status_interval | 10 | s
wal_receiver_timeout | 60000 | ms
wal_recycle | on |
wal_retrieve_retry_interval | 5000 | ms
wal_segment_size | 16777216 | B
wal_sender_timeout | 60000 | ms
wal_skip_threshold | 2048 | kB
wal_sync_method | fdatasync |
wal_writer_delay | 200 | ms
wal_writer_flush_after | 128 | 8kB
(19 rows)
【 설정 항목의 반영 타이밍 】
설정 항목이 반영되는 타이밍에는 아래의 표와 같이 3종류가 있습니다.
설정 항목의 일부에는, PostgreSQL 기동 시뿐만 아니라, SET 명령으로 설정 가능한 항목도 있습니다. 아래의 예시처럼, PostgreSQL 기동 직후의 enable_seqscan 파라미터의 값을 SHOW 명령으로 확인하면 on으로 설정되어 있는 것을 볼 수 있습니다.
그 후, SET 명령으로 enable_seqscan 파라미터의 값을 변경하고, 변경 후의 값을 SHOW 명령으로 다시 확인하면 off로 설정이 변경된 것을 확인할 수 있습니다.
< SET 커맨드에 의한 설정 반영 예 >
[postgres@postgresql ~]$ psql
psql (14.9)
Type "help" for help.
postgres=# show enable_seqscan;
enable_seqscan
----------------
on
(1 row)
postgres=# SET enable_seqscan = 'off';
SET
postgres=# SHOW enable_seqscan;
enable_seqscan
----------------
off
(1 row)SET 명령에 의한 변경이 가능한 설정 항목은, pg_settings 시스템 뷰의 context 열이 'user' 또는 'superuser'로 되어있는 항목만 가능합니다. 이것은 아래의 커맨드와 같이 SELECT 커맨드로 확인할 수 있습니다.
< SET 명령으로 변경 가능한 설정 항목 확인 >
[postgres@postgresql ~]$ psql
psql (14.9)
Type "help" for help.
postgres=# SELECT name, context FROM pg_settings WHERE context IN ('user','superuser');
name | context
-------------------------------------+-----------
allow_in_place_tablespaces | superuser
allow_system_table_mods | superuser
application_name | user
array_nulls | user
backend_flush_after | user
backslash_quote | user
backtrace_functions | superuser
bytea_output | user
check_function_bodies | user
client_connection_check_interval | user
client_encoding | user
client_min_messages | user
commit_delay | superuser
commit_siblings | user
compute_query_id | superuser
constraint_exclusion | user
cpu_index_tuple_cost | user
cpu_operator_cost | user
cpu_tuple_cost | user
cursor_tuple_fraction | user
DateStyle | user
deadlock_timeout | superuser
debug_discard_caches | superuser
debug_pretty_print | user
debug_print_parse | user
debug_print_plan | user
debug_print_rewritten | user
default_statistics_target | user
default_table_access_method | user
default_tablespace | user
default_text_search_config | user
default_toast_compression | user
default_transaction_deferrable | user
default_transaction_isolation | user
default_transaction_read_only | user
dynamic_library_path | superuser
effective_cache_size | user
effective_io_concurrency | user
enable_async_append | user
enable_bitmapscan | user
enable_gathermerge | user
enable_hashagg | user
enable_hashjoin | user
enable_incremental_sort | user
enable_indexonlyscan | user
enable_indexscan | user
enable_material | user
enable_memoize | user
enable_mergejoin | user
enable_nestloop | user
--More--
【 설정 파일의 분할과 통합 】
설정 파일은 분할하여 관리할 수도 있습니다. 복제 구성 등, 복수의 서버에서 일부 설정을 공유하고 싶은 경우 등에, 공유하는 설정과 서버 고유의 설정으로 파일을 분할하고, include 지시자로 통합(읽어올 다른 파일을 지정)하는 등의 사용법이 가능합니다. 예를 들어 아래의 예시에서는, postgresql.conf의 설정 중 메모리에 관한 설정만을 분리하여, include 지시자로 통합(읽어올 다른 파일을 지정)하고 있습니다.
< include 사용의 예>
・ postgresql.conf 파일의 내용 중...
include 'memory.conf'
・ memory.conf 파일의 내용...
# Memory Settings
shared_buffers = 512MB
work_mem = 64MB 커맨드 라인 파라미터에 의한 설정 기동 시 커맨드 라인 파라미터를 지정함으로써, postgresql.conf에서 설정하는 것과 마찬가지로 설정 값을 변경할 수 있습니다. 그러나, 설정값은 저장되지 않기 때문에 postgresql.conf로 설정하도록 하고, 설정 파일 자체도 버전 관리나 백업을 하는 것이 좋습니다.
【 ALTER SYSTEM 명령에 의한 변경 】
PostgreSQL 9.4 이후에서는, postgresql.conf와 같은 설정 파일을 직접 편집하는 방법 외에, ALTER SYSTEM이라는 SQL 커맨드로 설정 내용을 변경하는 것이 가능하게 되었습니다. SET 명령에 의한 설정 변경과는 달리, ALTER SYSTEM 명령에 의한 설정 변경은, 즉시 반영되지 않습니다. 또한, ALTER SYSTEM의 SET 서브 커맨드에 의한 설정을 RESET 서브 커맨드에 의해 취소할 수 있습니다. ALTER SYSTEM의 SET 서브 커맨드에 의한 설정 변경을 하면, postgresql.auto.conf라는 설정 파일의 내용이 갱신됩니다.
또한, ALTER SYSTEM의 RESET 서브 커맨드로 ALTER SYSTEM의 SET 서브 커맨드로 설정한 내용을 삭제할 수 있습니다. 다만, ALTER SYSTEM에 의한 설정 변경은, SET 명령이나 RESET 명령처럼 즉시 반영되지 않습니다. 설정 재로드(pg_ctl reload)나, 서버 재시작(pg_ctl restart)을 실시함으로써, postgresql.auto.conf에 설정된 내용이 반영됩니다. 서버의 재부팅이 필요한 설정 변경(예: 공유 버퍼의 변경)의 경우에는, pg_clt reload에서는 반영되지 않고, pg_ctl restart에 의한 서버의 재부팅이 필요하므로 주의가 필요합니다. ALTER SYSTEM 명령에 의한 설정 변경은, 종래와 같이 설정 파일을 직접 편집하는 것보다, 아래와 같은 점에서 장점이 있습니다.
< ALTER SYSTEM 명령에 의한 영구적인 설정 변경의 예 >
· shared_buffers에는 128MB
[postgres@postgresql ~]$ psql -U postgres postgres -c "SHOW shared_buffers"
shared_buffers
----------------
128MB
(1 row)
· shared_buffers의 값을 256MB로 변경하지만, 현재의 설정값은 변경되지 않음
[postgres@postgresql ~]$ psql -U postgres postgres -c "ALTER SYSTEM SET shared_buffers = '256MB'"
ALTER SYSTEM
[postgres@postgresql ~]$ psql -U postgres postgres -c "SHOW shared_buffers"
shared_buffers
----------------
128MB
(1 row)
· Postgresql.auto.conf 파일에, ALTER SYSTEM 명령에 의한 설정이 추가 됨
[postgres@postgresql ~]$ cat $PGDATA/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
shared_buffers = '256MB'
· PostgreSQL 서버를 재부팅한 후, shared_buffers의 설정을 확인하면, 256MB로 변경되어 있음
[postgres@postgresql ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@postgresql ~]$ pg_ctl start
waiting for server to start....2023-10-30 03:41:55.340 UTC [2994] LOG: redirecting log output to logging collector process
2023-10-30 03:41:55.340 UTC [2994] HINT: Future log output will appear in directory "log".
done
server started
[postgres@postgresql ~]$ psql postgres -c "SHOW shared_buffers"
shared_buffers
----------------
256MB
(1 row)
· ALTER SYSTEM RESET 서브 커맨드를 실행하면 postgresql.auto.conf 내의 설정을 삭제할 수 있음
[postgres@postgresql ~]$ psql -U postgres postgres -c "ALTER SYSTEM RESET shared_buffers"
ALTER SYSTEM
[postgres@postgresql ~]$ cat $PGDATA/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
· 설정의 실수를, ALTER SYSTEM 커맨드를 실행할 때 체크할 수 있음
· 슈퍼 유저가 원격 서버에서 로그인할 수 있는 환경의 경우, 영구적인 설정 변경을 원격 서버에서 실행할 수 있음
ALTER SYSTEM에 의한 변경과, postgresql.conf의 변경이 모두 이루어진 경우에는, ALTER SYSTEM에 의한 변경 결과가 우선되니 주의해야 합니다. 예를 들어, ALTER SYSTEM에서 shared bufters의 값을 '512MB'로 설정하고, 그 후, postgresql.conf 내에서 shared_bufters의 값을 '1024MB'로 설정하고 서버를 다시 시작한 경우에는, ALTER SYSTEM에서 설정한 'shared_buffers = "512MB"가 반영됩니다.
서버 설정을 변경할 때, ALTER SYSTEM을 사용하든지, postgresql.conf의 수정만 하든지, 체계화하는 것을 권장합니다.
「 pg_hba.conf 파일 」
클라이언트에서 PostgreSQL로의 접속과 인증에 관한 설정은, pg_hba. conf 파일에 기술합니다. 파일 이름의 hba는 host-based authentication(호스트 기반 인증)을 의미합니다. 접속과 인증 기능은, 데이터베이스로의 접속을 제한하기 위한 중요한 기능입니다. PostgreSQL에서는, 어디서 누가 어느 데이터베이스에 접속하느냐에 따라 접속과 인증을 관리하고 있습니다. 보통, pg_hba.conf 파일은 postgresql.conf와 마찬가지로, 데이터베이스 클러스터에 위치해 있습니다만, postgresql.conf의 hba_file 파라미터를 지정함으로써 파일이 위치할 곳을 변경할 수 있습니다. pg_hba.conf 파일의 기술 예는 아래와 같습니다.
< pg_hba.conf 파일의 기술 예 >
[postgres@postgresql ~]$ cat $PGDATA/pg_hba.conf
~~~~~~~~~~~~~~//~~~~~~~~~~~~~~
# TYPE DATABASE USER ADDRESS METHOD
local all postgres trust
host all postgres localhost trust
host db1,db2 admin 192.168.100.10 scram-sha-256
host db1 user1 192.168.100.0/24 scram-sha-256
host db2 user2 192.168.100.0/24 scram-sha-256위의 예에서는 아래 표와 같은 설정으로 인증하고 있습니다.
pg_hba.conf 파일은, PostgreSQL 기동 시와 마스터 서버 프로세스에 SIGHUP 시그널을 보낸 타이밍에 로드됩니다. SIGHUP 시그널은, PostgreSQL 서버 명령의 pg_ctl 명령에 reload 옵션을 부여하여 수행할 수 있습니다.
【 기술형식 】
pg_hba.conf는, 1행에 1개의 인증 규칙을 기술합니다. 1개의 접속에 필요한 정보는 아래의 표와 같습니다.
각 행에서는 '접속 방법', '접속 데이터베이스'. '접속 사용자', '인증 방식'의 정보를 공백 문자(스페이스 또는 탭) 구분으로 기술합니다. 또한, #(해시 기호) 이후의 줄 끝까지는 코멘트로 간주됩니다.
PostgreSQL에 대해, 1개의 클라이언트뿐만 아니라 복수의 클라이언트에서 접속하는 경우도 있기 때문에, pg_hba.conf 파일에서는 복수의 접속에 관한 설정 정보를 기술할 수 있게 되어 있습니다.
< 기술 순서의 적절 / 부적절한 예 >
· ① 적절한 설정
# TYPE DATABASE USER ADDRESS METHOD
local all all trust
host all all 192.168.100.0/32 trust
host all all 192.168.100.0/24 scram-sha-256
· ② 부적절한 설정
# TYPE DATABASE USER ADDRESS METHOD
host all all 192.168.100.0/24 scram-sha-256
host all all 192.168.100.10/32 trust
local all all trust동일한 접속 조합 파라미터(접속 방식, 접속 데이터베이스, 접속 사용자, 접속을 하는 곳의 IP주소)가, 다른 인증 방식으로 여러 줄로 기술된 경우, PostgreSQL 기동 시에 에러나 경고는 출력되지 않습니다. 접속 요구 시에는, 위에서부터 순서대로 체크합니다. 이 순차성을 이용하여, 먼저 범위를 좁혀 접속을 하는 곳의 IP 주소와 패스워드가 필요 없는 느슨한 인증 방식을 기술하고, 그 이후에는 보다 접속을 하는 곳의 IP 주소의 범위를 넓히면서 인증 정보가 필요한 보다 엄격한 인증 방식(예: scram-sha-256)을 지정합니다.
위의 예 ②처럼, 만일 192.168.100.10/32에서 trust 인증으로 접속하려고 해도, ①의 192.168,100.0/24가 먼저 범위에 포함되어 있기 때문에, md5 인증이 적용되어 버리는 의도하지 않은 설정이 될 수도 있으니 주의해야 합니다.
【 접속 방식 (TYPE) 】
pg_hba.conf 파일에 지정하는 접속 방식(TYPE)은 'local', 'host'. 'hostssl'. 'hostnossl'의 4종류로, postgresql.conf 파일의 listen_addresses 설정값(PostgreSQL 서버가 접속을 받는 호스트명/IP 주소)에 따라 달라집니다.
또한, PostgreSQL 12 버전 이후에서는, GSSAPI 암호화와 관련된 접속 방식이 추가되었습니다.
· local
Unix 도메인 소켓을 사용하는 접속에 대응합니다. Postgresql.conf의 listen_addresses에 빈 문자열을 지정한 경우에 지정합니다.
· host
TCP/IP를 사용한 접속에 대응합니다. SSL 통신의 유무는 묻지 않습니다. 당연하지만, postgresql.conf의 listen_addresses에서 'localhost'만 설정되어 있는 경우는, 다른 서버에서 접속할 수 없습니다.
· hostssl
SSL을 이용한 통신 방식에만 대응하는 지정입니다.
· hostnossl
SSL을 사용하지 않는 통신 방식에만 대응하는 지정입니다.
· hostgssenc
TCP/IP를 사용한 접속이며, GSSAPI 암호화를 사용한 접속에만 대응하는 지정입니다(PostgreSQL 12 이후).
· hostnogssenc
TCP/IP를 사용한 접속이며, GSSAPI 암호화를 사용하지 않는 접속에만 대응하는 지정입니다(PostgreSQL 12 이후).
덧붙여 local 접속의 경우는 '접속 데이터베이스', '접속 유저', '인증 방식'을 지정하고, host / hostssl / hostnossl / hostgssenc / hostnogssenc 접속의 경우는, 더불어 'IP 주소'를 지정합니다.
PostgreSQL에서는 SSL 접속을 지원하고 있어, 클라이언트와 PostgreSQL 서버 간의 통신을 암호화할 수 있습니다. 이용에는 다음 조건이 필요합니다.
・ OpenSSL이 PostgreSQL 서버뿐만 아니라 클라이언트 양쪽에 설치되어 있음
・ PostgreSQL 빌드 시 SSL 연결을 활성화하는 옵션을 부여
(RPM으로 설치한 경우에는, SSL접속을 유효하게 하는 지정으로 되어 있습니다. 소스 코드로 빌드하는 경우에는, configure 명령 실행 시 -withopenssl 옵션을 부여해야 합니다.)
・ 설치 후, postgresql.conf 파일에서 ssl 파라미터의 설정값을 'on'으로 지정하고 PostgreSQL을 기동
【 접속 데이터베이스 (DATABASE) 】
접속 대상이 되는 데이터베이스명을 기술합니다. 복수 지정하는 경우는 쉼표로 구분합니다. 또한, 데이터베이스명 이외에 'all', 'sameuser'. 'samerole', 'replication'을 지정할 수도 있습니다.
・ all
모든 데이터베이스에의 접속에 대응합니다. 사용자에 의해 액세스 하는 데이터베이스를 제한하지 않는 경우에 지정합니다.
< 접속 데이터베이스의 이름이 'all'의 예 >
[postgres@postgresql ~]$ cat $PGDATA/pg_hba.conf
~~~~~~~~~~~~~~//~~~~~~~~~~~~~~
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all postgres trust
# IPv4 local connections:
host all postgres localhost trust
host db1,db2 admin 127.0.0.1/32 scram-sha-256
host db1 test1 127.0.0.1/32 scram-sha-256
host db2 test2 127.0.0.1/32 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
[postgres@postgresql ~]$ psql
psql (14.9)
Type "help" for help.
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# exit
[postgres@postgresql ~]$ psql -d db1
psql (14.9)
Type "help" for help.
db1=#※ postgres 유저를 이용해 local 접속 또는 Iocalhost로부터의 TCP/IP 접속을 할 경우, 임의의 데이터베이스(db1, db2, postgres 등)에 trust 인증으로 접속할 수 있습니다.
・ sameuser
지정한 유저(접속하려는 OS 유저)와 같은 이름의 데이터베이스에의 접속에 대응합니다.
< 접속 데이터베이스의 이름이 'sameuser'의 예 >
[postgres@postgresql ~]$ cat $PGDATA/pg_hba.conf
~~~~~~~~~~~~~~//~~~~~~~~~~~~~~
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local sameuser all trust
# IPv4 local connections:
host sameuser all 127.0.0.1/32 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
[postgres@postgresql ~]$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
db1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
db2 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
tpch | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(7 rows)
[postgres@postgresql ~]$ psql -c "\du" postgres
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+---------------------
ezis | | {pg_read_all_stats}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test | | {}
test1 | | {}
test2 | | {}
[test@postgresql ~]$ psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: no pg_hba.conf entry for host "[local]", user "test", database "test", no encryption
[test@postgresql ~]$ psql
psql (14.9)
Type "help" for help.
test=> \c db1
connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: no pg_hba.conf entry for host "[local]", user "test", database "db1", no encryption
Previous connection kept
test=> exit
[test@postgresql ~]$ psql -d db1
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: no pg_hba.conf entry for host "[local]", user "test", database "db1", no encryption
[test@postgresql ~]$ psql
psql (14.9)
Type "help" for help.※ psql의 '\du' 커맨드로 유저의 목록을 확인할 수 있습니다. 위의 pg_hba.conf의 설정에서는, 같은 사용자 이름과 같은 이름의 데이터베이스에의로만 접속을 허가하고 있습니다.
・ samerole
데이터베이스와 같은 이름의 역할의 멤버여야 한다는 의미입니다.
< 접속 데이터베이스의 이름이 'samerole'의 예 >
[postgres@postgresql ~]$ cat $PGDATA/pg_hba.conf
~~~~~~~~~~~~~~//~~~~~~~~~~~~~~
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local samerole all trust
# IPv4 local connections:
host samerole all 127.0.0.1/32 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
[postgres@postgresql ~]$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
db1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
db2 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
tpch | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(7 rows)
[postgres@postgresql ~]$ psql -c "\du" postgres
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+---------------------
ezis | | {pg_read_all_stats}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test | Cannot login | {}
test1 | | {test}
test2 | | {test}
[test1@postgresql ~]$ psql -d test
psql (14.9)
Type "help" for help.
test=> \c postgres
connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: no pg_hba.conf entry for host "[local]", user "test1", database "postgres", no encryption
Previous connection kept
test=> exit
[test1@postgresql ~]$ psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: database "test1" does not exist
[test1@postgresql ~]$ exit
logout
[root@postgresql ~]# su - postgres
Last login: Wed Nov 1 01:51:50 UTC 2023 on pts/2
[postgres@postgresql ~]$ psql -d test
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: no pg_hba.conf entry for host "[local]", user "postgres", database "test", no encryption
※ 위의 예에서는 test1이 속한 역할(test)과 같은 이름의 데이터베이스(test)에 접속을 허가하고 있습니다. postgres는 test 역할에 속하지 않기 때문에, 비록 슈퍼유저라고 해도 test 데이터베이스에의 접속은 허용되지 않습니다.
・ replication
복제 접속에 대응합니다.
'@' 기호가 앞에 있는 경우
데이터베이스명 자체가 아니라, 데이터베이스명을 포함한 파일명을 나타냅니다. 파일명은, pg_hba.conf가 존재하는 디렉터리로부터의 상대 경로, 또는 절대 경로로 기술합니다.
< 접속 데이터베이스의 이름이 '@파일명'의 예 >
[postgres@postgresql data]$ cat dbname.conf
db1,test
[postgres@postgresql ~]$ cat $PGDATA/pg_hba.conf
~~~~~~~~~~~~~~//~~~~~~~~~~~~~~
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local @dbname.conf all trust
# IPv4 local connections:
host @dbname.conf all 127.0.0.1/32 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
[postgres@postgresql ~]$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
db1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
db2 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
tpch | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(7 rows)
[postgres@postgresql ~]$ psql -c "\du" postgres
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+---------------------
ezis | | {pg_read_all_stats}
file_user | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
[postgres@postgresql ~]$ psql -U file_user db2
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: no pg_hba.conf entry for host "[local]", user "file_user", database "db2", no encryption
[postgres@postgresql ~]$ psql -U file_user db1
psql (14.9)
Type "help" for help.
db1=> \c test
You are now connected to database "test" as user "file_user".
test=> \c db2
connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: no pg_hba.conf entry for host "[local]", user "file_user", database "db2", no encryption
Previous connection kept
test=>※ $PGDATA/dbname.conf 파일에 db1과 test의 데이터베이스 이름이 기술되어 있습니다. pg_hba.conf 파일에서 @dbname.conf라고 기술함으로써, 텍스트 파일 내에 기술된 db1과 test 데이터베이스에 로그인할 수 있습니다.
PostgreSQL에서는 롤이라는 개념을 사용하여 사용자나 사용자 그룹을 관리하고 있습니다. 롤은 주로 데이터베이스 오브젝트의 소유 권한이나, 각종 조작의 실행 권한을 제어하는 데 사용됩니다. 롤은 권한뿐만 아니라 롤 자체의 속성을 가지고 있습니다. 속성 중 하나에 '로그인'이 있습니다. CREATE USER 명령으로 사용자를 작성한 경우, 로그인 속성은 기본적으로 활성화되지만, CREATE ROLE 명령으로 사용자를 작성한 경우에는 기본적으로 무효가 됩니다. 예를 들어, 로그인 속성이 없는 유저로 데이터베이스에 로그인하려고 하면, 로그인이 거부됩니다.
【 접속 유저 (USER) 】
접속 시 데이터베이스 유저명을 기술합니다. All은, 모든 데이터베이스 유저로부터의 접속에 대응합니다. 그 외 지정의 경우는, 앞에 '+'가 있음/없음의 2가지 패턴으로 설정이 가능합니다.
・ 앞에 '+'가 없는 경우
기술에 완전히 일치하는 데이터베이스 사용자명에 대응합니다.
・ 앞에 '+'가 있는 경우
지정된 역할의 멤버와 일치하는 경우에 대응합니다.
< '+'가 쓰여진 경우의 동작 예 >
[postgres@postgresql data]$ cat pg_hba.conf
~~~~~~~~~~~~~~//~~~~~~~~~~~~~~
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host users +users 127.0.0.1/32 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
[postgres@postgresql data]$ psql -l -U postgres
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
tpch | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
users | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(6 rows)
[postgres@postgresql data]$ psql -c "\du"
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+---------------------
ezis | | {pg_read_all_stats}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test | | {}
user1 | | {users}
user2 | | {users}
users | Cannot login | {}
[postgres@postgresql data]$ psql -h 127.0.0.1 users -U user1
Password for user user1:
psql (14.9)
Type "help" for help.
users=> \c test
connection to server at "127.0.0.1", port 5432 failed: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "user1", database "test", no encryption
Previous connection kept
users=> \q
[postgres@postgresql data]$ psql -h 127.0.0.1 users -U user2
Password for user user2:
psql (14.9)
Type "help" for help.
users=> \q
[postgres@postgresql data]$ psql -h 127.0.0.1 users -U users
Password for user users:
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL: role "users" is not permitted to log in
[postgres@postgresql data]$ psql -h 127.0.0.1 users -U test
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "test", database "users", no encryption※ user1과 user2는 users 롤의 멤버이기 때문에, users 데이터베이스에 로그인할 수 있습니다. 또한, users 롤 자체는 로그인 권한이 없기 때문에, 로그인할 수 없으며, test 유저는 users의 멤버가 아니기 때문에, 로그인할 수 없습니다.
'all'이라는 키워드는 pg_hba.conf에서는 특수한 의미를 가지지만, PostgreSQL로서는 all이라는 명칭의 데이터베이스나 롤의 작성을 금지하고 있지는 않습니다.
하지만, all이라는 이름의 데이터베이스나 롤이 존재하더라도 상관없이, all 이라는 키워드로 규정된 동작을 하게 됩니다.
이중 따옴표로 이러한 특수한 키워드를 인용하면, 특수한 의미는 없어집니다. 예를 들어 all이라는 이름의 롤을 만들고, all이라는 데이터베이스에만 접속 가능하게 한 경우에는, "al"과 같이 설정합니다. 이처럼 회피 방법은 있지만, all / samerole / sameuser / replication 등, 특수한 명칭의 데이터베이스나 롤 작성은 피하는 편이 좋습니다.
【 접속을 하는 곳의 IP 주소 (ADDRESS) 】
서버에 연결하는 클라이언트의 주소를 기술합니다. 주소의 기술방법은 '호스트명' 'IP주소(IPv4, IPv6)'를 지정할 수 있습니다.
IPv4에서의 '0.0.0.0/0', IPV6에서의 ':/0'은, 각각 모든 IP 주소를 나타내는 특수한 표기법입니다. ' All'은, IPv4/IPv6 모두 모든 IP 주소에 일치한다는 의미입니다. 'samehost'는 서버가 가진 모든 IP 주소로, 'samenet'은 서버가 접속하고 있는 서브넷 내의 IP 주소와 일치한다는 의미입니다.
IP 주소를 지정할 경우, CIDR 마스크를 지정함으로써, 단일, 또는 임의의 범위의 IP 주소로부터의 연결을 허용하는 기술도 가능합니다. 단일 주소를 지정할 경우, IPv4에서는 CIDR 마스크로 32를 지정하고, IPv6에서는 128을 지정합니다.
【 인증 방식 (METHOD) 】
클라이언트에서 접속할 때의 인증 방식을 기술합니다. PostgreSQL에서는, 아래와 같은 인증 방식이 지원됩니다.
인증 방식의 일부에는 특정 접속 방식을 이용해야 하는 것도 있습니다. ident 인증과 GSSAPI 인증은, pg_hba.conf 파일뿐만 아니라, pg_ident.conf 파일(다음 절)에도 설정할 필요가 있습니다.
어떤 인증 방식을 선택할지는, 시스템의 요건에 달려 있습니다. 시스템 요건으로 명확하게 인증 방식이 요구되면, 그에 적합한 인증 방식을 선택하게 됩니다. 단순한 인증 제한을 두고 싶은 경우에도, 비밀번호 문자열의 암호학적 해시 형식을 보내는 'scram-sha-256' 방식을 선택하는 것이 무난합니다. 'trust'는, 검증 환경이나 외부에서 고립된 네트워크 내에서 사용하는(또한 데이터베이스에 접근 가능한 사용자를 말할 수 있는) 경우에 사용합니다.
특정 호스트를 제외하기 위해서는 'reject'를 사용합니다. 예를 들어, reject 지정의 주소로 '192168100.100'이라고 기술하고, 그 후에 tust 지정의 IP주소로 '192.168100.0/24'를 지정했다고 합시다. 이렇게 함으로써, 192.168.100.* 범위의 IP주소에서 접속을 한 경우에, 192168.100100으로부터의 접속만 거부하고, 그 이외의 모든 주소로부터의 접속 지정을 허가합니다.
PostgreSQL 10에서 pg_hba.conf 파일의 내용을 SELECT 명령으로 참조할 수 있는 pg_hba_file_rules 뷰가 추가되었습니다.
< pg_hba_file_rules 뷰 >
[postgres@postgresql data]$ psql
psql (14.9)
Type "help" for help.
postgres=# select * from pg_hba_file_rules;
line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+-------+---------------+-----------+-----------+-----------------------------------------+---------------+---------+-------
83 | local | {all} | {all} | | | peer | |
85 | host | {all} | {all} | 0.0.0.0 | 0.0.0.0 | md5 | |
86 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | scram-sha-256 | |
88 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | scram-sha-256 | |
91 | local | {replication} | {all} | | | peer | |
92 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | scram-sha-256 | |
93 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | scram-sha-256 | |
(7 rows)
postgres=# \q
[postgres@postgresql data]$ psql -h 127.0.0.1 -U user1 -d users
Password for user user1:
psql (14.9)
Type "help" for help.
users=> select * from current_user;
current_user
--------------
user1
(1 row)
users=> select * from pg_hba_file_rules;
ERROR: permission denied for view pg_hba_file_rules이 뷰는, pg_hba.conf의 파일 내에 기술 에러가 있을 경우, 그 에러 내용을 error 열에 표시합니다. pg_ctl reload로 설정을 반영하기 전에, 이 pg_hba_file_rules 뷰를 확인하고, 설정 실수를 미연에 방지할 수도 있습니다. 또한, 이 뷰는 권한을 가진 유저만 확인할 수 있습니다.
「 pg_ident.conf 파일 」
pg_ident.conf 파일은, ident 인증(아래 그림 참조)이나 GSSAPI 인증 등 외부 인증 시스템을 이용하는 경우에 사용되는, 데이터베이스 클러스터에 위치한 유저명 맵 설정 파일입니다. ident 인증이나 GSSAPI 인증은, 외부 인증용 서버를 통해서 인증이 됩니다. 이 때문에, 인증 서버가 있는 환경에서 사용이 가능합니다.
외부 인증 시스템을 사용할 때는, OS의 사용자명이 데이터베이스 사용자명과 다를 수 있으므로, pg_ident.conf에 사용자 이름의 매핑 정보를 기술하여 대응합니다. pg_ident.conf 파일은 다음 3개의 열로 구성된 행을 1개의 설정으로 기술합니다.
・ map name (MAPNAME)
pg_hba.conf의 auth-options에서 참조되는 임의의 명칭을 설정합니다.
・ system user name (SYSTEM-USERNAME)
접속을 허용하는 클라이언트의 OS 사용자명을 설정합니다. 정규 표현식을 이용한 지정이 가능합니다.
・ database user name (PG-USERNAME)
system user name으로 설정한 클라이언트의 OS 유저가, PostgreSQL 서버의 어느 유저로 접속할지를 설정합니다. 아래의 2가지 예는, pg_ident.conf와 pg_hba.conf의 설정 예입니다.
먼저 아래의 예는, OS 유저 foo로 로그인했을 때, 데이터베이스 유저 user1로 접속하는 설정입니다.
< pg_ident.conf의 설정 예 >
[postgres@postgresql data]$ cat pg_ident.conf
~~~~~~~~~~~~~~//~~~~~~~~~~~~~~
# MAPNAME SYSTEM-USERNAME PG-USERNAME
foo_ident foo user1
아래의 예는, pg_hba.conf의 map 옵션에서 사용자 이름 맵의 규칙을 설정하는 예입니다.
< pg_hba.conf 설정의 예 >
[postgres@postgresql data]$ cat pg_hba.conf
~~~~~~~~~~~~~~//~~~~~~~~~~~~~~
# TYPE DATABASE USER ADDRESS METHOD
host all all 192.168.10.0/24 ident map=foo_ident
* 기본 설정으로 운용하지 말고, 필요한 설정은 환경에 맞게 변경하도록 합니다.
* 시스템의 보안 요건에 맞춰 적절한 접속 인증을 설정하도록 합니다.
'database > postgresql' 카테고리의 다른 글
| 처리 및 제어의 기본 (0) | 2023.11.06 |
|---|---|
| 아키텍처의 기본 (0) | 2023.10.07 |
| PostgreSQL이란? (0) | 2023.10.06 |
최근댓글