postgres configuration auto.conf hba - ghdrako/doc_snipets GitHub Wiki

• https://www.postgresql.org/docs/current/config-setting.html

Every configuration parameter is associated with a context, and depending on the context, you can apply changes with or without a cluster restart. Available contexts are as follows:

• internal: A group of parameters that are set at compile time and therefore cannot be changed at runtime.
• postmaster: All the parameters that require the cluster to be restarted (that is, to kill the postmaster process and start it again) to activate them.
• sighup: All the configuration parameters that can be applied with a SIGHUP signal sent to the postmaster process, which is equivalent to issuing a reload signal in the operating system service manager.
• backend and superuser-backend: All the parameters that can be set at runtime but will be applied to the next normal or administrative connection.
• user and superuser: A group of settings that can be changed at runtime and are immediately active for normal and administrative connection.

The postgresql.auto.conf file has the very same syntax as the main postgresql.conf file but is automatically overwritten by PostgreSQL when the configuration is changed at runtime directly within the system, by means of specific administrative statements such as ALTER SYSTEM. The postgresql.auto.conf file is always loaded at the very last moment, therefore overwriting other settings. In a fresh installation, this file is empty, meaning it will not overwrite any other custom setting.

PostgreSQL configuration files are stored in the /etc/postgresql/<version>/main directory. For example, if you install PostgreSQL 12, the configuration files are stored in the /etc/postgresql/12/main directory.

The configuration file is reread whenever the main server process receives a SIGHUP signal; this signal is most easily sent by running pg_ctl reload from the command line or by calling the SQL function pg_reload_conf(). The main server process also propagates this signal to all currently running server processes, so that existing sessions also adopt the new values (this will happen after they complete any currently-executing client command). Alternatively, you can send the signal to a single server process directly.

PostgreSQL data directory contains a file postgresql.auto.conf , which has the same format as postgresql.conf but is intended to be edited automatically, not manually.This file holds settings provided through the ALTER SYSTEM command. This file is read whenever postgresql.conf is, and its settings take effect in the same way. Settings in postgresql.auto.conf override those in postgresql.conf.

The system view pg_file_settings can be helpful for pre-testing changes to the configuration files, or for diagnosing problems if a SIGHUP signal did not have the desired effects.

• ALTER SYSTEM command provides an SQL-accessible means of changing global defaults; it is functionally equivalent to editing postgresql.conf
• ALTER DATABASE command allows global settings to be overridden on a per-database basis
• ALTER ROLE command allows both global and per-database settings to be overridden with user-specific values.

System view pg_settings can be used to view and change session-local values.

• select * from pg_settings
• SHOW ALL
• SET configuration_parameter TO DEFAULT;
• UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter';

• 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), 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).

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.