Allow external access in PostgreSQL • Andrey Nering

By default, PostgreSQL only allow connections from the same machine the server is installed (localhost). The reason for this is security: if all connections were allowed, anyone connected in the same network (like wi-fi) would have access to your databases (if they know the password, of course).

But allowing external access is necessary when you are building applications that will access any database, or for debugging reasons. To do so, follow the following steps:

Find pg_hba.conf and postgresql.conf files and backup them

On Windows, you will probably find them at C:\Program Files\PostgreSQL\9.4\data. On Ubuntu, search for /etc/postgresql/9.3/main. If you can’t find them, a quick search on Google should help. Backup both files.

Edit the files

postgresql.conf

Open the postgresql.conf file and search for the line containing:

# listen_addresses = 'localhost'

Uncomment the line and change localhost to *:

listen_addresses = '*'

pg_hba.conf

Open the pg_hba.conf file and add the following to the end of the file:

host all all samenet md5

Restart the PostgreSQL server service

On Ubuntu, just run:

sudo service postgresql restart

On Windows, open “services” from the control panel, search for the PostgreSQL service, and restart it.

Make sure the user you intent to use have a password

When accessing a local PostgreSQL instance in Linux, a password usually isn’t necessary: you can login with you operating sistem user account. But since we choose md5 as login method, you will need to have a user with a password to remotely access the database. You can either crete a new user, or create a password for an existing user:

CREATE USER myuser WITH PASSWORD 'mypassword';
-- or
ALTER USER myuser WITH PASSWORD 'mypassword';

For advanced options, take a look on CREATE USER and ALTER USER documentations.

To allow to user to login, run the following command:

ALTER USER myuser WITH LOGIN;

For more information about permissions in PostgreSQL see this article.

Test the connection

The easier way to test the connection is throught PgAdmin. Just click on “Add a connection to a server” button, type the IP, user and password, and try to connect.