PostgreSQL Authentication
A client connecting to PostgreSQL must be authenticated, and authentication is handled through the $PGDATA/pg_hba.conf file. Each record in this file consists of 5 fields (Type, Database, User, Address, Method) and either authenticates specific users or restricts connections from specific IP ranges. The file follows these rules:
- Anything after a # (comment) character is ignored
- Blank lines are ignored
- Ending a line with a backslash (\) continues the record on the next line
- Fields are separated by spaces and tabs
📌 HBA stands for Host-Based Authentication.
pg_hba.conf Sample
📌 127.0.0.1/32 : the local loopback address, the IP address that refers to the host itself (same as localhost)
::1/128 : the IPv6 local loopback address
Record format
Each record in pg_hba.conf consists of 5 fields. The meaning of each field and its available options are as follows.
TYPE
Set one of local, host, hostssl, hostnossl, hostgssenc, or hostnogssenc.
- local is used for connections over Unix domain sockets.
- host, hostssl, hostnossl, hostgssenc, and hostnogssenc are all used for connections over TCP/IP.
DATABASE
Specifies the database names that can be connected to. Separate multiple names with commas (,).
- all means every database except replication.
- sameuser means the database with the same name as the user.
- samerole means the connecting user must be a member of the role with the same name as the database being connected to.
- replication allows replication connections by users with the replication privilege.
- A separate file containing database names can be specified as @filename.
USER
Specifies the roles that can connect. Separate multiple roles with commas (,).
- all means every role.
- Prefixing a database user or group name with + means the roles that are members of that user or group.
- A separate file containing role names can be specified by prefixing the file name with @, as @filename.
ADDRESS
Specifies the host names or IP address ranges that can connect. Specify IPv4 addresses in the form 10.10.30.160/24 and IPv6 addresses in the form fe80::a4e8:f42d:2556:f68a/96. You can also specify a hostname (alias).
- all means every IP address.
- samehost means the IP addresses of the server PostgreSQL is running on.
- samenet means the same network as the PostgreSQL server.
METHOD
Specifies the authentication method used when connecting to the database.
METHOD | Description |
trust | Any role can connect without a password |
reject | Reject the connection |
scram-sha-256 | encrypted password
The most secure password authentication, but not supported by some clients |
md5 | Password authentication |
password | Password authentication that sends the password in plain text |
gss | Single sign-on using GSSAPI.
One login gives access to multiple services
** GSSAPI : the authentication itself is secure, but without SSL, data sent over the database connection is transmitted unencrypted |
sspi | Single sign-on using SSPI
** SSPI : a Windows technology for secure authentication |
ident | Ident authentication. Authenticates through ident.conf |
peer | Peer authentication
Takes the client OS user and uses it as the DB user. Supported only for local connections. |
ldap | Uses the LDAP authentication mechanism |
radius | Uses the RADIUS authentication mechanism. Works like the password method |
cert SSL | Authentication using client certificates |
pam | Uses the PAM authentication mechanism. Works like the password method
** PAM : Pluggable Authentication Modules |
bsd | Uses the BSD authentication mechanism. Works like the password method |
Related articles
