Enabling access for network/remote users

PostgreSQL comes in a variety of distributions. In many of these, you will note that remote access is initially disabled as a security measure.

How to do it…

By default, PostgreSQL gives access to clients who connect using Unix sockets, provided that the database user is the same as the system's username. Here, we'll show you how to enable other connections.

In this recipe, we mention configuration files, which can be located as shown in the Finding the current configuration settings recipe in Chapter 3, Configuration.

The steps are as follows:

1. Add or edit this line in your postgresql.conf file:

listen_addresses = '*'

2. Add the following line as the first line of pg_hba.conf to allow access to all databases for all users with an encrypted password:

# TYPE   DATABASE   USER        CIDR-ADDRESS   METHOD 
host      all        all          0.0.0.0/0       md5

3. After changing listen_addresses, we restart the PostgreSQL server, as explained in the Updating the parameter file recipe in Chapter 3, Configuration

This recipe assumes that postgresql.conf does not include any other configuration files, which is the case in a default installation. If changing listen_addresses in postgresql.conf does not seem to work, perhaps that setting is overridden by another configuration file. Check out the Updating the parameter file recipe in Chapter 3, Configuration, for more details.

How it works…

The listen_addresses parameter specifies which IP addresses to listen to. This allows you to flexibly enable and disable listening on interfaces of multiple network cards (NICs) or virtual networks on the same system. In most cases, we want to accept connections on all NICs, so we use *, meaning all IP addresses.

The pg_hba.conf file contains a set of host-based authentication rules. Each rule is considered in sequence, until one rule fires or the attempt is specifically rejected with a reject method.

The preceding rule means that a remote connection that specifies any user or database on any IP address will be asked to authenticate using an MD5-encrypted password. The following are the parameters required for MD5-encrypted passwords:

• Type: For this, host means a remote connection.
• Database: For this, all means for all databases. Other names match exactly, except when prefixed with a plus (+) symbol, in which case we mean a group role rather than a single user. You can also specify a comma-separated list of users, or use the @ symbol to include a file with a list of users. You can even specify sameuser, so that the rule matches when you specify the same name for the user and database.
• User: For this, all means for all users. Other names match exactly, except when prefixed with a plus (+) symbol, in which case we mean a group role rather than a single user. You can also specify a comma-separated list of users, or use the @ symbol to include a file with a list of users.
• CIDR-ADDRESS: This consists of two parts: an IP address and a subnet mask. The subnet mask is specified as the number of leading bits of the IP address that make up the mask. Thus, /0 means 0 bits of the IP address, so that all IP addresses will be matched. For example, 192.168.0.0/24 would mean matching of the first 24 bits, so any IP address of the form 192.168.0.x would match. You can also use samenet or samehost.
• Method: For this, md5 means that PostgreSQL will ask the client to provide a password encrypted with MD5. Another common setting is trust, which effectively means no authentication. Other authentication methods include GSSAPI, SSPI, LDAP, RADIUS, and PAM. PostgreSQL connections can also be made using SSL, in which case client SSL certificates provide authentication. See the Using SSL certificates to authenticate the client recipe in Chapter 6, Security, for more details.

Don't use the password setting, as this sends the password in plain text. This is not a real security issue if your connection is encrypted with SSL, and there are normally no downsides with MD5 anyway, and you have extra security for non-SSL connections.

There's more…

In earlier versions of PostgreSQL, access through the network was enabled by adding the -i command-line switch when you started the server. This is still a valid option, but now it means the following:

listen_addresses = '*'

So, if you're reading some notes about how to set things up and this is mentioned, be warned that those notes are probably long out of date. They are not necessarily wrong, but it's worth looking further to see whether anything else has changed.

See also

Look at installer and/or operating system-specific documentation to find the standard location of the files.