postgres configuration 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.** Not use to authenticate external users!!!!!** |
| 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.
Examples
local all postgres peer # Dozwolone jest logowanie się użytkownika postgres do wszystkich baz danych (all),
# jeśli przedstawia się on nazwą użytkownika systemu linux (peer) i łączy się za pośrednictwem unix-socket (local)
local all all peer # Dozwolone jest logowanie się dowolnego użytkownika (all) do wszystkich baz danych (all), jeśli
# przedstawia się on nazwą użytkownika systemu linux (peer) i łączy się za pośrednictwem unix-socket (local)
host all all 127.0.0.1/32 md5 # Dozwolone jest logowanie się dowolnego użytkownika (all) do każdej bazy danych (all) jeśli łączy się za pomocą
# połączenia sieciowego (host) z adresu pętli lokalnej (127.0.0.1/32) i uwierzytelnia się za pomocą loginu i hasła (md5)
Example1
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# For dedicated database host
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
host all all 0.0.0.0/0 scram-sha-256
- https://www.postgresql.org/docs/current/auth-pg-hba-conf.html The PostgreSQL HBA file (pg_hba.conf) is another text file that contains the connection allowance: it lists the databases, users, and networks that are allowed to connect to your cluster. The HBA method can be thought of as a firewall embedded into PostgreSQL.
The rules are evaluated from top to bottom, and the first matching rule causes the end of the evaluation. So the order of rules within pg_hba.conf matter.
<connection-type> <database> <role> <remote-machine> <auth-method>
- connection-type is the type of connection supported by PostgreSQL and is either local (meaning via operating system sockets), host (TCP/IP connection, either encrypted or not), or hostssl (TCP/IP encrypted only connection), or nohostssl (TCP/IP non-encrypted connections).
- database is the name of a specific database that the line refers to or the special keyword all, which means every available database. The special replication keyword is used to handle a special type of connection used to replicate the data to another cluster, and it will be explained in later chapters.
- role is the specific role (either a username or a group) that the line refers to or the special keyword all, which means all available roles (and groups).
- connection-type is the type of connection supported by PostgreSQL and is either
local(meaning via operating system sockets),host(TCP/IP connection, either encrypted or not), orhostssl(TCP/IP encrypted only connection), ornohostssl(TCP/IP non-encrypted connections). - database is the name of a specific database that the line refers to or the special keyword all, which means every available database. The special replication keyword is used to handle a special type of connection used to replicate the data to another cluster, and it will be explained in later chapters.
- role is the specific role (either a username or a group) that the line refers to or the special keyword all, which means all available roles (and groups).
The authentication method trust should never be used; it allows any role to connect to the database if the Host-Based-Access (HBA) has a rule that matches the incoming connection. This is the method that is used when the cluster is initialized in order to enable the freshly created superuser to connect to the cluster. You can always use this trick as a last resort if you get yourself locked out of your own cluster.
If more rules have the same authentication method and connection protocol, then it is possible to collapse them into an aggregation. This can help you manage the host-based access configuration.
host forumdb,learnpgdb luca, enrico samenet scram-sha-256
Example
host all luca carmensita scram-sha-256
hostssl all test 192.168.222.1/32 scram-sha-256
host digikamdb pgwatch2 192.168.222.4/32 trust
host digikamdb enrico carmensita reject
The first line indicates that the user luca can connect to every database within the cluster (via the all clause) via a TCP/IP connection (via the host clause) coming from a host named carmensita, but he must provide a valid username/password to verify the SCRAM authentication method.
The second line states that the user test can connect to every database in the system over an SSL-encrypted connection (see the hostssl clause), but only from a machine that has the IPv4 address of 192.168.222.1; again, the credentials must pass the SCRAM authentication method.
The third line states that access to the digikamdb database is granted only to the pgwatch2 user over a nonencrypted connection from the host 192.168.222.4; this time, access is granted (trust) without any credential being required.
Finally, the last line rejects any incoming connection from the host named carmensita, opened by the user enrico against digikamdb; in other words, enrico is not able to connect to digikamdb from the carmensita host.
The role field in every pg_hba.conf rule can be substituted by the name of a group (remember that a group is itself a role); however, in order to make the rule valid for every member of the group, you have to prefix the group name with a + (plus) sign. Example
host forumdb book_authors all scram-sha-256 # loca as member of book_authors group cannot connect
host forumdb +book_authors all scram-sha-256 # luca as member of book_authors group can connect
The pg_hba.conf rules, when applied to a group name (that is, with the + preceding the role name) include all the direct and indirect members
If we want to allow every group member except one to access the database
host forumdb luca all reject
host forumdb +book_authors all scram-sha-256
If you specify the role field with an “at” sign prefix (@), the name is interpreted as a line-separated text file (as a relative name to the PGDATA directory).
host forumdb @rejected_users.txt all reject
host forumdb @allowed_users.txt all scram-sha-256
$ sudo cat $PGDATA/rejected_users.txt
luca
enrico
$ sudo cat $PGDATA/allowed_users.txt
+book_authors, postgres
It is possible to specify the file contents as either a line-separated list or a comma-separated list of usernames. It is also possible to specify which roles to use as a group by placing a + sign in front of the role name.
SELECT line_number, type,
database, user_name,
address, auth_method
FROM pg_hba_file_rules;
It is possible to include other HBA configuration files into the main pg_hba.conf file. PostgreSQL provides three main directives:
- include_file includes a specific file in pg_hba.conf
- include_if_exist includes a specific file but only if it exist; if it does not exist (or was removed), no error will occur
- include_dir includes all files specified in the given directory
Thanks to this directive, it is possible to define a set of small configuration files that will be included literally in the HBA configuration as if the administrator had edited the pg_hba.conf file directly.
In order to understand where a specific rule comes from, the pg_hba_file_rules catalog includes a file_name column that reports from which file (and at which line, thanks to line_number) a rule has been parsed.