PostgreSQL: Allow remote connections and access

By default PostgreSQL is not accessible from a remote location. That means if you want to access your database instance from your working machine you have to adjust the config files and make PostgreSQL accessible from the outside. Please note that the default values are there for a reason, by making them accessible from the outside you open your database to attack vectors. Think twice before blindly allowing remote access to your PG server.

Change listen_addresses in postgresql.conf

By default the paramter "listen_addresses" in the postgresql.conf file is commented out. If you want to allow remote access you have to change the listen address. Open this config file with an editor of your choice.

vi /etc/postgresql/11/main/postgresql.conf

Search for "listen_addresses" and change its value and make sure that line is not commented out.

listen_addresses='*'

Please note that by adding the asterisk you allow remote access from any IP or host. If you want to bind it to one or more IP addresses you could do something like the following.

listen_addresses='1.2.3.4 4.3.2.1'

Add desired host to authentication file

To allow access to PostgreSQL for a specific IP or host you need to change the pg_hba.conf file. HBA stands for Host Based Authenticaion and this file makes sure to only allow the specified hosts to access PostgreSQL.

If you simply want to allow access from any IP you can simply open the config file:

vi /etc/postgresql/11/main/pg_hba.conf

And add the following line to the very end of this file:

host	all		all		0.0.0.0/0		md5

The structure of this file is very simple. The first column can be "host" or "local", "host" matches connections made via TCP/IP, "local" would match connections done via unix-domain sockets.

The second column matches the database, with the value "all" we can access all databases. The third column is similar to the previous one, but instead of database it matches the user. The next column is the actual IP address, valid values are either a hostname, IP subnet or the value "all".

The last column is the auth method. There are several options, for example: trust, reject, md5 and password. "Trust" would open access to everything without providing a password, please NEVER use this option if you allow remote access. "Reject" is the opposite, it could be used to block certain hosts. The option "md5" requires a MD5 encrypted password, while the option "password" simply wants an unencrypted password.

Generally you can use multiple lines that work together, for example you could add a "reject" entry that blocks certain subnets, but later add an entry that would allow a single IP out of the rejected subnets.

Restart PostgreSQL

To make the changes available you need to restart PostgreSQL.

service postgresql restart

Source: postgresql.org/docs