PostgreSQL comes in a variety of distributions. In many of these, you will note that remote access is initially disabled as a security measure.
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.
Note
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.
- Add or edit this line in your
postgresql.conffile:
listen_addresses = '*'- Add the following line as the first line of
pg_hba.confto 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
- After changing
listen_addresses, we restart the PostgreSQL server, as explained in the Updating the parameter file recipe in Chapter 3, Configuration
Note
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.
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,
hostmeans a remote connection. - Database: For this,
allmeans 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 specifysameuser, so that the rule matches when you specify the same name for the user and database. - User: For this,
allmeans 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,
/0means 0 bits of the IP address, so that all IP addresses will be matched. For example,192.168.0.0/24would mean matching of the first 24 bits, so any IP address of the form192.168.0.xwould match. You can also usesamenetorsamehost. - Method: For this,
md5means that PostgreSQL will ask the client to provide a password encrypted with MD5. Another common setting istrust, 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.
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.