postgres pg_hba.conf - ghdrako/doc_snipets GitHub Wiki
Use to specify and limit connections to the server from particular IP addresses or ranges. By utilizing this well-established feature, the server’s attack surface is effectively minimized, ensuring that access remains confined to trusted sources and contributing to enhanced overall security.
By default, this file is located in the PGDATA directory.
HBA - Host-Based Authentication
Any change made to the pg_hba.conf file will not become
active immediately. This file is read during the PostgreSQL server
startup or after the SIGHUP signal. This can be done with the pg_ctl reload command or by using the database function pg_reload_conf()
The authentication rules contain one per line (or row),each divided into columns:
- The type of connection.
- The name of the target database.
- The database user name.
- The source IP address or the network range, if applicable.
- The authentication method.
- local - client is connecting using the Unix-domain socket
- host - client connects through TCP/IP protocol; this rule would match independently if the communication uses or not the Secure Sockets Layer (SSL) for encryption.
- hostssl - This rule will match any connection through TCP/IP, and SSL is enabled for encryption. https://www.postgresql.org/docs/14/ssl-tcp.html.
- hostnossl - this rule matches any connection attempt made through TCP/IP that does not use SSL.
- hostgssenc -Similar to the SSL connection rules, this one matches those connection attempts with TCP/IP that use Generic Security Service Application Program Interface (GSSAPI) for the encryption.
- hostnogssenc - The opposite of the previous, it only matches when the connection is made through TCP/IP and does not use GSSAPI for encryption.
| Value | Desc |
|---|---|
| all | rule will match all the databases. |
| sameuser | The rule will match if the database is named after the user name used in the connection. |
| samerole | The rule matches if the database is named after a role the requested user is part of. |
| replication | The rule matches if the requested connection is a physical replication connection. |
Also, it is possible to specify multiple databases in the same column for
a single rule. In such cases, each is separated by a comma (,). As an
extension of this last option, you can add the list of databases in a
separate file and specify it by preceding the file name with the @ sign.
| Value | Desc |
|---|---|
| all | The rule will match all the user names. |
| + | The rule will match all the users* who are directly or indirectly members of the specified role. |
The value set for this column matches the rule for the client system IP address. You can specify an IPv4 or IPv6 address and include the corresponding Classless Inter-Domain Routing (CIDR) mask length after the slash sign (/)
| Auth method | Description |
|---|---|
| trust | Allow the connection unrestrictedly. Using this, there is no need for a password or other authentication. |
| reject | Reject the connection unconditionally. Using it, there won’t be anyauthentication requirement, and the connection request is immediately |
| rejected. It is helpful to filter out a host or an IP address range. | |
| scram-sha-256 | This performs a SCRAM-SHA-256 challenge-response authentication that prevents password sniffing and supports storing the password on |
| the server in a cryptographically hashed form that is considered very secure. Currently, it is the most secure method, but some old client libraries don’t support it. | |
| md5 | It uses a challenge-response mechanism that prevents password sniffing and doesn’t store the passwords on the server in plain text. However, it |
| doesn’t protect if an attacker steals the password hash. If md5 is set, but the password was encrypted for SCRAM, then SCRAM-based authentication is used. | |
| password | This method sends the password in clear text, so it is vulnerable to sniffing attacks. It should be avoided unless the connection is protected using SSL. |
| gss | Uses GSSAPI to authenticate the user; it is only available when using TCP/IP connections. |
| sspi | It uses SSPI, the full form is Security Support Provider Interface, to perform the user authentication. This option only applies to systems running Windows. |
| ident | Operates by obtaining the operating system user name for the client from an external ident server and using it as the database user. A user |
| name mapping (see next section pg_ident.conf) can be used in conjunction optionally. This method only is available in TCP/IP connections. | |
| peer | This one gets the operating system user name for the client from the kernel and verifies if matches with the database user. This is only available for local connections. |
| pam | This method performs the user authentication via the Pluggable Authentication Modules (PAM) service from the operating system. |
| ldap | Authenticates by using a Lightweight Directory Access Protocol or LDAP external server. |
| radius | Authenticates by using a Remote Authentication Dial-In User Service or RADIUS external server. |
| cert | Authenticates by using SSL client certificates, the server and the client have to interchange valid certificates. Only available when SSL |
| connections type is used. | |
| bsd | This method uses the BSD service from the operating system to perform the authentication. |
Some authentication methods accept these extra options. The values for this column can be a list of parameters in the form name=value. The following authentication methods can accept the extra options: ldap, pam, radius, sspi, ident, cert, and peer.
# TYPE DATABASE USER ADDRESS METHOD
# 1.
host all all 172.58.0.0/16 ident
map=sales
# 2.
host pgbench all 192.168.12.10/32 scram-sha-256
# 3.
host all fred .testdom.org md5
host all all .testdom.org scram-sha-256
# 4.
local sameuser all md5
local all @managers md5
local all +dba md5
pg_ident.conf
# NAME OS USER PG USER
# 5.
sales fred fred
sales karen karen
sales robert bob
sales fred tester
The following are the explanation of the lines from the previous example files.
- It allows connections from 172.58.0.0 hosts to any database if the ident is passed. If ident says the user is “fred” and the PostgreSQL connection was requested as user “tester,” it will succeed since there is an entry in pg_ident.conf for map “sales” that allows “fred” to connect as “tester.”
- It lets users from host 172.58.12.10 connect to the database “pgbench” if the password is correct.
- Any user from the testdom.org domain can connect to any database if the password is supplied. It requires SCRAM authentication except for user “fred” since it uses an old application that doesn’t support SCRAM.
- These lines let local users connect only to databases with the same name as their database user name, except for the users listed in the PGDATA/managers file and the members of role “dba,” who can connect to all databases. The password is asked in all cases.
- From the “sales” user mapping definition, we can see the operating system “robert” can connect to the database as “bob” no “robert,” “karen” as the same name, and “fred” can connect as “fred” or “tester” user.