PostgreSQL | PostgreSQL Configuration Files | How to Configure the pg_hba.conf File
The pg_hba.conf file describes authentication settings for clients that connect to PostgreSQL. This page explains how to configure the pg_hba.conf file.
Location of the pg_hba.conf File
Like postgresql.conf, pg_hba.conf is stored by default in the data directory where PostgreSQL was installed.
C:\Program Files\PostgreSQL\12\data>dir
C 드라이브의 볼륨에는 이름이 없습니다.
볼륨 일련 번호: XXXX-XXXX
C:\Program Files\PostgreSQL\12\data 디렉터리
2020-07-08 오전 12:00 <DIR> .
2020-07-08 오전 12:00 <DIR> ..
2020-07-08 오전 12:28 <DIR> base
2020-07-08 오전 12:00 45 current_logfiles
2020-07-08 오전 12:20 <DIR> global
2020-07-08 오전 12:00 <DIR> log
2020-03-10 오전 12:24 <DIR> pg_commit_ts
2020-03-10 오전 12:24 <DIR> pg_dynshmem
2020-03-10 오전 12:24 4,156 pg_hba.conf <------------- stored here.
2020-03-10 오전 12:24 1,678 pg_ident.conf
2020-07-08 오전 12:38 <DIR> pg_logical
2020-03-10 오전 12:24 <DIR> pg_multixact
2020-07-07 오후 11:20 <DIR> pg_notify
2020-03-10 오전 12:24 <DIR> pg_replslot
2020-03-10 오전 12:24 <DIR> pg_serial
2020-03-10 오전 12:24 <DIR> pg_snapshots
2020-07-07 오후 11:20 <DIR> pg_stat
2020-07-08 오후 11:35 <DIR> pg_stat_tmp
2020-03-10 오전 12:24 <DIR> pg_subtrans
2020-03-10 오전 12:24 <DIR> pg_tblspc
2020-03-10 오전 12:24 <DIR> pg_twophase
2020-03-10 오전 12:24 3 PG_VERSION
2020-03-10 오전 12:24 <DIR> pg_wal
2020-03-10 오전 12:24 <DIR> pg_xact
2020-03-10 오전 12:24 90 postgresql.auto.conf
2020-03-10 오전 12:24 27,377 postgresql.conf
2020-07-07 오후 11:20 91 postmaster.opts
2020-07-07 오후 11:20 70 postmaster.pid
8개 파일 33,510 바이트
20개 디렉터리 424,537,530,368 바이트 남음
C:\Program Files\PostgreSQL\12\data>
The pg_hba.conf file is a text file, so you can open it in a text editor to view or edit its contents.
# PostgreSQL Client Authentication Configuration File
# ===================================================
#
# Refer to the "Client Authentication" section in the PostgreSQL
# documentation for a complete description of this file. A short
# synopsis follows.
#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access. Records take one of these forms:
#
# local DATABASE USER METHOD [OPTIONS]
# host DATABASE USER ADDRESS METHOD [OPTIONS]
# hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
#
# (The uppercase items must be replaced by actual values.)
#
# The first field is the connection type: "local" is a Unix-domain
# socket, "host" is either a plain or SSL-encrypted TCP/IP socket,
# "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a
# plain TCP/IP socket.
#
# DATABASE can be "all", "sameuser", "samerole", "replication", a
# database name, or a comma-separated list thereof. The "all"
... omitted ...
pg_hba.conf Settings
The pg_hba.conf file is used to specify client addresses and role names, and to configure whether connections to databases are allowed. The current contents configured in pg_hba.conf are as follows.
# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
Each line is one setting. For example, the following setting means that all roles from clients with the IP address 127.0.0.1/32 are allowed to connect to all databases except replication, using the md5 authentication method.
# IPv4 local connections:
host all all 127.0.0.1/32 md5
Note: 127.0.0.1/32 is the local loopback address, which refers to the host itself. In this case, it is the IP address of the server where PostgreSQL is running. localhost is used with the same meaning. The IPv6 local loopback address is ::1/128.
Now examine each setting item.
Each setting has one of the following formats.
local DATABASE USER METHOD [OPTIONS]
host DATABASE USER ADDRESS METHOD [OPTIONS]
hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
TYPE is one of local, host, hostssl, and hostnossl. local is used when using a Unix-domain socket, so it is not used here because this is a Windows environment. host, hostssl, and hostnossl are all used for TCP/IP connections. host allows SSL or non-SSL connections, hostssl allows SSL connections, and hostnossl allows non-SSL connections.
DATABASE specifies the database names that can be connected to. When specifying multiple databases, separate them with commas. As a special value, all means all databases except replication, sameuser means a database with the same name as the user, and samerole means the connecting user must be a member of a role with the same name as the database being connected to. You can also specify a list of database names in another file by using the @file_name format.
USER specifies the role names that can connect. When specifying multiple roles, separate them with commas. The special value all means all roles. If you prefix a role name with +, it means a role used as a group, and members of that group role are allowed to connect. You can also specify a list of role names in another file by using the @file_name format.
ADDRESS specifies the host name or IP address range that can connect. For IPv4, specify an address in a format such as 172.20.143.0/24; for IPv6, specify an address in a format such as fe80::7a31:c1ff:0000:0000/96. The special value all means all IP addresses, samehost means the same IP address as the server where PostgreSQL is running, and samenet means the same network as the server.
When specifying a host name in ADDRESS, specify a host name such as blog.devkuma.com. If you specify a value such as .devkuma.com, it means all hosts under devkuma.com, such as blog.example.com.
METHOD specifies the authentication method. See the following table for setting values and authentication methods.
| Setting value | Description |
|---|---|
| trust | Allow connection with any role name without a password |
| reject | Reject the connection |
| scram-sha-256 | Password authentication; most secure, but not supported by some clients |
| md5 | Password authentication |
| password | Password authentication; sends the password in plain text |
| gss | Single sign-on using GSSAPI |
| sspi | Single sign-on using SSPI |
| ident | Ident authentication |
| peer | Peer authentication |
| ldap | LDAP authentication |
| radius | RADIUS authentication |
| cert SSL | Authentication using a client certificate |
| pam | PAM authentication |
| bsd | BSD authentication |
For example, to allow the user devkuma to connect to the database mydb from the 192.168.1.0/24 network using the md5 authentication method, write the following.
host mydb devkuma 192.168.1.0/24 md5
Regardless of the settings in the pg_hba.conf file, a connection cannot be made unless the client IP address or host is allowed to connect to PostgreSQL by the listen_addresses parameter in the postgresql.conf file. See “postgresql.conf Settings”.
–
Now try it in practice. In a situation where the client IP address is the same as the server IP address, allow access to the mydb database for the postgres role. In pg_hba.conf, comment out the existing settings and add the following line. After saving the configuration file, restart PostgreSQL once.
# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
# host all all 127.0.0.1/32 md5
# IPv6 local connections:
# host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
# host replication all 127.0.0.1/32 md5
# host replication all ::1/128 md5
host mydb postgres ::1/128 md5
First, connect to the mydb database that was configured to allow connections. Start Command Prompt and run the following.
psql -U postgres -d mydb
You are prompted for the user password, and after entering the password for the postgres role, the connection is established.
C:\>psql -U postgres -d mydb
Password for user postgres:
psql (12.2)
Type "help" for help.
mydb=#
Exit the PostgreSQL connection once, and this time try to connect to the postgres database by running the following.
psql -U postgres -d postgres
The following error appears and the connection fails.
C:\>psql -U postgres -d postgres
psql: error: could not connect to server: FATAL: no pg_hba.conf entry for host "::1", user "postgres", database "postgres", SSL off
C:\>
Therefore, if a connection is allowed in the pg_hba.conf file, you can connect to that database, but an error occurs when you try to connect to a database that has not been allowed.
–
This page explained how to configure pg_hba.conf.