Pg hba conf как редактировать

All about pg_hba.conf(authentication methods- Postgresql)

pg_hba.conf is the PostgreSQL access policy configuration file, which is located in the /var/lib/pgsql/10/data/ directory (PostgreSQL10) by default.

The configuration file has 5 parameters, namely: TYPE (host type), DATABASE (database name), USER (user name), ADDRESS (IP address and mask), METHOD (encryption method)

host all all 192.168.109.103/22 md5

host dbName user 192.168.109.106/22 trust

Modify the server-side pg_hba.conf file

Make the shell can connect to the postgres database secretly:

Modify the authentication file $PGDATA/pg_hba.conf, add the following lines, and reload to make the configuration take effect immediately.
host pankajconnect postgresql 192.168.8.103/32 trust

Reload to take effect: pg_ctl reload -D $PGDATA

Examples:
1. Allow local login to the database using PGAdmin3, database address localhost, user user1, database user1db:

host user1db user1 127.0.0.1/32 md5

2. Allow 10.1.1.0

10.1.1.255 network segments to log in to the database:

host all all 10.1.1.0/24 md5

3. Trust 192.168.1.10 to log in to the database:

host all all 192.168.1.10/32 trust
After pg_hba.conf is modified, use pg_ctl reload to re-read the pg_hba.conf file. If pg_ctl cannot find the database, use -D /…/pgsql/data/ to specify the database directory, or export PGDATA=/…/ pgsql/data/ imports environment variables.

1. trust

Allow connections unconditionally. This method allows any user who can connect to the PostgreSQL database server to log in as any PostgreSQL database user they desire without requiring a password or any other authentication.

2. reject

Unconditionally reject the connection. This helps to “filter out” specific hosts from a group. For example, a reject line can block a specific host connection, while the following line allows the remaining hosts in a specific network to connect.

3. md5

The client is required to provide a double MD5 encrypted password for authentication.

4. password

The client is required to provide an unencrypted password for authentication. Because passwords are sent over the network in clear text, we should not use this method on untrusted networks.

5. gss

Use GSSAPI to authenticate users. Only available for TCP/IP connections.

6. sspi

Use SSPI to authenticate users. Only available on Windows.

7. ident

Obtain the client’s operating system name by contacting the client’s ident server, and check whether it matches the requested database user name. Ident authentication can only be used on TCIP/IP connections. When this authentication method is specified for local connection, peer authentication will be used instead.

8. peer

Obtain the operating system user of the client from the operating system and check whether it matches the requested database user name. This is only available for local connections.

9. ldap

Use LDAP server for authentication.

10. radius

Authentication with RADIUS server.

11. cert

Use SSL client certificate authentication.

12. pam

Use the pluggable authentication module service (PAM) authentication provided by the operating system.

13. bsd

Use the BSD authentication service provided by the operating system for authentication.

Common Errors:
The server doesn’t grant access to the database: the server reports
FATAL: no pg_hba.conf entry for host “192.168.0.123”, user “postgres”, database “postgres” FATAL: no pg_hba.conf entry for host “192.168. 0.123”, user “postgres”, database “postgres”

For security, the PostgreSQL database will not listen to all connection requests except for the local. When the user accesses through JDBC, it will report some exceptions as follows:

org . postgresql . util .PSQLException: FATAL: no pg_hba . conf entry for host

To solve this problem, just in PostgreSQL find / data / installation directory database pg_hba . Conf , find “# IPv4 local connections:”

Add the IP of the machine requesting to connect under it

host all all 127.0.0.1/32 md5

pg_ctl start [-w] [-s] [-D datadir ] [-l filename ] [-o options ] [-p path ]
pg_ctl stop [-W] [-s] [-D datadir ] [-ms[mart ] | f[ast] | i[mmediate]]
pg_ctl restart [-w] [-s] [-D datadir ] [-ms[mart] | f[ast] | i[mmediate]] [-o options ]
pg_ctl reload [-s] [-D datadir ]
pg_ctl status [-D datadir ]
pg_ctl kill [ signal_name ] [ process_id ]

Configuring PostgreSQL user authentication

One of the first things you'll need to think about when working with a PostgreSQL database is how to connect and interact with the database instance. This requires coordination between the database client — the component you use to interact with the database, and the database server — the actual PostgreSQL instance that stores, organizes, and provides access to your data.

To manage this successfully, you need to know how to configure a PostgreSQL instance to allow the type of access and authentication methods you require. In this guide, we'll cover how to modify your database server's authentication mechanisms to match your environment's requirements.

In a companion guide, we cover how to connect to a PostgreSQL instance using a database client. Consider reading both guides for a more complete picture of how authentication works in PostgreSQL.

Understanding PostgreSQL's authentication file

If you want to modify the rules that dictate how users can authenticate to your PostgreSQL instances, you can do so by modifying your server's configuration.

The specific file you need to modify is called pg_hba.conf .

Note that you will have to restart your PostgreSQL instance for any new authentication configuration to take effect. Different operating systems will handle this in different ways. In most Linux distributions, you can type sudo systemctl restart postgresql . If you've installed PostgreSQL through brew , you can try typing brew services restart postgresql . An alternative that might work on a broader set of systems is pg_ctl restart .

Finding the pg_hba.conf file

To find the pg_hba.conf file on your server, you can look in the PostgreSQL configuration directory. The specific location will depend on the operating system and PostgreSQL version you are using.

If you don't know where the authentication configuration file is, but you do have access to the database, you can query PostgreSQL for the file location, as Craig Ringer demonstrates in this post.

If you are on the command line, you can type the following, which queries for the location of the pg_hba.conf file and tells PostgreSQL to print only the file location without formatting:

If you already have a PostgreSQL session open, you can simply type:

Once you have the location of the pg_hba.conf file, open it in your text editor to view the configuration and make changes:

By default, the file contains the current configuration as well as a number of helpful comments.

Understanding the pg_hba.conf file format

The pg_hba.conf file uses a table-like structure implemented in plain text. With blank lines and comments removed, a basic file might look something like this:

Let's take a look at the different fields mean and how the file's contents are interpreted.

How the pg_hba.conf file is structured and interpreted

Each line in the pg_hba.conf file describes a way for clients to authenticate to the system. The majority of each line describes match conditions used to compare with incoming connection requests. The final components specify an authentication method allowed and any options needed for authentication.

When PostgreSQL evaluates connection requests against the authentication rules, it does so in sequence, from top to bottom. If the configuration in a line matches the characteristics of the connection request, PostgreSQL will use the authentication information specified on the line to decide whether to authenticate the client.

If the client successfully authenticates, a connection will be established. If authentication is unsuccessful, the connection will be refused. PostgreSQL does not continue processing to see if other rules match the request. Because of this, the order of your rules is significant.

Within each line, fields are separated by white space — either spaces or tabs. Although it is usually visually helpful to format these fields into columns, PostgreSQL does not require this.

What each field in the pg_hba.conf file means

Now that you understand a bit about how the file is structured and interpreted, we can begin to talk about what each of the fields means.

The fields we'll cover are:

• Connection type
• Database
• Username
• Address
• Method of authentication
• Options for the authentication method

The first field in each record specifies the type of connection request to match. Only connections that use the specified connection will match each rule.

The connection type must be one of the following:

• local : Records with local match connections made over a local Unix domain socket file instead of over a network. Local connections are preferred when possible for security and performance reasons.
• host : Lines that begin with host match any connection request made over the network. This is a general catch-all for network connections. More granular matching is available with the following types.
• hostssl : The hostssl connection type matches any connections that are made over the network with TLS/SSL encryption. This is usually the best connection type to use when allowing external connections.
• hostnossl : The hostnossl type matches any network connection that is not secured by TLS/SSL.

Starting with PostgreSQL 12, support has been added for GSSAPI connections as well, introducing these additional options:

• hostgssenc : The hostgssenc connection type matches any network connection that uses GSSAPI encryption. This option only makes sense for those who already use GSSAPI ifor security.
• hostnogssenc : The hostnogssen type matches every network connection that doesn't use GSSAPI encryption.

The next field specifies the database that the request is attempting to access. The database specified in the connection request must satisfy the value found in this column for the line to match.

The values can be any of the following:

• all : A database value of all is a catch all value that matches any database requested. This is useful if you don't want the current match rule to evaluate the database value.
• sameuser : The sameuser value matches connections where the requested database and username are the same.
• samerole : The samerole database value will match a connection if the user specified is a member of a role with the same name as the requested database.
• replication : Using a value of replication will match any incoming connection used for database replication. Connections used for replication to not provide a database target, so this matches replication requests instead.
• [specific database name]: You can also provide one or more specific database names to match. These will only match connections if they request one of the listed databases. You can separate multiple database names with a comma or specify a file to read names from by preceding the filename with an @ symbol.

The next field is used to match against the user provided by the connection request. The connection's user value must satisfy the rule's user field to match the rule.

The user field can take these options:

• all : A value of all tells PostgreSQL that any value in the connection's user parameter satisfies this rule's user requirements.
• [specific user or group name]: The only other option for the user field is to provide a specific user, a list of users, or a group. Multiple users can be specified by separating the values with a comma. If a name begins with a + symbol, it is interpreted as a group name rather than a user name. In this case, the rule will match if the requested user is a member of the group the rule specifies. Again, you can tell PostgreSQL to read values from a file instead of providing them inline by instead providing a filename preceded by the @ symbol.

For all connection types that begin with host ( host , hostssl , and hostnossl , as well as hostgssenc and hostnogssenc in PostgreSQL 12 and later), an address field comes next. For local connections, this field is skipped.

The address field specifies the client machine's addresses or patterns to match against the connection's address. This means that the connection is evaluated according to where it is originating. The connection's origin must satisfy the rule's address value for the rule to match.

The address field can be filled out with any of these:

• all : An address value of all tells PostgreSQL that any client address will satisfy this condition.
• samehost : The value samehost is used to indicate that any networked connections originating from one of the server's own IP addresses should match.
• samenet : The samenet value indicates that any IP address from the server's network subnets will match.
• [CIDR IP address range]: You can also supply an IP address range using CIDR notation. This can specify a single IP address (using a /32 subnet for IPv4 addresses or a /128 subnet for IPv6 addresses) or a range of addresses by providing a more expansive CIDR mask. IP address ranges will only match client connections made from within the specified range using the IP protocol specified.
• [host name]: A host name can also be specified directly. In this case, the client's host name will be evaluated using a forward and reverse DNS query to ensure it resolves as expected. If the specified hostname starts with a dot, any host that resolves correctly on that domain will satisfy the requirements.

If a connection satisfies all of the previous match criteria, the given authentication method is then applied. This is the next field in each line.

The authentication method is the way that PostgreSQL decides whether to accept connections that match the rule. It can be set to any of the following choices:

• trust : A value of trust immediately accepts the connection without further requirements. This assumes that other external authentication methods are in place. It is not recommended in most cases.
• reject : A value of reject immediately rejects the connection. This is mainly used to filter out connections that match unwanted patterns.
• scram-sha-256 : The scram-sha-256 method will check the password provided by the user using SCRAM-SHA-256 authentication. If all of your clients support it, this is currently the most secure option for password authentication.
• md5 : The md5 method also checks user passwords. This method is less secure than scram-sha-256 but more widely supported. The current implementation will automatically use scram-sha-256 even if md5 is specified if the password is encrypted with SCRAM.
• password : The password method is the least secure password authentication method. It sends passwords in plain text and should not be used unless the connection uses TLS/SSL to encrypt the entire connection.
• gss : The gss method uses GSSAPI for authentication. This can be used for authentication regardless of whether GSSAPI encryption is used for the connection. This allows authenticating through Kerberos and similar software.
• sspi : The sspi method uses the Windows-only Security Support Provider Interface API to authenticate clients.
• ident : The ident method checks with a client's ident server for the user initiating the connection. Since this relies on the client's machine, it should only be used for trusted networks where the client machines are tightly controlled.
• peer : The peer authentication method is used for local connections. It asks the local operating system for the client's system username. It checks if the name matches the requested database name.
• ldap : The ldap method authenticates by using an LDAP server to validate usernames and passwords.
• radius : Select radius to use a RADIUS server to check username and password combinations.
• cert : The cert method authenticates clients using TLS/SSL client certificates. This is only available for TLS/SSL connections. The client certificate must be a valid, trusted certificate to be accepted.
• pam : The pam option will defer authentication to the operating system's PAM service.
• bsd : The bsd method uses the BSD Authentication service to validate usernames and passwords. This method is only available on OpenBSD hosts.

Some of the methods above are only applicable to certain types of connections or with additional pieces of infrastructure in place. For most deployments, reject , peer , and scram-sha-256 or md5 are sufficient to start with additional methods, like ldap available depending on your infrastructure.

After the authentication method, a final, optional column may be present to provide additional options for the authentication method. The use of this column is largely dependent on the type of authentication method selected.

For authentication methods that reference external servers, these options often specify the host and connection information so that PostgreSQL can successfully query the authentication service. Another option that is common to quite a few authentication methods is a map parameter that allows you to translate between system and PostgreSQL database usernames.

Each authentication method has its own set of valid options. Be sure to check the applicable options on the page for each method in the PostgreSQL documentation.

Configuring common authentication policies

We've introduced some of the main authentication options, but how do you use these to implement reasonable policies? In this section, we'll cover how to configure some of the most common authentication policies.

Allow local users to connect to matching databases

It is common to configure PostgreSQL to allow users on the same machine machine to authenticate to the same PostgreSQL username. For example, using peer authentication, an operating system user named john would be able to log in automatically without a password if PostgreSQL also has a username named john .

This will work for any local connections made using the PostgreSQL socket file. If you specify any network address, even if it is the 127.0.0.1 local loopback device, the connection will not use the socket and will not match the peer authentication line. Connections to localhost , however, will use the socket file and will match these lines.

To allow all PostgreSQL users to authenticate from a matching operating system user, add a line that matches the local connection type, allows all databases and usernames, and uses peer authentication:

If you want to limit this so that only the john and sue PostgreSQL users can authenticate in this way, limit the scope of the USER column:

If you need allow an operating system user named sue to authenticate to a database user named susan , you can specify a map option at the end of the line. Choose a map name to identify this mapping:

Then, you can map your users by opening the pg_ident.conf file in the same directory:

Create the map you need by adding a line in this file specifying your chosen map name, the operating system username, and the PostgreSQL username, separated by spaces:

Now, the sue operating system will be able to authenticate to the susan PostgreSQL user with peer authentication as if they matched.

Allow network connections from the same machine using passwords

To authenticate network connections from the PostgreSQL server's machine (non-socket connections) using passwords, you need to match a host connection type instead of local . You can then limit the acceptable addresses to the local loopback devices and allow users to authenticate using md5 or scram-sha-256 .

For instance, if a user on the machine that PostgreSQL is hosted on tries to connect by specifying 127.0.0.1 as the host, PostgreSQL can perform password authentication.

To set this up, we need to use the host connection type. Next, we need to specify the range of acceptable addresses. Since this rule should only match local connections, we'll specify the local loopback device. We will have to add two separate lines to match the IPv4 and IPv6 loopback devices.

Afterwards, you can specify the password scheme you want to use for authentication. The scram-sha-256 method is more secure, but the md5 method is more widely supported.

The finished authentication lines will look something like this:

You can limit the databases or users that are allowed to authenticate using this method by changing the appropriate columns from all to a comma-separated list of specific entities.

Allow automatic maintenance

An assortment of automated maintenance tasks are performed on a regular basis. To ensure that these operations can authenticate and run as expected, you need to ensure that a administrative account is capable of authenticating non-interactively.

By default, the postgres account is configured for this role using peer authentication. This line is very likely already present in your pg_hba.conf file:

Ensure that this or a similar line is present in your file, especially if you are changing a lot of other authentication methods.

Allow connections used for replication

Replication is a special processes that copies data from one database to another, usually on a frequent basis. Unlike other types of connections, replication connections do not specify a specific database they want to connect to.

The replication keyword in the database column is used to match these replication connections. Any user with the replication privilege is able to establish a replication connection.

To allow for all local replication connections, in a way that mirrors our previous values for regular connections ( peer for connections over the Unix socket and md5 for connections over the local network), we can add the following lines:

To allow replication from additional locations, you can add additional addresses. For example, to allow replication from any machines on the local 192.0.2.0/24 network, you can add a line that looks like this:

This will allow any replication connections coming from machines within that network to authenticate using md5 -encrypted passwords.

Allow connections from local network using passwords

Above, we demonstrated how to configure password authentication for local replication connections. This can be generalized to allow password authentication for any local network connections.

To allow md5 password authentication for any connections coming from the local 192.0.2.0/24 network, you can add a line like this:

This will allow all hosts within the 192.0.2.0/24 network to authenticate to PostgreSQL over the network using md5 -encrypted passwords.

Allow remote connections using SSL and passwords

To allow connections from outside of a trusted network, you should always tunnel the connection through secure encryption, like TLS/SSL. If you need to allow these connections, you should match against the hostssl connection type.

For example, to allow password authentication from anywhere that can connect to the database server, but only if TLS/SSL is used, you can add a line like this to your authentication file:

This will allow any external connections using TLS/SSL to authenticate using md5 -encrypted passwords. You can easily limit the access by specifying more restrictive addresses.

If you use the hostssl connection type, you will have to configure SSL for your PostgreSQL instance. You will have to generate or otherwise obtain an SSL certificate, an SSL key, and an SSL root certificate and then modify the postgresql.conf configuration file, as specified in the PostgreSQL documentation on configuring SSL.

Allow remote connections using SSL and SSL client certificates

If you are already forcing SSL for external connections, you may want to consider using SSL client certificates for authentication instead of passwords. This will allow clients to present their client SSL certificate. The server checks that it is valid and signed by a trusted certificate authority. If so, it will allow authentication according to the rules provided.

To set up SSL client authentication, we can use a similar line to the one we used before:

With this configuration, the server will not prompt users for passwords, but will instead require a valid SSL certificate. The certificate's common name (CN) field must match the database user that is being requested, or else be configured with a map file.

For instance, for a certificate with a CN of katherine to authenticate to a PostgreSQL user named kate , you'd need to specify a map file in the pg_hba.conf file:

Afterwards, you'd edit the pg_ident.conf file to explicitly map those two users together:

You can learn how to create and configure client certificates in PostgreSQL's documentation on TLS/SSL client certificates.

In this guide, we covered PostgreSQL authentication from the server side. We demonstrated how to modify a PostgreSQL instance's configuration to change how clients are allowed to authenticate. After discussing the different options available in the authentication file, we covered how to implement some common authentication strategies using what we learned earlier.

Knowing how to configure authentication, combined with an understanding of how to connect with a PostgreSQL client, allow you to grant access to legitimate clients while guarding against unwanted connections. This configuration is an important part of securing your database instances and preventing disruptive login problems that might hinder your operations.

Configuring PostgreSQL for the First Time

We have discussed the PostgreSQL installation steps here .

Next comes the initial configuration part. We will separate the initial configuration into two steps:

1. Enable Remote Connections
2. Set Server-Level Options

Before we actually proceed further, let’s briefly discuss about the two most important PostgreSQL configuration file – pg_hba.conf and postgresql.conf , which we are going to modify soon as part of the initial configuration.

pg_hba.conf file:

The client authentication methodology that PostgreSQL follows is called ‘host based authentication’ and is generally controlled by the pg_hba.conf file. The file consists of a number of entries for different hosts and its associated permissions related to database, users authentication methods etc.

Whenever PostgreSQL receives a connection request, it verifies the machine(host) from which the application is requesting a connection and if it has rights to connect to the specified database. All of these verifications are being done with the help of the pg_hba.conf file. Any changes in pg_hba.conf file are dynamic and don’t require a re-start of PostgreSQL.

You can find the PostgreSQL documentation about pg_hba.conf file here.

postgresql.conf file:

Most global configuration settings for PostgreSQL are stored in postgresql.conf file, which is created automatically when you install PostgreSQL.

Configuration parameters in the postgresql.conf file specifies server behavior with regards to authentication, encryption, auditing, memory management, query performance and other behaviors.

Changing some of the parameters in postgresql.conf file may require a reload or restart of the server for the new parameter value to take effect. You will generally notice a comment beside the parameter stating that changing that parameter requires a reload or restart.

You can also find out the list of the parameters that require a server restart by executing the below query.

For those, who are looking for the PostgreSQL documentation about these parameter settings, can take a look at the PostgreSQL documentation here.

Next comes the below question-

How to locate these configuration files?

Traditionally the configuration files are stored inside the database cluster’s data directory. The below command will tell you the location of the data directory.

Apart from this, there are a number of ways to find out the location of the configuration files directly.

You can simply run the below query on psql prompt which will give you the file location as output.

You may also use the below command as well to find out the location:

1. Enable Remote Connections:

PostgreSQL is generally configured to allow the root user to login as the PostgreSQL superuser postgres locally. With this, you can create database, roles etc. as usual.

However, if you want do this using some GUI tool over the network from some remote server, it will simply not work. By default, your server will not accept remote connections and you will get error something like below:

You have to explicitly allow remote connections to PostgreSQL by editing the pg_hba.conf and postgresql.conf file and making specific entry to the file.

Let’s jump into modifying the pg_hba.conf and postgresql.conf files.

NOTE: It is always recommended to keep a backup copy of the configuration files before actually modifying them. Just in case, if need arises, it will help you to return to the old \ default settings.

You can simply execute the below command which will create a backup file named postgresql.conf.bak inside the same directory where the actual file is located.

• Edit postgresql.conf to listen on any address:

The file looks something like below:

• Search for listen_addresses field inside the file, uncomment it, and set it to ‘ * ‘. Then save and quit out of the postgresql.conf file.

Please don’t forget to uncomment the line by removing the pound sign ‘ # ‘ preceded by the parameter that you’ve just changed. People generally change the parameter values but tend to forget to uncomment the lines and the change doesn’t come into effect.

• Edit pg_hba.conf file:

Initially the pg_hba.conf file looks like below:

• Append the below mentioned line to the bottom of the file, Save and exit. The 0.0.0.0 IP address acts as a “wildcard” for all IPv4 addresses. You may replace this with the specific IP address, if PostgreSQL is running on a server with a static IP address.

• Restart the PostgreSQL service:

You can connect to the server remotely at this point, without making the below recommended changes. However, there are a few the initial settings that people generally change before getting started with the new installation. For example, if you want PostgreSQL to listen to some different port other than the default 5432, you have to change the server level settings accordingly.

1. Set Server-Level Options:

• Edit the postgresql.conf file:

The file will look something like below:

• If you want PostgreSQL to listen to some different port, then find the port setting under CONNECTIONS AND AUTHENTICATION section, uncomment it, and change the value you want to set accordingly. For this demonstration, let’s change it to 1433.

• Next look at the max_connection settings. You’ll find the max_connection settings set to a value, typically 100. It is recommended not to set this parameter too high as each connection uses a small amount of shared memory and limited shared memory will not be able to allow very high number of connections. Just for this demonstration, let’s change max_connections to 500.

• Similarly, you may want to change the value of the shared_buffers. This parameter defines the size of the PostgreSQL shared buffer pool which governs the amount of memory to be used by PostgreSQL for caching data. The default value is 128 MB which can be increased as per your requirement, based on the available server resource and expected load. Find the shared_buffers setting under Memory and change the value to 256MB for this demonstration purpose and Save and exit the file.

• Perform a restart of the PostgreSQL service:

Test the changes:

Let’s test the change that we have made to verify is everything is working as expected.

But before that, let’s create a test database ‘dbaguides’ and a test user ‘dbaguidesapp’ which we will use while connecting to the PostgreSQL server remotely for testing.

Let’s connect to the PostgreSQL server using an external client of your choice. You can download and use pgAdmin tool as well, found at https://www.pgadmin.org. Once connected, run the below query to see if the changes we have made is getting reflected correctly.

N.B: Please use the public IP address of your server or server name in place of the below <Server IP> field.

We can now see that the the PostgreSQL server is reachable remotely and the settings are also reflected correctly.

These are the bare minimal changes that are required for you to get going with the server post installation.

There are a way lot more optimization possible by tuning these configuration parameters according to the business requirement and server load and it requires broader discussion to cover all those points. I’ll save them up for the future posts.

Pg hba conf как редактировать

Client authentication is controlled by a configuration file, which traditionally is named pg_hba.conf and is stored in the database cluster’s data directory. (HBA stands for host-based authentication.) A default pg_hba.conf file is installed when the data directory is initialized by initdb . It is possible to place the authentication configuration file elsewhere, however; see the hba_file configuration parameter.

The general format of the pg_hba.conf file is a set of records, one per line. Blank lines are ignored, as is any text after the # comment character. A record can be continued onto the next line by ending the line with a backslash. (Backslashes are not special except at the end of a line.) A record is made up of a number of fields which are separated by spaces and/or tabs. Fields can contain white space if the field value is double-quoted. Quoting one of the keywords in a database, user, or address field (e.g., all or replication ) makes the word lose its special meaning, and just match a database, user, or host with that name. Backslash line continuation applies even within quoted text or comments.

Each record specifies a connection type, a client IP address range (if relevant for the connection type), a database name, a user name, and the authentication method to be used for connections matching these parameters. The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no “ fall-through ” or “ backup ” : if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

A record can have several formats:

The meaning of the fields is as follows:

This record matches connection attempts using Unix-domain sockets. Without a record of this type, Unix-domain socket connections are disallowed.

Remote TCP/IP connections will not be possible unless the server is started with an appropriate value for the listen_addresses configuration parameter, since the default behavior is to listen for TCP/IP connections only on the local loopback address localhost .

This record matches connection attempts made using TCP/IP, but only when the connection is made with SSL encryption.

To make use of this option the server must be built with SSL support. Furthermore, SSL must be enabled by setting the ssl configuration parameter (see Section 19.9 for more information). Otherwise, the hostssl record is ignored except for logging a warning that it cannot match any connections.

This record type has the opposite behavior of hostssl ; it only matches connection attempts made over TCP/IP that do not use SSL .

This record matches connection attempts made using TCP/IP, but only when the connection is made with GSSAPI encryption.

This record type has the opposite behavior of hostgssenc ; it only matches connection attempts made over TCP/IP that do not use GSSAPI encryption.

Specifies which database name(s) this record matches. The value all specifies that it matches all databases. The value sameuser specifies that the record matches if the requested database has the same name as the requested user. The value samerole specifies that the requested user must be a member of the role with the same name as the requested database. ( samegroup is an obsolete but still accepted spelling of samerole .) Superusers are not considered to be members of a role for the purposes of samerole unless they are explicitly members of the role, directly or indirectly, and not just by virtue of being a superuser. The value replication specifies that the record matches if a physical replication connection is requested, however, it doesn’t match with logical replication connections. Note that physical replication connections do not specify any particular database whereas logical replication connections do specify it. Otherwise, this is the name of a specific PostgreSQL database. Multiple database names can be supplied by separating them with commas. A separate file containing database names can be specified by preceding the file name with @ .

Specifies which database user name(s) this record matches. The value all specifies that it matches all users. Otherwise, this is either the name of a specific database user, or a group name preceded by + . (Recall that there is no real distinction between users and groups in PostgreSQL ; a + mark really means “ match any of the roles that are directly or indirectly members of this role ” , while a name without a + mark matches only that specific role.) For this purpose, a superuser is only considered to be a member of a role if they are explicitly a member of the role, directly or indirectly, and not just by virtue of being a superuser. Multiple user names can be supplied by separating them with commas. A separate file containing user names can be specified by preceding the file name with @ .

Specifies the client machine address(es) that this record matches. This field can contain either a host name, an IP address range, or one of the special key words mentioned below.

Typical examples of an IPv4 address range specified this way are 172.20.143.89/32 for a single host, or 172.20.143.0/24 for a small network, or 10.6.0.0/16 for a larger one. An IPv6 address range might look like ::1/128 for a single host (in this case the IPv6 loopback address) or fe80::7a31:c1ff:0000:0000/96 for a small network. 0.0.0.0/0 represents all IPv4 addresses, and ::0/0 represents all IPv6 addresses. To specify a single host, use a mask length of 32 for IPv4 or 128 for IPv6. In a network address, do not omit trailing zeroes.

An entry given in IPv4 format will match only IPv4 connections, and an entry given in IPv6 format will match only IPv6 connections, even if the represented address is in the IPv4-in-IPv6 range. Note that entries in IPv6 format will be rejected if the system’s C library does not have support for IPv6 addresses.

You can also write all to match any IP address, samehost to match any of the server’s own IP addresses, or samenet to match any address in any subnet that the server is directly connected to.

If a host name is specified (anything that is not an IP address range or a special key word is treated as a host name), that name is compared with the result of a reverse name resolution of the client’s IP address (e.g., reverse DNS lookup, if DNS is used). Host name comparisons are case insensitive. If there is a match, then a forward name resolution (e.g., forward DNS lookup) is performed on the host name to check whether any of the addresses it resolves to are equal to the client’s IP address. If both directions match, then the entry is considered to match. (The host name that is used in pg_hba.conf should be the one that address-to-name resolution of the client’s IP address returns, otherwise the line won’t be matched. Some host name databases allow associating an IP address with multiple host names, but the operating system will only return one host name when asked to resolve an IP address.)

A host name specification that starts with a dot ( . ) matches a suffix of the actual host name. So .example.com would match foo.example.com (but not just example.com ).

When host names are specified in pg_hba.conf , you should make sure that name resolution is reasonably fast. It can be of advantage to set up a local name resolution cache such as nscd . Also, you may wish to enable the configuration parameter log_hostname to see the client’s host name instead of the IP address in the log.

These fields do not apply to local records.

Users sometimes wonder why host names are handled in this seemingly complicated way, with two name resolutions including a reverse lookup of the client’s IP address. This complicates use of the feature in case the client’s reverse DNS entry is not set up or yields some undesirable host name. It is done primarily for efficiency: this way, a connection attempt requires at most two resolver lookups, one reverse and one forward. If there is a resolver problem with some address, it becomes only that client’s problem. A hypothetical alternative implementation that only did forward lookups would have to resolve every host name mentioned in pg_hba.conf during every connection attempt. That could be quite slow if many names are listed. And if there is a resolver problem with one of the host names, it becomes everyone’s problem.

Also, a reverse lookup is necessary to implement the suffix matching feature, because the actual client host name needs to be known in order to match it against the pattern.

Note that this behavior is consistent with other popular implementations of host name-based access control, such as the Apache HTTP Server and TCP Wrappers.

These two fields can be used as an alternative to the IP-address / mask-length notation. Instead of specifying the mask length, the actual mask is specified in a separate column. For example, 255.0.0.0 represents an IPv4 CIDR mask length of 8, and 255.255.255.255 represents a CIDR mask length of 32.

These fields do not apply to local records.

Specifies the authentication method to use when a connection matches this record. The possible choices are summarized here; details are in Section 21.3. All the options are lower case and treated case sensitively, so even acronyms like ldap must be specified as lower case.

Allow the connection unconditionally. This method allows anyone that can connect to the PostgreSQL database server to login as any PostgreSQL user they wish, without the need for a password or any other authentication. See Section 21.4 for details.

Reject the connection unconditionally. This is useful for “ filtering out ” certain hosts from a group, for example a reject line could block a specific host from connecting, while a later line allows the remaining hosts in a specific network to connect.

Perform SCRAM-SHA-256 authentication to verify the user’s password. See Section 21.5 for details.

Perform SCRAM-SHA-256 or MD5 authentication to verify the user’s password. See Section 21.5 for details.

Require the client to supply an unencrypted password for authentication. Since the password is sent in clear text over the network, this should not be used on untrusted networks. See Section 21.5 for details.

Use GSSAPI to authenticate the user. This is only available for TCP/IP connections. See Section 21.6 for details. It can be used in conjunction with GSSAPI encryption.

Use SSPI to authenticate the user. This is only available on Windows. See Section 21.7 for details.

Obtain the operating system user name of the client by contacting the ident server on the client and check if it matches the requested database user name. Ident authentication can only be used on TCP/IP connections. When specified for local connections, peer authentication will be used instead. See Section 21.8 for details.

Obtain the client’s operating system user name from the operating system and check if it matches the requested database user name. This is only available for local connections. See Section 21.9 for details.

Authenticate using an LDAP server. See Section 21.10 for details.

Authenticate using a RADIUS server. See Section 21.11 for details.

Authenticate using SSL client certificates. See Section 21.12 for details.

Authenticate using the Pluggable Authentication Modules (PAM) service provided by the operating system. See Section 21.13 for details.

Authenticate using the BSD Authentication service provided by the operating system. See Section 21.14 for details.

After the auth-method field, there can be field(s) of the form name = value that specify options for the authentication method. Details about which options are available for which authentication methods appear below.

In addition to the method-specific options listed below, there is a method-independent authentication option clientcert , which can be specified in any hostssl record. This option can be set to verify-ca or verify-full . Both options require the client to present a valid (trusted) SSL certificate, while verify-full additionally enforces that the cn (Common Name) in the certificate matches the username or an applicable mapping. This behavior is similar to the cert authentication method (see Section 21.12) but enables pairing the verification of client certificates with any authentication method that supports hostssl entries.

On any record using client certificate authentication (i.e. one using the cert authentication method or one using the clientcert option), you can specify which part of the client certificate credentials to match using the clientname option. This option can have one of two values. If you specify clientname=CN , which is the default, the username is matched against the certificate’s Common Name (CN) . If instead you specify clientname=DN the username is matched against the entire Distinguished Name (DN) of the certificate. This option is probably best used in conjunction with a username map. The comparison is done with the DN in RFC 2253 format. To see the DN of a client certificate in this format, do

Care needs to be taken when using this option, especially when using regular expression matching against the DN .

Files included by @ constructs are read as lists of names, which can be separated by either whitespace or commas. Comments are introduced by # , just as in pg_hba.conf , and nested @ constructs are allowed. Unless the file name following @ is an absolute path, it is taken to be relative to the directory containing the referencing file.

Since the pg_hba.conf records are examined sequentially for each connection attempt, the order of the records is significant. Typically, earlier records will have tight connection match parameters and weaker authentication methods, while later records will have looser match parameters and stronger authentication methods. For example, one might wish to use trust authentication for local TCP/IP connections but require a password for remote TCP/IP connections. In this case a record specifying trust authentication for connections from 127.0.0.1 would appear before a record specifying password authentication for a wider range of allowed client IP addresses.

The pg_hba.conf file is read on start-up and when the main server process receives a SIGHUP signal. If you edit the file on an active system, you will need to signal the postmaster (using pg_ctl reload , calling the SQL function pg_reload_conf() , or using kill -HUP ) to make it re-read the file.

The preceding statement is not true on Microsoft Windows: there, any changes in the pg_hba.conf file are immediately applied by subsequent new connections.

The system view pg_hba_file_rules can be helpful for pre-testing changes to the pg_hba.conf file, or for diagnosing problems if loading of the file did not have the desired effects. Rows in the view with non-null error fields indicate problems in the corresponding lines of the file.

To connect to a particular database, a user must not only pass the pg_hba.conf checks, but must have the CONNECT privilege for the database. If you wish to restrict which users can connect to which databases, it’s usually easier to control this by granting/revoking CONNECT privilege than to put the rules in pg_hba.conf entries.

Some examples of pg_hba.conf entries are shown in Example 21.1. See the next section for details on the different authentication methods.

Example 21.1. Example pg_hba.conf Entries

Prev	Up	Next
Chapter 21. Client Authentication	Home	21.2. User Name Maps

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.